Database - Last insert ID

[azaleas]

I would like to fetch the id (auto incremented Primary Key) of last inserted element to DB. How can I do this?

Something like this:

Code: Select all

resources.ResourceName.insert();
var KeyID = last_inserd_id;

ID is generated with MySQL auto increment.

[Steve James]

The field that is mapped to your pk column should hold the value post insert.

[azaleas]

Thanks Steve. I got it

[AJDulk]

I have an Apache Derby database with an auto-increment column that is mapped in a non-table Database Resource as follows:
- Field Name: ID
- Type: BIGINT
- Length: 19
- Required: true
- Unique Key: true
- Read Only: true
- Persistent: true
- Dynamic SQL: false

Select columns: *
Select from tables: ADDRESSES
Where Clause: ID=&&ID

When I do an insert I am getting:
... ERROR ... com.ebasetech.xi.exceptions.FormRuntimeException: Field ID of resource dbAddress is required but has no value

If I turn off required then it does the insert but does not give back the ID.

I would think the required would be ignored at insert on a read-only field and I would expect the auto-increment ID to be returned but it does not seem to be working as such.

Any idea what I have done wrong that it is not working?

Here is what I have found about Derby and AutoIncrement on the web: https://db.apache.org/derby/docs/10.5/r ... togen.html

[Jon]

The Ebase field mapped to the auto-increment column will have the new value after the insert.

[AJDulk]

That is not the case. I am getting a null back or cannot even insert the record as per the description above.

This could be a Derby specific bug in Ebase.

[Jon]

Sorry, I didn't see your latest post before I replied.

An auto-increment column should have:
read only: true
persistent: true
required: false

From what you say, it inserts OK like that so presumably the database increments the column successfully. If you have debug turned on for the resource you should see a line in the log something like:

DEBUG auto-increment column ID set with value xxx

If you don't see this line, it would imply that it's not working with Derby.

[AJDulk]

Here is the log:

Code: Select all

Debug: Debug for database resource dbAddress - SQL statement:
Debug: insert into ADDRESSES (POSTCODE_NUMBER,STREET,NUMBER,POSTCODE_LETTERS,EXTRA) values (?,?,?,?,?)
Debug:   parameter1 : value 1000
Debug:   parameter2 : value Postbus
Debug:   parameter3 : value 1
Debug:   parameter4 : value AA
Debug:   parameter5 : value null
Debug: End execution of command - insert  : 11:01:04.655
Info : ** END EXECUTION OF RESTFUL WEB SERVICE getAddressNL using endpoint: byPostcodeNumber **

So nothing about the ID is there.

[Jon]

Yes you're right, it doesn't work with Derby. This is because Derby is returning false to DatabaseMetadata.supportsGetGeneratedKeys() so Ebase assumes the database doesn't provide support for generated keys. I've raised this as a bug as Derby does in fact provide some support (probably enough support) to return a single auto-increment column.