RE: Recursive declare cursor statement with parameters...

[Zabach, Elke]

Benoit Menendez  wrote:   > Using the latest SAP DB and JDBC driver, I have the following recursive declare cursor statement: >  > declare c cursor for with recursive result(ID, PARENT) as ( >   select MESSAGE_ID, null from KMS_MESSAGE_REFERENCE where PARENT_TYPE=[$scope] and PARENT=[$department] >   union all select KMS_MESSAGE.ID, KMS_MESSAGE.PARENT from KMS_MESSAGE, result where KMS_MESSAGE.PARENT=result.ID > ) select KMS_MESSAGE.*, READ_TIME from KMS_MESSAGE, KMS_USER_MESSAGE, result where KMS_MESSAGE.ID=MESSAGE_ID(+) and USER_ID(+)=[$userID] and KMS_MESSAGE.ID=result.ID > > If the square brackets are replaced with SQL literal values, the query works correctly... >  > If I use parameters to specify [$scope] and [$department] by replacing with a '?' and using statement.setXXX, the query works correctly... >  > But if I also use a parameter to specify [$userID], I get a weird error. > com.sap.dbtech.jdbc.exceptions.DatabaseException: SAP DBTech SQL: [-4004] Unknown table name >  at com.sap.dbtech.jdbc.packet.ReplyPacket.createException(ReplyPacket.java:56) >  at com.sap.dbtech.jdbc.ConnectionSapDB.throwSQLError(ConnectionSapDB.java:698) >  at com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:379) >  at com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:281) >  at com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:333) >  at com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:252) >  at com.sap.dbtech.jdbc.CallableStatementSapDB.executeQuery(CallableStatementSapDB.java:585) >  at org.jboss.resource.adapter.jdbc.local.LocalPreparedStatement.executeQuery(LocalPreparedStatement.java:289) >  at com.kms.engine.jdbc.JDBCDataSource.select(JDBCDataSource.java:119) >  ... > It appears that parameters are not allowed in the final select of a recursive declare cursor statement. Is this true or is it a problem with the JDBC driver?    After some mailing outside the list we found out that parameter in the final select of a recursive select will sometimes result in the error -4004. If at least one parameter is compared with a column which is indexed or part of the primary key, the error will occur. If all parameters are compared with 'normal' columns (columns which are neither indexed (part of an index) nor part of the primary key of the table) the error will not occur.   No error will occur if a constant value is used instead of the parameter.   With one of the next versions we will fix the bug.   Thank you for reporting   Elke SAP Labs Berlin