MSSQL 2000 uniqueidentifier guid problem

[dcowan]

I am trying to use CMR EJB's with mssql server guid fields which are UNIQUEIDENTIFIER in Sql Server. I have added a mapping in jboss.xml and jbosscmp-jdbc.xml like this


<java-type>java.lang.String</java-type>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>UNIQUEIDENTIFIER</sql-type>


When I set the guid field in the CMP bean it is populated in the database correctly. for example i set guid to

0E34925E-9D97-4AEC-ABCE-071D54A85FE4

If I use the query analyzer in the sql tools i get the same string back however if I then do a get on the cmp i get back

5E92340E979DEC4AABCE071D54A85FE4

I think this is an issue with the conversion from sql->jdbc->java somewhere but I am not sure were. I can use jdbc from an application straight to the database and I get the correct strings, so I think it is somewhere in the jaws mapping or in JBossCMP. I found an article on weblogic's website that may be of interest

http://www.weblogic.com/docs45/classdocs/release_notes_452_sp2.html

the snippet of importance is

CR041037: MS SQLServer has a special column type 'uniqueidentifier' with some valuable behaviors and properties. MS stores this column data in a varbinary(10) format. Previous drivers have treated this type as any other generic varbinary(10) field. However, one of the special behaviors MS provides for this column type, at least in it's front-end tools like isql, is a special string representation of this field. This change to the driver makes our getString() methods return this data in the MS-specific string representation. For example, a varbinary(10) value whose generic representation would be, 0x814B42718B2AD411B8E500104B9643BB, if it is a uniqueidentifier value, its String representation will be changed to, 71424B81-2A8B-11D4-B8E5-00104B9643BB, as isql does.


If anyone has a solution to this problem I would greatly appreciate some enlightenment.

[emdevlin]

Hey,
This is an alternative process. one.world has got a class one.util.Guid which hooks into the native guid generator. It comes with full source to generate the dll/so (win/linux) or you can use the supplied binaries.

The upshot is drop the uniqueidentifier and go with a char. I'm using this same process while porting cgi's to servlets. yum yum

Hope it helps.

[dcowan]

Thanks for the link. That looks even better than what I was trying to do. When you say you are using a char in your servlets. Do you mean that you are generating some value yourself or you are using a GUID and putting it in a char instead of UniqueId

[emdevlin]

I'm generating a String and putting it in a varchar column in the database instead of a UNIQUEIDENTIFIER.