Thread: Bug #794: JDBC 7.3 PreparedStatement.setNull(..) .setObject(.., null) throws exception

Olaf Liepelt (olafl@comrad.co.nz) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
JDBC 7.3 PreparedStatement.setNull(..) .setObject(.., null) throws exception

Long Description
I'm using your JDBC driver 7.3 beta 1 (tested the developer one as well).
Folowing steps i've done:
 - Connecting to the datatbase
 - Set the connection into transaction mode
 - Created a PreparedStatement

Filled in the set column=? with setObject(column, Object);
when an Object is null the PreparedStatement.executeUpdate() throws an exception:
No value specified for parameter 3
        at org.postgresql.core.QueryExecutor.sendQuery(QueryExecutor.java:143)
        at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:65)
        at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:468)
        at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
        at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
        at comrad.database.sql.DatabaseAccess.doUpdate(DatabaseAccess.java:1497)
... my stuff

where parameter 3 was 'null'.

The same happens if I'm using the method setNull(column, type).


After the exception I'd like to rollback all changes. I get another exception when calling rollback():

java.sql.SQLException: ERROR:  Attribute 'nullqrollback' not found

        at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:126)
        at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Co
nnection.java:449)
        at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Co
nnection.java:432)
        at org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1C
onnection.java:967)
        at comrad.database.sql.DatabaseAccess.endTransaction(DatabaseAccess.jav
a:929)
... my stuff



Sample Code


No file was uploaded with this report

I cannot reproduce this bug on CVS tip, nor the 7.3b1 driver you mention.
I know about a month ago there was a problem with batched prepared
statements, but that does not seem to be the case here.  Could you send a
complete example or at least some context as to what
comrad.database.sql.DatabaseAccess.doUpdate(DatabaseAccess.java:1497)
is attempting to do?

The following works for me...

import java.sql.*;

public class a {
        public static void main(String args[]) throws Exception {
                String sql = "CREATE TABLE t(a int, b int)";
                Class.forName("org.postgresql.Driver");
                Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5730/test","test","");
                try {
                        Statement stmt = conn.createStatement();
                        stmt.executeUpdate(sql);
                        sql = "INSERT INTO t(a,b) VALUES (3,4)";
                        stmt.executeUpdate(sql);
                        stmt.close();
                } catch(SQLException sqle) {
                        sqle.printStackTrace();}

                sql = "UPDATE t SET a = ? WHERE b = ?";
                PreparedStatement pstmt = conn.prepareStatement(sql);
                pstmt.setObject(1,null);
                pstmt.setInt(2,4);
                pstmt.executeUpdate();
                pstmt.close();
                Statement stmt = conn.createStatement();
                stmt.executeUpdate("DROP TABLE t");
                stmt.close();
                conn.close();
        }
}

On Tue, 8 Oct 2002 pgsql-bugs@postgresql.org wrote:

> Olaf Liepelt (olafl@comrad.co.nz) reports a bug with a severity of 1
> The lower the number the more severe it is.
>
> Short Description
> JDBC 7.3 PreparedStatement.setNull(..) .setObject(.., null) throws exception
>
> Long Description
> I'm using your JDBC driver 7.3 beta 1 (tested the developer one as well).
> Folowing steps i've done:
>  - Connecting to the datatbase
>  - Set the connection into transaction mode
>  - Created a PreparedStatement
>
> Filled in the set column=? with setObject(column, Object);
> when an Object is null the PreparedStatement.executeUpdate() throws an exception:
> No value specified for parameter 3
>         at org.postgresql.core.QueryExecutor.sendQuery(QueryExecutor.java:143)
>         at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:65)
>         at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:468)
>         at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
>         at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
>         at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
>         at comrad.database.sql.DatabaseAccess.doUpdate(DatabaseAccess.java:1497)
> ... my stuff
>
> where parameter 3 was 'null'.
>
> The same happens if I'm using the method setNull(column, type).
>
>
> After the exception I'd like to rollback all changes. I get another exception when calling rollback():
>
> java.sql.SQLException: ERROR:  Attribute 'nullqrollback' not found
>
>         at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:126)
>         at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Co
> nnection.java:449)
>         at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Co
> nnection.java:432)
>         at org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1C
> onnection.java:967)
>         at comrad.database.sql.DatabaseAccess.endTransaction(DatabaseAccess.jav
> a:929)
> ... my stuff
>
>
>
> Sample Code
>
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Subject says it all

JLL