parameter against null evaluation fails with [-7016] Parameter spec not allowed in java program

[Joost Verhagen]

Hi there,

I have a java program which calls a simple query. In this query a parameter
is evaluated against null and if it is not it is used for further
evaluation:

select * 
from project 
where ((? is not null) and ....

Although this query is standard SQL it does not seem te run on SAPDB. When
running the application the parse of the prepared statement fails with:
[-7016] Parameter spec not allowed in this context.

This poses a mayor problem for us, since this kind of query is commonly
used.

We use SAPDB Kernel 3.0.0.8 with the JDBC driver 7.3.0.19a (also tested with
prior JDBC versions).

Any help would be appreciated.

Joost Verhagen


************* test source and result below *******************

import java.sql.*;

public class test
{
  private final String host     = "dummy";
  private final String dbname   = "dummy";
  private final String user     = "dummy";
  private final String password = "dummy";

  public static void main(String[] args)
  {
    test t = new test();
    try
    {
      t.run();
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
  }

  private void run()
  throws Exception
  {
    Connection conn = getConnection();
    runTest(conn);
    conn.close();
  }

  private Connection getConnection()
  throws Exception
  {
      Class.forName ("com.sap.dbtech.jdbc.DriverSapDB");
      return DriverManager.getConnection ("jdbc:sapdb://" + host + "/" +
dbname, user, password);
  }

  private void runTest(Connection conn)
  throws Exception
  {
    PreparedStatement ps = conn.prepareStatement("select * from project
where (? is not null) and status = 1");
  }
}

D:\test>java -cp sapdb-jdbc-bin-7.3.0.19a.jar;. test
com.sap.dbtech.jdbc.exceptions.DatabaseException: SAP DBTech SQL: [-7016]
(at 30) Parameter spec not allowed in this con
text
        at
com.sap.dbtech.jdbc.packet.ReplyPacket.createException(ReplyPacket.java:51)
        at
com.sap.dbtech.jdbc.ConnectionSapDB.throwSQLError(ConnectionSapDB.java:652)
        at
com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:336)
        at
com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:272)
        at
com.sap.dbtech.jdbc.CallableStatementSapDB.sendCommand(CallableStatementSapD
B.java:1209)
        at
com.sap.dbtech.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:559)
        at
com.sap.dbtech.jdbc.CallableStatementSapDB.doParse(CallableStatementSapDB.ja
va:209)
        at
com.sap.dbtech.jdbc.CallableStatementSapDB.constructor(CallableStatementSapD
B.java:180)
        at
com.sap.dbtech.jdbc.CallableStatementSapDB.<init>(CallableStatementSapDB.jav
a:57)
        at
com.sap.dbtech.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:54
5)
        at test.runTest(test.java:41)
        at test.run(test.java:27)
        at test.main(test.java:15)