Patch AbstractJdbc1Statement.setBoolean support BIT and INTEGER columns

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I use SMALLINT columns for booleans for compatibility with other DB's
(SQL92, BOOLEAN type was introduced in SQL99) . However the JDBC
driver does not support this.
It can be fixed simply by using '1' and '0' instead of 't' and 'f' in
AbstractJdbc1Statement.setBoolean(int parameterIndex, boolean x)

Actual patch:

diff -w -b -i -r1.18 AbstractJdbc1Statement.java
923c923
<         bind(parameterIndex, x ? "'t'" : "'f'", PG_BOOLEAN);
- ---
>         bind(parameterIndex, x ? "'1'" : "'0'", PG_BOOLEAN);


Column type test using psql:

pp=> CREATE TABLE mytest ( bit_col BIT, boolean_col BOOLEAN,
smallint_col SMALLINT, int_col INT );
CREATE TABLE
pp=> INSERT INTO mytest (bit_col, boolean_col, smallint_col, int_col)
VALUES ('0', '0', '0', '0');
INSERT 1669409 1
pp=> INSERT INTO mytest (bit_col, boolean_col, smallint_col, int_col)
VALUES ('1', '1', '1', '1');
INSERT 1669410 1
pp=> SELECT * FROM mytest;
 bit_col | boolean_col | smallint_col | int_col
- ---------+-------------+--------------+---------
 0       | f           |            0 |       0
 1       | t           |            1 |       1
(2 rows)


I received a response from Barry Lind asking me to check if this
functionality hadn't been removed previously because it would
introduce bugs, so:

- - I've googled a while but could not find any messages containing
patches. Loads of issues would benefit from my approach IMHO.

- - I've browsed the log for some relevant java files to no avail then:
- - I've 'updated' to revision 1.1 of the driver at
:pserver:anoncvs@anoncvs.postgresql.org:/projects/cvsroot/pgsql-server
/src/interfaces/jdbc/org/postgresql
  and saw that the first checkin already used 't' and 'f'.


I could imagine that older versions of postgresql *PERHAPS* would not
be able to handle 't' and 'f' but this could be handled using a
'haveMinimumServerVersion' approach (as in Connection)

I noticed that the postgresql documentation states that BOOLEAN is a
SQL99 data type, but I adhere to SQL92 which doesn't contain it
yet...
Note that SQL99 actually only defines the literals TRUE and FALSE
(not 't' and 'f')!!!

A different approach could be to create a driver flag to toggle
between the to kinds of behaviour...

If someone who has in-depth knowledge of the handling of BOOLEAN type
by the backend and its history could tell if previous versions will
handle '1' and '0' as BOOLEAN literals, we could safely apply this
patch or start a discussion for the 'haveMinimumServerVersion' or
driver flag work-arounds.

Met vriendelijke groet,

Jeroen Habets
Technology manager
Twofold Mediamarkt


-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBPoAf9PIWL7P9qCbyEQKe9ACgiVdZj5IB32bcrfhMrp5jo0VVmBgAn0HJ
gZTCyn0H6xecok0jb0iRb61o
=fui5
-----END PGP SIGNATURE-----