Thread: Patch AbstractJdbc1Statement.setBoolean support BIT and INTEGER columns

Patch AbstractJdbc1Statement.setBoolean support BIT and INTEGER columns

From

"Jeroen Habets"

Date:

25 March 2003, 08:23:02

-----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-----

Re: Patch AbstractJdbc1Statement.setBoolean support BIT and

From

Dave Cramer

Date:

25 March 2003, 08:43:17

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>

Re: Patch AbstractJdbc1Statement.setBoolean support BIT andINTEGER columns

From

"Jeroen Habets"

Date:

25 March 2003, 08:56:38

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

I've been doing so for two years now in the projects that use
PostgreSQL ;-)

http://www.pp-partners.nl (JDBC)
http://www.seasons.nl (MMBase CMS and JDBC)
http://www.twofoldmediamarkt.nl (EJB1.1 and JDBC)

> -----Oorspronkelijk bericht-----
> Van: Dave Cramer [mailto:Dave@micro-automation.net]
> Verzonden: Tuesday, March 25, 2003 11:43
> Aan: Jeroen Habets
> CC: pgsql-jdbc@postgresql.org
> Onderwerp: Re: [JDBC] Patch AbstractJdbc1Statement.setBoolean
> support BIT andINTEGER columns
>
>
> 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-ser
> >ver
> > /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>

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

iQA/AwUBPoAn1PIWL7P9qCbyEQJqSgCgkdZKWA9262LfPxRmczrq8QPAI4QAoKxG
EthIdvcJ4n1d/PB63z5cFX1L
=3fuH
-----END PGP SIGNATURE-----