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.

Issue is you are trying to open a `ADODB.Recordset` using an `INSERT` statement which returns no resultset? This is never going to work, what are you excepting to return? — user692942, Jun 01 '16 at 14:40

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

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

Unfortunately it seems that you cannot pass multiple queries to either `RS.Open()` or `CN.Execute()`. Are you able to provide an example of your proposed solution using ADODB.Command? Thanks. — David Gard, Jun 01 '16 at 15:02
@DavidGard Sorry don't understand what you mean, that last edit is an example of how to use the `ADODB.Command` object? I've used `Execute()` method of the object but if you want to use `RS.Open()` just pass the `cmd` object as the first argument instead of the SQL string. — user692942, Jun 01 '16 at 15:04
Sorry, I hadn't updated the page when I commented and didn't see that you had edited the answer. I'll give that a bash now. — David Gard, Jun 01 '16 at 15:05
@DavidGard Ah ok I understand now, that might be a provider limitation I'm not overly familiar with MySQL to be honest, work mostly with SQL Server. — user692942, Jun 01 '16 at 15:11
Unfortunately the long and short of it is... same result :( It does appear that when connecting to MySQL externally, only single statements can be executed, so I'm sadly left with the same issue. — David Gard, Jun 01 '16 at 15:27
@DavidGard You probably need to use something other then `;` in MySQL, you could try placing the statement on a new line using `& vbNewLine &`. — user692942, Jun 01 '16 at 15:29
@DavidGard Actually scrape that `;` does work but you have to jump through a few hoops - [Error while executing multiple operations in one query (ASP/MyODBC)](https://bugs.mysql.com/bug.php?id=3745) — user692942, Jun 01 '16 at 15:30
No joy. I seem to remember encountering this problem with WordPress/MySQL in the past. I'll have to somehow find a way around it - `SELECT MAX(ID) FROM db.table` should hopefully do the job. — David Gard, Jun 01 '16 at 15:32
@DavidGard Please don't use `SELECT MAX(ID) ...` even I know that's a bad idea and I don't use MySQL. See [How to get the ID of INSERTed row in mysql?](http://stackoverflow.com/q/7501464/692942) — user692942, Jun 01 '16 at 15:36
@DavidGard Judging by [this](http://stackoverflow.com/a/17683000/692942), it looks as though your only option is to make two calls to the database one for the insert and one for the `id` using `SELECT LAST_INSERT_ID();`. Even that seems dangerous though which is maybe why they used `adLockPessimistic`. — user692942, Jun 01 '16 at 15:43
Yep, two queries it'll have to be. Never mind, I do at least feel as though I've learnt something today :) — David Gard, Jun 01 '16 at 16:08
@DavidGard It should work fine as long as you maintain the same `ADODB.Connection` object, as pointed out in the quoted documentation the `LAST_INSERT_ID()` is... *"maintained in the server on a **per-connection** basis"*. Hope this has helped. — user692942, Jun 02 '16 at 08:45
@DavidGard Another approach would be to package the `INSERT` and `LAST_INSERT_ID()` in a stored procedure then call this once through the `ADODB.Command` using `CommandType = adCmdStoredProc`, I assume [MySQL supports stored procedures](http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html)? — user692942, Jun 02 '16 at 08:48

Rahul Tripathi · Answer 2 · 2016-06-01T14:44:48.090

0

Try like this:

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

ie, you can use the SELECT LAST_INSERT_ID() query to get the ID of the inserted row.

edited Jun 01 '16 at 14:44

answered Jun 01 '16 at 13:29

Rahul Tripathi

168,305
31
280
331

1

Thanks, but this appears to be VB as opposed to VBS, and I'm unable to get it to work. I do like the idea of using `SELECT LAST_INSERT_ID()`, but when I try and run that from the `RS.Open()` command that I am using, a MySQL syntax error is generated (although it works perfectly when running directly on the DB). – David Gard Jun 01 '16 at 14:20
@DavidGard That's actually VB.Net which is a whole different ball park again. – user692942 Jun 01 '16 at 14:39
@DavidGard:- May be its because I missed to add semicolon after `SELECT LAST_INSERT_ID()`;`. – Rahul Tripathi Jun 01 '16 at 14:39
Wrong language matey, this isn't VB.Net – user692942 Jun 01 '16 at 14:39
@Lankymart:- yes I realized it. My emphasis was majorly on the `SELECT LAST_INSERT_ID();` part. Updated my answer! – Rahul Tripathi Jun 01 '16 at 14:40
That will still break as `RS.Open` expects a `string`. – user692942 Jun 01 '16 at 14:43
1

Unfortunately `RS.Open()` will not accept multiple queries, so it's not possible to `INSERT INTO` and `SELECT` in one. `SELECT LAST_INSERT_ID()` (unsurprisingly) then returns nothing when called independently. Thanks. – David Gard Jun 01 '16 at 15:04

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

2 Answers2

Update