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

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 From: Art Kagel [mailto:art.kagel@gmail.com] Sent: Thursday, January 26, 2012 4:49 PM To: Waters, Benjamin Cc: informix-list@iiug.org Subject: 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<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 Thu, Jan 26, 2012 at 5:17 PM, Waters, Benjamin <BWaters@scottsdaleaz.gov<mailto: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]<mailto:[mailto:art.kagel@gmail.com]> Sent: Wednesday, January 25, 2012 12:12 PM To: Waters, Benjamin Cc: informix-list@iiug.org<mailto: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<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 1:46 PM, Waters, Benjamin <BWaters@scottsdaleaz.gov<mailto: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. :( Thanks, Ben Waters From: Art Kagel [mailto:art.kagel@gmail.com<mailto:art.kagel@gmail.com>] Sent: Wednesday, January 25, 2012 10:24 AM To: Waters, Benjamin Cc: informix-list@iiug.org<mailto: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