Solved

MySQL stored procedure

Posted on 2008-06-24
10
542 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

831 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