cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dov Rosenberg <drosenb...@inquira.com>
Subject Re: MS SQL Server Unicode Support
Date Wed, 20 Sep 2006 01:24:54 GMT
I opened a support case with MSFT to try to resolve this issue and paid them
$245. They ended up refunding my money because they could not find a
resolution.

This article is a posting I made regarding our issues with EOF:
http://lists.apple.com/archives/Webobjects-dev/2006/Jul/msg00743.html

Here is some more info about it:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8
_con_03_6voh.asp

This article explains in more detail the N' nomenclature. It is specific to
SQL Server.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/intlfeaturesinsqlserver2000.asp

Here is an excerpt from the above link:

Encoding of String Literals in an SQL Statement
After the SQL string itself is encoded, a technique for handling string
literals must also be employed. Essentially, the only choices here are a
string in the computer's default code page or a Unicode string: the latter
is designated by placing an "n" (for National) prefix in front of the
string, as in:


This string (the Hindi word for the Hindi language) will be converted to
"??????" if the "n" prefix is not placed there. This will also happen with
data that does have a code page, but does not match the system defaults.

Warning: Remember that the use of the "n" prefix to represent Unicode data
in both string literals and data types (nchar, nvarchar, and ntext) is
specific to SQL Server. The ANSI-92 SQL specification does define the
National character data types but does not specify them as having to be
Unicode. The ANSI-99 SQL specification (which was not complete when SQL
Server 2000 was released and will likely be discussed and modified from its
present status in regards to Unicode support) does discuss using a set of
Unicode types with a "u" prefix (for example, utext, uchar, and uvarchar).
These data types are not available in SQL Server 2000.This is not true for
some other server database products. For more information, see Interacting
with Other Database Products later in this article.



On 9/19/06 5:51 PM, "Andrus Adamchik" <andrus@objectstyle.org> wrote:

> I'll be curious to know the results. And also if there are any
> differences between MS and jTDS drivers.
> 
> If there is indeed a requirement to build prepared statements like
> "... WHERE N?" that would be extremely bizarre, but we should still
> be able to build an adapter for it.
> 
> Andrus
> 
> 
> On Sep 19, 2006, at 5:44 PM, Dov Rosenberg wrote:
> 
>> I ran into this on the EOF side so I assumed that I would have the
>> same
>> issue on the Cayenne side. How it manifested itself on the EOF side
>> was the
>> insert appeared to work correctly until we logged out and then
>> reconnected
>> with our WO app. Once we reconnected and went back to the record
>> that we
>> just inserted we saw that the chinese characters that looked good
>> before now
>> all showed ????. We checked the database and saw that the
>> characters were
>> stored as ???? As well.
>> 
>> I will try this with the cayenne stuff to verify - but since it
>> looks like
>> EOF also generates prepared statements I think the problem will
>> occur here
>> as well
>> 
>> 
>> 
>> 
>> On 9/19/06 5:26 PM, "Andrus Adamchik" <andrus@objectstyle.org> wrote:
>> 
>>> Yeah, I would think this has to be abstracted in the JDBC layer. Dov,
>>> do you have any specific problems that make you believe that this is
>>> broken?
>>> 
>>> Andrus
>>> 
>>> 
>>> On Sep 19, 2006, at 5:20 PM, Tore Halset wrote:
>>> 
>>>> Hello.
>>>> 
>>>> We are using MS SQL Server with cayenne. nvarchar works with the
>>>> old (have not tried latest as we moved to jtds) ms jdbc driver and
>>>> jtds without problems. Perhaps the prepared statement handles it
>>>> automatically as cayenne creates prepared statements?
>>>> 
>>>>  - Tore.
>>>> 
>>>> On Sep 19, 2006, at 23:12, Dov Rosenberg wrote:
>>>> 
>>>>> We are using Cayenne for some web services alongside our EOF based
>>>>> application. Our application supports unicode formatted characters
>>>>> in the
>>>>> database but in order to support it properly using MS SQL Server
>>>>> apparently
>>>>> we need to change our data types from char, varchar, text to nchar,
>>>>> nvarcahr, ntext. This is no big deal. However in order to properly
>>>>> store the
>>>>> data into those fields and to be able to query against them we
>>>>> need to
>>>>> preface all of the strings with a N ­ this signals MSSQL Server to
>>>>> use the
>>>>> Unicode encoding for that column. For example say we have a table:
>>>>> 
>>>>> CREATE TABLE "CONTENTDATA" (
>>>>>     RECORDID    NVARCHAR(64)        NOT NULL ,
>>>>>     XML         NTEXT               NULL ,
>>>>>     CONSTRAINT PK_CONTENTDATA PRIMARY KEY (RECORDID)
>>>>> );
>>>>> 
>>>>> In order to insert unicode characters into that table I need to
>>>>> generate a
>>>>> SQL statement like:
>>>>> 
>>>>> INSERT into CONTENTDATA (RECORDID, XML) values (N¹1234ABC¹,
>>>>> N¹<MYXML>....</MYXML>¹);
>>>>> 
>>>>> Notice the N in front of the strings I am inserting. In order to
>>>>> query on
>>>>> this table properly I need to add the N in front of the WHERE
>>>>> clause pieces
>>>>> such as:
>>>>> 
>>>>> SELECT * from CONTENTDATA where xml like N¹<MYXML>%¹;
>>>>> 
>>>>> I only need to put the N when the data contains unicode characters
>>>>> ­ but I
>>>>> really won¹t know that so I probably need to do it all of the time.
>>>>> 
>>>>> I am looking for the correct place in both cayenne and EOF to add
>>>>> the N¹ to
>>>>> the queries (INSERTs, and WHERE clauses). Any help or suggestions
>>>>> would be
>>>>> HIGHLY appreciated.
>>>>> 
>>>>> BTW ­ it seems that Oracle can at least tolerate this unusual SQL
>>>>> format.
>>>>> MSFT says that it is SQL 92 compliant but I haven¹t found anything
>>>>> documenting it yet.
>>>>> 
>>>>> 
>>>>> 
>>>>> -- 
>>>>> Dov Rosenberg
>>>>> Conviveon/Inquira
>>>>> Knowledge Management Experts
>>>>> http://www.conviveon.com
>>>>> http://www.inquira.com
>>>>> 
>>>>> 
>>>> 
>>>> 
>>> 
>> 
>> -- 
>> Dov Rosenberg
>> Inquira Inc
>> 370 Centerpointe Circle, ste 1178
>> Altamonte Springs, FL 32701
>> (407) 339-1177 x 102
>> (407) 339-6704 (fax)
>> (407) 310-8316 (cell)
>> drosenberg@inquira.com
>> AOL IM: dovrosenberg
>> 
>> 
>> 
> 

-- 
Dov Rosenberg
Inquira Inc
370 Centerpointe Circle, ste 1178
Altamonte Springs, FL 32701
(407) 339-1177 x 102
(407) 339-6704 (fax)
(407) 310-8316 (cell)
drosenberg@inquira.com
AOL IM: dovrosenberg



Mime
  • Unnamed multipart/related (inline, None, 0 bytes)
View raw message