Link to home
Start Free TrialLog in
Avatar of neildrichards
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_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

                  ps.setNull( 1, Types.VARCHAR );

                  rs = ps.executeQuery();
                  if (rs.next()) {
                        logger.debug("Found it!");
                        return DataHandlerUtil.loadString("ROWS_FOUND", rs);
                  }
            }
            catch (SQLException se) {
                  ZodiacException.handleThrowable(se, userId, sql, this.getClass());
            }
            finally {
                  SqlUtil.closeResultSetAndStatement(rs, 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_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

                  rs = ps.executeQuery();
                  if (rs.next()) {
                        logger.debug("Found it!");
                        return DataHandlerUtil.loadString("ROWS_FOUND", rs);
                  }
            }
            catch (SQLException se) {
                  ZodiacException.handleThrowable(se, userId, sql, this.getClass());
            }
            finally {
                  SqlUtil.closeResultSetAndStatement(rs, 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
ASKER CERTIFIED SOLUTION
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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;
Avatar of neildrichards
neildrichards

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
One more thing. You have

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forced accept.

Computer101
EE Admin