calling MySQL stored procedures from VBScript

[MDCrab]

does anyone have the trick for calling mysql stored procs from VBScript?

I keep getting: "Procedure X can't return a result set in the given context."

I asked at mysql; here's my question put there:

http://forums.mysql.com/read.php?10,228573,228573#msg-228573

thanks,

 

[johnwm]

Without seeing your code or what you have tried it is always going to be a bit of a guess, but I use a simple connection object and a Call statement

<%
Dim rs
Dim objconn
Set ObjCon = Server.CreateObject("ADODB.Connection")
ObjCon.Open "Driver={MySQL ODBC 3.51 Driver};" & _ 
           "Server=127.0.0.1;" & _
           "Port=3306;" & _
           "Option=3;" & _
           "Stmt=;" & _
           "Database=test;" & _
           "Uid=myID;" & _
           "Pwd=myPWD;"
StrSQL = "call mySP();"
Set rs = objcon.Execute(StrSQL)
'use your recordset here
%>

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[MDCrab]

hi John,

thanks for the reply. I've created a basic stored procedure very similiar to this:

PROCEDURE sp_Test_Proc3()
BEGIN
select * from mySQLDB.MyTable order by id desc limit 5;

END

I am currently trying to call this using the same VBScript connectio string that you provided:

"Driver={MySQL ODBC 3.51 Driver}; Server=myserver.com; Database=mySQLDB; UID=myUserName; PWD=myPass; Option=3"

when running from VBScript, I get this error message: "PROCEDURE mySQLDB.sp_Test_Proc3 can't return a result set in the given context."

the stored proc runs in phpMyIDE. The stored procedure works.

If I take the select statement that is in sp_Test_Proc3 and make that the SQL statement that my VBScript runs, it executes perfectly. (In other words, the SQL outside of the proc runs from VBScript.)

So, i'm unable to call a proc from VBScript. that's what is holding me back.

Any help is appreciated.

thanks,

 

[whosrdaddy]

Try using ADODB.Command

->

http://www.w3schools.com/ADO/ado_ref_command.asp

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[tsuji]

The problem should be related to the minor build of the MySQL ODBC 3.51 Drive. It should not be earlier than the build 3.51.12.00 (exclusive). If you update to the latest build 3.51.12.26.00, it should then work fine either way, adodb.connection execute method or through adodb.command execute method with commandtext "call sp_test_proc3()".

 

[MDCrab]

Hi Daddy,

the command object isn't working very well either. Here's my VBScript:

dim oConn, oCmd
dim adCmdText
adCmdText = 1

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = "Driver={MySQL ODBC 3.51 Driver}; Server=mySQLServer.com; Database=mySQLDB; UID=myUserName; PWD=MyPass; Option=3"
oCmd.CommandText = "select * from mySQLDB.myTable order by id desc limit 5;"
oCmd.CommandType = adCmdText

oCmd.Execute
response.write ("state: " & oCmd.state & "<br>")

...the state of the command object is 0 after I call execute, indicating that it is closed.

The mySQL query delivers a result set in phpMyIDE.

when I change the mySQL statement to my stored proc, I get the same error as before; i.e., "PROCEDURE mySQLDB.sp_Test_Proc3 can't return a result set in the given context."

Any help is appreciated.

thanks,

 

[tsuji]

[1] You don't even capture the resultset.
>oCmd.Execute
[tt]set rs=oCmd.Execute[/tt]
That's your resultset.

[2] You have not opened the connection. You can pass a connection string to the activeconnection (with implicit type conversion) only if the real connection is opened.
[tt]
dim oConn, oCmd
dim adCmdText
adCmdText = 1
[blue]
set oconn=server.createobject("adodb.connection")
sconn="Driver={MySQL ODBC 3.51 Driver}; Server=mySQLServer.com; Database=mySQLDB; UID=myUserName; PWD=MyPass; Option=3"
oconn.open sconn
[/blue]
Set oCmd = Server.CreateObject("ADODB.Command")
[blue]set oCmd.ActiveConnection = oconn[/blue]
oCmd.CommandText = "[blue]call sp_Test_Proc3()[/blue]"
oCmd.CommandType = adCmdText
[blue]
dim rs
set rs=oCmd.Execute
do while not rs.eof then
'do thing with the rs
loop[/blue]
[/tt]
[3] Have you checked the build of the driver on the server? (In my post, there is a typo: I meant "the latest build 3.51[red].26.[/red]00".

 

[tsuji]

amendment
There is a typo.
>[self]do while not rs.eof [red]then[/red]
It should be read sure like this.
[tt]do while not rs.eof[/tt]

 

[MDCrab]

hi Tsuji,

Thanks for replying. I have made the changes you specified.

This time, when I keep the SQL to be "select * from mySQLDB.MyTable limit 10;" I get the correct result set from the command object.

However, when I substitute in the stored procedure, I get the same error I mentioned at the start of this thread:

"PROCEDURE mySQLDB.sp_Test_Proc3 can't return a result set in the given context."

Meanwhile, I can use this syntax in phpMyIDE and get a result set:

call mySQLDB.sp_Test_Proc3();

This is what I'm passing into the VBScript command object.

So, unfortunately so far I can't call mySQL stored procedures from VBScript.

All help is appreciated. Thanks,

 

[MDCrab]

Tsuji,

Also, I'm using MySQL 5.0+. This version of MySQL allows stored procedures.

thanks,

 

[tsuji]

Again if I need to elucidate [3] again: Go to control panel and under system info, there is an odbc info. Click it out and look for the MySQL driver build. Specific attention is on the minor build. Even with MySQL 5.0, the driver is an independent downloadable update. You can even blindly download the latest odbc driver for windows from the mysql site and install the latest - that's is important, because, legacy version contains bugs for calling stored procedure.

 

[MDCrab]

Hi Tsuji,

thanks for replying again. Sorry that I failed to give more information to the point you made.

I pay for the hosting. So, it isn't easy to get proper builds downloaded. I have to ask my hosting company for help there.

A few weeks ago they told me that they were running php 5.2.5. I asked them to copy the libmysql.dll from version 5.2.1, which they did. (The newest version of that DLL conflicts with phpMyIDE. I found that there was no problem using libmysql.dll from version 5.2.1 with the php 5.2.5.)

It's hard to verify the version of MySQL. They gave me a phpMyAdmin interface. However, it doesn't seem to express what the version of PHP is. After I login to phpMyAdmin, I see two columns, one for localhost and the other is phpMyAdmin. The server version given for localhost is 5.0.51-log. Meanwhile, underneath the phpMyAdmin column it says MySQL client version 4.1.15.

I suspect that the two version numbers given in phpMyAdmin are not accurate, and that the hosting company is correct in saying that I am running php 5.2.5.

Is this helpful? I sure appreciate your helping me.

thanks,

 

[tsuji]

>I pay for the hosting. So, it isn't easy to get proper builds downloaded.
I see and I understand. In that case add this line after connection established for inspection.
[tt] response.write "driver version: " & oconn.properties("driver version").value & "<br />"
[/tt]

 

[ingresman]

Bt isn't the ODBC driver on your PC not the hosting company server ?

 

[MDCrab]

Hi Tsuji,

the response i get is: driver version: 03.51.04

Ingresman, i've tried connecting two ways, with DSN-less connection strings and a DSN connection string. My DSN-less connection string is mentioned above. The DSN connection string was accomplished by creating a DSN on my hosting company's server. I don't think i'm using the ODBC on my machine at all.

thanks for the replies.

 

[MDCrab]

Have you checked the build of the driver on the server? (In my post, there is a typo: I meant 'the latest build 3.51.26.00'".

So, since i'm running 03.51.04, that could be the culprit. i'll ask my hosting company if i can move up to 3.51.26.00.

thanks,

 

[ingresman]

Ahh got you, should have looked at your code in more detail. Your running classic ASP ??

 

[MDCrab]

Hi Ingresman, yes, i'm running classic ASP.

Tsuji, i've sent an email to my hosting company asking for a higher minor build of the ODBC driver. I'll let you know if that solves the problem. I greatly appreciate your help.

 

[ingresman]

You could try the OLEDB method by specfiying

Provider=MySQLProv;Data Source=Your_MySQL_Database;User Id=Your_Username; Password=Your_Password

for your connection string.
I got this from

http://www.sqlstrings.com/MySQL-connection-strings.htm

There is also

http://www.connectionstrings.com

 

[MDCrab]

Hi Ingresman,

I have a connection string that connects to the mysql database. I can do simple select statements with it. I just can't get a select statement to run from a stored procedure.

i'm following Tsuji's advice and trying to get my hosting company to download a more current ODBC driver.

thanks,