MySQL stored procedure

Hello Experts,
I am trying to execute attached function from .vbs file but for some reason I am getting stored procedure not found error, however, when I run the same from ASP page it works fine.
Is there anything wrong with this script please advice.
Thanks
Sam
LVL 8
newbie27Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it should actually work.
you might try:
set cObj.ActiveConnection = cnObj
instead of:
cObj.ActiveConnection = cnObj

0
 
newbie27Author Commented:
this is what i am getting ... please can someone advice
[MySQL][ODBC 5.1 Driver][mysqld-5.1.22-rc-community]PROCEDURE mysql.sp_archive_folyes_data does not exist
Function archivefolyesData()
 
    Set cObj= CreateObject("ADODB.Command")
    cObj.ActiveConnection = cnObj
        wscript.echo cnObj.state ' returns 1 
    msgbox adCmdStoredProc ' returns 4 
    cObj.CommandText = "sp_archive_folyes_data"
    cObj.CommandType = 4
     
    cObj.Execute
    
    If err.number <> 0 then
        archivefolyesData = err.Description 
    Else
        archivefolyesData = "OK"    
    End If
    
    Set cObj = Nothing     
End Function

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the error message says that it could not find the procedure in the database "mysql", which is the default schema in the mysql database (hopefully you did not create the procedure in there?)

specify the proper database in the connection string, or in the procedure call to solve this
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
newbie27Author Commented:
Hello angel111,
thanks for picking this up, please see the attached if this is what you mean by specifying the proper database connection string.

this however, still gives the same error ..!

thanks for your time

Function archivefolyesData()
    
Set cnObj = createobject("ADODB.Connection")
set rsObj = createobject("ADODB.Recordset")
 
cnObj.open = "DRIVER={MySQL ODBC 5.1 Driver};"_
& "SERVER=localhost;"_
& "DATABASE=mysql;"_
& "UID=root;"_
& "PWD=root;"
	
    Set cObj= CreateObject("ADODB.Command")
    cObj.ActiveConnection = cnObj
        wscript.echo cnObj.state ' returns 1 
    msgbox adCmdStoredProc ' returns 4 
    cObj.CommandText = "sp_archive_folyes_data"
    cObj.CommandType = 4
     
    cObj.Execute
    
    If err.number <> 0 then
        archivefolyesData = err.Description 
    Else
        archivefolyesData = "OK"    
    End If
    
    Set cObj = Nothing     
End Function

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
again: is the stored procedure (really) created in the mysql database named "mysql"?

if yes => you should NOT have done that. mysql is the internal database, and should remain clean.
if no => specify the real database name in the connection string.
0
 
newbie27Author Commented:
hello,
Yes, I have created this stored procedure in the mysql database. I did this because I wanted to use multiple select statements and apparently this SP works fine from the ASP page.

Is there no way I can use it from the VBS file?

please advice
thanks
0
 
newbie27Author Commented:
I have amended it as per yours suggestion but still gives the same error ...
screen.JPG
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that leaves with 2 possibilities:
* you connect to the wrong server
* the procedure name is eventually case sensitive.
0
 
newbie27Author Commented:
angelIII,
unfortunately this is not the case, i have checked the case and they are in smaller.
If I call this from ASP page it works, apparently the server/connection details are correct.
i think i must leave it as it is now i.e: ASP script.
thanks for your time angelIII:
sam
0
 
newbie27Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.