IT Process Automation

Hello,

I'm using the Select from Database Operator with a JDBC Driver which is not in the list, so I use the Database type "other"

As input source "Inline Text" and the SQL statement is "SELECT * FROM KUNDE" (without the quotes)

The call fails with [DBAccess-Server] SEV9V18 STATEMENT TYPE IN PREPARECALL MUST BE CALL STATEMENT.

This is the call stack:
java.sql.SQLException: [DBAccess-Server] SEV9V18 STATEMENT TYPE IN PREPARECALL MUST BE CALL STATEMENT.
    at de.siemens.sesam.dbaccess.SesamResponder.readDBError(SesamResponder.java:670)
    at de.siemens.sesam.dbaccess.SesamResponder.readStatus(SesamResponder.java:424)
    at de.siemens.sesam.dbaccess.SesamResponder.readPrepareCall(SesamResponder.java:1125)
    at de.siemens.sesam.dbaccess.SesamPrepareStatement.doCall(SesamPrepareStatement.java:94)
    at de.siemens.sesam.dbaccess.SesamDBAccess.invokeStatement(SesamDBAccess.java:725)
    at de.siemens.sesam.dbaccess.SesamDBStatement.prepareCall(SesamDBStatement.java:888)
    at de.siemens.sesam.dbaccess.SesamPreparedStatement.<init>(SesamPreparedStatement.java:156)
    at de.siemens.sesam.dbaccess.SesamCallableStatement.<init>(SesamCallableStatement.java:70)
    at de.siemens.sesam.dbaccess.SesamConnection.prepareCall(SesamConnection.java:1844)
    at de.siemens.sesam.dbaccess.SesamConnection.prepareCall(SesamConnection.java:461)
    at com.optinuity.c2o.service.serviceoperation.jdbcserviceoperation.JDBCGenericWrapper.executeSQL(JDBCGenericWrapper.java:220)
    at com.optinuity.c2o.service.serviceoperation.jdbcserviceoperation.RunnerJDBCSelectServiceOperation.processRequestedOperation(RunnerJDBCSelectServiceOperation.java:405)
    at com.optinuity.c2o.service.serviceoperation.jdbcserviceoperation.RunnerJDBCSelectServiceOperation.run(RunnerJDBCSelectServiceOperation.java:77)
    at java.lang.Thread.run(Thread.java:745)

The JDBC Standard specifies that prepareCall is for preparing stored procedure.

For a simple SQL statement the prepareStatement should be used.

Is there a setting to change this behaviour? Or might this be an bug?

What is the database you are using?  And what is the JDBC URL that is built in Process Automation?  Perhaps a screen shot of the Connection Wizard would clarify that.  Do you have a JDBC driver for this database that you are pointing the wizard to?

It does seem like for some reason it thinks a stored procedure is involved here.  Hopefully knowing more about what database you are using will clarify what is going on here.

Hi Andrew,

thanks for the quick answer. Below is the screenshot of the Connection Wizard. The database I'm using is SESAM running on our mainfraime. AS you see we have JDBC driver for this database. It runs ok with other connectors, but not with the SELECT or QUERY database (Screenshot below for Get Version)

Is there a trace option for this Connector?

Greetings

Klaus

Thanks for the screen shots.  Can you click on the failed operator and send a screen shot of the dataset there?  It would be helpful to see what the query that is being sent looks like at the dataset level of the failed operator.

I have not seen behavior like this on other databases and unfortunately I don't have a SESAM database to test this out on.  Can you try using the expression field instead of inline text and enclosing the statement in double quotes?  Also replace the * with an actual field name.  Just want to see if these things give the same results.

Hi Andrew,

using the expression field and not using the * didn't make a difference

the screenshot of the Dataset

When I'm invoking a stored procedure e.g. "CALL MYPROC(1)" then the operator works ok. In this example the procedure does not return a value.

As screenshot the dataset after the successful invocation.

Anyway you can accomplish what you are trying to do through a stored procedure since that seems to work?

I haven't seen this sort of behavior with any other databases so I'm not sure what could be causing this.  The problem could be with the JDBC driver itself or the way PAM is using the driver.  If you would like us to research I would recommend opening a support case.

The problem has to to with the JDBC specification.
prepareCall: Note: This method is optimized for handling stored procedure call statements.
prepareStatement: Note: This method is optimized for handling parametric SQL statements

It does not state prepareCall is only for stored procedure call statements.

The SESAM/JDBC implementation does make this differentiation: prepareCall is only for handling stored procedures, prepareStatement for "standard" SQl statements.

A possible solution: a new parameter OtherDatabasePrepareType with the values "call" or "statement".

Then the burden is on the caller.

I'll open a support case.

Additional information:

the SESAM JDBC driver works with most of the Database operators.

The Select from Database and Query Database don't work currently.

I didn't test the update and insert functionality.

More information about the configuration:

The version of the JDBC-Connector: "SESAM/SQL-Server 8.0A"

The resource name is "de.siemens.sesam.dbaccess.SesamDriver"
jar (basename) "dbaccess.jar"

The operating system where the SESAM-Database is running is BS2000 Version 18

http://www.fujitsu.com/fts/products/computing/servers/mainframe/bs2000/

%BS2000-ID :      NAME              = R13BXS              
%                 VERSION           = V18.0A0000          
%                 OSD-BC-VERSION    = V09.0A0000         

Sorry, we don't have a publicly (from the internet) accessible test database.

This problem with the PREPARE STATEMENT / PREPARE CALL is fixed now. The SESAM JDBC driver removed the restriction.