From: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
---|---|
To: | Dave Cramer <pg(at)fastcrypt(dot)com>, 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 11:55:10 |
Message-ID: | 125886.83155.qm@web31806.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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(at)fastcrypt(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Lewis | 2007-05-01 14:10:16 | Re: Null bind variable in where clause |
Previous Message | Dave Cramer | 2007-05-01 11:21:39 | Re: Null bind variable in where clause |