3 Replies Latest reply on Apr 16, 2002 3:34 PM by Eric Devlin

    MSSQL 2000 uniqueidentifier guid problem

    David Cowan Newbie

      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.