Thread-Topic:Thread-Index:Date:Message-ID:

Art,

Based off you feedback I have tried a few things to try and get this to work with no success. I have tried making the parameter object have a type of String with a size of 1. This cause the ODBC driver to raise a Truncation exception (I knew I was truncating, but did not know it would be an exception). Next I tried removing the size limit on the parameter and did a substr of the first character in the update SQL. In ODBC this work, so I then switched to DRDA (DB2) and got same error message. Next I tried changing the SQL to use a DECODE with 1 going to't' everything else to 'f' keeping the DBType as Boolean. DRDA still gave the same exception. I then changed the DBType to Int16, still same error.

I am baffled by how to handle this. I use far too many Booleans as part of datasets to be able to convert them all to retrieve a string and then convert it to a Boolean, and the use of Data Adapters would have to be stripped out for Data Reader / writers. I have several hundred tables that would be impacted by this change and the performance of the data reads and writes would suffer as a result.

It looks like I will have to stick with ODBC and the Informix driver.

Thanks,
Ben Waters
Phone: 480-312-3101
Cell: 602-618-6471

From: Art Kagel [mailto:art.kagel (AT) gmail (DOT) com]
Sent: Wednesday, January 25, 2012 10:24 AM
To: Waters, Benjamin
Cc: informix-list (AT) iiug (DOT) org
Subject: Re: Exception using IBM.Data.DB2 and DBType.Boolean

Ben:

The DB2 driver does not understand any Informix specific types like BOOLEAN.. Fortunately Informix has some built-in casts that you can take advantageof. If you use a CHARACTER(1) host variable and use 't' for true and 'f' for false this will work fine for inserts and on fetching you can also use the character type host variables and the engine will return 't' or 'f' to your applications. If you want to translate that into a binary truth (1 or0) you will have to do that in the code. The nice thing about taking thisapproach is that it will work fine with the Informix native drivers as well as with the DRDA DB2 drivers.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com<http://www.advancedatatools.com>)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, orby inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.


On Wed, Jan 25, 2012 at 12:16 PM, Waters, Benjamin <BWaters (AT) scottsdaleaz (DOT) gov<mailto:BWaters (AT) scottsdaleaz (DOT) gov>> wrote:
I have an Informix IDS 11.1 server running on a Linux x64 server. I recently started testing using the DRDA driver and have installed the 9.7 FP5 client software.

I have a table with 2 Boolean columns in a table called AWCourtrooms and I use the System.Data.Common classes to implement the connections so that theclass factories create the specific instances I need. When I use System.Data.Odbc for the provider I can update the rows in the AWCourtrooms table changing any value needed. When I switched to the IBM.Data.DB2 and IBM.Data.Informix provider names I get the following exception when trying to update the rows:

Specified cast is not valid. System.InvalidCastException at System.Data.Common.DbDataAdapter.UpdatedRowStatusE rrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus( RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTab le(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at IBM.Data.DB2.DB2DataAdapter.Update(DataSet dataset)
at SCC.V3.Data.DataObjectBase.UpdateTableData(String P_tableName, DbTransaction P_transaction, DataSet P_dataSet) in C:\Documents and Settings\bwaters\My Documents\Visual Studio 2010\Projects\SCC.V3\SCC.V3.Data\DataObjectBase.vb :line 160

I use the following code to create the DBCommand Object and I assign it to a DBDataAdapter to perform the updates:

Private Function GetAWCourtroomsUpdateCommand() As DbCommand
Dim command As DbCommand
Dim param As DbParameter

Logger.Trace(String.Format("GetAWCourtroomsUpdateC ommand {0}", Me.GetType.FullName))

CheckDisposed()

command = AWizData.Connection.DbConnection.CreateCommand
command.CommandText = "UPDATE AWCourtrooms SET Virtual = ?,Restricted = ?,DefaultTime = ?,AWBoilerPlateID = ?,BailiffPhone = ? WHERE Courtroom = ?;"

param = command.CreateParameter
param.ParameterName = "@Virtual"
param.SourceColumn = DataSet.AWCourtrooms.VirtualColumn.ColumnName
param.DbType = DbType.Boolean
command.Parameters.Add(param)

param = command.CreateParameter
param.ParameterName = "@Restricted"
param.SourceColumn = DataSet.AWCourtrooms.RestrictedColumn.ColumnName
param.DbType = DbType.Boolean
command.Parameters.Add(param)

param = command.CreateParameter
param.ParameterName = "@DefaultTime"
param.SourceColumn = DataSet.AWCourtrooms.DefaultTimeColumn.ColumnName
param.DbType = DbType.DateTime
command.Parameters.Add(param)

param = command.CreateParameter
param.ParameterName = "@AWBoilerPlateID"
param.SourceColumn = DataSet.AWCourtrooms.AWBoilerPlateIDColumn.ColumnN ame
param.DbType = DbType.Int32
command.Parameters.Add(param)

param = command.CreateParameter
param.ParameterName = "@BailiffPhone"
param.SourceColumn = DataSet.AWCourtrooms.BailiffPhoneColumn.ColumnName
param.DbType = DbType.String
command.Parameters.Add(param)

'Original
param = command.CreateParameter
param.Direction = ParameterDirection.Input
param.SourceVersion = DataRowVersion.Original
param.ParameterName = "@Courtroom_in"
param.SourceColumn = DataSet.AWCourtrooms.CourtroomColumn.ColumnName
param.DbType = DbType.Int32
command.Parameters.Add(param)

Return command
End Function

I have seen in the documentation (http://publib.boulder.ibm.com/infoce...2Fc0053251.htm) that the DB2 client treats the BooleanInformix data type as an Int16, but I am not sure how I can get my code towork correctly in saving the values using the Common programming approach.

Can someone assist me in updating my code so that it works correctly, I cannot change to a implementation specific coding because the deployment of myapplication needs to be able to work on systems that do not have the IBM Data Server Drivers installed (hence the ODBC).

Thanks,
Ben Waters
Systems Integrator
Scottsdale City Court
_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org<ma...iiug (DOT) org>
http://www.iiug.org/mailman/listinfo/informix-list