RE: Exception using IBM.Data.DB2 and DBType.Boolean

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@gmail.com] Sent: Wednesday, January 25, 2012 10:24 AM To: Waters, Benjamin Cc: informix-list@iiug.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 advantage of. 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 or 0) you will have to do that in the code. The nice thing about taking this approach 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, or by 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@scottsdaleaz.gov<mailto:BWaters@scottsdaleaz.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 the class 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.UpdatedRowStatusErrors(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.UpdateFromDataTable(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("GetAWCourtroomsUpdateCommand {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.ColumnName 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/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.net_cc.doc%2Fcom.ibm.swg.im.dbclient.adonet.doc%2Fdoc%2Fc0053251.htm) that the DB2 client treats the Boolean Informix data type as an Int16, but I am not sure how I can get my code to work 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 my application needs to be able to work on systems that do not have the IBM Data Server Drivers install