Re: Null bind variable in where clause

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