MS SQL Server, JDBC, and Unicode?

> 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.

I want to second Erland's suggestion to capture the actual SQL with
Profiler. Also, check the JDBC sendStringParametersAsUnicode setting to
make sure it is set to true.

We had exactly the opposite situation where a JDBC application that was
sending all strings as Unicode even though the we did not use Unicode data
types in that database. This was killing performance due to non-sargable
values. The developers changed a setting (I think it was the
sendStringParametersAsUnicode) so that parameter values were passed as
non-Unicode strings.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Tom Anderson" wrote in message
news:alpine.DEB.1.10.0907111232530.30152@urchin.earth.li...
> 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 Sat, 11 Jul 2009, Arne Vajh?j wrote:
>
>> 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.
>>>
>>> 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
>
> All of that is under the hood where i can't really get at it, sadly.
>
> Although i could trap the queries and the results with the SQL Server
> profiler.
>
> And i could run the app under a debugger and breakpoint all the
> interesting methods, to see what's actually being called.

More info is definitely needed.

Right now it is like trying to catch a black cat in a dark room
blindfolded.

Arne

Dan Guzman wrote:
>> 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.
>
> I want to second Erland's suggestion to capture the actual SQL with
> Profiler. Also, check the JDBC sendStringParametersAsUnicode setting to
> make sure it is set to true.

I would be worth trying.

Arne

Arne Vajhøj wrote:
> Right now it is like trying to catch a black cat in a dark room
> blindfolded.

Open can of tuna.
Place open tuna inside cloth bag.
Maintain loose grip on drawstring.
When you feel cat inside the bag, pull on drawstring, securing cat.

Simple.

--
Lew

Lew wrote:
> Arne Vajhøj wrote:
>> Right now it is like trying to catch a black cat in a dark room
>> blindfolded.
>
> Open can of tuna.
> Place open tuna inside cloth bag.
> Maintain loose grip on drawstring.
> When you feel cat inside the bag, pull on drawstring, securing cat.
>
> Simple.

Can you backtranslate analogu->real world ?

Arne

On Sat, 11 Jul 2009, Arne Vajh?j wrote:

> Lew wrote:
>> Arne Vajh?j wrote:
>>> Right now it is like trying to catch a black cat in a dark room
>>> blindfolded.
>>
>> Open can of tuna.
>> Place open tuna inside cloth bag.
>> Maintain loose grip on drawstring.
>> When you feel cat inside the bag, pull on drawstring, securing cat.
>>
>> Simple.
>
> Can you backtranslate analogu->real world ?

Obviously, one would stuff the SQL Server machine with tuna.

However, in this case, the server is on a virtual machine. Not sure how to
proceed here.

tom

--
For me, thats just logic. OTOH, Spock went bananas several times using
logic. -- Pete, mfw

Arne Vajhøj wrote:
> Lew wrote:
>> Arne Vajhøj wrote:
>>> Right now it is like trying to catch a black cat in a dark room
>>> blindfolded.
>>
>> Open can of tuna.
>> Place open tuna inside cloth bag.
>> Maintain loose grip on drawstring.
>> When you feel cat inside the bag, pull on drawstring, securing cat.
>>
>> Simple.
>
> Can you backtranslate analogu->real world ?

Sure: it wasn't a very good analogy. It's actually not like trying to catch
a black cat in a dark room blindfolded.

--
Lew

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?
[ SNIP ]

FWIW, Tom, I saw his original characters OK when I read his post...just
a bunch of unlauts that he'd need when he reads and writes in his
heathen language... :-)

AHS

* I'm one to talk - if I read and write in my mother tongue I need äöüõ.

Tom Anderson wrote:
>> Silly question, but those ?s were unicode characters before you pasted
>> this into usenet, right?
> [ SNIP ]

Arved Sandstrom wrote:
> FWIW, Tom, I saw his original characters OK when I read his post...just
> a bunch of unlauts [sic] that he'd need when he reads and writes in his
> heathen language... :-)
....
> * I'm one to talk - if I read and write in my mother tongue I need äöüõ.

Even to write in my heathen mother tongue, American English, in which the use
of foreign "loan" words is hardly taboo, if one writes with éclat, casting
one's words into the æther for all to read, hoping to make a difference in the
noösphere, or perhaps just to explain to one's classmate Ramòn how to expand
all the digits of π, one might need to incorporate a soupçon of non-ASCII
characters.

--
Lew
http://en.wikipedia.org/wiki/Noosphere