Re: Exception using IBM.Data.DB2 and DBType.Boolean
I didn't miss the point. I was playing with the SELECT rather than INSERT
or UPDATE because to really test the INSERT I would have to write some
code, but I was able to test the SELECT version and then suggest you try
the reverse casts for inserting.
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.
On Thu, Jan 26, 2012 at 5:17 PM, Waters, Benjamin
<BWaters@scottsdaleaz.gov>wrote:
> 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.kagel@gmail.com]
> *Sent:* Wednesday, January 25, 2012 12:12 PM
> *To:* Waters, Benjamin
> *Cc:* informix-list@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:
>
> 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****
>
> ****
>
> *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