Solved

MySQL stored procedure

Posted on 2008-06-24
10
537 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 142

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
 
LVL 142

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
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 142

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 142

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

912 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

17 Experts available now in Live!

Get 1:1 Help Now