1

I've read many threads regarding this topic, but everybody point to the character set in the database.

I'm using strings with special characters like 'ñ' and I'm not able to set them right in the database. As you may guess, the characters are changed to '?'.

The thing is that using this statement, I get it RIGHT:

stmt = con.prepareStatement("INSERT INTO LONG_CODES_TABLE (TIMESTAMP, TABLE_NAME, CODE__C, CODE_DESC) 
VALUES (GET_TIMESTAMP, 'MUNICIPIOS', '" + municipio + "',  '" + municipio + "') ");

And just in the same database and table, without changing anything, if I use the following I get the '?' character in the DB:

stmt = con.prepareStatement("INSERT INTO LONG_CODES_TABLE (TIMESTAMP, TABLE_NAME, CODE__C, CODE_DESC) 
VALUES (GET_TIMESTAMP, 'MUNICIPIOS', ?,  ?) ");

stmt.setString(1, municipio);

stmt.setString(2, municipio);

So, the character problem is happening ONLY if I use setString. Any ideas?

EDIT: The value of the 'municipio' variable is, for example: 'ABADIÑO'. Later, I can check the differences between doing it on way or the other by asking for that value with an sql statement, for example:

select * from long_codes_table 
where table_name = 'MUNICIPIOS' and code__c = 'ABADIÑO'

One way I get the result row. The other way, I don't.

Thank you.

Nivas
  • 18,126
  • 4
  • 62
  • 76
dave
  • 11
  • 3
  • 2
    How are you judging whether the data is correct or not? I've seen situations where the "wrong" approach works for inserting data because the wrong approach was *also* used for extracting it... I'm not saying that's what's going on here, just that it's worth checking. – Jon Skeet Aug 03 '11 at 09:27
  • http://stackoverflow.com/questions/4724299/java-preparedstatement-setstring-changes-characters http://stackoverflow.com/questions/945543/java-inserting-special-characters-with-preparedstatement-fails – Parth Aug 03 '11 at 09:32
  • @dave, do you mind updating the question with your database character set? If everybody has pointed eventually to the character set in the database, then surely it must be important. Also, can you post the OS locale of the system which is issuing this query. AFAIK, the Oracle driver issues an `ALTER SYSTEM SET NLS_LANG=...` statement when establishing a connection. – Vineet Reynolds Aug 03 '11 at 09:40
  • @Jon Skeet, I think I'm not understanding your point. The variable 'municipio' is just a String that is obtained from a map that is passed as a parameter. My point here is that the same statement with the same variable 'municipio' is inserted correctly or not depending on whether using setString or not, as shown in the examples. – dave Aug 03 '11 at 12:58
  • @dave: But you've only shown code which *stores* the data. How are you then fetching it? With Java or something else? How are you *displaying* the data? There are any number of places this can go wrong. – Jon Skeet Aug 03 '11 at 13:00
  • @Jon Skeet: ok, so how do I check later that the value is correct or not? Just making a couple of sql statements asking for the value, from TOAD or SQLPlus or whatever. That's what you were asking, right? Thanks. – dave Aug 03 '11 at 13:02
  • @dave: Yes, that's what I'm asking - which were you using? It would be interesting to try to fetch the data again *in the same Java code* (i.e. with the same JDBC connection etc) and see if that's any better. Print out the characters within the string, converting each to an integer to find out *exactly* what's in the Java string. Won't be conclusive, but will all help diagnostics... – Jon Skeet Aug 03 '11 at 13:07
  • @Jon Skeet: I will prepare the code to print out each character and its numeric value from the variable 'municipio', and then after doing a select statement of what I inserted, I will print each character and its numeric value from the result string. – dave Aug 03 '11 at 13:52
  • @Vineet Reynolds: How and where should I exactly check the OS locale and the database character set? (I think this last one is by doing a SELECT NLS_CHARACTERSET FROM NLS_DATABASE_PARAMETERS). As I suggested, with the same OS and DB, I get different result doing it with a simple statement and by using setString. That's why I think it shouldn't a DB character set issue (character sets are the same in both tests). – dave Aug 03 '11 at 13:53
  • @dave, using the same OS and DB can have no bearing on the behavior. The thin JDBC driver will perform encoding and decoding of data when you use the `setString` and `getString` methods (you can read more of this in [one of my other answers for details](http://stackoverflow.com/questions/2534391/understanding-character-encoding-in-typical-java-web-app/2534814#2534814). That's why you might be seeing different behavior when concatenating Strings to the SQL query, compared to the use of `setString`. The OS locale can be determined using `Locale.getDefault()` which is also relied on by the driver – Vineet Reynolds Aug 03 '11 at 14:56
  • Make sure your os locale and oracle locale are both set to support Unicode. SetString is crushing your Ñs and ás, which very likely means your os / jvm locale is set to some kind of ASCII version. Alternatively, entitize your strings in the dbms, for example representing Ñ as Ñ and so forth. – O. Jones Sep 14 '11 at 14:57

1 Answers1

1

I had that behaviour, too. On top of that I observed that this error did not occur when the application was started from the IDE. That's how I realized that in the JVM - attributes, the one for the encoding was missing.

java %vm-opts% %clspth% -Dfile.encoding=UTF-8 ...
Fildor
  • 14,510
  • 4
  • 35
  • 67