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) 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 > 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 installed (hence the ODBC). > > Thanks, > Ben Waters > Systems Integrator > Scottsdale City Court > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >