New post
Question
Dmitry Maslennikov · Nov 4, 2022

Get the primary id of inserted line in SQL

#Key Question #Python #SQL #InterSystems IRIS

I have a table, with autoincremented id

CREATE TABLE users (
    id SERIAL NOT NULL,
    name VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
)

I can add a new item there with an explicit id 

INSERT INTO users (id, name) VALUES (2, 'fred')

And while my id is autoincremented, I can omit it

INSERT INTO users (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
0
0 425
Discussion (13)3
Log in or sign up to continue
Dmitry Maslennikov · Nov 4, 2022

Found this way with IDENTITY and ALLOWIDENTITYINSERT=1

CREATE TABLE users (
	id identity NOT NULL,
	name VARCHAR(30) NOT NULL,
	PRIMARY KEY (id)
)
WITH %CLASSPARAMETER ALLOWIDENTITYINSERT = 1;

INSERT INTO users (id, name) VALUES (2, 'fred');
SELECT LAST_IDENTITY();

INSERT INTO users (name) VALUES ('ed');
SELECT LAST_IDENTITY();

Not sure if actually a good way to solve the issue

0 0
Dmitry Maslennikov · Nov 4, 2022

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 0
Dmitry Maslennikov · Nov 4, 2022

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 0
Sean Connelly · Nov 5, 2022

I guess

select id from users where %ID=2

would give you 3

0 0
Dmitry Maslennikov · Nov 5, 2022

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 0
Robert Cemper · Nov 5, 2022
  • LAST_IDENTITY ()  only refers to %ROWID  and is quite volatile and just a getter for  %ROWID  
  • so you may run this query immediately following your INSERT 

      &SQL( SELECT id into :pid from users where %ID=LAST_IDENTITY() ) 
or simply
       &SQL( SELECT id into :pid from users where %ID=:%ROWID ) 

0 0
Dmitry Maslennikov · Nov 7, 2022

Yes, I could do it, if I would have more control over it. But I develop a python library, and in the method which should return that id, I can only get a table name, not a primary key. So, it makes it too complex.

0 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 0
Dmitry Maslennikov · Nov 8, 2022

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

And thanks for the tip with getGeneratedKeys

0 0
Dmitry Maslennikov · Nov 8, 2022

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

0 0
Robert Cemper · Nov 9, 2022

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 0