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

Olaf Liepelt (olafl(at)comrad(dot)co(dot)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(at)postgresql(dot)org wrote:

> Olaf Liepelt (olafl(at)comrad(dot)co(dot)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(at)postgresql(dot)org)
>

Subject says it all

JLL

I have applied the following patch which will page psql \h. This will
appear in 7.3beta3 and in tomorrow's snapshot.

---------------------------------------------------------------------------

Jean-Luc Lachance wrote:
> Subject says it all
>
> JLL
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073