Announcement Announcement Module
Collapse
No announcement yet.
Can't use KeyHolder with INSERT ... SELECT on Oracle Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Can't use KeyHolder with INSERT ... SELECT on Oracle

    This statement works if I run it from my Oracle client (SQL Developer):

    Code:
    insert into Person (Name) select 'Bob' from dual
    It also works if I issue it via JDBC, without using a KeyHolder:

    Code:
    final PreparedStatementCreator psc = new PreparedStatementCreator() {
                
        @Override
        public PreparedStatement createPreparedStatement(Connection con)
            throws SQLException
        {
            return con.prepareStatement(
                    "insert into Person (Name) select 'Bob' from dual");
        }
    };
    jdbcOperations.update(psc);
    However I need to use a KeyHolder in order to get the ID of the newly inserted row. If I alter the above code as follows:

    Code:
    final KeyHolder keyHolder = new GeneratedKeyHolder();
    final PreparedStatementCreator psc = new PreparedStatementCreator() {
                
        @Override
        public PreparedStatement createPreparedStatement(Connection con)
            throws SQLException
        {
            return con.prepareStatement(
                "insert into Person (Name) select 'Bob' from dual",
                new String[] {"PersonID"});
        }
    };
    jdbcOperations.update(psc, keyHolder);
    ... then I get this error:
    Code:
    Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
    
        at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:94)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:842)
        at au.com.bisinfo.codecombo.logic.ImportServiceImpl.insertLoginRedirectRule(ImportServiceImpl.java:107)
        at au.com.bisinfo.codecombo.logic.ImportServiceImpl.runImport(ImportServiceImpl.java:68)
        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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
        at $Proxy8.runImport(Unknown Source)
        at au.com.bisinfo.codecombo.ui.Main.main(Main.java:39)
    Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
    
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3530)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:844)
        at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:1)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
        ... 15 more
    FWIW, everything's fine if I do an INSERT ... VALUES instead of an INSERT ... SELECT (although this doesn't help me, as I need to select things):

    Code:
    final KeyHolder keyHolder = new GeneratedKeyHolder();
    final PreparedStatementCreator psc = new PreparedStatementCreator() {
                
        @Override
        public PreparedStatement createPreparedStatement(Connection con)
            throws SQLException
        {
            return con.prepareStatement(
                "insert into Person (Name) values ('Bob')",
                new String[] {"PersonID"});
        }
    };
    jdbcOperations.update(psc, keyHolder);
    I'm using:
    • Spring JDBC 3.0.3.RELEASE
    • JDBC driver: ojdbc6.jar version 11.2.0.1.0
    • RDBMS: Oracle9i Release 9.2.0.5.0 - Production
    • commons-dbcp 1.4
    Thanks for any help.

  • #2
    i just got the same problem , anyone has some solution ,pls let me know ,thanks

    Comment

    Working...
    X