Problem saving/retrieving unicode characters NVARCHAR with unicode collation (java jdbc)

I'm connecting to a SQL Server 2005 database using the latest (beta) sql server driver (Microsoft SQL Server 2005 JDBC Driver 1.1 CTP June 2006) from within Java (Rational Application Developer).

The table in SQL Server database has collation Latin1_General_CI_AS and one of the columns is a NVARCHAR with collation Indic_General_90_CI_AS. This should be a Unicode only collation. However when storing for instance the following String:

€_￡_ùúü?ùúü?_???????￡????_???_??????_прстуф_????
... it is saved with ? for all unicode characters as follows (when looking in the database):
€_￡_ùúü?ùúü?_???????￡????_???_?_?_?

The above is not correct, since all unicode characters should still be visible. When inserting the same string directly into the sql server database (without using Java) the result is ok.

Also when trying to retrieve the results again it complains about the following error within Java:

Codepage 0 is not supported by the Java environment.

Hopefully somebody has an answer for this problem. When I alter the collation of the NVARCHAR column to be Latin1_General_CI_AS as well, the data can be stored and retrieved however then of course the unicode specific characters are lost and results into ? So in that case the output is as described above (ie €_￡_ùúü?ùúü?_???????￡????_???_?_?_?)

We would like to be able to persist and retrieve unicode characters in a SQL Server database using the correct JDBC Driver. We achieved this result already with an Oracle UTF8 database. But we need to be compliant with a SQL Server database as well. Please help.

Thanks in advance for your help.So far nobody replied on my question. unfortunately.
Please help me to sort out this problem.

Bottom line we just want to be able to save and retrieve Unicode characters in SQL server within our Java application. We want to do this by setting the NVARCHAR columns in all tables to a Unicode collation.

Please help.
Thanks.|||

Hugo,

Can you tell me whether you have sendStringParametersAsUnicode set to "false" in your connection properties?

Thank you,

--David Olix

JDBC Development

|||

Hi David,

Thanks for your reply. I can ensure that I didn't set the sendStringParametersAsUnicode to "false". This parameter is default set to "true" so I didn't change it in the connection properties.

I found out that the unicode Collation I used "Indic_General_90_CI_AS" had a Codepage 0 in my SQL Server 2005 installation. In fact all unicode only collations do have a codepage 0 if I check them all using the following command:

SELECT COLLATIONPROPERTY('Indic_General_90_CI_AS', 'CodePage') as CodePage

Is this perhaps related to the problem I have in Java? Because in there the exception explains about the fact that the codepage is 0.

I really hope you can help me to sort this out.

Thanks in advance for your help David.

Hugo

|||Hi David,

I found the solution. When updating a SQL Server 2005 NVARCHAR column, you have to use the following syntax:

UPDATE <table> SET <column> = N'<value>'

So for example:
UPDATE customer SET id = '€_???????￡????_???_??????_прстуф_????'

Has to be this for a SQL Server connection:

UPDATE customer SET id = N'€_???????￡????_???_??????_прстуф_????'

The prefix N before the value is required to make sure to save the string in Unicode format. I did not do this so far. Now I'm able to store and retrieve data into a SQL Server database and my collatin is now Latin1_General_CI_AS since this supports Unicode as well.

Hugo|||

hi hugo

this is gr8 thread

i have facing a problem

i want to insert indic values in a table in SQL server

the update statement u mentioned is useful

UPDATE <table> SET <column> = N'<value>'

but when i use INSERT INTO <table> VALUES(N'<value>')

can u tell me y doesnt this work

tahnx in advance.

Problem saving/retrieving unicode characters NVARCHAR with unicode collation (java jdbc)

I'm connecting to a SQL Server 2005 database using the latest (beta) sql server driver (Microsoft SQL Server 2005 JDBC Driver 1.1 CTP June 2006) from within Java (Rational Application Developer).

The table in SQL Server database has collation Latin1_General_CI_AS and one of the columns is a NVARCHAR with collation Indic_General_90_CI_AS. This should be a Unicode only collation. However when storing for instance the following String:

€_￡_ùúü?ùúü?_???????￡????_???_??????_прстуф_????
... it is saved with ? for all unicode characters as follows (when looking in the database):
€_￡_ùúü?ùúü?_???????￡????_???_?_?_?

The above is not correct, since all unicode characters should still be visible. When inserting the same string directly into the sql server database (without using Java) the result is ok.

Also when trying to retrieve the results again it complains about the following error within Java:

Codepage 0 is not supported by the Java environment.

Hopefully somebody has an answer for this problem. When I alter the collation of the NVARCHAR column to be Latin1_General_CI_AS as well, the data can be stored and retrieved however then of course the unicode specific characters are lost and results into ? So in that case the output is as described above (ie €_￡_ùúü?ùúü?_???????￡????_???_?_?_?)

We would like to be able to persist and retrieve unicode characters in a SQL Server database using the correct JDBC Driver. We achieved this result already with an Oracle UTF8 database. But we need to be compliant with a SQL Server database as well. Please help.

Thanks in advance for your help.
So far nobody replied on my question. unfortunately.
Please help me to sort out this problem.

Bottom line we just want to be able to save and retrieve Unicode characters in SQL server within our Java application. We want to do this by setting the NVARCHAR columns in all tables to a Unicode collation.

Please help.
Thanks.
|||

Hugo,

Can you tell me whether you have sendStringParametersAsUnicode set to "false" in your connection properties?

Thank you,

--David Olix

JDBC Development

|||

Hi David,

Thanks for your reply. I can ensure that I didn't set the sendStringParametersAsUnicode to "false". This parameter is default set to "true" so I didn't change it in the connection properties.

I found out that the unicode Collation I used "Indic_General_90_CI_AS" had a Codepage 0 in my SQL Server 2005 installation. In fact all unicode only collations do have a codepage 0 if I check them all using the following command:

SELECT COLLATIONPROPERTY('Indic_General_90_CI_AS', 'CodePage') as CodePage

Is this perhaps related to the problem I have in Java? Because in there the exception explains about the fact that the codepage is 0.

I really hope you can help me to sort this out.

Thanks in advance for your help David.

Hugo

|||Hi David,

I found the solution. When updating a SQL Server 2005 NVARCHAR column, you have to use the following syntax:

UPDATE <table> SET <column> = N'<value>'

So for example:
UPDATE customer SET id = '€_???????￡????_???_??????_прстуф_????'

Has to be this for a SQL Server connection:

UPDATE customer SET id = N'€_???????￡????_???_??????_прстуф_????'

The prefix N before the value is required to make sure to save the string in Unicode format. I did not do this so far. Now I'm able to store and retrieve data into a SQL Server database and my collatin is now Latin1_General_CI_AS since this supports Unicode as well.

Hugo
|||

hi hugo

this is gr8 thread

i have facing a problem

i want to insert indic values in a table in SQL server

the update statement u mentioned is useful

UPDATE <table> SET <column> = N'<value>'

but when i use INSERT INTO <table> VALUES(N'<value>')

can u tell me y doesnt this work

tahnx in advance.

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

While running the code, I got error "PlatformNotSupportedException". I realized that it is a problem on different locale on the PDA and the SQL Server. So I tried to re-install the SQL Server for another collation, which is Latin1_General_CI_AI. I have also set the collation to Latin1_General_CI_AI at database-level.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer.

Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum.

Mike

|||

Thanks Mike!

"You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer."

-> Yes, I understand that I cannot change Server Setting for Collation after installation. So I DID remove the whole SQL Server Express, and then install it again. At that moment, I selected "SQL_Latin1_General_CP1_CI_AI", that's also what I can see from the SQL Server Management window. It is the same for the database collation setting, which was set to "SQL_Latin1_General_CP1_CI_AI"

So, the problem comes that the actual running in the debugger shows a different collation (Chinese Hong Kong) while it run sqlclient code in the PDA. => That is for sure a contradiction with the server setting. So, what's the problem on this case and how to solve it?

"Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum."

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

What code are you running to show the collation? Please provide a sample. Do you see similar results if you take the PDA out of the scenario and run code directly on the machine where SQL is installed?

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 AsNew SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd AsNew SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i AsInteger = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessEnvChange()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlInternalConnection.Login()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.rfidcpsDataSetTableAdapters.VehicleTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Form1.Main()

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

While running the code, I got error "PlatformNotSupportedException". I realized that it is a problem on different locale on the PDA and the SQL Server. So I tried to re-install the SQL Server for another collation, which is Latin1_General_CI_AI. I have also set the collation to Latin1_General_CI_AI at database-level.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer.

Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum.

Mike

|||

Thanks Mike!

"You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer."

-> Yes, I understand that I cannot change Server Setting for Collation after installation. So I DID remove the whole SQL Server Express, and then install it again. At that moment, I selected "SQL_Latin1_General_CP1_CI_AI", that's also what I can see from the SQL Server Management window. It is the same for the database collation setting, which was set to "SQL_Latin1_General_CP1_CI_AI"

So, the problem comes that the actual running in the debugger shows a different collation (Chinese Hong Kong) while it run sqlclient code in the PDA. => That is for sure a contradiction with the server setting. So, what's the problem on this case and how to solve it?

"Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum."

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

What code are you running to show the collation? Please provide a sample. Do you see similar results if you take the PDA out of the scenario and run code directly on the machine where SQL is installed?

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 As New SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i As Integer = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessEnvChange()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlInternalConnection.Login()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.rfidcpsDataSetTableAdapters.VehicleTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Form1.Main()

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike