Announcement Announcement Module
Collapse
No announcement yet.
Exception when calling HSQLDB Identity stored procedure. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Exception when calling HSQLDB Identity stored procedure.

    Please help,

    I am writing a class to invoke HSQLDB stored procedure identity. Below is my class.

    I am using Spring version 1.1

    Code:
    final public class FunctionDao extends JdbcDaoSupport {
    
        /**
         *  
         */
    
        public int identity() {
            HsqldbIdentity func = new HsqldbIdentity(getDataSource());
            
            int ret = func.execute();
    
            return ret;
        }
        
        /**
         * this calls the HSQLDB identity for recent transaction.
         * @author knguyen
         * created on Sep 16, 2004
         */
    
        private class HsqldbIdentity extends StoredProcedure {
    
            public HsqldbIdentity(DataSource ds) {
                super(ds, "IDENTITY");
                setFunction(true);
                declareParameter(new SqlOutParameter("p0", Types.INTEGER));
    
                compile();
            }
    
            public int execute() {
                Map out = execute(new HashMap());
    
                Integer reslt = null;
                if (out.size() != 0) {
    
                    List l = (List) out.get("theIdentity");
                    reslt = (Integer) l.get(0);
                }
    
                int retval = -1;
                if (reslt != null)
                    retval = reslt.intValue();
                return retval;
    
            }
    
        }
    
    }
    But when I run the test to invoke the excecute, I got this error:

    org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [{? = call IDENTITY()}] in task 'executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call IDENTITY()}]: params=[{}]]'; nested exception is java.sql.SQLException: Unknown JDBC escape sequence: {
    java.sql.SQLException: Unknown JDBC escape sequence: {
    at org.hsqldb.jdbc.jdbcConnection.nativeSQL(Unknown Source)
    at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unkno wn Source)
    at org.hsqldb.jdbc.jdbcCallableStatement.<init>(Unkno wn Source)
    at org.hsqldb.jdbc.jdbcConnection.prepareCall(Unknown Source)
    at org.apache.commons.dbcp.DelegatingConnection.prepa reCall(DelegatingConnection.java:212)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuar dConnectionWrapper.prepareCall(PoolingDataSource.j ava:268)
    at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.createCal lableStatement(CallableStatementCreatorFactory.jav a:169)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:625)
    at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:654)
    at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:100)
    at com.primustel.pas.ups.data.dao.hsqldb.FunctionDao$ HsqldbIdentity.execute(FunctionDao.java:56)
    at com.primustel.pas.ups.data.dao.hsqldb.FunctionDao. identity(FunctionDao.java:34)
    at com.primustel.pas.ups.data.dao.hsqldb.TestFunction Dao.testIdentity(TestFunctionDao.java:56)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:324)
    at junit.framework.TestCase.runTest(TestCase.java:154 )
    at junit.framework.TestCase.runBare(TestCase.java:127 )
    at junit.framework.TestResult$1.protect(TestResult.ja va:106)
    at junit.framework.TestResult.runProtected(TestResult .java:124)
    at junit.framework.TestResult.run(TestResult.java:109 )
    at junit.framework.TestCase.run(TestCase.java:118)
    at junit.framework.TestSuite.runTest(TestSuite.java:2 08)
    at junit.framework.TestSuite.run(TestSuite.java:203)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:421)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:305)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:186)

  • #2
    You can get the identity using this jdbc syntax:
    Code:
    &#91;&#123;call IDENTITY&#40;&#41;&#125;&#93;
    I changed your initial code to retrieve the ResultSet returned by the jdbc call
    Code:
      private class HsqldbIdentity extends StoredProcedure &#123;
    
        public HsqldbIdentity&#40;DataSource ds&#41; &#123;
          super&#40;ds, "IDENTITY"&#41;;
          declareParameter&#40;new SqlReturnResultSet&#40;"p0", new DemoRowMapper&#40;&#41;&#41;&#41;;
    
          compile&#40;&#41;;
        &#125;
    
        public int execute&#40;&#41; &#123;
          Map out = execute&#40;new HashMap&#40;&#41;&#41;;
          List lst = &#40;List&#41; out.get&#40;"p0"&#41;;
    
          return &#40;&#40;Integer&#41; lst.get&#40;0&#41;&#41;.intValue&#40;&#41;;
        &#125;
      &#125;
    
      private class DemoRowMapper implements RowMapper &#123;
        public Object mapRow&#40;ResultSet rs, int rowNum&#41; throws SQLException &#123;
          return new Integer&#40;rs.getInt&#40;1&#41;&#41;;
        &#125;
      &#125;
    You can also retrieve HSQLDB IDENTITY value using
    Code:
    select identity&#40;&#41;
    HTH

    Comment


    • #3
      Originally posted by irbouho View Post
      You can also retrieve HSQLDB IDENTITY value using
      Code:
      select identity()
      Hi irbouho!

      I tried to use your solution for HSQLDB stored procedure. Unfortunately, the stored procedure always return 0.

      Here is my create script:
      Code:
      CREATE TABLE as_agency_settings
      (
              id                  integer not null identity,
              name                varchar(40) not null,
              phone               varchar(40) not null,
              email               varchar(40) not null,
      
              constraint pk_as_agency_settings primary key (id)
      ) ;
      I use the following steps:
      - Created class, that extends SqlUpdate
      - in constructor initialize the parent property sql:
      Code:
      setSql("insert into as_agency_settings (id, name, ...");
      - call
      Code:
      super.update(new Object[]
      		{
      			null,
      			getAgencyName(),
      ....
      - then I tried to call SELECT IDENTITY() FROM as_agency_settings and as well as use the provided HsqldbIdentity class -- all with the same effect: 0 is returned. I also tried to change the declaration from integer not null identity to integer generated by default as identity (start with 5) not null primary key -- no effect.

      Environment:
      - hsqldb 1.8.0.1 from maven repo
      - spring-jdbc 2.0.1 from maven repo

      Comment


      • #4
        Have a look at the 'Sequences and Identity' chapter in the HSQLDB reference manual.
        http://hsqldb.org/doc/guide/ch02.html

        Comment


        • #5
          Thanks for the reply!
          Originally posted by karldmoore View Post
          Have a look at the 'Sequences and Identity' chapter in the HSQLDB reference manual.
          http://hsqldb.org/doc/guide/ch02.html
          Unfortunately, i cannot use INSERT INTO <childtable> VALUES (...,IDENTITY(),...), as I am tying to use the same classes (algos) to
          - insert into MySQL tables in production
          - insert into HSQL tables in unit tests
          So, it is necessary to avoid a lot of forks in the code to reflect the specific of MySQL or HSQL.

          I suspect, that one should call IDENTITY() should be called within one connection to HSQL. However, JdbcTemplate.execute() always opens a new connection. I know, that the problem does not exist in Hibernate.

          If one have a successful experience with HSQL + JdbcTemplate, please, post a small Java code snapshot.

          Comment

          Working...
          X