Thread: Null bind variable in where clause
It looks like you can not use a bind variable when trying to match null
in a where clause. Is there any way to get the desired effect without
having to remove the bind variable and rewrite the query to "WHERE b IS
null"?
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://10.20.1.70:5432/firm", "gaiam", "");
try {
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
sql = "INSERT INTO t(a,b) VALUES (3,null)";
stmt.executeUpdate(sql);
stmt.close();
} catch (SQLException sqle) {
sqle.printStackTrace();
}
sql = "UPDATE t SET a = ? WHERE b = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 4);
pstmt.setObject(2, null);
int x = pstmt.executeUpdate();
System.out.println(x);
pstmt.close();
Statement stmt = conn.createStatement();
stmt.executeUpdate("DROP TABLE t");
stmt.close();
conn.close();
}
}
Will print 0 instead of 1
-Chris
This is a server issue.
If you really want this behaviour then
set
transform_null_equals = off to on
in postgresql.conf
Dave
On 30-Apr-07, at 9:34 PM, Chris Stuhr wrote:
> It looks like you can not use a bind variable when trying to match
> null in a where clause. Is there any way to get the desired effect
> without having to remove the bind variable and rewrite the query to
> "WHERE b IS null"?
>
> 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://10.20.1.70:5432/firm", "gaiam", "");
> try {
> Statement stmt = conn.createStatement();
> stmt.executeUpdate(sql);
> sql = "INSERT INTO t(a,b) VALUES (3,null)";
> stmt.executeUpdate(sql);
> stmt.close();
> } catch (SQLException sqle) {
> sqle.printStackTrace();
> }
>
> sql = "UPDATE t SET a = ? WHERE b = ?";
> PreparedStatement pstmt = conn.prepareStatement(sql);
> pstmt.setInt(1, 4);
> pstmt.setObject(2, null);
> int x = pstmt.executeUpdate();
> System.out.println(x);
> pstmt.close();
> Statement stmt = conn.createStatement();
> stmt.executeUpdate("DROP TABLE t");
> stmt.close();
> conn.close();
> }
> }
>
> Will print 0 instead of 1
>
> -Chris
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
I am just a beginner at JDBC, but another why to achieve this it to rewrite your query this way:
> > "UPDATE t SET a = ? WHERE b = ?";
becomes:
UPDATE T SET a = ? WHERE ( b = ? ) IS KNOWN OR ( b = ? ) IS UNKNOWN;
or you can get the same effect by:
UPDATE T SET a = ? WHERE b = ? OR b IS NULL;
looks like the second way is probably better.
--- Dave Cramer <pg@fastcrypt.com> wrote:
> This is a server issue.
>
> If you really want this behaviour then
>
> set
> transform_null_equals = off to on
>
> in postgresql.conf
>
>
> Dave
> On 30-Apr-07, at 9:34 PM, Chris Stuhr wrote:
>
> > It looks like you can not use a bind variable when trying to match
> > null in a where clause. Is there any way to get the desired effect
> > without having to remove the bind variable and rewrite the query to
> > "WHERE b IS null"?
> >
> > 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://10.20.1.70:5432/firm", "gaiam", "");
> > try {
> > Statement stmt = conn.createStatement();
> > stmt.executeUpdate(sql);
> > sql = "INSERT INTO t(a,b) VALUES (3,null)";
> > stmt.executeUpdate(sql);
> > stmt.close();
> > } catch (SQLException sqle) {
> > sqle.printStackTrace();
> > }
> >
> > sql = "UPDATE t SET a = ? WHERE b = ?";
> > PreparedStatement pstmt = conn.prepareStatement(sql);
> > pstmt.setInt(1, 4);
> > pstmt.setObject(2, null);
> > int x = pstmt.executeUpdate();
> > System.out.println(x);
> > pstmt.close();
> > Statement stmt = conn.createStatement();
> > stmt.executeUpdate("DROP TABLE t");
> > stmt.close();
> > conn.close();
> > }
> > }
> >
> > Will print 0 instead of 1
> >
> > -Chris
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
Others have already given good suggestions, but just as a matter of
form, you shouldn't use setObject() to set a null value. You should
always use setNull() instead. I don't think that the distinction
matters for PG, so if you're only ever going to support PG it won't
matter, but it does on some databases. Sybase is the only one that I
know definitely suffered from this, but there may be others as well.
-- Mark
On Mon, 2007-04-30 at 18:34 -0700, Chris Stuhr wrote:
> It looks like you can not use a bind variable when trying to match null
> in a where clause. Is there any way to get the desired effect without
> having to remove the bind variable and rewrite the query to "WHERE b IS
> null"?
>
> 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://10.20.1.70:5432/firm", "gaiam", "");
> try {
> Statement stmt = conn.createStatement();
> stmt.executeUpdate(sql);
> sql = "INSERT INTO t(a,b) VALUES (3,null)";
> stmt.executeUpdate(sql);
> stmt.close();
> } catch (SQLException sqle) {
> sqle.printStackTrace();
> }
>
> sql = "UPDATE t SET a = ? WHERE b = ?";
> PreparedStatement pstmt = conn.prepareStatement(sql);
> pstmt.setInt(1, 4);
> pstmt.setObject(2, null);
> int x = pstmt.executeUpdate();
> System.out.println(x);
> pstmt.close();
> Statement stmt = conn.createStatement();
> stmt.executeUpdate("DROP TABLE t");
> stmt.close();
> conn.close();
> }
> }
>
> Will print 0 instead of 1
>
> -Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
Dave Cramer <pg@fastcrypt.com> writes:
> If you really want this behaviour then
> set
> transform_null_equals = off to on
> in postgresql.conf
Don't think that will help him --- that kluge just causes "foo = NULL"
to be translated to "foo IS NULL" *when the NULL is written as a
literal constant*. He seems to want a null passed through a parameter
symbol to be treated as a normal comparable value. The short answer
is that you can't do that in SQL; better rethink your usage of NULL,
because it hasn't got the behavior you want.
regards, tom lane
Hello Chris, Another approach is ... WHERE (b=?) IS TRUE or ... WHERE (b=?) IS NOT FALSE depending which behavior you want when b IS NULL. Monday, April 30, 2007, 6:34:56 PM, you wrote: > It looks like you can not use a bind variable when trying to match null > in a where clause. Is there any way to get the desired effect without > having to remove the bind variable and rewrite the query to "WHERE b IS > null"?