|
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? Mhm, we cannot
reproduce the problem.
There is no
restriction on the number of parameters or the position in the statement (last
select or something).
We checked and
there was no error like this (but we had to imagine how the tables could look
like).
If you did not
find a simple writing error in your test case, then please
provide
the table
definitions, the select in the form where the error occurs
and
please provide a
vtrace (see http://sapdb.2scale.net/moin.cgi/VTrace)
but please
change default in step 1 and 6
to default order .
The we have a
chance to see what may be different with your select to
our checked
selects.
Elke
SAP Labs
Berlin
|
- Recursive declare cursor statement with parameters... Benoit Menendez
