Remove unicode from SQL

Thread view:

Keith - 21 Aug 2004 02:58 GMT

When using Jturbo 3.0 JDBC driver we are seeing the SQl sent to MS SQL
Server 2000 with Unicode N prepended to the string parameters.

Example:
Parameterized SQL: Select name, address, phone from myaddresstable
where name = ?

Resulting SQL:
Select name, address, phone from myaddresstable where name = N'myname'

The collation settings are set to Latin1_General_BIN and the JTurbo
connection string has the charset=Cp1252.

The string fields are of Char type, not nchar.

We are using the setString method on the preparedstatement.

The execution plans on these queries are showing that the indexes are
not being used because of the conversion that needs to be done.

Any ideas?
Thanks in advance for any help on this.

Reply to this Message

Joe Weinstein - 21 Aug 2004 06:12 GMT

There should be a driver property you can define to stop the driver doing
that. All commercial drivers I know of do. The default should be to do as the
driver does, to ensure that any valid Java string (16-bit characters) get sent
to the DBMS unchanged. However, for varchar values and indexes it makes the
DBMS have to convert back to varchar, and the conversion means that the DBMS
will skip indexes and do table scans (slow).
Joe Weinstein at BEA

> When using Jturbo 3.0 JDBC driver we are seeing the SQl sent to MS SQL
> Server 2000 with Unicode N prepended to the string parameters.
[quoted text clipped - 18 lines]
> Any ideas?
> Thanks in advance for any help on this.

Reply to this Message

Keith - 21 Aug 2004 14:09 GMT

> There should be a driver property you can define to stop the driver doing
> that. All commercial drivers I know of do. The default should be to do as the
[quoted text clipped - 3 lines]
> will skip indexes and do table scans (slow).
> Joe Weinstein at BEA

Thanks Joe.
Is this the property that you are refering to on JTurbo?
SendStringParametersAsUnicode={true | false}.
Determines whether string parameters are sent to the SQL
Server database in Unicode or in the default character
encoding of the database. True means that string parameters are sent
to SQL Server in Unicode.
False means that they are sent in the default encoding.

Reply to this Message

Joe Weinstein - 21 Aug 2004 16:31 GMT

>>There should be a driver property you can define to stop the driver doing
>>that. All commercial drivers I know of do. The default should be to do as the
[quoted text clipped - 12 lines]
> to SQL Server in Unicode.
> False means that they are sent in the default encoding.

That's it. Set it to false.
Joe

Reply to this Message

Keith - 24 Aug 2004 02:30 GMT

Joe,

It seems as if there is a patch for JTurbo 3.0.1 that has this fixed.
The property setting isn't needed.

Thanks for your help.

Keith

> >>There should be a driver property you can define to stop the driver doing
> >>that. All commercial drivers I know of do. The default should be to do as the
[quoted text clipped - 15 lines]
> That's it. Set it to false.
> Joe

Reply to this Message

Luke Webber - 21 Aug 2004 06:48 GMT

Have you tried the jTDS JDBC driver? It's always worked well for me, and
unlike JTurbo, it's free.

Luke

Reply to this Message

Keith - 21 Aug 2004 13:43 GMT

> Have you tried the jTDS JDBC driver? It's always worked well for me, and
> unlike JTurbo, it's free.
>
> Luke

No. Unfortunately the driver is delivered with the application we are using.

Thanks for the tip.

Reply to this Message

Remove unicode from SQL

Free Magazines