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(at)anoncvs(dot)postgresql(dot)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(at)postgresql(dot)org
--
Dave Cramer <Dave(at)micro-automation(dot)net>