Using Unicode data Through a DataSource

Let me rephrase my question. I want to support running the application with
either Unicode columns (NVARCHAR) or not (VARCHAR). If running VARCHAR, then
sendStringParametersAsUnicode should be false, otherwise it should be true.
If creating connections through DriverManager and a URL, then insuring the
URL parameter is set correctly. If creating connections from an externally
defined DataSource/ConnectionPool, then this setting must be made in the
external configuration, and I want to verify it is set correctly when
initializing the application server. As a test, I created a
SQLServerXADataSource object, and called the setURL method with a URL with
"sendStringParametersAsUnicode=false" in the string. Subsequently, calling
getSendStringParametersAsUnicode returned true, not false. I'll do some more
testing, including setting the property explicitly.

I haven't looked at our supported app servers, Tomcat, WebSphere and
WebLogic to see if they have explicit configuration options for this that
would assumably call this setter.

I am running SQL Server 2008 without any patches on Windows XP Professional
x64 Edition Version 2003 SP2. Sun JVM Java 1.5.0_14. I connect to a named
instance configured to use a specific port.
 >> Stay informed about: Using Unicode data Through a DataSource 

(Sorry about the duplicate post.)

Let me ask a related question. The big issue is make sure the indexes
are used, so if the database is non-Unicode, then the string
parameters should be non-Unicode, and if the database is Unicode, the
string parameters should be Unicode.

Up until now, we have only supported VARCHAR columns, which means the
Collation controls the character set, and the character set will be
some single-byte, non-Unicode character set. We have also been
wrapping any parameters markers (question marks ?) for character data
with "CAST(? AS VARCHAR)". So as long as the data we send can be
represented in the native TDS collation format of the database, no
loss of data should occur. Also, it seems that the setting of
"sendStringParametersAsUnicode" might not matter because of the cast.
That is, the non-Unicode indexes should be usable if we are casting
character data to VARCHAR, even if we are sending Unicode (NVARCHAR).

So the two choices seem to be, depending on whether or not the
database was created to support Unicode data, either explicitly
casting as character parameters as VARCHAR or NVARCHAR, or making sure
the DataSource or JDBC URL has the correct
sendStringParametersAsUnicode setting.

But laying it all out here, I see my flaw. If
sendStringParametersAsUnicode is set to the non-default value of
false, and the database is created with NVARCHAR, the cast won't help
if the data really is Unicode, and can't be represented "in the native
TDS collation format of the database." This last phrase is from the
driver doc, and I don't know how that is different from the collation
format of the database. I know TDS is the network protocol.

So my approach will be to make sure the connection parameter is
correctly set depending on whether the database is using VARCHAR or
NVARCHAR for character data, and remove any casting of the prepared
statement parameters.
 >> Stay informed about: Using Unicode data Through a DataSource 

Wes,

I used this code:

import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;

public class MultipleResultSets2005 {
public MultipleResultSets2005() {
}

public static void main(String[] args) {
try {
System.out.println("Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver");
java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
SQLServerDataSource ds = new SQLServerDataSource();
ds.setUser("aaaaa");
ds.setPassword("xxxxx");
ds.setServerName("servername");
ds.setDatabaseName("AdventureWorks");
ds.setSendStringParametersAsUnicode(false);
System.out.println("SendParametersasUnicode is " + ds.getSendStringParametersAsUnicode());
Connection conn = null;
conn = ds.getConnection();
// Driver information
DatabaseMetaData dm = conn.getMetaData();
System.out.println("Connected to " + dm.getURL());
System.out.println("Driver Information");
System.out.println("\tDriver Name: " + dm.getDriverName());
System.out.println("\tDriver Version: " + dm.getDriverVersion());
System.out.println("\tDatabase Major Version: " + dm.getDatabaseMajorVersion());
System.out.println("\tDatabase Minor Version: " + dm.getDatabaseMinorVersion());
System.out.println();
Statement stmt = conn.createStatement();
stmt.execute("Select ColVarchar from Table_1 where ColNVarchar='BBBBB'");
int resultSetNum = 0;
for (boolean resultsEndOrUpdateCount = false;
;
resultsEndOrUpdateCount = !stmt.getMoreResults())
{
if (resultsEndOrUpdateCount)
{
int updateCount = stmt.getUpdateCount();
if (-1 == updateCount) // end of results
break;

System.out.println("Update count is: " + updateCount);
}
else // result is ResultSet
{
ResultSet rs = stmt.getResultSet();



System.out.println("Found a result set");
while (rs.next())
{
System.out.println("ResultSetValue: " + rs.getString(1));
}


rs.close();
}
}

stmt.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
}
}

against this table:

USE [AdventureWorks]
GO

CREATE TABLE [dbo].[Table_1](
[ColVarchar] [varchar](50) NULL,
[ColNVarchar] [nvarchar](50) NULL
) ON [PRIMARY]

GO

DECLARE @i int

set @i=1
While @i<100000
BEGIN
INSERT INTO Table_1 (ColVarchar, ColNVarchar) VALUES ('AAAAA' + CAST(@i AS varchar),'BBBBB' + CAST(@i AS nvarchar))
set @i=@i+1
END
GO

getSendStringParametersAsUnicode returns the expected value every time. I am see some oddities with regard to the actual parameter type sent to the
database, but I need to doublecheck some things before I come to my final conclusion.

Evan

--------------------
>Thread-Topic: Using Unicode data Through a DataSource
>thread-index: Ack/dcVihrEQ7iLDSoWoJsQPpSppUw==
>X-WBNR-Posting-Host: 65.55.21.8
>From: =?Utf-8?B?V2VzIENsYXJr?=
>References:
>Subject: RE: Using Unicode data Through a DataSource
>Date: Wed, 5 Nov 2008 10:39:01 -0800
>Lines: 20
>Message-ID:
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3168
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Path: TK2MSFTNGHUB02.phx.gbl
>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:602
>NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
>
>Let me rephrase my question. I want to support running the application with
>either Unicode columns (NVARCHAR) or not (VARCHAR). If running VARCHAR, then
>sendStringParametersAsUnicode should be false, otherwise it should be true.
>If creating connections through DriverManager and a URL, then insuring the
>URL parameter is set correctly. If creating connections from an externally
>defined DataSource/ConnectionPool, then this setting must be made in the
>external configuration, and I want to verify it is set correctly when
>initializing the application server. As a test, I created a
>SQLServerXADataSource object, and called the setURL method with a URL with
>"sendStringParametersAsUnicode=false" in the string. Subsequently, calling
>getSendStringParametersAsUnicode returned true, not false. I'll do some more
>testing, including setting the property explicitly.
>
>I haven't looked at our supported app servers, Tomcat, WebSphere and
>WebLogic to see if they have explicit configuration options for this that
>would assumably call this setter.
>
>I am running SQL Server 2008 without any patches on Windows XP Professional
>x64 Edition Version 2003 SP2. Sun JVM Java 1.5.0_14. I connect to a named
>instance configured to use a specific port.
>

Evan T. Basalik
This posting is provided “AS IS” with no warranties, and confers no rights.
 >> Stay informed about: Using Unicode data Through a DataSource 

cough...cough...I guess it helps if my sample was actually representative of the question. The astute among you would have noticed that my initial code
sample did not use a parameter.

So, here's the new code sample:
--------------------------------------------------------
import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;
import java.math.*;

public class MultipleResultSets2005 {
public MultipleResultSets2005() {
}

public static void main(String[] args) {
try {
System.out.println("Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver");
java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
SQLServerDataSource ds = new SQLServerDataSource();
ds.setUser("sa");
ds.setPassword("sW62292");
ds.setServerName("evanba380");
ds.setDatabaseName("AdventureWorks");
ds.setSendStringParametersAsUnicode(true);
System.out.println("SendParametersasUnicode is " + ds.getSendStringParametersAsUnicode());
Connection conn = null;
conn = ds.getConnection();
// Driver information
DatabaseMetaData dm = conn.getMetaData();
System.out.println("Connected to " + dm.getURL());
System.out.println("Driver Information");
System.out.println("\tDriver Name: " + dm.getDriverName());
System.out.println("\tDriver Version: " + dm.getDriverVersion());
System.out.println("\tDatabase Major Version: " + dm.getDatabaseMajorVersion());
System.out.println("\tDatabase Minor Version: " + dm.getDatabaseMinorVersion());
System.out.println();
PreparedStatement stmt = conn.prepareStatement("select * from Table_1 where ColVarchar = ?");

stmt.setString(1, "AAAAA");
stmt.executeQuery();

int resultSetNum = 0;
for (boolean resultsEndOrUpdateCount = false;
;
resultsEndOrUpdateCount = !stmt.getMoreResults())
{
if (resultsEndOrUpdateCount)
{
int updateCount = stmt.getUpdateCount();
if (-1 == updateCount) // end of results
break;

System.out.println("Update count is: " + updateCount);
}
else // result is ResultSet
{
ResultSet rs = stmt.getResultSet();



System.out.println("Found a result set");
while (rs.next())
{
System.out.println("ResultSetValue: " + rs.getString(1));
}


rs.close();
}
}

stmt.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
}
}

--------------------------------------------------------

I tried the various combinations of sendParametersAsUnicode and column type (varchar and nvarchar) and I see the implicit conversions when expected
and don't see them when I don't expect them.

Sorry about the confusion.

Evan


--------------------
>X-Tomcat-ID: 116529902
>References:
>MIME-Version: 1.0
>Content-Type: text/plain
>Content-Transfer-Encoding: 7bit
>From: evanba.RemoveThis@online.microsoft.com (Evan T. Basalik (MSFT))
>Organization: Microsoft
>Date: Sun, 16 Nov 2008 22:05:38 GMT
>Subject: RE: Using Unicode data Through a DataSource
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
>Message-ID:
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Lines: 142
>Path: TK2MSFTNGHUB02.phx.gbl
>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:607
>NNTP-Posting-Host: TOMCATIMPORT3 10.201.220.210
>
>Wes,
>
>I used this code:
>
>import java.sql.*;
>import com.microsoft.sqlserver.jdbc.*;
>
>public class MultipleResultSets2005 {
> public MultipleResultSets2005() {
> }
>
> public static void main(String[] args) {
> try {
> System.out.println("Class being loaded com.microsoft.sqlserver.jdbc.SQLServerDriver");
> java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
> SQLServerDataSource ds = new SQLServerDataSource();
> ds.setUser("aaaaa");
> ds.setPassword("xxxxx");
> ds.setServerName("servername");
> ds.setDatabaseName("AdventureWorks");
> ds.setSendStringParametersAsUnicode(false);
> System.out.println("SendParametersasUnicode is " + ds.getSendStringParametersAsUnicode());
> Connection conn = null;
> conn = ds.getConnection();
> // Driver information
> DatabaseMetaData dm = conn.getMetaData();
> System.out.println("Connected to " + dm.getURL());
> System.out.println("Driver Information");
> System.out.println("\tDriver Name: " + dm.getDriverName());
> System.out.println("\tDriver Version: " + dm.getDriverVersion());
> System.out.println("\tDatabase Major Version: " + dm.getDatabaseMajorVersion());
> System.out.println("\tDatabase Minor Version: " + dm.getDatabaseMinorVersion());
> System.out.println();
> Statement stmt = conn.createStatement();
> stmt.execute("Select ColVarchar from Table_1 where ColNVarchar='BBBBB'");
> int resultSetNum = 0;
> for (boolean resultsEndOrUpdateCount = false;
> ;
> resultsEndOrUpdateCount = !stmt.getMoreResults())
> {
> if (resultsEndOrUpdateCount)
> {
> int updateCount = stmt.getUpdateCount();
> if (-1 == updateCount) // end of results
> break;
>
> System.out.println("Update count is: " + updateCount);
> }
> else // result is ResultSet
> {
> ResultSet rs = stmt.getResultSet();
>
>
>
> System.out.println("Found a result set");
> while (rs.next())
> {
> System.out.println("ResultSetValue: " + rs.getString(1));
> }
>
>
> rs.close();
> }
> }
>
> stmt.close();
> conn.close();
> } catch (ClassNotFoundException e) {
> e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
> } catch (SQLException e) {
> e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
> }
>}
>}
>
>against this table:
>
>USE [AdventureWorks]
>GO
>
>CREATE TABLE [dbo].[Table_1](
> [ColVarchar] [varchar](50) NULL,
> [ColNVarchar] [nvarchar](50) NULL
>) ON [PRIMARY]
>
>GO
>
>DECLARE @i int
>
>set @i=1
>While @i<100000
>BEGIN
>INSERT INTO Table_1 (ColVarchar, ColNVarchar) VALUES ('AAAAA' + CAST(@i AS varchar),'BBBBB' + CAST(@i AS nvarchar))
>set @i=@i+1
>END
>GO
>
>getSendStringParametersAsUnicode returns the expected value every time. I am see some oddities with regard to the actual parameter type sent to the
>database, but I need to doublecheck some things before I come to my final conclusion.
>
>Evan
>
>--------------------
>>Thread-Topic: Using Unicode data Through a DataSource
>>thread-index: Ack/dcVihrEQ7iLDSoWoJsQPpSppUw==
>>X-WBNR-Posting-Host: 65.55.21.8
>>From: =?Utf-8?B?V2VzIENsYXJr?=
>>References:
>>Subject: RE: Using Unicode data Through a DataSource
>>Date: Wed, 5 Nov 2008 10:39:01 -0800
>>Lines: 20
>>Message-ID:
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3168
>>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>>Path: TK2MSFTNGHUB02.phx.gbl
>>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:602
>>NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
>>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
>>
>>Let me rephrase my question. I want to support running the application with
>>either Unicode columns (NVARCHAR) or not (VARCHAR). If running VARCHAR, then
>>sendStringParametersAsUnicode should be false, otherwise it should be true.
>>If creating connections through DriverManager and a URL, then insuring the
>>URL parameter is set correctly. If creating connections from an externally
>>defined DataSource/ConnectionPool, then this setting must be made in the
>>external configuration, and I want to verify it is set correctly when
>>initializing the application server. As a test, I created a
>>SQLServerXADataSource object, and called the setURL method with a URL with
>>"sendStringParametersAsUnicode=false" in the string. Subsequently, calling
>>getSendStringParametersAsUnicode returned true, not false. I'll do some more
>>testing, including setting the property explicitly.
>>
>>I haven't looked at our supported app servers, Tomcat, WebSphere and
>>WebLogic to see if they have explicit configuration options for this that
>>would assumably call this setter.
>>
>>I am running SQL Server 2008 without any patches on Windows XP Professional
>>x64 Edition Version 2003 SP2. Sun JVM Java 1.5.0_14. I connect to a named
>>instance configured to use a specific port.
>>
>
>Evan T. Basalik
>This posting is provided “AS IS” with no warranties, and confers no rights.
>
>
>

Evan T. Basalik
This posting is provided “AS IS” with no warranties, and confers no rights.
 >> Stay informed about: Using Unicode data Through a DataSource