vba 通过VBA在Access前端使用参数执行MySQL存储过程

Execute MySQL stored procedure with parameters in Access frontend via VBA

提问by lhiapgpeonk

So, database hotchpotch.

所以，数据库热点。

• I have a MySQL database which has stored procedures.
• I have an Access document which is linked to the MySQL database (I can survey tables) serving as a frontend (with forms and stuff)
• I have successfully created a ODBC Pass Through Query (I think that's what they are called) to call one of the stored procedures from the database. The query in Access actually is just CALL myProcName;and gives me the results I expect when I call it from VBA with Set Result = CurrentDb.OpenRecordset("myProcName")

• 我有一个带有存储过程的 MySQL 数据库。
• 我有一个 Access 文档，它链接到 MySQL 数据库（我可以调查表）作为前端（带有表单和东西）
• 我已经成功创建了一个 ODBC Pass Through Query（我认为这就是它们的名字）来调用数据库中的存储过程之一。Access 中的查询实际上只是CALL myProcName;提供了我从 VBA 调用它时所期望的结果Set Result = CurrentDb.OpenRecordset("myProcName")

What I am trying to achieve is the following:

我想要实现的是以下内容：

• Have a stored procedure in MySQL that takes one parameter => check!
• Have that stored procedure working as expected => checked in phpMyAdmin => check!
• Have a saved query in Access which links to this parametric stored procedure with a fixed parameter value given => check! (CALL myProcName('myParameterValue'), I can call that from VBA just as I do the unparametric query)
• Have the ability to specify 'myParameterValue'everytime I execute the query => not check

• 在 MySQL 中有一个存储过程，它接受一个参数 => 检查！
• 让该存储过程按预期工作 => 检查 phpMyAdmin => 检查！
• 在 Access 中有一个已保存的查询，该查询链接到此参数化存储过程，并使用给定的固定参数值 => 检查！（CALL myProcName('myParameterValue')，我可以像执行非参数查询一样从 VBA 调用它）
• 有能力在'myParameterValue'每次执行查询时指定=>不检查

I need to somehow specify a parameter placeholder in the SQL definition of the saved query and set that parameter in VBA. For the VBA part I have and idea I would find rather elegant:

我需要以某种方式在已保存查询的 SQL 定义中指定一个参数占位符，并在 VBA 中设置该参数。对于我拥有的 VBA 部分，我认为我会觉得相当优雅：

Private Sub ParametricQuery()
  Dim QDef As QueryDef
  Set QDef = CurrentDb.QueryDefs("myProcName")
  QDef.Parameters(*insert parameter name here*) = parameter value
  Dim Result As Recordset
  Set Result = QDef.OpenRecordset

  Do While Not Result.EOF
    MsgBox Result.Fields(1) 'Display a field from the results
  Loop
End Sub

But how would I build my SQL definition?

但是我将如何构建我的 SQL 定义？

PARAMETERS in_param TEXT;
CALL myProcName(in_param);

does NOT work. If I try

不起作用。如果我尝试

Set QDef = CurrentDb.QueryDefs("myProcName")
MsgBox QDef.Parameters.Count

I get a Messagebox telling me there is a total of 0 parameters in my query definition, so that doesn't work.

我收到一个消息框，告诉我在我的查询定义中总共有 0 个参数，所以这不起作用。

What I have found online is a lot of people building the actual SQL in VBA via string operations. That makes me shudder for so many reasons (security, maintainability and elegance among them). I firmly believe that there is a better way, hopefully along the lines I have sketched above. The only problem is: How to do it?

我在网上发现很多人通过字符串操作在 VBA 中构建实际的 SQL。这让我不寒而栗，原因有很多（其中包括安全性、可维护性和优雅性）。我坚信有更好的方法，希望按照我上面勾画的路线。唯一的问题是：怎么做？

回答by Parfait

Consider using ADOfor a parameterized query to call your stored procedure. Currently, you are using DAO(Access' default database API) to access a pass-thru query (a saved Access querydef). However, this type of query does not see anything in the frontend, only the backend RDMS particularly the MySQL SQL dialect and its connected database objects. Hence, you cannot bind local parameter values to it. And PARAMETERSclause is only part of the Access SQL dialect and will fail MySQL syntax.

考虑将ADO用于参数化查询以调用存储过程。目前，您正在使用DAO（Access 的默认数据库 API）来访问传递查询（已保存的 Access querydef）。然而，这种类型的查询在前端看不到任何东西，只有后端 RDMS，特别是 MySQL SQL 方言及其连接的数据库对象。因此，您不能将本地参数值绑定到它。AndPARAMETERS子句只是 Access SQL 方言的一部分，将失败 MySQL 语法。

MySQLStored Procedure

MySQL存储过程

CREATE PROCEDURE `mystoredproc`(IN param VARCHAR(254))
BEGIN
   SELECT * FROM table WHERE field=param;
END

ADOParameterized Query

ADO参数化查询

Public Sub CallMySQLProc()    
    Dim conn As Object, cmd As Object, rst As Object
    Const adCmdStoredProc = 4, adParamInput = 1, adVarChar = 200

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    ' DSN-LESS CONNECTION
    conn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};host=hostname;database=databasename;" _
                & "UID=username;PWD=****"

    ' CONFIGURE ADO COMMAND
    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection = conn
        .CommandText = "mystoredproc"
        .CommandType = adCmdStoredProc
        .CommandTimeout = 15
    End With

    ' APPEND NAMED PARAM
    cmd.Parameters.Append cmd.CreateParameter("param", adVarChar, _
                                              adParamInput, 254, "[email protected]")
    Set rst = cmd.Execute

    ' FREE RESOURCES
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    Set conn = Nothing

End Sub

---

---

DAODynamic Pass-Through Query

DAO动态传递查询

Here, you can build the querydef's .SQLstatement dynamically, but will not be parameterized:

在这里，您可以.SQL动态构建 querydef 的语句，但不会被参数化：

Private Sub ParametricQuery()
  Dim QDef As QueryDef
  Dim Result As Recordset

  Set QDef = CurrentDb.QueryDefs("PassThruQ")
  QDef.SQL = "CALL mystoredproc('[email protected]')"

  Set Result = QDef.OpenRecordset

  Do While Not Result.EOF
    Debug.Print Result.Fields(1)    'Print to immediate window a field from the results
  Loop

  Result.close
  Set Result = Nothing
  Set QDef = Nothing
End Sub