Master Detail saving problem with non-sequence type auto generated Primary Key's

[kmkale]

Hi

Smartclient version : v9.0p_2013-09-12/PowerEdition Deployment 2013-09-12

We use Postgresql 9.1 as our database. Instead of sequences, we use a 15 digit bigint column as the primary key in each of our tables. This column in all tables uses a common next_id() function to autogenerate next values.

So in our DS declarations we use autoGenerated="true" instead of type="sequence"

But this is causing problems with using the Master-Detail save functionality as described in http://www.smartclient.com/docs/9.0/a/system/reference/SmartClient_Explorer.html#FSqueuedAdd


When the master table data is saved, the resulting fetch brings back all the rows in the table instead of only the just inserted row. as a result the detail table rows are always added with id of the first row in the returned master table data.

Please find a complete sample code and server log attached.

Please suggest how to create master/detail add functionality in such a situation where the primary key is not a sequence.

[Isomorphic]

See operationBinding.cacheSyncOperation - basically you can add your own, replacement SQL for retrieving the new row, since the framework is no longer able to do this automatically based on the sequenceName.

[kmkale]

In JDBC I would do something like

PreparedStatement insert_stmt = conn.prepareStatement("INSERT INTO blah blah..",Statement.RETURN_GENERATED_KEYS);
insert_stmt.execute();
ResultSet insert_stmt_rs=insert_stmt.getGeneratedKeys();

to get the generated keys and then do the further inserts inside the transaction.

How do I use cacheSyncOperation to achieve this? Or is there another way to get the generated keys? ( In my case its simply the single id column).
Do the DML statements generated by Smartclient use Statement.RETURN_GENERATED_KEYS ( or a form of the statement like http://docs.oracle.com/javase/7/docs...va.lang.String, int[]) )?

[Isomorphic]

We're not sure whether that API works with Postgres, or with what versions of Postgres - support is pretty spotty overall across various DBs.

A sure-fire but more expensive way to do this would be to use SQL max() to find the newly inserted row because it has the largest sequence value. There may be a native SQL way to do this better, similar to SQL Server's SCOPE_IDENTITY(), but we don't immediately see one form Googling. Please let us know if you find one.

We'll also look at introducing a new API to allow you to explicitly choose between a few different forms of sequence handling, but this would be 4.1 at the soonest.

[kmkale]

FWIW I can confirm that

PreparedStatement insert_stmt = conn.prepareStatement("INSERT INTO blah blah..",Statement.RETURN_GENERATED_KEYS);
insert_stmt.execute();
ResultSet insert_stmt_rs=insert_stmt.getGeneratedKeys();

works on Postgresql 9.0 & 9.1 using postgresql-9.0-801.jdbc4 driver..