Re: [Monetdb-developers] [Monetdb-sql-checkins] sql/src/backends/monet5 sql.mx, , 1.321, 1.322 sql_scenario.mx, , 1.337, 1.338
On 07-03-2009 21:41:21 +0000, Stefan de Konink wrote:
This code adds the last_id variable to MonetDB5/SQL to implement 'in SQL' access to the last updated sequence, in a similar fashion as the @@IDENTITY found in MSSQL. For future compatibility a procedure like get_last_row_id() found in other databases, this function would have to return the variable found. The variable is updated when m->last_id is updated.
The code was added to have last_id support within the ODBC interface; required for phpBB compatibility.
Weird, ODBC already knows it, and more reliable, via the response of Q_UPDATE. I think it was already implemented in ODBC even? It's available in mapilib, using mapi_get_last_id.
Fabian Groffen wrote:
On 07-03-2009 21:41:21 +0000, Stefan de Konink wrote:
This code adds the last_id variable to MonetDB5/SQL to implement 'in SQL' access to the last updated sequence, in a similar fashion as the @@IDENTITY found in MSSQL. For future compatibility a procedure like get_last_row_id() found in other databases, this function would have to return the variable found. The variable is updated when m->last_id is updated.
The code was added to have last_id support within the ODBC interface; required for phpBB compatibility.
Weird, ODBC already knows it, and more reliable, via the response of Q_UPDATE. I think it was already implemented in ODBC even? It's available in mapilib, using mapi_get_last_id.
mapi_get_last_id is not used in the odbc sources. And Q_UPDATE denotes: case Q_UPDATE: /* Q_UPDATE */ /* result count generating query */ So I don't know if you were thinking about something else. I am still fighting with cvs to get my patches in diff -u format. Hence the previous off by one screwup. Stefan
On 07-03-2009 23:09:13 +0100, Stefan de Konink wrote:
The code was added to have last_id support within the ODBC interface; required for phpBB compatibility.
Weird, ODBC already knows it, and more reliable, via the response of Q_UPDATE. I think it was already implemented in ODBC even? It's available in mapilib, using mapi_get_last_id.
mapi_get_last_id is not used in the odbc sources. And Q_UPDATE denotes:
case Q_UPDATE: /* Q_UPDATE */ /* result count generating query */
Yes, but for ODBC it was extended also to return the generated number of a sequence (key) as second field. Last week I implemented the use of this field in JDBC.
So I don't know if you were thinking about something else. I am still fighting with cvs to get my patches in diff -u format. Hence the previous off by one screwup.
I'm pretty sure the information is already there, and that ODBC should use that information instead of a separate query which is prone to transaction race conditions and more. I was under the impression the extra information was added because ODBC needed it.
Fabian Groffen wrote:
On 07-03-2009 23:09:13 +0100, Stefan de Konink wrote:
The code was added to have last_id support within the ODBC interface; required for phpBB compatibility. Weird, ODBC already knows it, and more reliable, via the response of Q_UPDATE. I think it was already implemented in ODBC even? It's available in mapilib, using mapi_get_last_id. mapi_get_last_id is not used in the odbc sources. And Q_UPDATE denotes:
case Q_UPDATE: /* Q_UPDATE */ /* result count generating query */
Yes, but for ODBC it was extended also to return the generated number of a sequence (key) as second field. Last week I implemented the use of this field in JDBC.
Is it a proprietary extension to the protocol? I have not found any references online to last_id other then the hacks with get_last_row_id(), @@IDENTITY, etc. If you do know a way how to get this value back using standard ODBC functions, my patch can be reverted without a problem. Then I'll just add this native ODBC implementation to phpBB, which especially leaves open place for this function.
So I don't know if you were thinking about something else. I am still fighting with cvs to get my patches in diff -u format. Hence the previous off by one screwup.
I'm pretty sure the information is already there, and that ODBC should use that information instead of a separate query which is prone to transaction race conditions and more. I was under the impression the extra information was added because ODBC needed it.
The question if it will race is only valid if one connection is reused for all ODBC connections, as far as I see now this is not the case. The use of the variable is also something that can be seen as 'stupid'... but I have no desire to defend the current implementation of other convenience functions. Stefan
On 07-03-2009 23:27:00 +0100, Stefan de Konink wrote:
mapi_get_last_id is not used in the odbc sources. And Q_UPDATE denotes:
case Q_UPDATE: /* Q_UPDATE */ /* result count generating query */
Yes, but for ODBC it was extended also to return the generated number of a sequence (key) as second field. Last week I implemented the use of this field in JDBC.
Is it a proprietary extension to the protocol? I have not found any references online to last_id other then the hacks with get_last_row_id(), @@IDENTITY, etc. If you do know a way how to get this value back using standard ODBC functions, my patch can be reverted without a problem. Then I'll just add this native ODBC implementation to phpBB, which especially leaves open place for this function.
The patch might be useful for others in any case. I'm just suprised to see ODBC doesn't seem to be able to return the ID in some way or another.
I'm pretty sure the information is already there, and that ODBC should use that information instead of a separate query which is prone to transaction race conditions and more. I was under the impression the extra information was added because ODBC needed it.
The question if it will race is only valid if one connection is reused for all ODBC connections, as far as I see now this is not the case. The use of the variable is also something that can be seen as 'stupid'... but I have no desire to defend the current implementation of other convenience functions.
Some "meta-requests" perform queries, which might overwrite your last-id, or even queries on your own stack that interleave and mess it up, hence it is safer to use the attached last-id. However, if there is no way to get it out of the interface, you're stuck with workarounds, of course.
participants (2)
-
Fabian Groffen -
Stefan de Konink