Read "special" characters (é,°) from MySQL database

Hi guys,

  My database contains some special characters like "é" or "°" and when I'm reading my resultset (from a select query) these characters are not "translated" correctly when I'm using resultset.getString("name_of_column").  How can I get them right?
teagishAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimYatesCommented:
do you have some example code?

and results?  and expected results?
0
GumBCommented:
You could try getCharacterStream() or getAsciiStream() (treat it as a CLOB), which seems to handle special character conversions better than getString().  However, I put this up in the spirit of collaboration and with the hope that someone else has a better (simpler) solution.

However, this is something I did in the past when I ran into this problem and it produced good results.
0
teagishAuthor Commented:
Here is the function :

  private boolean set_titres(int id_table_choisie)
  {
    java.sql.ResultSet resultat;
    String titre_graphique = null,
        nom[] = new String[2];

    for(int i=1;i>=0;i--)
    {
      try
      {
        if ( (resultat = requete("SELECT * FROM tinfo_on_tables WHERE id=" + id_table_choisie + " AND nom_colonne='" + colonne[i] + "'")) == null)
        {
          return false;
        }
        else
        {
          if (!resultat.next())
          {
            return false;
          }
          else
          {
            <b>nom[i] = resultat.getString("nom_tableau");</b>
            System.out.println(nom[i]);
            if(i==1)
            {
              titre_graphique = "Valeur "+resultat.getString("article")+" "+nom[i]+" en fonction ";
            }
            if(i==0)
            {
              titre_graphique += resultat.getString("article")+" "+nom[i];
            }
            if (resultat.getString("unites") != null)
            {
              nom[i] += " (" + resultat.getString("unites") + ")";
            }
          }
        }
      }
      catch (java.sql.SQLException e)
      {        
        return false;
      }
    }
    //here is some instantiation of a private variable
    toutes_donnees.set_titre_axe_x(nom[0]);
    toutes_donnees.set_titre_axe_y(nom[1]);
    toutes_donnees.set_titre_graphique(titre_graphique);

    return true;
  }

I get "d?formation" from System.out.println(nom[i]) when it is "déformation" in the database.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

teagishAuthor Commented:
private java.sql.ResultSet requete(String nom_commande)
  {
    java.sql.Statement rapport;
    java.sql.ResultSet resultat;

    try
    {
      rapport = conn_mysql.createStatement();
    }
    catch(java.sql.SQLException e)
    {
      //sortie.append("Erreur lors de la création d'un Statement : \""+e.getMessage()+"\" code n° "+e.getErrorCode()+"\n");
      return null;
    }

    try
    {
      resultat = rapport.executeQuery(nom_commande);
    }
    catch(java.sql.SQLException e)
    {
      //sortie.append("Erreur lors de la requête \""+nom_commande+"\" à la base de données : \""+e.getMessage()+"\" code n° "+e.getErrorCode()+"\n");
      return null; //DEBUGRESTE -> tenir compte du null
    }


    return resultat;
  }
0
teagishAuthor Commented:
I forgot to paste the requete method:

  private java.sql.Connection conn_mysql;

  private java.sql.ResultSet requete(String nom_commande)
  {
    java.sql.Statement rapport;
    java.sql.ResultSet resultat;

    try
    {
      rapport = conn_mysql.createStatement();
      resultat = rapport.executeQuery(nom_commande);
    }
    catch(java.sql.SQLException e)
    {
      return null;
    }

    return resultat;
  }
0
teagishAuthor Commented:
I forgot to paste the requete method:

  private java.sql.Connection conn_mysql;

  private java.sql.ResultSet requete(String nom_commande)
  {
    java.sql.Statement rapport;
    java.sql.ResultSet resultat;

    try
    {
      rapport = conn_mysql.createStatement();
      resultat = rapport.executeQuery(nom_commande);
    }
    catch(java.sql.SQLException e)
    {
      return null;
    }

    return resultat;
  }
0
CEHJCommented:
This seems to me like a problem with your driver. You could of course do a character encoding translation by manipulating bytes/string/streams, but that would not be tackling the problem at source. What encoding is used in the database?
0
ebbesenCommented:
You may have to reconfigure your system to view the special characters on your server console.

I recommend trying to re-insert the special characters you pull from the database that appear as ?s in the console back into the database.  This will help you to determine where the corruption is occurring, if it in fact is.

Please see my post on http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_20538200.html for information about your NLS_LANG setting in the registry.  Modifying this may allow your console to display non-standard ASCII characters -- it did for me on OC4J.

Let me know if this helps.
0
GumBCommented:
A few more things to try here...

A. Use a prepared statement, which handles special characters for you (although this might be limited to single quotes etc.) ...but it's worth a try.


B. Play around with this Statement method:

Statement stmt = conn.createStatement();
stmt.setEscapeProcessing(true);
stmt.executeQuery(query);


C. Try this (if your driver and db support it):

If your database supports it, and you define the field as expecting utf-16 chars, you can try getting it back by something like:
byte buff[] = rs.getBytes(index);
String s = new String( buff.toByteArray(), "utf-16");

If these techniques don't work, I will be looking forward to seeing the solution from someone else as well.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CEHJCommented:
>>I get "d?formation" from System.out.println(nom[i]) when it is "diformation" in the database.

I missed this earlier. Are you basingwhat you say is the problem by what you see printed out on the console? If so, consoles don't normally support 'exotic' characters. Try showing the result in a Java component such as a JTextArea and see what you get.
0
teagishAuthor Commented:
I've test my program drawing my text using Graphics2D and i'm able to print the letter '&eacute;' but the values given by the method getString("nom_tableau") shows a little rectangle instead of that letter.
0
teagishAuthor Commented:
How do I do to see / change the value of NLS_LANG??  I looked in the registry and it wasn't there...
0
CEHJCommented:
>>I've test my program drawing my text using Graphics2D and i'm able to print the letter '&eacute;' but the values given by the method getString("nom_tableau") shows a little rectangle instead of that letter.
>>

In the first part of the above, you must of course use a Font that can display the characters you want. In the second part, i'm not clear what you're actually doing.
0
teagishAuthor Commented:
My program is drawing a graphic using input from a MySQL database.
My font can draw '&eacute;' because i tried it manually.  But when i take the String that i took from the database it shows a rectangle instead of a '&eacute;'.  And, when i'm accessing the data from DOS using the mysql program I can see the '&eacute;' in my values.
0
CEHJCommented:
>>But when i take the String that i took from the database it shows a rectangle instead of a '&eacute;'.  


I take it that it's the String in the code below?

>><b>nom[i] = resultat.getString("nom_tableau");</b>

Can you print out the following

char[] chars = resultat.getString("nom_tableau").toCharArray();
for(int i = 0;i < chars.length ;i++) {
  System.out.println("0x" + Integer.toHexString(chars[i]));
}

0
teagishAuthor Commented:
it gives me :
"0x64, 0x82, 0x66, 0x6f, 0x72, 0x6d, 0x61, 0x74, 0x69, 0x6f, 0x6e" and the unicode letter i'm trying to do (supposed to be the second) is E9
0
teagishAuthor Commented:
Is it possible that the characters returned are in ASCII code (that stops to 0x7F)??
0
CEHJCommented:
Not sure yet.

Does the number of characters in your string equal the number returned in the above loop?

What is the unicode encoding for the character you're trying to use - \u00e9?
0
teagishAuthor Commented:
yes the unicode is \u00E9
and yes the number of character equals the number in the string...
0
CEHJCommented:
It's this 0x82 character that's the problem. I don't know what encoding it is - do you?
0
teagishAuthor Commented:
No I don't, and I'm wondering which it is...
0
teagishAuthor Commented:
I decided to use a code in the database that specified the Unicode code for the letter as \U00E9...  When I meet this kind of code, I "translate" the code to its real value as a letter...
0
CleanupPingCommented:
teagish:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
TimYatesCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Split between GumB and CEHJ.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

TimYates
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.