Thread: Using boolean '1' in jdbc2
I noticed that jdbc1 getBoolean allows '1', while jdbc2 does not. The following patch makes jdbc2 accept '1' also. Is this OK? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 Index: src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java =================================================================== RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,v retrieving revision 1.33 diff -c -r1.33 ResultSet.java *** src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java 2001/09/10 15:07:05 1.33 --- src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java 2001/09/13 16:43:51 *************** *** 1396,1402 **** if (s != null) { int c = s.charAt(0); ! return ((c == 't') || (c == 'T')); } return false; // SQL NULL } --- 1396,1402 ---- if (s != null) { int c = s.charAt(0); ! return ((c == 't') || (c == 'T') || (c == '1')); } return false; // SQL NULL }
Bruce Momjian writes: > I noticed that jdbc1 getBoolean allows '1', while jdbc2 does not. The > following patch makes jdbc2 accept '1' also. Is this OK? Why? Booleans always come out as 't' or 'f'. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> Bruce Momjian writes: > > > I noticed that jdbc1 getBoolean allows '1', while jdbc2 does not. The > > following patch makes jdbc2 accept '1' also. Is this OK? > > Why? Booleans always come out as 't' or 'f'. Someone emailed me privately that they wanted to use '1' for boolean. No idea why, and no idea why jdbc1 has it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Thu, 13 Sep 2001 15:29:38 -0400 (EDT), you wrote: >> Bruce Momjian writes: >> > I noticed that jdbc1 getBoolean allows '1', while jdbc2 does not. The >> > following patch makes jdbc2 accept '1' also. Is this OK? >> >> Why? Booleans always come out as 't' or 'f'. > >Someone emailed me privately that they wanted to use '1' for >boolean. No idea why, and no idea why jdbc1 has it. In jdbc1 the string to boolean conversion is implemented within getBoolean() itself, which interprets data from the PostgreSQL backend. In jdbc2 its implemented in a helper method ResultSet.toBoolean(), which is public, probably because its now also used in the new Array feature. Perhaps someone is now using ResultSet.toBoolean() as a utility method. I don't think that's a good idea. Even though its public its not part of any published interface. So anyone who wants to convert 1 to true should use his own method. We could rule this out by declaring the method protected or package (no access specifier). This doesn't explain why jdbc1 accepts '1' though. Anyone? Regards, René Pijlman <rene@lab.applinet.nl>
Bruce, This patch is fine. thanks, --Barry Bruce Momjian wrote: > I noticed that jdbc1 getBoolean allows '1', while jdbc2 does not. The > following patch makes jdbc2 accept '1' also. Is this OK? > > > > ------------------------------------------------------------------------ > > Index: src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java > =================================================================== > RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,v > retrieving revision 1.33 > diff -c -r1.33 ResultSet.java > *** src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java 2001/09/10 15:07:05 1.33 > --- src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java 2001/09/13 16:43:51 > *************** > *** 1396,1402 **** > if (s != null) > { > int c = s.charAt(0); > ! return ((c == 't') || (c == 'T')); > } > return false; // SQL NULL > } > --- 1396,1402 ---- > if (s != null) > { > int c = s.charAt(0); > ! return ((c == 't') || (c == 'T') || (c == '1')); > } > return false; // SQL NULL > } > > > ------------------------------------------------------------------------ > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > > /bjm/diff > > Content-Type: > > text/plain > Content-Encoding: > > 7bit > > > ------------------------------------------------------------------------ > Part 1.3 > > Content-Type: > > text/plain > Content-Encoding: > > binary > >
I'm that person. I'm not trying to do anything convoluted. ;-) I have a schema & data from Oracle that uses a 'fake' boolean column. (Stupid oracle doesn't support type boolean afaik - uses number). I find the easiest way to adapt the schema & data to postgresql is to *not* convert the 'fake' boolean fields (enforced in Oracle using constraints) to postgresql boolean but to use either numeric(1) or integer. Why? Because all my data (already exported) has 0's and 1's and it would be a waste to try convert all the data to true/false, which postgresql requires for a boolean field. If there is a trick which allows 0's and 1's to be imported into a boolean field, I guess I could use that instead!? So - the submitted patch allows a boolean field to be implemented using either numeric or integer fields - I think this is pretty common. An according to the archives, this has been dicussed before. Glenn On Thu, 2001-09-13 at 14:46, Rene Pijlman wrote: > On Thu, 13 Sep 2001 15:29:38 -0400 (EDT), you wrote: > >> Bruce Momjian writes: > >> > I noticed that jdbc1 getBoolean allows '1', while jdbc2 does not. The > >> > following patch makes jdbc2 accept '1' also. Is this OK? > >> > >> Why? Booleans always come out as 't' or 'f'. > > > >Someone emailed me privately that they wanted to use '1' for > >boolean. No idea why, and no idea why jdbc1 has it. > > In jdbc1 the string to boolean conversion is implemented within > getBoolean() itself, which interprets data from the PostgreSQL > backend. > > In jdbc2 its implemented in a helper method > ResultSet.toBoolean(), which is public, probably because its now > also used in the new Array feature. > > Perhaps someone is now using ResultSet.toBoolean() as a utility > method. I don't think that's a good idea. Even though its public > its not part of any published interface. So anyone who wants to > convert 1 to true should use his own method. We could rule this > out by declaring the method protected or package (no access > specifier). > > This doesn't explain why jdbc1 accepts '1' though. Anyone? > > Regards, > René Pijlman <rene@lab.applinet.nl> > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Peter, Good question. There was a mail thread on this probably about six months ago. IIRC this was done so that you could call getBoolean() on an integer column. I think that some other database supported this and they wanted to have postgres support it in the same way. I think that is when the jdbc1 code was patched to support the value '1'. I don't know why the jdbc2 code wasn't changed at the same time. thanks, --Barry Peter Eisentraut wrote: > Bruce Momjian writes: > > >>I noticed that jdbc1 getBoolean allows '1', while jdbc2 does not. The >>following patch makes jdbc2 accept '1' also. Is this OK? >> > > Why? Booleans always come out as 't' or 'f'. > >
At 22:58 13/09/2001 -0500, you wrote: >I'm that person. I'm not trying to do anything convoluted. ;-) > >I have a schema & data from Oracle that uses a 'fake' boolean column. >(Stupid oracle doesn't support type boolean afaik - uses number). I >find the easiest way to adapt the schema & data to postgresql is to >*not* convert the 'fake' boolean fields (enforced in Oracle using >constraints) to postgresql boolean but to use either numeric(1) or >integer. Why? Because all my data (already exported) has 0's and 1's >and it would be a waste to try convert all the data to true/false, which >postgresql requires for a boolean field. If there is a trick which >allows 0's and 1's to be imported into a boolean field, I guess I could >use that instead!? > >So - the submitted patch allows a boolean field to be implemented using >either numeric or integer fields - I think this is pretty common. An >according to the archives, this has been dicussed before. > >Glenn Because the "1" features is not suppossed to be in the spécification, cant you use a personnal class that is a child of the Resultset and overwrite the method ? Then - you don't have to permute all your data and this features (which is not "standard") won't be added to the Driver. My 2c Ps: I personnally does that with my MySQL Driver. I use enum('1','0') for boolean in MySql. >On Thu, 2001-09-13 at 14:46, Rene Pijlman wrote: > > On Thu, 13 Sep 2001 15:29:38 -0400 (EDT), you wrote: > > >> Bruce Momjian writes: > > >> > I noticed that jdbc1 getBoolean allows '1', while jdbc2 does not. The > > >> > following patch makes jdbc2 accept '1' also. Is this OK? > > >> > > >> Why? Booleans always come out as 't' or 'f'. > > > > > >Someone emailed me privately that they wanted to use '1' for > > >boolean. No idea why, and no idea why jdbc1 has it. > > > > In jdbc1 the string to boolean conversion is implemented within > > getBoolean() itself, which interprets data from the PostgreSQL > > backend. > > > > In jdbc2 its implemented in a helper method > > ResultSet.toBoolean(), which is public, probably because its now > > also used in the new Array feature. > > > > Perhaps someone is now using ResultSet.toBoolean() as a utility > > method. I don't think that's a good idea. Even though its public > > its not part of any published interface. So anyone who wants to > > convert 1 to true should use his own method. We could rule this > > out by declaring the method protected or package (no access > > specifier). > > > > This doesn't explain why jdbc1 accepts '1' though. Anyone? > > > > Regards, > > René Pijlman <rene@lab.applinet.nl> > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On 13 Sep 2001 22:58:23 -0500, you wrote: >I have a schema & data from Oracle that uses a 'fake' boolean column. >(Stupid oracle doesn't support type boolean afaik - uses number). I >find the easiest way to adapt the schema & data to postgresql is to >*not* convert the 'fake' boolean fields (enforced in Oracle using >constraints) to postgresql boolean but to use either numeric(1) or >integer. [...] >So - the submitted patch allows a boolean field to be implemented using >either numeric or integer fields - I think this is pretty common. Ah, I see. Well, for what its worth, this is allowed by the JDBC spec. The getXXX()/datatypes mapping table at the end of the JDBC book says mapping getBoolean() to SQL BIT is recommended, but calling it on TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, CHAR, VARCHAR, LONGVARCHAR is also allowed. In fact, I think this means our driver is required to support these mappings. The book doesn't say how the values in these types should map to true and false though. I guess this means there is no objection to the patch. But personally, I would prefer to do it right the first time for all datatypes, rather than extending the if statement for every request. Regards, René Pijlman <rene@lab.applinet.nl>
On Fri, 14 Sep 2001 11:22:53 +0200, you wrote: >Because the "1" features is not suppossed to be in the spécification I think it is. See my other posting. To be more precise: the int->boolean mapping is in the spec, but the spec doesn't say how int values should be mapped to true and false. Regards, René Pijlman <rene@lab.applinet.nl>
OK, patch applied. > Bruce, > > This patch is fine. > > thanks, > --Barry > > > Bruce Momjian wrote: > > I noticed that jdbc1 getBoolean allows '1', while jdbc2 does not. The > > following patch makes jdbc2 accept '1' also. Is this OK? > > > > > > > > ------------------------------------------------------------------------ > > > > Index: src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java > > =================================================================== > > RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,v > > retrieving revision 1.33 > > diff -c -r1.33 ResultSet.java > > *** src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java 2001/09/10 15:07:05 1.33 > > --- src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java 2001/09/13 16:43:51 > > *************** > > *** 1396,1402 **** > > if (s != null) > > { > > int c = s.charAt(0); > > ! return ((c == 't') || (c == 'T')); > > } > > return false; // SQL NULL > > } > > --- 1396,1402 ---- > > if (s != null) > > { > > int c = s.charAt(0); > > ! return ((c == 't') || (c == 'T') || (c == '1')); > > } > > return false; // SQL NULL > > } > > > > > > ------------------------------------------------------------------------ > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > /bjm/diff > > > > Content-Type: > > > > text/plain > > Content-Encoding: > > > > 7bit > > > > > > ------------------------------------------------------------------------ > > Part 1.3 > > > > Content-Type: > > > > text/plain > > Content-Encoding: > > > > binary > > > > > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026