0
809

Written by

Dmitry Maslennikov
IRIS Developer Advocate, Software developer at CaretDev, Tabcorp
Follow
Question Dmitry Maslennikov · Nov 4, 2022

Get the primary id of inserted line in SQL

#InterSystems IRIS #Key Question #Python #SQL

I have a table, with autoincremented id

CREATETABLEusers (
    idSERIALNOTNULL,
    nameVARCHAR(30) NOTNULL,
    PRIMARY KEY (id)
)

I can add a new item there with an explicit id 

INSERTINTOusers (id, name) VALUES (2, 'fred')

And while my id is autoincremented, I can omit it

INSERTINTOusers (name) VALUES ('ed')

So, this time, I don't know the id, and I want to somehow get it.

I could do it with LAST_IDENTITY() function, but it just uses %RowID, and have no relation to the primary id

SELECT LAST_IDENTITY()

Gives 2, instead of 3

Is there any way, to make LAST_IDENTITY() match to the primary id key, or I just forget about it and use something else?

Product version: IRIS 2022.1
Discussion (11)0
Log in or sign up to continue

Comments

Dmitry Maslennikov · Nov 4, 2022

Found this way with IDENTITY and ALLOWIDENTITYINSERT=1

CREATETABLEusers (
	ididentityNOTNULL,
	nameVARCHAR(30) NOTNULL,
	PRIMARY KEY (id)
)
WITH %CLASSPARAMETER ALLOWIDENTITYINSERT = 1;

INSERTINTOusers (id, name) VALUES (2, 'fred');
SELECT LAST_IDENTITY();

INSERTINTOusers (name) VALUES ('ed');
SELECT LAST_IDENTITY();

Not sure if actually a good way to solve the issue

0
Dmitry Maslennikov  Nov 4, 2022 to Alexander Koblov

InterSystems DB-API Driver does not support it, yet, But yeah, this can solve it, and I like this way more.

sqlalchemy.exc.InterfaceError: (iris.dbapi._DBAPI.InterfaceError) Error parsing statement 'SET OPTION PKEY_IS_IDKEY = TRUE':
0
Dmitry Maslennikov  Nov 4, 2022 to Alexander Koblov

Hmm, it's system-wide, and now I see, that it's the wrong way. I can't use this. Are there any possibilities to have such option just for a process?

0
Dmitry Maslennikov  Nov 5, 2022 to Sean Connelly

Right, but from that point of code, I have no idea what the primary key is, I may get the table name, and that's it. And I don't want to do any extra queries, to find it out. Even the to do query with LAST_IDENTITY() seems too much for performance reasons. It's a part of Python SQLAlchemy ORM Dialect in my development right now.

0
Benjamin De Boe · Nov 7, 2022

In the case of JDBC, there's also the getGeneratedKeys() method that you may find useful.

We're looking into support for a RETURNING clause for the INSERT command as a possible enhancement (Postgres' flavour of this nonstandard clause is very nice), but don't hold your breath as we have a long list of such candidate enhancements and a much shorter list of people who can work on the surprising amount of places in our codebase that would need to be updated to accommodate such a syntax extension.

0
Dmitry Maslennikov  Nov 8, 2022 to Benjamin De Boe

Great news about RETURNING, yeah, that will help a lot, especially for external adapters.

And thanks for the tip with getGeneratedKeys

0
Dmitry Maslennikov  Nov 8, 2022 to Benjamin De Boe

Well, looks like the result of getGeneratedKeys() exactly the same as LAST_IDENTITY()

0
Robert Cemper  Nov 9, 2022 to Dmitry Maslennikov

no big surprise.
if IDKEY <> PrimaryKey then PrimaryKey is just a UNIQUE Key on some property.

I expected it as on object side you have %OpenId()  but no %OpenPrimary() or similar.
only Classmethod  <uniqueProperty>KeyOpen(...)

0