Re: Null bind variable in where clause

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