Обсуждение: Why is bool == java.sql.Types.BIT ??
Hello, I noticed that table columns that are defined as "bool" (Postgres datatype) are reported as java.sql.Types.BIT through DatabaseMetadata (or ResultSetMetaData). Is there any valid reason why they are not reported as java.sql.Types.BOOLEAN? Regards Thomas
On Wed, 23 Nov 2005, Thomas Kellerer wrote: > I noticed that table columns that are defined as "bool" (Postgres datatype) > are reported as java.sql.Types.BIT through DatabaseMetadata (or > ResultSetMetaData). > > Is there any valid reason why they are not reported as > java.sql.Types.BOOLEAN? > Because BOOLEAN is only available to JDBC3. Our driver still supports JDBC2 so we use BIT. BOOLEAN and BIT are the same thing as far as we can tell. Kris Jurka
Hi Thomas : Long ago I had the same doubt. The reason is first versions of JBDC associated the Types.BIT to the boolean of SQL, and is keeped this way for compatibility reasons, I think. - Andres Ledesma =================
Kris Jurka wrote on 23.11.2005 19:41: >> I noticed that table columns that are defined as "bool" (Postgres >> datatype) are reported as java.sql.Types.BIT through DatabaseMetadata >> (or ResultSetMetaData). >> >> Is there any valid reason why they are not reported as >> java.sql.Types.BOOLEAN? >> > > Because BOOLEAN is only available to JDBC3. Our driver still supports > JDBC2 so we use BIT. BOOLEAN and BIT are the same thing as far as we > can tell. Makes sense. I knew there was a reason :) The problem comes when supporting multiple DBMS. With MS SQL Server a bit column can only store 0/1 (which is not what the driver returns, as it uses a java.lang.Boolean) but PG only accepts the literals true/false. That's why I stumbled over this in the first place. So I'll have to check the native type rather then the JDBC type to be sure. Regards Thomas
Thomas Kellerer wrote: > The problem comes when supporting multiple DBMS. With MS SQL Server a > bit column can only store 0/1 (which is not what the driver returns, as > it uses a java.lang.Boolean) but PG only accepts the literals > true/false. That's why I stumbled over this in the first place. > > So I'll have to check the native type rather then the JDBC type to be sure. Can't you use setBoolean()/getBoolean()? I think that's specified to always work on a BIT column, whatever the real underlying type is. -O
Oliver Jowett wrote on 24.11.2005 00:50: > Thomas Kellerer wrote: > >> The problem comes when supporting multiple DBMS. With MS SQL Server a >> bit column can only store 0/1 (which is not what the driver returns, >> as it uses a java.lang.Boolean) but PG only accepts the literals >> true/false. That's why I stumbled over this in the first place. >> >> So I'll have to check the native type rather then the JDBC type to be >> sure. > > > Can't you use setBoolean()/getBoolean()? I think that's specified to > always work on a BIT column, whatever the real underlying type is. > Yes of course I can and actually I do when using PreparedStatements But when I'm generating SQL Scripts I need to use the correct literal for such a column. Regards Thomas
Thomas Kellerer wrote: > Oliver Jowett wrote on 24.11.2005 00:50: > >> Thomas Kellerer wrote: >> >>> The problem comes when supporting multiple DBMS. With MS SQL Server a >>> bit column can only store 0/1 (which is not what the driver returns, >>> as it uses a java.lang.Boolean) but PG only accepts the literals >>> true/false. That's why I stumbled over this in the first place. >>> >>> So I'll have to check the native type rather then the JDBC type to be >>> sure. >> >> >> >> Can't you use setBoolean()/getBoolean()? I think that's specified to >> always work on a BIT column, whatever the real underlying type is. >> > > Yes of course I can and actually I do when using PreparedStatements > But when I'm generating SQL Scripts I need to use the correct literal > for such a column. Well, if you're going to bypass the abstractions that JDBC gives you to deal with this sort of case, of course you will end up with DBMS-specific code.. -O
Kris Jurka wrote: > > On Wed, 23 Nov 2005, Thomas Kellerer wrote: > >> I noticed that table columns that are defined as "bool" (Postgres >> datatype) >> are reported as java.sql.Types.BIT through DatabaseMetadata (or >> ResultSetMetaData). >> >> Is there any valid reason why they are not reported as >> java.sql.Types.BOOLEAN? >> > > Because BOOLEAN is only available to JDBC3. Our driver still supports > JDBC2 so we use BIT. BOOLEAN and BIT are the same thing as far as we can > tell. > Hi, apologies in case this gets posted twice. I know this is an old thread, but I just hit this problem now and have the same question. I'm currently using postgresql-9.0-801.jdbc4.jar and can't see any reason not to use BOOLEAN. boolean and bit are not interchangeable or even compatible (except semantically); it definitely looks like a bug to me. Adrian -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-bool-java-sql-Types-BIT-tp2169629p4421529.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On 24 May 2011 21:20, aditsu <aditsu@yahoo.com> wrote: > > Kris Jurka wrote: >> >> On Wed, 23 Nov 2005, Thomas Kellerer wrote: >> >>> I noticed that table columns that are defined as "bool" (Postgres >>> datatype) >>> are reported as java.sql.Types.BIT through DatabaseMetadata (or >>> ResultSetMetaData). >>> >>> Is there any valid reason why they are not reported as >>> java.sql.Types.BOOLEAN? >>> >> >> Because BOOLEAN is only available to JDBC3. Our driver still supports >> JDBC2 so we use BIT. BOOLEAN and BIT are the same thing as far as we can >> tell. >> > > Hi, apologies in case this gets posted twice. > I know this is an old thread, but I just hit this problem now and have the > same question. I'm currently using postgresql-9.0-801.jdbc4.jar and can't > see any reason not to use BOOLEAN. > boolean and bit are not interchangeable or even compatible (except > semantically); it definitely looks like a bug to me. Can you explain what (in your application) you would do differently for a column that returned Types.BOOLEAN versus a column that returned Types.BIT? Oliver
Oliver Jowett wrote: > > Can you explain what (in your application) you would do differently > for a column that returned Types.BOOLEAN versus a column that returned > Types.BIT? > It has to do with analyzing/comparing table structures, copying tables on another server (especially when it uses a different DBMS), and generally handling column types automatically in various situations. If it returns BIT for a boolean column, then I have to add some special handling when using postgres to check for the type name and adjust accordingly. In the particular application where I found the problem, I was doing a "diff" between 2 table structures, and it was generating something like "ALTER TABLE request ADD COLUMN active bit" where "active" was supposed to be boolean. While both types seem to use Boolean on the java side, they are not compatible in postgres. Adrian -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-bool-java-sql-Types-BIT-tp2169629p4421744.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On 24 May 2011 23:05, aditsu <aditsu@yahoo.com> wrote: > > Oliver Jowett wrote: >> >> Can you explain what (in your application) you would do differently >> for a column that returned Types.BOOLEAN versus a column that returned >> Types.BIT? >> > > It has to do with analyzing/comparing table structures, copying tables on > another server (especially when it uses a different DBMS), and generally > handling column types automatically in various situations. > If it returns BIT for a boolean column, then I have to add some special > handling when using postgres to check for the type name and adjust > accordingly. > In the particular application where I found the problem, I was doing a > "diff" between 2 table structures, and it was generating something like > "ALTER TABLE request ADD COLUMN active bit" where "active" was supposed to > be boolean. While both types seem to use Boolean on the java side, they are > not compatible in postgres. Don't you have a general problem here that the JDBC metadata cannot fully represent many database-specific types? (For example, how would you distinguish BIT from BIT(n) from BIT VARYING(n)?) Oliver
Oliver Jowett wrote: > > Don't you have a general problem here that the JDBC metadata cannot > fully represent many database-specific types? > (For example, how would you distinguish BIT from BIT(n) from BIT > VARYING(n)?) > Good question, and it can indeed be an issue, although I hardly use any database-specific type (varchar with no length is the only one I can think of right now). However, it is a different issue - we are talking about a type that can be represented correctly by JDBC metadata, and I am arguing that it should be doing exactly that. While I was considering boolean to be a fundamental, standard, basic, core data type, it turns out it's not exactly so in the sql world. Apparently pg is one of very few DBMSes that follows the standard. Nevertheless, since JDBC (at least in recent versions) distinguishes between BIT and BOOLEAN, I think it's wrong not to do that when the database has a boolean type. Adrian -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-bool-java-sql-Types-BIT-tp2169629p4421835.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.