----- Original Message -----
Sent: Thursday, September 19, 2002 10:34
AM
Subject: Recursive declare cursor
statement with parameters...
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?
Thanks for your help,
Benoit
