CODEWITHSUNDEEP
jdbcspring-jdbcsqlanywhere
Erica Kane
Erica Kane

Reputation: 3352

getGeneratedKeys feature is not supported by this database

I am running SQL Anywhere 16.0 and trying my hand at the Spring JDBC templates. What I need to do is very simple: insert a row into a table, then get back the auto-generated ID value.

public int log() {
    SimpleJdbcInsert insertActor = 
            new SimpleJdbcInsert(ds)
            .withTableName("DBA.REQUESTS")
            .usingGeneratedKeyColumns("REQUEST_ID");

    Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("USER_ID", userId);
    parameters.put("DATA_TYPE_ID", getProductSku());
    parameters.put("PRICE", price);
    // ...more parameters

    Number requestIdNumber = insertActor.executeAndReturnKey(parameters);
    return requestIdNumber.intValue();
}

But Spring repeatedly gives me the error 

org.springframework.dao.InvalidDataAccessResourceUsageException: 
  The getGeneratedKeys feature is not supported by this database

The driver I am using should support JDBC 4.0 (the library is dbjdbc16.dll and it is in the path, and sajdbc4.jar is in the Tomcat lib directory). The relevant database connection info from Tomcat is

    <Resource auth="Container" description="Pooled connection to the web database" 
    driverClassName="sybase.jdbc4.sqlanywhere.IDriver" 
    maxActive="30" maxIdle="5" maxWait="10000" name="jdbc/web" 
    removeAbandoned="true" 
    removeAbandonedTimeout="60" 
    type="javax.sql.DataSource" 
    url="jdbc:sqlanywhere:Server=web;UID=xxx;PASSWORD=xxx;port=xxxx;LINKS=tcpip(PORT=xxxx)"/>

and the Spring application context for the datasource is

<jee:jndi-lookup id="dbDataSource" jndi-name="jdbc/web"
            expected-type="javax.sql.DataSource" />

So my question is, is there a way to configure things better so that this type of statement works? OR if the database truly does not support this on some fundamental level, is there a non-ugly alternative for me to insert the values and get back the generated id.

UPDATE: It appears that the database driver does not support this feature. Suggestions here and elsewhere were to do the INSERT than a SELECT immediately following. The problem, of course, is if another user inserts into the table between those two statements you will get the wrong value and that would be quite bad in this case.

My workaround for right now is to use a class-level lock on the relevant DAO, and do the select based on several columns (not just IDENTITY) so that I can be 99.9% sure I'm getting the same row back. It is good enough to work. That said, I'd much prefer to have a transactional way of locking the table. I don't think marking the function @Transactional would work for this, right, as that just delays the commit until all statements are successful?

Upvotes: 1

Views: 1576

Answers (1)

Graeme Perrow
Graeme Perrow

Reputation: 57248

I'm no JDBC expert at all so I can't answer the question directly, but I can tell you that the SQL Anywhere server certainly does have this ability. If the JDBC stuff isn't working, you could use the @@identity variable. Fetching select @@identity which will return the auto-generated value for the last statement.

Disclaimer: I work for SAP in SQL Anywhere engineering.

Upvotes: 0

Related Questions