1. Thomas Goertler
  2. Valentina Database ADK
  3. 木, 3月 10 2016, 11:12 AM
  4.  メールで購読
today I've a question about how to get the last inserted record id of a given sql query in .NET when using .SqlExecute command on a VServer in a asynchronous scenario on different tables.

Our scenario:
On one db client (.NET) we open a connection to a VServer.

Our client software creates data that should be inserted into the database asynchronously. Asynchronously in this context would mean that we have different master- and detail tables. First we insert a master record and get the id of that record back.

Now we have two (or more) threads that insert a few hundred records asynchronously into the one detail table each (one thread -> one detail table). Then we "collect" the inserted record ids and in the last step try to link them to the master record id.
Currently we try to get the .lastRecId from the database object to later link the records to the master record. But as soon as we use this function from the database object there may be some new records from one other thread in another table.
Then we get .lastRecId's back that belong to the wrong thread/table. Currently we get the .lastRecId through the corresponding function of our database object.

We found out that it is possible via SQL to get the last record id of a table but that would mean we've to "insert" -> then "select" and so on. Seems like some overhead to me.

So the question is: What would be the best way to get the .lastRecId from one .SqlExecute command without using the .lastRecId function from the database object and get it for each table instead?
The return data from the .SqlExecute function in .NET is only "0" or "1". It looks like this should only show sort of "false"/"true" if the query worked or not. Maybe there's another way to handle such a scenario.

Thanks in advance!

Best Regards,
There are no comments made yet.
Ivan Smahin 承諾済みの回答
Database.SqlQuery() returns QueryResult structure which has lastRecID field. But V4NET ignores it for now. Please report it as a feature request to the Mantis.
There are no comments made yet.
Ivan Smahin 承諾済みの回答
Also, you can use following approach:
There is SQL function - Last_RecID().
If you run something like this:

db.SqlSelect( "INSERT INTO t1 ....; SELECT Last_RecID();" )

as a single statement you will get lastRecID, and it is guaranteed that it would be consistent result.
There are no comments made yet.
Thomas Goertler 承諾済みの回答
Hi Ivan,
for now we use your second approach with the SELECT Last... query. I'll add that to the BT as feature request so we get this sort of info natively in the .NET lib.
There are no comments made yet.
  • ページ :
  • 1

There are no replies made for this post yet.
However, you are not allowed to reply to this post.