Re: Null bind variable in where clause

From: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>
To: Chris Stuhr <cstuhr(at)ephibian(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Null bind variable in where clause
Date: 2007-05-01 14:10:16
Message-ID: 1178028616.20922.259.camel@archimedes
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Tom Lane 2007-05-01 14:51:00 Re: Null bind variable in where clause
Previous Message Richard Broersma Jr 2007-05-01 11:55:10 Re: Null bind variable in where clause