MS SQL Server, JDBC, and Unicode?

On Jul 10, 3:36*am, Tom Anderson wrote:
> Greets yalls,
>
> Has anyone made SQL Server work with unicode in java?
>
> I'm working on a system which wants to put unicode in a database. It does
> this fine with Oracle, but we haven't been able to make it do so when the
> database is SQL Server - and indeed the manufacturers of the system list
> this as something that it won't do. Anything that isn't on the current
> code page turns into a question mark.
>
> Our columns are nvarchar, and sendStringParametersAsUnicode is true in the
> JDBC URL. Is there more than this we need to do?
>
> I've come across mention of a syntax which looks like N'this is a unicode
> string' for writing unicode literals in SQL. Do i need to do that? How do
> i do that if i'm using PreparedStatements?
>
> We're using the MS driver. An alternative would be the open source jTDS -
> any idea if that will fix the problem?
>
> Thanks,
> tom
>
> --
> Sometimes it takes a madman like Iggy Pop before you can SEE the logic
> really working.

Which driver are you using now? By default, the driver will send
string data (parameter values) to the DBMS as 16-bit characters,
as you'd want. Are you saying that you have good strings in your
Java client, but on insert, the DBMS has it wrong? Or are you saying
that the DBMS can have good data, but on extracting, it's bad, or
that insert+extract gets bad stuff? Show the JDBC code.

Tom Anderson wrote:
> Has anyone made SQL Server work with unicode in java?
>
> I'm working on a system which wants to put unicode in a database. It
> does this fine with Oracle, but we haven't been able to make it do so
> when the database is SQL Server - and indeed the manufacturers of the
> system list this as something that it won't do. Anything that isn't on
> the current code page turns into a question mark.
>
> Our columns are nvarchar, and sendStringParametersAsUnicode is true in
> the JDBC URL. Is there more than this we need to do?
>
> I've come across mention of a syntax which looks like N'this is a
> unicode string' for writing unicode literals in SQL. Do i need to do
> that? How do i do that if i'm using PreparedStatements?
>
> We're using the MS driver. An alternative would be the open source jTDS
> - any idea if that will fix the problem?

I can't get it not to work.

:-)

The following is tested with the MS driver (driver for 2000
against 2000, but I expect 2005 against 2005 to work identical):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class Unicode {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
// SQLServer 2000
Connection con =
DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost;DatabaseName=Test",
"sa", "");
Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE unifun (id INTEGER NOT NULL,
data NVARCHAR(50), PRIMARY KEY(id))");
stmt.executeUpdate("INSERT INTO unifun VALUES(1,N'ÆØÅæøå the
wrong way')");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO
unifun VALUES(?,?)");
pstmt.setInt(1, 2);
pstmt.setString(2, "ÆØÅæøå the correct way");
pstmt.executeUpdate();
ResultSet rs = stmt.executeQuery("SELECT id,data FROM unifun");
while(rs.next()) {
System.out.println(rs.getInt(1) + " : " + rs.getString(2));
}
rs.close();
stmt.executeUpdate("DROP TABLE unifun");
stmt.close();
con.close();
}
}

Arne

Arne Vajhøj (arne@vajhoej.dk) writes:
> The following is tested with the MS driver (driver for 2000
> against 2000, but I expect 2005 against 2005 to work identical):

I don't know much about JDBC, but I don't think Microsoft JDBC driver
for SQL 2000 has a very good reputation. The SQL 2005 is likely to be
better.

> Statement stmt = con.createStatement();
> stmt.executeUpdate("CREATE TABLE unifun (id INTEGER NOT NULL,
> data NVARCHAR(50), PRIMARY KEY(id))");
> stmt.executeUpdate("INSERT INTO unifun VALUES(1,N'ÆØÅæøå the
> wrong way')");
> PreparedStatement pstmt = con.prepareStatement("INSERT INTO
> unifun VALUES(?,?)");
> pstmt.setInt(1, 2);
> pstmt.setString(2, "ÆØÅæøå the correct way");
> pstmt.executeUpdate();
> ResultSet rs = stmt.executeQuery("SELECT id,data FROM unifun");
> while(rs.next()) {
> System.out.println(rs.getInt(1) + " : " + rs.getString(2));
> }

First of all, are you saying that ÆØÅæøå does not come back correctly?
That would be even stranger, since I would expect your ANSI code page
to be 1252, in which case things cannot go wrong.

In any case, did you look in Management Studio or Query Analyzer to see how
the data looks like? That is, is data mutilated on input or on output?
You can also use Profiler to see what the driver sends to SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Fri, 10 Jul 2009, joe.no_junk@gmail.com wrote:

> On Jul 10, 3:36*am, Tom Anderson wrote:
>
>> Has anyone made SQL Server work with unicode in java?
>>
>> I'm working on a system which wants to put unicode in a database. It does
>> this fine with Oracle, but we haven't been able to make it do so when the
>> database is SQL Server - and indeed the manufacturers of the system list
>> this as something that it won't do. Anything that isn't on the current
>> code page turns into a question mark.
>>
>> Our columns are nvarchar, and sendStringParametersAsUnicode is true in the
>> JDBC URL. Is there more than this we need to do?
>>
>> I've come across mention of a syntax which looks like N'this is a unicode
>> string' for writing unicode literals in SQL. Do i need to do that? How do
>> i do that if i'm using PreparedStatements?
>>
>> We're using the MS driver. An alternative would be the open source jTDS -
>> any idea if that will fix the problem?
>
> Which driver are you using now?

One from Microsoft, downloaded a couple of weeks ago. I don't have access
to the machines with the driver on right now, so i can't be more specific,
i'm afraid.

> By default, the driver will send string data (parameter values) to the
> DBMS as 16-bit characters, as you'd want. Are you saying that you have
> good strings in your Java client, but on insert, the DBMS has it wrong?

That.

> Or are you saying that the DBMS can have good data, but on extracting,
> it's bad, or that insert+extract gets bad stuff? Show the JDBC code.

I can't - i didn't write it, and don't have the source. This is the crux
of the problem - i don't know if the problem is in SQL Server and/or its
drivers, or the way the web app is using it. But if it's doing it wrong,
why does it work with Oracle? It's a mystery!

tom

--
SOY! SOY! SOY! Soy makes you strong! Strength crushes enemies! SOY!

On Fri, 10 Jul 2009, Arne Vajh?j wrote:

> Tom Anderson wrote:
>> Has anyone made SQL Server work with unicode in java?
>
> I can't get it not to work.
>
> :-)
>
> The following is tested with the MS driver (driver for 2000
> against 2000, but I expect 2005 against 2005 to work identical):
>
> public class Unicode {
> public static void main(String[] args) throws Exception {
> Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); // SQLServer 2000
> Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost;DatabaseName=Test", "sa", "");
> Statement stmt = con.createStatement();
> stmt.executeUpdate("CREATE TABLE unifun (id INTEGER NOT NULL, data NVARCHAR(50), PRIMARY KEY(id))");
> stmt.executeUpdate("INSERT INTO unifun VALUES(1,N'?????? the wrong way')");
> PreparedStatement pstmt = con.prepareStatement("INSERT INTO unifun VALUES(?,?)");
> pstmt.setInt(1, 2);
> pstmt.setString(2, "?????? the correct way");
> pstmt.executeUpdate();
> ResultSet rs = stmt.executeQuery("SELECT id,data FROM unifun");
> while(rs.next()) {
> System.out.println(rs.getInt(1) + " : " + rs.getString(2));
> }
> rs.close();
> stmt.executeUpdate("DROP TABLE unifun");
> stmt.close();
> con.close();
> }
> }

Silly question, but those ?s were unicode characters before you pasted
this into usenet, right?

Thanks for doing this, Arne - i should probably have tried it myself. It
eliminates one area of doubt about the problem, but still leaves me none
the wiser as to why the system won't do unicode right. Maybe it's
constructing SQL strings internally, rather than using PreparedStatements,
and not using the N'?' syntax. I really don't think that's the case,
though - i've seen evidence from debugging and stack traces that
PreparedStatements are indeed used.

tom

--
SOY! SOY! SOY! Soy makes you strong! Strength crushes enemies! SOY!

Tom Anderson wrote:
> On Fri, 10 Jul 2009, Arne Vajh?j wrote:
>
>> Tom Anderson wrote:
>>> Has anyone made SQL Server work with unicode in java?
>>
>> I can't get it not to work.
>>
>> :-)
>>
>> The following is tested with the MS driver (driver for 2000
>> against 2000, but I expect 2005 against 2005 to work identical):
>>
>> public class Unicode {
>> public static void main(String[] args) throws Exception {
>> Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
>> // SQLServer 2000
>> Connection con =
>> DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost;DatabaseName=Test",
>> "sa", "");
>> Statement stmt = con.createStatement();
>> stmt.executeUpdate("CREATE TABLE unifun (id INTEGER NOT NULL,
>> data NVARCHAR(50), PRIMARY KEY(id))");
>> stmt.executeUpdate("INSERT INTO unifun VALUES(1,N'?????? the
>> wrong way')");
>> PreparedStatement pstmt = con.prepareStatement("INSERT INTO
>> unifun VALUES(?,?)");
>> pstmt.setInt(1, 2);
>> pstmt.setString(2, "?????? the correct way");
>> pstmt.executeUpdate();
>> ResultSet rs = stmt.executeQuery("SELECT id,data FROM unifun");
>> while(rs.next()) {
>> System.out.println(rs.getInt(1) + " : " + rs.getString(2));
>> }
>> rs.close();
>> stmt.executeUpdate("DROP TABLE unifun");
>> stmt.close();
>> con.close();
>> }
>> }
>
> Silly question, but those ?s were unicode characters before you pasted
> this into usenet, right?

Yes.

I am surprised that they did no go through.

> Thanks for doing this, Arne - i should probably have tried it myself. It
> eliminates one area of doubt about the problem, but still leaves me none
> the wiser as to why the system won't do unicode right. Maybe it's
> constructing SQL strings internally, rather than using
> PreparedStatements, and not using the N'?' syntax. I really don't think
> that's the case, though - i've seen evidence from debugging and stack
> traces that PreparedStatements are indeed used.

We will need more info to trouble shoot.

code
what is being inserted
what is being selected out

Arne

Erland Sommarskog wrote:
> Arne Vajhøj (arne@vajhoej.dk) writes:
>> The following is tested with the MS driver (driver for 2000
>> against 2000, but I expect 2005 against 2005 to work identical):
>
> I don't know much about JDBC, but I don't think Microsoft JDBC driver
> for SQL 2000 has a very good reputation. The SQL 2005 is likely to be
> better.

Yes.

But if it works with the bad driver, then it is a fair assumption that
it will also work with the good driver.

But not the other way around.

>> Statement stmt = con.createStatement();
>> stmt.executeUpdate("CREATE TABLE unifun (id INTEGER NOT NULL,
>> data NVARCHAR(50), PRIMARY KEY(id))");
>> stmt.executeUpdate("INSERT INTO unifun VALUES(1,N'ÆØÅæøå the
>> wrong way')");
>> PreparedStatement pstmt = con.prepareStatement("INSERT INTO
>> unifun VALUES(?,?)");
>> pstmt.setInt(1, 2);
>> pstmt.setString(2, "ÆØÅæøå the correct way");
>> pstmt.executeUpdate();
>> ResultSet rs = stmt.executeQuery("SELECT id,data FROM unifun");
>> while(rs.next()) {
>> System.out.println(rs.getInt(1) + " : " + rs.getString(2));
>> }
>
> First of all, are you saying that ÆØÅæøå does not come back correctly?

No.

I am saying that it work perfectly for me.

With the intention of getting the OP to check differences between
what he is doing and what I am doing.

> That would be even stranger, since I would expect your ANSI code page
> to be 1252, in which case things cannot go wrong.

Things can always go wrong.

But you think the test would be better with a unicode value > 255 ?

Arne

Arne Vajhøj wrote:
> Erland Sommarskog wrote:
>> That would be even stranger, since I would expect your ANSI code page
>> to be 1252, in which case things cannot go wrong.
>
> Things can always go wrong.
>
> But you think the test would be better with a unicode value > 255 ?

Just tried with:

stmt.executeUpdate("INSERT INTO unifun
VALUES(1,N'\u20AC\u20AC\u20AC the wrong way')");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO
unifun VALUES(?,?)");
pstmt.setInt(1, 2);
pstmt.setString(2, "\u20AC\u20AC\u20AC the correct way");
pstmt.executeUpdate();
ResultSet rs = stmt.executeQuery("SELECT id,data FROM unifun");
while(rs.next()) {
System.out.println(rs.getInt(1) + " : " + rs.getString(2));
}

and it displays euro signs nicely.

Arne

Tom Anderson (twic@urchin.earth.li) writes:
> Silly question, but those ?s were unicode characters before you pasted
> this into usenet, right?

And even after. The characters displayed correctly in my newsreader, so
did Arne's name. So there seems to be a problem at your end.

Whether that says anything about your database problems I don't know.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx