0

I get an "Invalid SQL statement" Exception while I try to invoke an MaxDB DBPROC (stored procedure) with a StoredProcedureQuery (JPA/JPQL). Its that not the correct way to do this?

Java Environment:

> <persistence-unit name="CAMPAIGN_AD_UNIT_Test"
> transaction-type="RESOURCE_LOCAL">
>     <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
>     <exclude-unlisted-classes>false</exclude-unlisted-classes>
>     <properties>
>       <property name="javax.persistence.jdbc.driver"   value="com.sap.dbtech.jdbc.DriverSapDB" />
>       <property name="javax.persistence.jdbc.url"      value="jdbc:sapdb://localhost/MAXDB" />
>       <property name="javax.persistence.jdbc.user"     value="ST" />
>       <property name="javax.persistence.jdbc.password" value="*********" />
>       <property name="eclipselink.target-database"         value="MaxDB"/> 
>       <property name="eclipselink.ddl-generation"      value="none" />
>     </properties>   </persistence-unit>


// StoredProcedureCall
StoredProcedureQuery spQuery = jem.createStoredProcedureQuery("ST.TESTPROC");
spQuery.registerStoredProcedureParameter("CAMPAIGNS", Integer.class, ParameterMode.OUT);    
try {
  tx.begin();
  boolean success = spQuery.execute();
  System.out.println("execute : " + success);
  Object obj = spQuery.getOutputParameterValue("CAMPAIGNS");
  tx.commit();
  System.out.println("result: " + String.valueOf(obj));
} catch (RuntimeException e) {
  // roll back
  if (tx != null && tx.isActive())
 tx.rollback();
  throw e;
}

[EL Warning]: 2015-04-16 15:17:33.06--UnitOfWork(1891955695)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.sap.dbtech.jdbc.exceptions.jdbc40.SQLSyntaxErrorException: [-3005]: Invalid SQL statement
Error Code: -3005
Call: EXECUTE PROCEDURE TESTPROC(CAMPAIGNS = ?)
 bind => [1 parameter bound]
Query: ResultSetMappingQuery()
Exception in thread "main" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.sap.dbtech.jdbc.exceptions.jdbc40.SQLSyntaxErrorException: [-3005]: Invalid SQL statement
Error Code: -3005
Call: EXECUTE PROCEDURE TESTPROC(CAMPAIGNS = ?)
 bind => [1 parameter bound]
Query: ResultSetMappingQuery()
 at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:382)
 at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
 at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:316)
 at de.homemade.education.jpa.Main.main(Main.java:80)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.sap.dbtech.jdbc.exceptions.jdbc40.SQLSyntaxErrorException: [-3005]: Invalid SQL statement
Error Code: -3005
Call: EXECUTE PROCEDURE TESTPROC(CAMPAIGNS = ?)
 bind => [1 parameter bound]
...

MaxDB DBPROC SQL

> CREATE DBPROCEDURE ST.TESTPROC (OUT CAMPAIGNS INTEGER) AS 
> TRY   
> DECLARE MYCURSOR CURSOR FOR   
>     SELECT COUNT(CAMPAIGN) FROM ST.CAMPAIGN_AD;
>     FETCH MYCURSOR INTO :CAMPAIGNS; 
> CATCH     
> BEGIN         
>    STOP ($RC, 'unexpected error');        
>    CLOSE MYCURSOR;    
> END;  
> 
> Test in SQL Studio / SQL Dialog:  
> 
> call TESTPROC(?)  
> 
> result:   | Out(1) 1  | 1
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
STR
  • 1
  • 1
  • 1
    Have you ever tried to use a JDBC CallableStatement with MaxDB? Just that not all RDBMS support "stored procedures" in the same way, and if a CallableStatement will work then this JPA 2.1 way should work since it is simply a wrapper around it. Try it? – Neil Stockton Apr 16 '15 at 16:59
  • 1
    I notice that the DB call in your stack trace (`EXECUTE PROCEDURE TESTPROC(CAMPAIGNS = ?)`) is somewhat different from the one you're testing (`call TESTPROC(?)`). Have you tried running the former to see if there are some strange results? The -3005 error indicates that your command may be invalid for your current sql mode. – Eric Hughes Apr 16 '15 at 17:05
  • Thanks Neil and Eric! Eric, I've also noticed. The correct calling of a DBProc in MaxDB is "Call ST.TESTPROC(?)". I have no idea how and where I have to change SQL-Mode of EntityManager. @Neil: Yes, with a JDBC CallableStatement it works. String myCall = "call ST.TESTPROC(?)"; CallableStatement callStmt = conn.prepareCall(myCall); callStmt.registerOutParameter(1, java.sql.Types.INTEGER); int changed = callStmt.executeUpdate(); System.out.println("changed : " + changed); Object obj = callStmt.getObject(1); System.out.println("result: " + String.valueOf(obj)); – STR Apr 17 '15 at 09:59
  • @STR, maybe EclipseLink is adding on the EXECUTE PROCEDURE? I know when I do JPA StoredProcedureQuery in DataNucleus JPA it simply adds on "CALL " before the procedure name so all works fine. – Neil Stockton Apr 17 '15 at 10:06

0 Answers0