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();
}
}
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. |