Possible regression: setNull() usage changed from 7.4 to 8.0 and up

From: Jeff Hubbach <jeff(dot)hubbach(at)chha(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Possible regression: setNull() usage changed from 7.4 to 8.0 and up
Date: 2006-04-13 23:26:56
Message-ID: r02010500-1046-FFD4AE24CB4411DAA9C0000D93451082@[205.238.79.158]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

In the process of upgrading an app, I came across a change in behavior
of PreparedStatement.setNull(). The behavior of the driver for 7.3 and
7.4 is consistent, a call to:
stmt.setNull(1,java.sql.Types.NULL);
succeeds. However, in 8.0 and up (including the 8.2 dev driver), this
call fails with a "Could not determine data type" error.

The following calls also fail:
stmt.setNull(1,java.sql.Types.OTHER);
stmt.setObject(1,null);
stmt.setObject(1,null,java.sql.Types.NULL);
stmt.setObject(1,null,java.sql.Types.OTHER);

Changelog for Version dev401 (2005-07-24):
* Improve null handling. Allow setNull(1, Types.XXX) for ARRAY, NULL,
DISTINCT, and STRUCT by mapping them to the unknown oid.

Changelog for Version 8.0-310 (2005-02-02):
* Implement the Describe Statement protocol message. Reallow untyped
nulls and get the backend to resolve the type for us (if necessary).

Changelog for Version 8.0beta1-308 (2004-11-09):
* Disallow these cases as we have insufficient type information:
setNull(i,Types.OTHER), setObject(i,null),
setObject(i,null,Types.OTHER).

Similar thread on the mailing list archives:
http://archives.postgresql.org/pgsql-jdbc/2005-02/msg00144.php

JDBC Drivers tested:
pg73jdbc3.jar
pg74.216.jdbc3.jar
postgresql-8.1-315.jdbc3.jar
postgresql-8.2dev-501.jdbc3.jar

Server Version:
PostgreSQL 8.1.3

Stack Trace (using postgresql-8.1-315.jdbc3.jar):
Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
could not determine data type of parameter $1
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(
QueryExecutorImpl.java:1512)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(
QueryExecutorImpl.java:1297)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
188)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(
AbstractJdbc2Statement.java:437)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(
AbstractJdbc2Statement.java:353)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(
AbstractJdbc2Statement.java:257)
at JDBCTest.main(JDBCTest.java:20)

Test Case:
createTestTable.sql
===================
create table test_null (test_null integer);

JDBCTest.java
=============
import java.sql.*;

public class JDBCTest {
public static void main(String[] args) throws Exception {
String url = "jdbc:postgresql://***/***";
String uname = "***";
String pword = "***";

Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection(url,uname,pword);

PreparedStatement st = conn.prepareStatement("select count(*) from
test_null where ? is null");
st.setNull(1,java.sql.Types.NULL); // Fails
//st.setNull(1,java.sql.Types.OTHER); // Fails
//st.setNull(1,java.sql.Types.INTEGER); // Works
//st.setObject(1,null); // Fails
//st.setObject(1,null,java.sql.Types.NULL); // Fails
//st.setObject(1,null,java.sql.Types.OTHER); // Fails
//st.setString(1,null); // Works
ResultSet rs = st.executeQuery();
while(rs.next()) {
System.out.print("Column 1 returned ");
System.out.println(rs.getString(1));
}
rs.close();
st.close();
conn.close();
}
}

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2006-04-14 00:07:30 Re: Possible regression: setNull() usage changed from 7.4 to
Previous Message Guy Rouillier 2006-04-13 15:46:56 Re: Date format problem with INSERT statement.