Thursday, September 1, 2005

GUID's are mangled passing through DB2 DAAB in Enterprise Library

Continuing my work on the DB2 interface for my application using the DB2 DAAB, I am working extensively with GUID's. The probelm I am running into is that somehow, the Byte Array conversion or DB2 itself is changing the GUID value when it is added to the database. I have a simple Stored Procedure in DB2 that accepts a CHAR(16) FOR BIT DATA. This is handled under the covers by the AddParameter Method keyed by the dbType.GUID field. A conversion takes place from a GUID to a binary byte array.continued...

Here is the code from db2Commandwrapper.cs:
[code lang='vb']
case DbType.Guid:
guidParameters.Add(param.ParameterName, "System.Guid");
param.DB2Type = DB2Type.Binary;
param.Size = 16;
// convert Guid value to byte array only if not null
if ((value is DBNull) || (value == null))
{
param.Value = Convert.DBNull;
}
else
{
param.Value = ((Guid)value).ToByteArray();
}
break;

[/code]
Now here is the weird part, lets say I supply a GUID of value 9c1fcfe7-d68e-4a6d-b395-823bac3022f7. I run it through my stored procedure:
[code lang='sql']
CREATE PROCEDURE DB2ADMIN.PROCESSORINFOADDSINGLE ( IN v_ProcessorID CHARACTER(16) FOR BIT DATA))
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

INSERT INTO DB2ADMIN.PROCESSORINFO (PROCESSORID)
VALUES (PROCESSORINFOADDSINGLE.v_ProcessorID);

END P1
[/code]

and it comes out as as x'E7CF1F9C8ED66D4AB395823BAC3022F7'. The first 16 characters are somehow changed. Weird. If I run a select statement against it from CLP, it will not match. However, if I used a parameterized query that runs the same GUID back through the db2Commandwrapper conversion function, it matches. This will cause problems if I try to run a standard GetSqlStringCommandWrapper without using a parameterized value for the GUID.

I posted this same information to the Enterprise Library Newsgroups. Maybe I will get some feedback.

No comments:

Post a Comment