使用ADO Command对象调用MySql存储的函数而不是存储过程

时间:2022-07-13 01:49:32

I'm trying to call a stored function like this from vba in access:

我试图在访问中从vba调用这样的存储函数:

SELECT my_function();

If it were a stored procedure it would be like this:

如果它是一个存储过程,它将是这样的:

CALL my_procedure();

For the stored procedure I can use:

对于存储过程,我可以使用:

Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")

With cmd
    Set .ActiveConnection = oConn 'ADODB connection created elsewhere
    .CommandType = adCmdStoredProc
    .CommandText = "my_procedure"
End With

cmd.execute

Specifically I'd like to know if there is an equivalent of 'adCmdStoredProc' for functions?

具体来说,我想知道是否有相同的'adCmdStoredProc'功能?

2 个解决方案

#1


1  

"Specifically I'd like to know if there is an equivalent of 'adCmdStoredProc' for functions?"

“具体来说,我想知道是否有相同的'adCmdStoredProc'用于函数?”

But the SQL you're using is a SELECT which references a function:

但是你正在使用的SQL是一个引用函数的SELECT:

SELECT my_function();

You have 7 choices from the CommandTypeEnum. adCmdUnspecified should work; probably adCmdUnknown too. I would use adCmdText, but it's not really "the equivalent of" adCmdStoredProc for a function.

CommandTypeEnum有7个选项。 adCmdUnspecified应该工作;可能也是adCmdUnknown。我会使用adCmdText,但它并不是“相当于”某个函数的adCmdStoredProc。

CommandTypeEnum Constants
Constant         Value  Description
adCmdFile        256    Evaluate as a previously persisted file
adCmdStoredProc    4    Evaluate as a stored procedure
adCmdTable         2    Have the provider generate a SQL query and return all rows from the specified table
adCmdTableDirect 512    Return all rows from the specified table
adCmdText          1    Evaluate as a textual definition
adCmdUnknown       8    The type of the CommandText parameter is unknown
adCmdUnspecified  -1    Default, does not specify how to evaluate

#2


0  

Have you tried running it as a SELECT statement?

您是否尝试将其作为SELECT语句运行?

SELECT *
FROM my_function()

#1


1  

"Specifically I'd like to know if there is an equivalent of 'adCmdStoredProc' for functions?"

“具体来说,我想知道是否有相同的'adCmdStoredProc'用于函数?”

But the SQL you're using is a SELECT which references a function:

但是你正在使用的SQL是一个引用函数的SELECT:

SELECT my_function();

You have 7 choices from the CommandTypeEnum. adCmdUnspecified should work; probably adCmdUnknown too. I would use adCmdText, but it's not really "the equivalent of" adCmdStoredProc for a function.

CommandTypeEnum有7个选项。 adCmdUnspecified应该工作;可能也是adCmdUnknown。我会使用adCmdText,但它并不是“相当于”某个函数的adCmdStoredProc。

CommandTypeEnum Constants
Constant         Value  Description
adCmdFile        256    Evaluate as a previously persisted file
adCmdStoredProc    4    Evaluate as a stored procedure
adCmdTable         2    Have the provider generate a SQL query and return all rows from the specified table
adCmdTableDirect 512    Return all rows from the specified table
adCmdText          1    Evaluate as a textual definition
adCmdUnknown       8    The type of the CommandText parameter is unknown
adCmdUnspecified  -1    Default, does not specify how to evaluate

#2


0  

Have you tried running it as a SELECT statement?

您是否尝试将其作为SELECT语句运行?

SELECT *
FROM my_function()