Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-jdbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group