returning MySQL output params from stored procs 1

[MDCrab]

Hello,

i really appreciate the help i just received on a related subject:

http://www.tek-tips.com/viewthread.cfm?qid=1504745&page=1

I've got a new question. From VBScript, how does one call a MySQL stored procedure and retrieve output params?

I created a MySQL stored procedure:

PROCEDURE sp_Test_Proc2(
OUT vOutID int,
OUT vOutID2 int)
BEGIN
set vOutID=5;
set vOutID2=25;
END

(i used phpMyIDE to do it.)


Here's my VBScript code:

dim adCmdStoredProc
adCmdStoredProc = 4
dim adInteger
adInteger = 3
dim adBigInt
adBigInt = 20
dim adVarchar
adVarchar = 200
dim adParamReturnValue
adParamReturnValue = 3
dim adParamInput
adParamInput = 1
dim adDBTimeStamp
adDBTimeStamp = 135

dim strConnection

strConnection = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=myHostingCompanysServer; PORT=3306;DATABASE=myMYSQLDB; USER=MyUser; PASSWORD=MyPass; OPTION=3;"

dim objCmd
set objCmd= Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = strConnection
objCmd.CommandText = "myMYSQLDB.sp_Test_Proc2"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("vOutID", adInteger, adParamReturnValue, , 0)
objCmd.Parameters.Append objCmd.CreateParameter("vOutID2", adInteger, adParamReturnValue, , 0 )
'execute the command
objCmd.Execute

'retrieve the values

response.write (objCmd.Parameters.Item("@vOutID").Value)

response.write (objCmd.Parameters.Item("@vOutID2").Value)
set objCmd = nothing


... the error message I receive is:

"[MySQL][ODBC 5.1 Driver][mysqld-5.0.51-log]OUT or INOUT argument 1 for routine mbcrazy_MySQL.sp_Test_Proc2 is not a variable or NEW pseudo-variable in BEFORE trigger"

Does anyone know how to get those output parameters back into my VBScript?

thanks,

 

[tsuji]

If the ultimate purpose is to get them back, do the following. If you want to make mysql look back, I won't go into it, and they have plenty of space and time to make up.
[tt]
strConnection = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=myHostingCompanysServer; PORT=3306;DATABASE=myMYSQLDB; USER=MyUser; PASSWORD=MyPass; OPTION=3;"
set oconn=server.createobject("adodb.connection")
oconn.open strConnection
set objCmd= Server.CreateObject("ADODB.Command")
set objCmd.ActiveConnection = oconn

[green]'your adParamReturnType is doubly mixed up, wrong value then wrong named constant assigned[/green]
adParamOutput=2
adInteger=3
objCmd.Parameters.Append objCmd.CreateParameter("@x", adInteger, adParam[red]Output[/red],4,0)
objCmd.Parameters.Append objCmd.CreateParameter("@y", adInteger, adParam[red]Output[/red],4,0)

adCmdText=1
objCmd.CommandType=adCmdTExt
objCmd.CommandText="call myMYSQLDB.sp_Test_Proc2(@x,@y)"

objCmd.execute

'Holders of answers
vID=""
vID2=""

set rs=oconn.execute("select @x,@y")
if not rs.eof then
vID=rs(0).value 'the answer
vID2=rs(1).value 'the answer
end if
rs.close
set rs=nothing
[/tt]

 

[MDCrab]

Hi Tsuji,

I appreciate the reply. I found the answer last night. I was about to post it here, but you replied before I could do so.

Regarding your command object solution...it doesn't work. That's because if we keep the "OUT" directive in the parameters of the stored procedure, that abysmal mySQL error about "OUT or INOUT argument 1 for routine mySQLDB.sp_Test_Proc2 is not a variable or NEW pseudo-variable in BEFORE trigger" keeps returning. Meanwhile, if I take the OUT directive out for both params in the stored proc, the command object can't see the first param. It gives me the error: "Unknown column 'vOutID' in 'field list'". I've looked over my code and I'm certain that I haven't spelled anything wrong.

However, your response touches on the solution. To get output parameters returned to VBScript, do the following:

1. remove 'OUT/INOUT' from the stored proc for the params;
2. have the stored proc select the parameters; e.g., 'select vOutID, vOutID2'.
3. use ado, make a recordset and retrieve the parameters from the select statement in the stored proc. For example:

strSQL = "call mbcrazy_MySQL.sp_Test_Proc2(0, 0);"
Set recSet = Server.CreateObject("ADODB.Recordset")
Set recSet = Conn.Execute (strSQL)
do while not recSet.eof
response.write ("vOutID: " & recSet ("vOutID") & "<br>")
response.write ("vOutID2: " & recSet ("vOutID2") & "<br>")
recSet.MoveNext
loop



This suggests that the best way to work with output parameters in MySQL is to avoid the command object! That's quite different from MSSQL.

I find this way of working to be second-rate. It is rather amateurish for MySQL to force developers to use this technique. For those of us who are migrating from MSSQL to MySQL, this method will involve two different sets of code, depending on the database that's being used. I'm not happy to find that out.

Once again, I appreciate the response. thanks again.

 

[tsuji]

... then your ado must be different from my ado.