MySQL Bugs: #28259: Execute the MySQL Stored Procedure From ASP Page

Bug #28259	Execute the MySQL Stored Procedure From ASP Page
Submitted:	5 May 2007 10:18	Modified:	20 May 2007 22:02
Reporter:	Muthu K	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.0.37-community-nt-via TCP/IP	OS:	Windows (Win XP)
Assigned to:		CPU Architecture:	Any

Description:
Hi Evreyone,

Mention the ASP Code for "Calling a Stored Procedure from an asp page".
and How to write the record values in page?

please suggest me.

Thanks in Advance
Muthu

How to repeat:
My Code :
<%
Const adParamInput       = 1
Const adParamOutput      = 2
Const adInteger          = 3
Const adCmdStoredProc    = 4
Const adCurrency         = 6
Const adExecuteNoRecords = 128

dim cnConn
dim rsStockIn

set cnConn=server.CreateObject("ADODB.Connection")
Set objCmd = Server.CreateObject("ADODB.Command") 

cnConn.open(Application("cnStr"))
ObjCmd.ActiveConnection = cnConn 
ObjCmd.CommandType = 4
ObjCmd.CommandText = "Stocks(@DescID,@Name, @Config,@Brand,@Inward,@bPrice,@Outward,@sPrice)" 
ObjCmd.Parameters.Refresh 
objCmd.Execute , , adAsyncExecute%>

Stored Procedure :-
CREATE  PROCEDURE  Stocks(
OUT DescID Varchar(255),
OUT Name Varchar(255),
OUT Config Varchar(255),
OUT Brand Varchar(255),
OUT Inward Numeric(9),
OUT bPrice Numeric(9),
OUT Outward Numeric(9),
OUT sPrice Numeric(9))
SELECT
SI.ProdID,
SI.ProdName,
SI.Configuration,
SI.Brand,
Sum(SI.Qty) as TotalIn,
SI.Price,
Sum(SO.Invoice_Qty) as TotalOut,
SO.Invoice_Amount
From StockIn as SI
INNER JOIN StockOut as SO
ON SI.ProdID=SO.Prod_Desc_ID
GROUP BY
SI.ProdID,
SI.ProdName,
SI.Configuration,
SI.Brand;

Excuted in Query Browser:-
CALL Stocks(@DescID,@Name, @Config,@Brand,@Inward,@bPrice,@Outward,@sPrice);
Its Executed and Returned the Resultset in QueryBrowser.

Its Fired Some Error as Below:-
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{ call Stocks(@DescID,@Name, @Config,@Brand,@Inward,@bPrice,@Outward,@sPrice) }' at line 1
/CRM/print/stock_report.asp, line 32

Thank you for a problem report. What exact version of Connector/ODBC do you use?

Note that as you were able to execute your procedure from the other environment, it is NOT a server's bug anyway.

I am just not sure you can use user variables (like @Name) in VB code directly. You have to use prepared statement and bind parameters, among other things. 

Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/myodbc-examples-overview.html, http://dev.mysql.com/doc/refman/5.0/en/myodbc-usagenotes-apptips.html#myodbc-usagenotes-ap...) and links from it also.

Hi My ODBC Version is below:-
 
MyODBC-3.51.11-2-win.exe

i changed the parameter name. 

(@DescID,@pName,@pConfig,@pBrand,@pInward,@bPrice,@pOutward,@sPrice)" 
ObjCmd.Parameters.Refresh 
objCmd.Execute , , adAsyncExecute

it fired as below error:-

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{ call Stocks(@DescID,@pName, @pConfig,@pBrand,@pInward,@bPrice,@pOutward,@sPric' at line 1
/CRM/print/stock_report.asp, line 32

plz suggest me.

hi 
plz mention syntax for execute the mysql stored procedure from asp page.

thanks in advace.

Hi Muthu. I believe this is a duplicate of Bug#17898 but will look into it once again.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Explanation: Muthu, see test cases and discussion in Bug#15635 please.