Re: Patch AbstractJdbc1Statement.setBoolean support BIT and

Jeroen,

Thanks for the patch. Have you verified that you can read a boolean from
a small int col?

ie getBoolean( "booleancol" );


Regarding SQL92 vs SQL99. In general postgres supports SQL99, so the
driver has little choice but to follow along.


Dave


On Tue, 2003-03-25 at 05:22, Jeroen Habets wrote:
> -----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-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Dave Cramer <Dave@micro-automation.net>