Announcement Announcement Module
No announcement yet.
Exception when calling HSQLDB Identity stored procedure. Page Title Module
Move Remove Collapse
Conversation Detail Module
  • 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

    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");
                declareParameter(new SqlOutParameter("p0", Types.INTEGER));
            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(
    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 (
    at org.springframework.jdbc.object.StoredProcedure.ex ecute(
    at$ HsqldbIdentity.execute(
    at identity(
    at Dao.testIdentity(
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De
    at java.lang.reflect.Method.invoke(
    at junit.framework.TestCase.runTest( )
    at junit.framework.TestCase.runBare( )
    at junit.framework.TestResult$1.protect(TestResult.ja va:106)
    at junit.framework.TestResult.runProtected(TestResult .java:124)
    at )
    at junit.framework.TestSuite.runTest( 08)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(

  • #2
    You can get the identity using this jdbc syntax:
    &#91;&#123;call IDENTITY&#40;&#41;&#125;&#93;
    I changed your initial code to retrieve the ResultSet returned by the jdbc call
      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;;
        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;;
      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;;
    You can also retrieve HSQLDB IDENTITY value using
    select identity&#40;&#41;


    • #3
      Originally posted by irbouho View Post
      You can also retrieve HSQLDB IDENTITY value using
      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:
      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:
      setSql("insert into as_agency_settings (id, name, ...");
      - call
      super.update(new Object[]
      - 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.

      - hsqldb from maven repo
      - spring-jdbc 2.0.1 from maven repo


      • #4
        Have a look at the 'Sequences and Identity' chapter in the HSQLDB reference manual.


        • #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.

          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.