Passing parameters OUT from Stored Procedures

[Haseeb]

Hi

I am trying to get parameters OUT from a Stored Procedures running in MySQL backend.

My Stored Procedure is(In MySQL)

CREATE PROCEDURE sp_grandtotal (IN orderid INT, OUT gtotal decimal(6,2))

SELECT SUM(quantity*unitprice) INTO gtotal

FROM OrderedProducts

WHERE _kf_OrderID=orderid

Now I am trying to run/call it from FMs Execute SQL Script as Calculated SQL text:

"SET @gtotal:=0.0;

call sp_grandtotal("&Orders::__kp_OrderID&",@gtotal);

Select @gtotal"

There are two problems:

Not sure about the syntex here in Calculated SQL text because its not running successfully though it runs in MySQL backend and how to capture the output variable and show on layout.

When I Run this ODBC Query log says:

-- Query logging

--

-- Driver name: MySQL ODBC 5.1 Driver Version: 05.01.0005

-- Timestamp: 091110 17:07:58

SET SQL_MODE='STRICT_ALL_TABLES';

SET @gtotal:=0.0; call sp_grandtotal(2,@gtotal); Select @gtotal;

And ODBC error in FM shows as the attachment

Edited November 10, 2009 by Guest

[jamesducker]

Generally, if you want a stored procedure to return stuff in to FileMaker, you should use "Import Records" in FileMaker, with the SQL statement that invokes the procedure specified as the query at the time of import.

Hope that points you a little way in the right direction.

James

[Haseeb]

Thanks for your response.

I am now also considering the Import option from Script lets see how it goes.

Actually I was thinking to pass a parameter from FM and then execute that Stored Procedure and get the resulted value as parameter back to FM.