Solved

MySQL stored procedure

Posted on 2008-06-24
10
533 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
  • 6
  • 4
10 Comments
 
LVL 8

Author Comment

by:newbie27
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 142

Accepted Solution

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

0
 
LVL 8

Author Comment

by:newbie27
Comment Utility
I have amended it as per yours suggestion but still gives the same error ...
screen.JPG
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now