Solved

MySQL stored procedure

Posted on 2008-06-24
10
544 Views
Last Modified: 2011-10-19
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
0
Comment
Question by:newbie27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 8

Author Comment

by:newbie27
ID: 21854569
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21855270
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
 
LVL 8

Author Comment

by:newbie27
ID: 21855376
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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21855429
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
 
LVL 8

Author Comment

by:newbie27
ID: 21855482
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21855542
it should actually work.
you might try:
set cObj.ActiveConnection = cnObj
instead of:
cObj.ActiveConnection = cnObj

0
 
LVL 8

Author Comment

by:newbie27
ID: 21855626
I have amended it as per yours suggestion but still gives the same error ...
screen.JPG
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21855737
that leaves with 2 possibilities:
* you connect to the wrong server
* the procedure name is eventually case sensitive.
0
 
LVL 8

Author Comment

by:newbie27
ID: 21855980
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
 
LVL 8

Author Closing Comment

by:newbie27
ID: 31470100
thanks
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question