openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: Multibyte characters on SQL Server and Sybase
Date Mon, 11 Jan 2010 19:18:13 GMT

On Jan 11, 2010, at 10:57 AM, Michael Dick wrote:

> On Mon, Jan 11, 2010 at 12:38 PM, Craig L Russell <Craig.Russell@sun.com 
> >wrote:
>
>> Hi Mike,
>>
>>
>> On Jan 11, 2010, at 7:24 AM, Michael Dick wrote:
>>
>> Hi Craig,
>>>
>>> That sounds reasonable for this specific use case. I'm a little  
>>> leery of
>>> doing too much validation of the columnDefinition attribute,  
>>> though. It
>>> just
>>> seems pretty easy for us to get it wrong (ie converting VARCHAR to
>>> LVARCHAR
>>> based on the column length, or some other optimization).
>>>
>>
>> I'm really not suggesting that we do extensive analysis of the
>> columnDefinition. Just transforming NVARCHAR(n) which is ANSI  
>> standard SQL
>> into the dialect needed by non-ANSI databases, instead of simply  
>> passing the
>> columnDefinition as is to the DDL.
>
>
> Where would we draw the line though? Just column types that we know  
> won't
> work? I could go along with that as long as we're clear on exactly  
> what we
> will change.

I'm suggesting that we look at the columnDefinition and if it contains  
NVARCHAR or NCHAR then we do a string substitution that is mediated by  
DBDictionary and implemented by a subclass.

Craig
>
>
>>> What about adding a variable to DBDictionary along the lines of
>>> "preferNLSVarChar", and then we'll try to use the database's  
>>> specific
>>> NVARCHAR equivalent?
>>>
>>
>> That's not the issue at all. As I understand it, the application  
>> has some
>> columns that have national use characters and those specific  
>> columns need to
>> be defined to use NVARCHAR or its non-ANSI dialect. Not all columns  
>> should
>> be NVARCHAR.
>>
>
>>
>>> Marc, presumably you have different persistence unit definitions  
>>> for each
>>> database. If that's the case then you could use a different
>>> xml-mapping-file
>>> and set the columnDefinitions to the database specific type in the
>>> xml-mapping-file.
>>>
>>> Or even more hacky, you could just override the VARCHAR type in the
>>> DBDictionary. Ie add this property to persistence.xml :
>>> <property  
>>> name="openjpa.jdbc.DBDictionary(varCharTypeName=NVARCHAR)"/>
>>>
>>> Either way the application will have to know the proper type, but  
>>> at least
>>> you can make some progress.
>>>
>>
>> I think that either you or I misunderstand the issue. As I  
>> understand it,
>> the application knows the the column type (national use or not),  
>> and the
>> problem is how to get OpenJPA to generate the proper DDL for the  
>> database
>> variant.
>>
>
> It was me, thanks for setting me straight. The multiple mapping-file
> approach would work for only a few columns, but DBDictionary hacks  
> wouldn't
> be optimal for most apps.
>
> -mike
>
> On Fri, Jan 8, 2010 at 1:34 PM, Craig L Russell <Craig.Russell@sun.com
>>>> wrote:
>>>
>>>
>>>> On Jan 8, 2010, at 11:00 AM, Marc.Boudreau wrote:
>>>>
>>>>
>>>> No, the problem is that code can be run on a variety of database
>>>>> platforms
>>>>> like DB2, SQL Server, Oracle, etc...
>>>>> So if I use @Column(columnDefinition="NVARCHAR(256)"), it will  
>>>>> only work
>>>>> on
>>>>> SQL Server and Sybase, because the other database platforms don't
>>>>> recognize
>>>>> the NVARCHAR type.
>>>>>
>>>>>
>>>> I see. How about having the DataDictionary process the  
>>>> columnDefinition
>>>> in
>>>> a database-specific way? IIRC, all of the databases support  
>>>> national use
>>>> character set columns but in their own way.
>>>>
>>>> The columnDefinition is not further standardized in the  
>>>> specification so
>>>> we
>>>> can do anything we want to with it.
>>>>
>>>> We could analyze the columnDefinition and look for the ANSI  
>>>> standard
>>>> strings NCHAR(n) and NVARCHAR(n) and translate these into the
>>>> database-specific type.
>>>>
>>>> Craig
>>>>
>>>>
>>>>
>>>>> Craig L Russell wrote:
>>>>>
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> On Jan 8, 2010, at 7:53 AM, Marc Boudreau wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>> Currently, OpenJPA maps String fields to VARCHAR on SQLServer
 
>>>>>>> and
>>>>>>> Sybase.
>>>>>>> There doesn't appear to be a way to cause a String field to be
>>>>>>> mapped to
>>>>>>> NVARCHAR other than by using the @Column annotation and  
>>>>>>> settings its
>>>>>>> columnDefinition to "NVARCHAR".
>>>>>>>
>>>>>>>
>>>>>> What is the objection to using this technique on the columns  
>>>>>> that you
>>>>>> want to hold national use characters? It seems this use case is
>>>>>> exactly suited to this feature.
>>>>>>
>>>>>> At the same time, blindly using NVARCHAR
>>>>>>
>>>>>>> for all String fields is too costly in terms of storage space
 
>>>>>>> on the
>>>>>>> database.  It ends up limiting the maximum size of the column
 
>>>>>>> (less
>>>>>>> characters can fit because more bytes are used to store them).
>>>>>>>
>>>>>>> Unfortunately, the applications we write are required to be 

>>>>>>> database
>>>>>>> neutral because we support multiple vendors.
>>>>>>>
>>>>>>> I'd like to start a discussion on this matter.  Here are a  
>>>>>>> couple of
>>>>>>> points
>>>>>>> to lead us off...
>>>>>>> What's the severity of this missing functionality?
>>>>>>> Could an OpenJPA specific annotation be introduced to allow the
>>>>>>> mapping
>>>>>>> tool to use NVARCHAR instead of VARCHAR?.
>>>>>>>
>>>>>>>
>>>>>> Is the problem that the OpenJPA mapping tool doesn't support the
>>>>>> standard columnDefinition annotation in the way you want it to?
>>>>>>
>>>>>> Craig
>>>>>>
>>>>>>
>>>>>>
>>>>>>>
>>>>>>> Marc Boudreau
>>>>>>> Software Developer
>>>>>>> IBM Cognos Content Manager
>>>>>>> marc.boudreau@ca.ibm.com
>>>>>>> Phone: 613-356-6412
>>>>>>>
>>>>>>>
>>>>>> Craig L Russell
>>>>>> Architect, Sun Java Enterprise System http://db.apache.org/jdo
>>>>>> 408 276-5638 mailto:Craig.Russell@sun.com
>>>>>> P.S. A good JDO? O, Gasp!
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>> View this message in context:
>>>>>
>>>>> http://n2.nabble.com/Multibyte-characters-on-SQL-Server-and-Sybase-tp4274154p4274294.html
>>>>> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>>>>>
>>>>>
>>>> Craig L Russell
>>>> Architect, Sun Java Enterprise System http://db.apache.org/jdo
>>>> 408 276-5638 mailto:Craig.Russell@sun.com
>>>> P.S. A good JDO? O, Gasp!
>>>>
>>>>
>>>>
>> Craig L Russell
>> Architect, Sun Java Enterprise System http://db.apache.org/jdo
>> 408 276-5638 mailto:Craig.Russell@sun.com
>> P.S. A good JDO? O, Gasp!
>>
>>

Craig L Russell
Architect, Sun Java Enterprise System http://db.apache.org/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


Mime
View raw message