mysql - VBS中，使用 'INSERT INTO'返回MySQL查询结果

Question

在 VBS 中，考虑以下示例，ADODB.Recordset 对象的 Open() 方法返回什么(如果有)结果？

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)

在此示例中，调用 RS.MoveFirst() 会导致错误，表明调用 RS.Open() 实际上没有返回任何记录，尽管查询运行成功。我收到的错误是 -

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

我需要这个的原因是因为查询会自动生成一个 ID，我的代码需要这个 ID。我当然可以运行 SELECT 查询，但不返回结果似乎很奇怪。

Answer 1

您需要为要实例化的 ADODB.Recordset 传回一些内容。

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)

就我个人而言，我倾向于使用 ADODB.Command 来编写此代码，而不是将 SQL 语句直接传递给 ADODB.Recordset。

它还会在构建参数化查询时防止 SQL 注入(inject)。

类似的东西；

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

不确定您在 the comment 中要求什么但是如果你的意思是如何将这种方法与 RS.Open 一起使用，这应该会有所帮助；

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)

---

更新

我现在已经阅读了一些关于 LAST_INSERT_ID() 的内容，这在文档中对我来说很突出

^{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.

这意味着只要您保持相同的连接，您就可以执行多个命令，并且仍然会返回一个特定于您的连接的 ID。

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