?
Solved

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

Posted on 2003-03-04
25
Medium Priority
?
527 Views
Last Modified: 2013-12-03
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?
0
Comment
Question by:teagish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
  • 2
  • +3
25 Comments
 
LVL 35

Expert Comment

by:TimYates
ID: 8065273
do you have some example code?

and results?  and expected results?
0
 

Expert Comment

by:GumB
ID: 8065338
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
 

Author Comment

by:teagish
ID: 8065430
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:teagish
ID: 8065459
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
 

Author Comment

by:teagish
ID: 8065472
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
 

Author Comment

by:teagish
ID: 8065487
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 8065881
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
 

Expert Comment

by:ebbesen
ID: 8066697
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
 

Accepted Solution

by:
GumB earned 100 total points
ID: 8067154
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
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 100 total points
ID: 8067492
>>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
 

Author Comment

by:teagish
ID: 8067555
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
 

Author Comment

by:teagish
ID: 8067640
How do I do to see / change the value of NLS_LANG??  I looked in the registry and it wasn't there...
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 8067711
>>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
 

Author Comment

by:teagish
ID: 8067772
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 8067859
>>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
 

Author Comment

by:teagish
ID: 8071955
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
 

Author Comment

by:teagish
ID: 8072043
Is it possible that the characters returned are in ASCII code (that stops to 0x7F)??
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 8074849
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
 

Author Comment

by:teagish
ID: 8075435
yes the unicode is \u00E9
and yes the number of character equals the number in the string...
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 8075859
It's this 0x82 character that's the problem. I don't know what encoding it is - do you?
0
 

Author Comment

by:teagish
ID: 8130996
No I don't, and I'm wondering which it is...
0
 

Author Comment

by:teagish
ID: 8315296
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
 

Expert Comment

by:CleanupPing
ID: 9058939
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
 
LVL 35

Expert Comment

by:TimYates
ID: 9762140
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses
Course of the Month8 days, 6 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question