Solved

MySQL stored procedure

Posted on 2008-06-24
10
543 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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