Using Indusoft VbSctript to find last insert id in MySql once every five seconds

[lunenburger]

I have tried using the following code:

'Find Row Number of current Job
Dim sql1,LII
sql1="Select LAST_INSERT_ID() FROM meererdb.run_summary AS LII"
$numCur=$DBCursorOpenSQL("MySqlDB",sql1)
$rownum=$DBCursorGetValue($numCur, LII)
$DBCursorClose($numCur)

It returns a value of zero when it should be 573

I feel like this should work.... but it doesn't

What am I missing?? (other than better VbScript skills)

 

[drbitboy]

I have tried using the following code:

'Find Row Number of current Job
Dim sql1,LII
sql1="Select LAST_INSERT_ID() FROM meererdb.run_summary AS LII"
$numCur=$DBCursorOpenSQL("MySqlDB",sql1)
$rownum=$DBCursorGetValue($numCur, LII)
$DBCursorClose($numCur)

It returns a value of zero when it should be 573

I feel like this should work.... but it doesn't

What am I missing?? (other than better VbScript skills)

$rownum=$DBCursorGetValue($numCur, "LII")

 

[lunenburger]

Even with the quotes, still returns a zero

 

[drbitboy]

Even with the quotes, still returns a zero

The [AS LII] alias is supposed to apply to the field/column, not the table; see below.

sql1="Select LAST_INSERT_ID() [COLOR=Red][I][B]AS LII[/B][/I][/COLOR] FROM meererdb.run_summary"

 

[lunenburger]

this: http://kbase.icbconsulting.com/home/indusoft

you want some form of $DBCursorGetValue e.g. summat like this:

sql1 = "SELECT LAST_INSERT_ID() AS LII" 
 
$numCur=$DBCursorOpenSQL("MySqlDB",sql1)

$LII = $DBCursorGetValue($numCur, "LII")

$DBCursorClose(numCur)

Hi DrBitBoy,
I want to thank you for trying to help me, and apologize for my lack of understanding of VbScript......

So, do I need to specify the column name and table that I want the value from?

The auto-incrementing column name is run_key

I have tried changing LII in the code to run_key and it doesn't return anything. ($rownum doesn't change value)

Thank you again for the help....

 

[drbitboy]

Ah, got it. Maybe.


From https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id:





The key phrase there is per-connection.



What that means is that you have to do the [SELECT LAST_INSERT_ID()] in the same connection, that is

• after the INSERT in a database connection
• AND
• before closing that database connection i.e. the database connection that did the INSERT.

Is it possible you are issuing the [SELECT LAST_INSERT_ID()] query on a new database connection, separate from the database connection that did the INSERT, or perhaps after closing and re-connecting to the same database connection?

 

[lunenburger]

Well I couldn't get Select Last_Insert_ID() to work in Indusoft VbScript.
It always returned a zero no matter how I tried to configure it.

So I had to go with Select Max() and it works.

This is the code that seems to work best for me:

Dim sql0, RecordId,sqlD, tmpResult
sql0 = "Insert into mdb.run_summary (machine_id) values ('" & $Getcomputername() & "')"
$DBExecute ("MySqlDB",sql0) 

sqlD="SELECT MAX(run_key) as RecordId FROM mdb.run_summary;"
tmpResult = $DBCursorOpenSQL ("MySqlDB",sqlD) 

$rownum = $DBCursorGetValue(tmpResult,"RecordId")

DrBitBoy, thanks for all the help!!

 

[Phrog30]

I wouldn't use MAX unless there is an index, but since this is your primary key should be by default. You could also order by 'run_key' desc and limit/use top of 1.

Did you ever contact Indusoft? Just hard to believe they don't have this built-in where you can request the key from the initial query.

 

[drbitboy]

...
This is the code that seems to work best for me:
...

Cool beans. Gotta love reverse engineering. Since is are only ever adding one record, and letting the INTEGER AUTO_INCREMENT field be auto-incremented, that should always work.

For the record, and I just remembered this, it may (may) be possible to send multiple queries over one connection in one SQL string using the MySQL semi-colon separator e.g.

sql0 = "INSERT INTO [...] [COLOR=Red][I][B];[/B][/I][/COLOR] SELECT LAST_INSERT_ID()"
...

See the attachment, which is admittedly from a Linux command line using the mysql client; processing multiple queries my not be built into the AVEVA $DB.. API.

 

[drbitboy]

Demo of LAST_INSERT_ID()-equivalent approach: https://github.com/drbitboy/last_insert_id


It's done in Python, but it should translate to other languages' broken APIs.