Search code examples
insertsqlanywhereidentity-columnlastinsertid

Sybase JDBC get generated keys

In Postgres, I can write

INSERT .. RETURNING *

To retrieve all values that had been generated during the insert. In Oracle, HSQLDB, I can use

String[] columnNames = ...
PreparedStatement stmt = connection.prepareStatement(sql, columnNames);
// ...
stmt.execute();
stmt.getGeneratedKeys();

To retrieve all values that had been generated. MySQL is a bit limited and only returns columns that are set to AUTO_INCREMENT. But how can this be done with Sybase SQL Anywhere? The JDBC driver does not implement these methods, and there is no INSERT .. RETURNING clause, as in Postgres. Is there way to do it, other than maybe running 

SELECT @@identity

immediately after the insert?

Solution

  • My current implementation executes three consecutive SQL statements:

    -- insert the data first
INSERT INTO .. VALUES (..)

-- get the generated identity value immediately afterwards
SELECT @@identity

-- get the remaining values from the record (possibly generated by a trigger)
SELECT * FROM .. WHERE ID = :previous_identity

    The third statement can be omitted, if only the ID column is requested