Exception using IBM.Data.DB2 and DBType.Boolean
Waters, Benjamin
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
Art Kagel
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.
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Waters, Benjamin
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. L
Thanks,
Ben Waters
Phone: 480-312-3101
Cell: 602-618-6471
Art Kagel
create function boolean_to_char( input boolean ) returns char(1);
return input::lvarchar::char(1);
end function;
create implicit cast (boolean as char(1) with boolean_to_char);
Once that is in place it should work. You can test the cast with:
select bool_column::char(1) from sometable;
Without the new cast this will return an error:
select aflag::int from tb_test3;
9634: No cast from boolean to integer.
create function boolean_to_int( input boolean ) returning int;
if (input == 't') then
return 1;
else
return 0;
end if;
end function;
create implicit cast (boolean as int with boolean_to_int);
select aflag::int from tb_test3;
(expression)
1
1
1
1
1
1
1
1
1
1
1
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
26 row(s) retrieved.
With these implicit casts in place, you should be able to fetch the data into a char(1) or an int using either driver. It should work without the explicit cast I used in the examples, but dbaccess doesn't let me specify what type of data type I want the host memory it's fetching data into to be. Worst case, you could code the casts explicitly into the projection clauses of your SELECT statements. The corresponding reverse casts are just as trivial (so I'll leave them to you) and will let you use int or char(1) to insert data as well.
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.
Waters, Benjamin
Art,
I think you may be missing my problem. My issue is not in selecting the Boolean. In ODBC and DRDA they are selecting fine, it is doing the Insert / Updates.
I think the issue is at the driver level. When I created a function called StringToBoolean that takes in a Char(5) and returns a Boolean I cannot get the DRDA to return me anything but the same exception I have been seeing. No matter how I identify the parameter. When I run an update in Server Studio with ‘t’, ‘true’, 1, ETC it updates without issue.
I tried creating the implicit cast based on your suggestion (modified to handle the insert/update scenario), but that made all my tables with a char(5) value try to convert to a Boolean (in .Net and Server Studio). I then changed (dropped and recreated) the cast to an explicit cast (based on the SQL Reference document) and the issue of not accessing the data was still present.
I am using VB.Net 4.0 and ADO.Net on the client side, in case that makes a difference.
Thanks,
Ben Waters
From: Art Kagel
[mailto:art....@gmail.com]
Sent: Wednesday, January 25, 2012 12:12 PM
To: Waters, Benjamin
Cc: inform...@iiug.org
Subject: Re: Exception using IBM.Data.DB2 and DBType.Boolean
OK, I was mistaken, there is no implicit cast from boolean to char(1), but it is simple to create one. In your database do:
From: Art Kagel [mailto:art....@gmail.com]
Art Kagel
That having the implicit cast from char to boolean caused problems inserting to CHAR type columns seems like a major bug to me. The cast should only be invoked when trying to insert a char value into a boolean column. Very odd.
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.
Waters, Benjamin
Art,
The error with the cast was occurring on SELECT from a table that has a CHAR(5) or a VARCHAR or LVARCHAR with a length of 5 characters. I agree, very odd.
Thanks,
Ben Waters
Systems Integrator
Scottsdale City Court