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:Microsoft Windows (Win XP)
Assigned to: CPU Architecture:Any

[5 May 2007 10:18] Muthu K
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
[5 May 2007 10:51] Valeriy Kravchuk
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.
[5 May 2007 11:17] Muthu K
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.
[5 May 2007 11:21] Muthu K
hi 
plz mention syntax for execute the mysql stored procedure from asp page.

thanks in advace.
[20 May 2007 21:47] Tonci Grgin
Hi Muthu. I believe this is a duplicate of Bug#17898 but will look into it once again.
[20 May 2007 22:02] Tonci Grgin
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.