Solved QODBC for MS Access doesn't support QSqlQuery::lastInsertId
-
I work with MS Access database (.accdb) and I need to insert some data and then get the last inserted ID. When I use QSqlQuery::lastInsertId method I see the following error:
"QODBCResult::lastInsertId: not implemented for this DBMS" Error: ""
I tried to useSELECT @@IDENTITY
and similar expressions (SCOPE_IDENTITY
andIDENTITY_CURRENT
), but I get the same error.
Some time ago I usedSELECT @@IDENTITY
in VBA and that worked. So it's not about MS Access, but the driver?
Of course, I can writeSELECT MAX(ID) FROM table
, but I'm not sure it's safe.
Is there a way to get the last inserted ID working with MS Access database? -
@BrokenVoodooDoll said in QODBC for MS Access doesn't support QSqlQuery::lastInsertId:
So it's not about MS Access, but the driver?
Yes, it's the odbc driver which does not support it
Of course, I can write SELECT MAX(ID) FROM table, but I'm not sure it's safe.
Correct, but if you're the only one working on this 'database' then it's ok
Is there a way to get the last inserted ID working with MS Access database?
only with the solution you posted above
-
@BrokenVoodooDoll
I would expect you to useSELECT @@IDENTITY
as you said. You say "but I get the same error", yet the error messageQODBCResult::lastInsertId
is fromQSqlQuery::lastInsertId
so how can that relate toSELECT @@IDENTITY
? -
ODBC has always about supporting the most common functionality of SQL in a platform independent manner. It is not surprising that it won't handle an M$ specific extention. Do it in the most transportable way, as is the intention of ODBC...and it will be safe if you follow transaction isolation rules.
-
@Kent-Dorfman said in QODBC for MS Access doesn't support QSqlQuery::lastInsertId:
and it will be safe if you follow transaction isolation rules.
No, it won't. It will only work if there is only one connection and if the query does not
updateinsert more than one record. -
@Christian-Ehrlicher said in QODBC for MS Access doesn't support QSqlQuery::lastInsertId:
if the query does not update more than one record
update-> insert ? -
@JonB Thx, fixed
-
Given the example, he's asking if the single SQL is atomic, and yes it is. Furthermore, if it exists within an isolated transaction block
BEGIN
SQL
SQL
SQL
END
then it's protected, depending upon the rules in effect for transaction isolation. -
@Kent-Dorfman No. If there is a stored procedure or something else which inserts something else somewhere it won't work.
I did not say that it does not work at all - I say that there a subtle cases where it does not work and therefore the recommendation to simply use it is plain wrong. -
Finally, it turned out, that a separate query with
SELECT @@IDENTITY
works (which is quite strange for me as I thought, thatlastInserdId
executes exactly the same query). I don't know how come I haven't noticed that. So one can doquery.exec("INSERT ...");
and in the next linequery.exec("SELECT @@IDENTITY");
. But also I found out thatSCOPE_IDENTITY
andCURRENT_IDENTITY
don't work. Thank you all for your help!