neildrichards
asked on
JDBC - PreparedStatement.setNull() sending 'NULL' to database
I currently have a problem using PreparedStatement.setNull( ) which I'm hoping someone will be able to solve.
We are using an Oracle 10 database, and whenever setNull(pos, Types.VARCHAR) is used, the parameter value received by the database is the word 'NULL' (according to the v$sql_bind_capture view).
I wrote a short method to test and illustrate the problem. BENEFICIARY_ID is a VARCHAR2(50). test1() finds no rows, but test2() does.
public String test1() throws ApplicationException {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID = ?";
try {
c = getConnection();
ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSE NSITIVE, ResultSet.CONCUR_READ_ONLY );
ps.setNull( 1, Types.VARCHAR );
rs = ps.executeQuery();
if (rs.next()) {
logger.debug("Found it!");
return DataHandlerUtil.loadString ("ROWS_FOU ND", rs);
}
}
catch (SQLException se) {
ZodiacException.handleThro wable(se, userId, sql, this.getClass());
}
finally {
SqlUtil.closeResultSetAndS tatement(r s, ps, this.getClass());
returnConnection(c);
}
logger.debug("Didn't find it!");
return null;
}
/**
*
* @return
* @throws ApplicationException
*/
public String test2() throws ApplicationException {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID is null";
try {
c = getConnection();
ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSE NSITIVE, ResultSet.CONCUR_READ_ONLY );
rs = ps.executeQuery();
if (rs.next()) {
logger.debug("Found it!");
return DataHandlerUtil.loadString ("ROWS_FOU ND", rs);
}
}
catch (SQLException se) {
ZodiacException.handleThro wable(se, userId, sql, this.getClass());
}
finally {
SqlUtil.closeResultSetAndS tatement(r s, ps, this.getClass());
returnConnection(c);
}
logger.debug("Didn't find it!");
return null;
}
I'm using Java 1.5 on Tomcat, Oracle 10g db, and ojdbc14.jar (version - 10.2.0.1.0).
Could someone please tell me where I'm going wrong?
Thanks in advance,
Neil Richards
We are using an Oracle 10 database, and whenever setNull(pos, Types.VARCHAR) is used, the parameter value received by the database is the word 'NULL' (according to the v$sql_bind_capture view).
I wrote a short method to test and illustrate the problem. BENEFICIARY_ID is a VARCHAR2(50). test1() finds no rows, but test2() does.
public String test1() throws ApplicationException {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID = ?";
try {
c = getConnection();
ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSE
ps.setNull( 1, Types.VARCHAR );
rs = ps.executeQuery();
if (rs.next()) {
logger.debug("Found it!");
return DataHandlerUtil.loadString
}
}
catch (SQLException se) {
ZodiacException.handleThro
}
finally {
SqlUtil.closeResultSetAndS
returnConnection(c);
}
logger.debug("Didn't find it!");
return null;
}
/**
*
* @return
* @throws ApplicationException
*/
public String test2() throws ApplicationException {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID is null";
try {
c = getConnection();
ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSE
rs = ps.executeQuery();
if (rs.next()) {
logger.debug("Found it!");
return DataHandlerUtil.loadString
}
}
catch (SQLException se) {
ZodiacException.handleThro
}
finally {
SqlUtil.closeResultSetAndS
returnConnection(c);
}
logger.debug("Didn't find it!");
return null;
}
I'm using Java 1.5 on Tomcat, Oracle 10g db, and ojdbc14.jar (version - 10.2.0.1.0).
Could someone please tell me where I'm going wrong?
Thanks in advance,
Neil Richards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
IOW the way to test for null is IS NULL, not = <something happening to be null>
I think you would like to achieve
select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID = NULL
In Oracle world this is wrong!
The correct syntax is
select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID IS NULL <----IS !!!!
Possibly you have to change the syntax when you are trying to select NULL values.
For not NULL parameters your syntax will work.
So you have to distinguish both cases:
select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID IS NULL;
select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID = 123;
select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID = NULL
In Oracle world this is wrong!
The correct syntax is
select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID IS NULL <----IS !!!!
Possibly you have to change the syntax when you are trying to select NULL values.
For not NULL parameters your syntax will work.
So you have to distinguish both cases:
select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID IS NULL;
select distinct 'Y' as ROWS_FOUND from Z_EPAYMENT_QUEUE WHERE BENEFICIARY_ID = 123;
ASKER
Thanks for your reply, but I understand that the correct syntax when it runs on the database is 'IS NULL'. The whole point of using bind parameters is so that the sql in the db cache doesn't need to change if the parameter values change (even if they are null). It would defeat the whole purpose if every time you had a null parameter value you had to change the sql from using '=' to 'IS'. I am expecting (and have seen this before) that Oracle handles null bind parameter values correctly - what I can't understand is why it is not working now :).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One more thing. You have
ps.setNull( 1, Types.VARCHAR );
What happens if you do
ps.setNull( 1, Types.NULL); ?
ps.setNull( 1, Types.VARCHAR );
What happens if you do
ps.setNull( 1, Types.NULL); ?
>>I don't believe this to be correct.
Moreover, you are not really using bind variables anyway. You are issuing an non-dynamic query the test value of which is known in advance.
Moreover, you are not really using bind variables anyway. You are issuing an non-dynamic query the test value of which is known in advance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin