In VBS, return MySQL query results when using 'INSERT INTO'

Question

In VBS, taking the following example into consideration, what (if any) results are returned by the Open() method of the ADODB.Recordset object?

Dim CN : Set CN = CreateObject("ADODB.Connection")
Dim RS : Set RS = CreateObject("ADODB.Recordset")

CN.Open connectionString
RS.Open ("INSERT INTO db.table (username, computer_name, count) VALUES ('Whoever', 'Whatever', '99');", CN, 3)

In this example, calling RS.MoveFirst() results in an error, suggesting that no records were actually returned by the call to RS.Open(), despite the query running successfully. The error I receive is -

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

The reason I require this is because an ID is auto generated by the query, and I need that ID for my code. I can of course run a SELECT query, but it seems odd that results wouldn't be returned.

Answer 1

You need to pass something back for the ADODB.Recordset to be instantiated.

Dim CN : Set CN = CreateObject("ADODB.Connection")
Dim RS : Set RS = CreateObject("ADODB.Recordset")

CN.Open connectionString
RS.Open ("INSERT INTO db.table (username, computer_name, count) VALUES ('Whoever', 'Whatever', '99'); SELECT LAST_INSERT_ID();", CN, 3)

Personally I'd be inclined to write this using ADODB.Command instead of passing a SQL statement directly to ADODB.Recordset.

It will also guard against SQL Injection as it builds a parametrised query.

Something like;

Dim cmd: Set cmd = CreateObject("ADODB.Command")
Dim rs, id

Const adCmdText = 1
Const adParamInput = 1
Const adVarWChar = 202
Const adInteger = 3

Dim sql: sql = "INSERT INTO db.table (username, computer_name, count) VALUES (?, ?, ?); SELECT LAST_INSERT_ID();"

With cmd
  .ActiveConnection = connectionString
  .CommandType = adCmdText
  .CommandText = sql
  Call .Parameters.Append(.CreateParameter("@username", adVarWChar, 50))
  Call .Parameters.Append(.CreateParameter("@computer_name", adVarWChar, 50))
  Call .Parameters.Append(.CreateParameter("@count", adInteger, 4))

  Set rs = .Execute(, Array("Whoever", "Whatever", 99))
  If Not rs.EOF Then id = rs(0)
End With

Not sure what you are asking for in the comment but if you mean how do you use this approach with RS.Open this should help;

Dim cmd: Set cmd = CreateObject("ADODB.Command")

Const adCmdText = 1
Const adParamInput = 1
Const adVarWChar = 202
Const adInteger = 3
Const adOpenStatic = 3

Dim sql: sql = "INSERT INTO db.table (username, computer_name, count) VALUES (?, ?, ?); SELECT LAST_INSERT_ID();"

With cmd
  .ActiveConnection = connectionString
  .CommandType = adCmdText
  .CommandText = sql
  Call .Parameters.Append(.CreateParameter("@username", adVarWChar, 50))
  Call .Parameters.Append(.CreateParameter("@computer_name", adVarWChar, 50))
  Call .Parameters.Append(.CreateParameter("@count", adInteger, 4))

  .Parameters("@username").Value = "Whoever"
  .Parameters("@computer_name").Value = "Whatever"
  .Parameters("@count").Value = 99
End With

Dim rs: Set rs = CreateObject("ADODB.Recordset")
Dim id

Call rs.Open(cmd, , adOpenStatic)

If Not rs.EOF Then id = rs(0)

---

Update

I've now read a bit about LAST_INSERT_ID() and this stood out to me in the documentation

^{From MySQL 5.7 Reference Manual - 13.14 Information Functions}
The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

This means that as long as you maintain the same connection you can execute multiple commands and still get back an ID specific to your connection.

Dim conn: Set conn = CreateObject("ADODB.Connection")
Dim cmd: Set cmd = CreateObject("ADODB.Command")
Dim rs, id

Const adCmdText = 1
Const adParamInput = 1
Const adVarWChar = 202
Const adInteger = 3
Const adExecuteNoRecords = &H00000080
Const adOpenStatic = 3

Dim sql: sql = "INSERT INTO db.table (username, computer_name, count) VALUES (?, ?, ?);"

Call conn.Open(connectionString)
With cmd
  Set .ActiveConnection = conn
  .CommandType = adCmdText
  .CommandText = sql
  Call .Parameters.Append(.CreateParameter("@username", adVarWChar, 50))
  Call .Parameters.Append(.CreateParameter("@computer_name", adVarWChar, 50))
  Call .Parameters.Append(.CreateParameter("@count", adInteger, 4))

  Set rs = .Execute(, Array("Whoever", "Whatever", 99), adExecuteNoRecords)
End With

Call rs.Open("SELECT LAST_INSERT_ID;", conn, adOpenStatic)
id = rs(0)

Set cmd = Nothing
Call conn.Close()
Set conn = Nothing