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 (AT) scottsdaleaz (DOT) gov

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 (AT) scottsdaleaz (DOT) gov>wrote:

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 (AT) scottsdaleaz (DOT) gov>wrote: