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:
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:
9634: No cast from boolean to char.But once you create the cast it will work fine. Or simpler than that for
your purposes, create a cast to int the same way:
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.
On Wed, Jan 25, 2012 at 1:46 PM, Waters, Benjamin
<BWaters@scottsdaleaz.gov>wrote:
> 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****
>
> ** **
>
> *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)
> 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:\\Doc