Exception when calling HSQLDB Identity stored procedure.

[bongosdude]

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");
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>(Unknown Source)
at org.hsqldb.jdbc.jdbcCallableStatement.<init>(Unknown 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)

[irbouho]

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;;

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

select identity&#40;&#41;
HTH

[dma_k]

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[]
{
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

[karldmoore]

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

[dma_k]

Thanks for the reply!

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 (http://fisheye.jboss.org/viewrep/Hibernate/trunk/Hibernate3/src/org/hibernate/dialect/HSQLDialect.java?r=5204).

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