aspose file tools*
The moose likes Spring and the fly likes Failed to execute Stored Proc due to bad SQL grammar Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Frameworks » Spring
Bookmark "Failed to execute Stored Proc due to bad SQL grammar" Watch "Failed to execute Stored Proc due to bad SQL grammar" New topic
Author

Failed to execute Stored Proc due to bad SQL grammar

Rick Wojo
Greenhorn

Joined: Jun 26, 2013
Posts: 2
I'm very new to spring and interaction with DB2. Could anyone offer suggestions I things I might try to resolve my grammar problem? I've tried both simplejdbc call and jdbctemplate and see similar issues. I'm posting my code here for the jdbctemplate call. I know the connection is good because I can see it in the debugger and the stack trace is generated from the cs.execute line. The stored proc executes fine from the IBM editor.

public void springjdbctemplatetest(final DataSource dataSource) {
final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
try {
jdbcTemplate.execute(new CallableStatementCreator() {
public CallableStatement createCallableStatement(final Connection con) throws SQLException {
final CallableStatement cs = con.prepareCall("{call PR_OM_CONV_OBFS_DATA(?, ?, ?, ?, ?, ?, ?)}");

cs.setString(1, "01"); // first argument
cs.setString(2, "999999999"); // second argument
cs.setString(3, " "); // third argument
cs.setString(4, " ");
cs.registerOutParameter(4, Types.CHAR);
cs.setString(5, " ");
cs.registerOutParameter(5, Types.CHAR);
cs.setString(6, " ");
cs.registerOutParameter(6, Types.CHAR);
cs.setString(7, " ");
cs.registerOutParameter(7, Types.CHAR);

return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(final CallableStatement cs) throws SQLException {
cs.execute();
return null; // Whatever is returned here is returned from the jdbcTemplate.execute method
}
});
} catch (final org.springframework.dao.DataAccessException e) {
throw buildDataAccessException(e, procedureName);
}
}


This is the stack trace when I run my test:

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar []; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=$RPPRMDD;CALL;PR_OM_CONV_OBFS_DATA, DRIVER=3.63.75
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1030)
at com.jpmorgan.rps.ts.definedbenefit.dao.impl.UnobfuscateDataDAOImpl.springjdbctemplatetest(UnobfuscateDataDAOImpl.java:84)
at com.jpmorgan.rps.ts.definedbenefit.dao.impl.UnobfuscateDataManagerImplTest.testGetUnobfuscatedSSN(UnobfuscateDataManagerImplTest.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=$RPPRMDD;CALL;PR_OM_CONV_OBFS_DATA, DRIVER=3.63.75
at com.ibm.db2.jcc.am.fd.a(fd.java:679)
at com.ibm.db2.jcc.am.fd.a(fd.java:60)
at com.ibm.db2.jcc.am.fd.a(fd.java:127)
at com.ibm.db2.jcc.am.yn.b(yn.java:2317)
at com.ibm.db2.jcc.am.yn.c(yn.java:2300)
at com.ibm.db2.jcc.t4.cb.l(cb.java:370)
at com.ibm.db2.jcc.t4.cb.f(cb.java:98)
at com.ibm.db2.jcc.t4.q.e(q.java:81)
at com.ibm.db2.jcc.t4.sb.k(sb.java:160)
at com.ibm.db2.jcc.am.yn.jb(yn.java:2266)
at com.ibm.db2.jcc.am.zn.b(zn.java:4315)
at com.ibm.db2.jcc.am.CallableStatement.fc(CallableStatement.java:113)
at com.ibm.db2.jcc.am.CallableStatement.execute(CallableStatement.java:96)
at com.jpmorgan.rps.ts.definedbenefit.dao.impl.UnobfuscateDataDAOImpl$2.doInCallableStatement(UnobfuscateDataDAOImpl.java:104)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1014)
... 31 more

Ankit Garg
Sheriff

Joined: Aug 03, 2008
Posts: 9305
    
  17

From what I know you don't have grants to call that stored procedure based on those error codes (reference)...


SCJP 6 | SCWCD 5 | Javaranch SCJP FAQ | SCWCD Links
Rick Wojo
Greenhorn

Joined: Jun 26, 2013
Posts: 2
Thanks Ankit!
 
 
subject: Failed to execute Stored Proc due to bad SQL grammar