I recently added native support for DB2 8.2 UDB from our product and was doing quite well until I went out for deployment. Many odd errors were occurring throughout the product with the DB2 error: “ERROR [22005] [IBM][CLI Driver] CLI0112E Error in assignment. SQLSTATE=22005 ” I couldn’t understand how it had worked perfectly in development but failed in deployment. We finally tracked the difference down to the fixpak. I was developing under FP9a and deploying under FixPak 10.

I copied my NUnit testing code over to deployment and ran it and about 1/3 of all of my functions were failing. After several days of troubleshooting I finally tracked it down to the way the DB2 client handles Enums. I was passing the enums directly as stored procedure parameters with no sort of conversion into an Int32 field. After changing the code to do a Ctype(myEnum, Integer), the code is working flawlessly again. Stupid on my part to not convert them directly but Option Strict didn’t catch them in the Enterprise Library AddInParameter method. Also very strange that the behavior changed between clients. In the development environment, we were able to verify that even if the database remained on FP9 and the client changed to FP10, the error would occur. So this is a 100% client problem. I don’t have a clue how to let IBM know about this but hopefully it will get to them from one of their direct customers. Until then, remember to explicitly convert your datatypes!

I wanted to automatically run my application after setup completed. I searched high and low for this option and it just isn’t included in the Setup Project options for Visual Studio.NET 2003. It looks like it will be in 2005 though. Here is the easy way to do it

1. Add an installer class to your application with the following code:

   Public Overrides Sub Install(ByVal stateSaver As IDictionary)
   MyBase.Install(stateSaver)
   Dim Asm As System.Reflection.Assembly = System.Reflection.Assembly.GetExecutingAssembly
   System.Diagnostics.Process.Start(Asm.Location)
   End Sub
   

2. Right click on your setup project and go to View – Custom Actions
3. Right click on Install under Custom Actions and select Add Custom Action…
4. Select the primary output of your project from the application folder
5. Do the same for the Commit

That’s it. Your installer will now run your application after setup.

UPDATE 10/5/2005

I figured out why I was getting the BC2010 error finally. I decided to isolate the failing build project into it’s own directory and determine what the differences were between it and the Visual Studio build. As soon as I reloaded the project in VS.NET, I started getting errors that the signature was incorrect on the methods I was implementing for an interface. Upon further investigation I realized that I had renamed the dll in Nant when it was copied to my lib directory. So the interface that I was implementing referenced the DLL as one name internally, MSDocument.dll, and the library I was trying to build had a reference to it as well as MyCompanyDocumentStructures.dll. Although the contents were the same, I assume the name difference affected the signature enough that I was unable to implement it with a different name. Some sort of compiler information on the error would have been much more helpful but at least I know what was going wrong. I guess the moral of the story is to isolate the problem and then compare the vbproj file to the nant build file and figure out where you have differences. I am now looking forward to taking full advantage of Nant without vbc compiler errors. » Read more: Visual Studio.NET and Nant

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:

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;

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:

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

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.

Continuing the saga of DB2, I am now trying to handle GUID’s/UniqueIdentifiers from the Enterprise Library. First thing I had to get a handle on is that DB2 UDB has no datatype similar to a GUID. In Microsoft SQL they are represented as UniqueIdentifiers and used quite extensively. Oracle represents them as RAW(16). When I ran the conversion on my database using the IBM Migration Toolkit (MTK), the GUID’s in my database were converted to CHARACTER(16) FOR BIT DATA. So basically, a container to hold 16 bytes worth of data. It takes 2 hexadecimal characters to represent 1 byte so 32 hexadecimal characters is 16 bytes or 128-bits. » Read more: Handling GUID's (UniqueIdentifiers) in DB2 using VB.NET