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

[lunenburger]

I am using VbScript in Indusoft to insert data into a MySql database.

Immediately after inserting a row, I need to know what that row number is to link another table to that data.

On a similar system, I was using AdvancedHMI and used this VB.Net code:

            Try
                MysqlConn.Open()
                COMMAND.CommandText = "SELECT Last_insert_id()"
                Dim RunKey = COMMAND.ExecuteScalar()
                TempRunKeyID = RunKey

and that was working perfectly.....

But I can't find a way to do it with VbScript...

I can find the total number of rows, and as long as nothing gets deleted I am using this code:

'Find Row Number of current Job
Dim sql1
sql1="Select * FROM meererdb.run_summary"
$numCur=$DBCursorOpenSQL("MySqlDB",sql1)
$rownum=$DBCursorRowCount($numCur)
sql1.close
sql1=Nothing

But if the number of rows changes, that won't work...

Any insight would be greatly appreciated

 

[parky]

Use the function SELECT LAST_INSERT_ID()
Try that, however, if you insert more than one entry before the function I think it returns the first inserted ID of multiple entries.
Not sure in MySql, as it is a long time since I used it.

 

[drbitboy]

Are you looking for an autoincrementing index?

Also, how does LAST_INSERT_ID know which table is being referred to?

I have done this by inserting a unique guard field sands there using a WHERE to find that record and SELECT the index

 

[Phrog30]

Indusoft doesn't let you request the ID on insert? Ignition does. I'd bet there is a way. You don't want to use MySQL for this, you want this to be done in the script.

 

[drbitboy]

SELECT MAX(ID) FROM table;

Since it autoincrements, this should work more reliably than the $DBCursorRowCount approach i.e. if intermediate rows are deleted, but may give a different number than LAST_INSERT_ID() if

• the last row is deleted. or
• multiple rows are added with a single action.

Although I am curios why LAST_INSERT_ID() does not work; that is a server function, and the client only sends the request.


Does it need a semi-colon? what was the error?


I notice that without an argument, it returns a BIGINT UNSIGNED (64-bit) value; with an argument, it return an unsigned integer; maybe VBS can't handle the former?

 

[parky]

SELECT MAX(ID) AS LastID FROM My_Table.
However, this is MSSQL, arghh... just noticed the last post so forget it.

 

[lunenburger]

Thank you all for the responses....

I have tried using the following VbScript:

'Find Row Number of current Job
Dim sql1
sql1="Select MAX(run_key) As LastID FROM meererdb.run_summary"
$numCur=$DBCursorOpenSQL("MySqlDB",sql1)
$rownum=$DBCursorRowCount($numCur)
sql1.close
sql1=Nothing

It seems to work but it is returning the number 1 as the value when it should be 523 (last line in table)

Should I change the command of DBCursorRowCount?

I am new to VbScript and MySql commands so please go easy....

thank you

 

[lunenburger]

I just tried

'Find Row Number of current Job
Dim sql1
sql1="Select LAST_INSERT_ID() FROM meererdb.run_summary"
$numCur=$DBCursorOpenSQL("MySqlDB",sql1)
$rownum=$DBCursorRowCount($numCur)
sql1.close
sql1=Nothing

And it works perfectly....

(It's funny, I am positive I tried that before and I couldn't get it to work????)

thanks to all...

 

[parky]

SELECT ID AS LastID FROM My_Table WHERE ID = @@Identity;

or
select *from My_Table order by Name desc limit 1;

select *from My_Table where Id = @MaxId;

or

set @MaxId = (select max(Id) from My_Table)

select *from My_Table ID, My_Row where Id = @MaxId;

Worth trying ?

 

[MikeyN]

In MSSQL, I would do something like this:

SELECT TOP 1 run_key FROM meererdb.run_summary

I haven't used MySQL for some time, but I think the equivalent code would be something like this:

SELECT run_key FROM meererdb.run_summary ORDER BY run_key DESC LIMIT 1

 

[parky]

SELECT AUTO_INCREMENT - 1 as CurrentId FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'My_DB' AND TABLE_NAME = 'My_Table'
Auto increment would be the next one so need to minus 1 as above

 

[drbitboy]

Thank you all for the responses....

I have tried using the following VbScript:

'Find Row Number of current Job
Dim sql1
sql1="Select MAX(run_key) As LastID FROM meererdb.run_summary"
$numCur=$DBCursorOpenSQL("MySqlDB",sql1)
$rownum=$DBCursorRowCount($numCur)
sql1.close
sql1=Nothing

It seems to work but it is returning the number 1 as the value when it should be 523 (last line in table)

Should I change the command of DBCursorRowCount?

I am new to VbScript and MySql commands so please go easy....

thank you

you were asking for one row (the single max value) and $rownum was correctly set to 1.


What you want was the value of the field in the row, not the number of rows returned.

 

[drbitboy]

I just tried

'Find Row Number of current Job
Dim sql1
sql1="Select LAST_INSERT_ID() FROM meererdb.run_summary"
$numCur=$DBCursorOpenSQL("MySqlDB",sql1)
$rownum=$DBCursorRowCount($numCur)
sql1.close
sql1=Nothing

And it works perfectly....

(It's funny, I am positive I tried that before and I couldn't get it to work????)

thanks to all...

Actually you are still retrieving the number of rows.

if you tried any of the following

sql1="Select NULL FROM meererdb.run_summary"

sql1="Select 0 FROM meererdb.run_summary"

sql1="Select -1 FROM meererdb.run_summary"

sql1="Select 1e+32 FROM meererdb.run_summary"


sql1="Select 'a modnar string' FROM meererdb.run_summary"

you would still get the same answer from $rownum=$DBCursorRowCount($numCur)

You need to figure out how to the the value of the field (i.e. LAST_INSERT_ID()) SELECTed in a row, not the number of rows (via $DBCursorRowcount(...)).

 

[drbitboy]

https://stackoverflow.com/questions/1366516/connect-to-mysql-5-0-database-using-pure-vbscript

https://stackoverflow.com/questions/26448129/how-to-return-count-and-store-it-in-a-variable-for-further-usage-in-vbscript

https://stackoverflow.com/questions/37570027/in-vbs-return-mysql-query-results-when-using-insert-into?noredirect=1&lq=1

And how close are you to retirement, anyway ?

 

[drbitboy]

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)