#316 Cannot edit bit fields in PostgreSQL

[Rob Manning]

It's impossible to insert new rows or edit existing rows
in a table that contains a bit field - bit(n).
For a sample table here:

create table bit_table (
bit_column bit
);

PostgreSQL seems to want the following SQL:

insert into bit_table values ( B'0' );

or

insert into bit_table values ( B'1' );

or

insert into bit_table values ( null );

SQuirreL throws the following exception :

SQLException: SQLState(0A000)
java.sql.SQLException: ERROR: column "bit_column" is of
type bit but expression is of type boolean
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:282)
at
net.sourceforge.squirrel_sql.client.session.DataSetUpdateableTableModelImpl.insertRow(DataSetUpdateableTableModelImpl.java:817)
at
net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.insertRow(ContentsTab.java:477)
at
net.sourceforge.squirrel_sql.fw.datasetviewer.DataSetViewerEditableTablePanel.insertRow(DataSetViewerEditableTablePanel.java:404)
at
net.sourceforge.squirrel_sql.fw.datasetviewer.RowDataInputFrame.actionPerformed(RowDataInputFrame.java:169)
at
javax.swing.AbstractButton.fireActionPerformed(Unknown
Source)
at
javax.swing.AbstractButton$Handler.actionPerformed(Unknown
Source)
at
javax.swing.DefaultButtonModel.fireActionPerformed(Unknown
Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown
Source)
at
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown
Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown
Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at
javax.swing.plaf.basic.BasicInternalFrameUI$Handler.retargetMouseEvent(Unknown
Source)
at
javax.swing.plaf.basic.BasicInternalFrameUI$Handler.forwardMouseEvent(Unknown
Source)
at
javax.swing.plaf.basic.BasicInternalFrameUI$Handler.mouseReleased(Unknown
Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown
Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at
java.awt.LightweightDispatcher.retargetMouseEvent(Unknown
Source)
at
java.awt.LightweightDispatcher.processMouseEvent(Unknown
Source)
at
java.awt.LightweightDispatcher.dispatchEvent(Unknown
Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at
net.sourceforge.squirrel_sql.client.Application$1.dispatchEvent(Application.java:172)
at
java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown
Source)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown
Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

[Rob Manning]

Logged In: YES
user_id=1287991

Hmmm... The SQL92 spec says that BIT datatype is a bit
"string" of some fixed length. So a value like '0110110' is
perfectly acceptable as long as the column is declared as
bit(7). However the JDBC spec treats this field like it can
only have length of 1 (boolean). It recommends the use of
getBoolean/setBoolean. At this point I don't
see how the BIT datatype can be fully supported with JDBC.

Rob Manning

[Rob Manning]

Logged In: YES
user_id=1287991

A little further explanation for this apparent incongruence:

From
http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html

QUOTE

8.3.3 BIT

The JDBC type BIT represents a single bit value that can be
zero or one.

SQL-92 defines an SQL BIT type. However, unlike the JDBC BIT
type, this SQL-92 BIT type can be used as a parameterized
type to define a fixed-length binary string. Fortunately,
SQL-92 also permits the use of the simple non-parameterized
BIT type to represent a single binary digit, and this usage
corresponds to the JDBC BIT type. Unfortunately, the SQL-92
BIT type is only required in "full" SQL-92 and is currently
supported by only a subset of the major databases. Portable
code may therefore prefer to use the JDBC SMALLINT type,
which is widely supported.

The recommended Java mapping for the JDBC BIT type is as a
Java boolean.

END QUOTE

So apparently, JDBC supports the use of
getBoolean/setBoolean for bit (with no parameter) and
also recommends the use of SMALLINT to JDBC driver
writers when identifying a parameterized BIT data fields
such as bit(7). Perhaps we should try setInt with the
PostgreSQL driver and see if it likes that.

Rob