Prepared statement

[Raja kamatchi]

Hi,

I am using PreparedStatement for table retrieval. I would need to fetch the records with column value null. Table Column is VARCHAR2 type.

String query = "SELECT * FROM <table_name> WHERE <col_name> = ?";
PreparedStatement ps = c.prepareStatement(query);
ps.clearParameters();
ps.setNull(1, Types.VARCHAR);

ResultSet rs = ps.executeQuery();
while (rs.next()) {
}

Here, if I set null value, like above, for the where condition, it cannot able to fetch the record even though for this column, table value is null.

If I use the query, SELECT * FROM <table_name> WHERE <col_name> is null, it can fetch the record.


Can anyone please help. thanks

[David Harkness]

This is how SQL works. One NULL value never equals another NULL value. If you want to search for NULL values, use the "is null" idiom as you've done.

[Raja kamatchi]

Hi David,

Thanks for your immediate reply..

Actually, the input parameter is variable one..It can have some value or can be null. How can i change the query in prepared statement, based on the input param value ?
can you please advise me. thanks.

[Adeel Ansari]

ok lets assume if this code below works fine for you.

String query = "SELECT * FROM <table_name> WHERE <col_name> = ?";
PreparedStatement ps = c.prepareStatement(query);
ps.clearParameters();
ps.setNull(1, Types.VARCHAR);
 
ResultSet rs = ps.executeQuery();
while (rs.next()) {
}

now if you have some value in the string then. you are not checking the value and just setting null there. anyways try this.

String query = "SELECT * FROM TABLE_NAME WHERE COL_1";
 
if(colValue == null){
 query = query+" is null"
}else{
 query = query+" = ?"
}
 
PreparedStatement ps = c.prepareStatement(query);
ps.clearParameters();
 
if(colValue != null){
  ps.setString(1, colValue);
}
 
ResultSet rs = ps.executeQuery();
while (rs.next()) {
}

[ December 21, 2004: Message edited by: Adeel Ansari ]

[Bear Bibeault]

"Raja",

We're pleased to have you here with us in the JDBC forum, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender
[ December 21, 2004: Message edited by: Bear Bibeault ]