Get data back from mysql stored procedure call

[RayBills]

Is there any way to get data back from a call to a mysql stored procedure? I am able to call the stored procedure just fine, but when I try to use types OUT or INOUT to return data/results the call to the stroed procedure fails because the data being passed into the stored procedure is being interpreted as a literal rather than as a variable.

Any ideas?
This is the error:

[MySQL][ODBC 5.1 Driver][mysqld-5.1.30]OUT or INOUT argument for routine xxxx is not a variable or NEW pseudo-variable in BEFORE trigger.

[forumsupport]

Hello,

Currently, unfortunately, you cannot directly get the recordset returned by executing a stored procedure into Automation Anywhere variable. However, you can try this, at the end of stored procedure you can store the resultant recordset into any temporary table using 'Insert Into Table' statement then using 'Execute Query' command get the resultant data from that temporary table.

Hope that helps.

[RayBills]

Ok. I understand the restriction for calling the stored sql procedure directly from my AA task. As a work around, I am assuming I could put the call to the stored procedure into a vbs or js script and in that script do the stored procedure call. This assume that vbs and js scripts can handle getting data back from the sql stored procedure - which I think is a pretty good assumption.

So - Do you have any examples of AA vbs or js scripts that would do a stored procedure call and get an updated paramaneter back?

Thanks again!

[forumsupport]

Hello,

You can certainly execute Stored Procedure from Automation Anywhere using Database:Run Stored Procedure command. In addition, you can pass parameter(s) to stored procedure. Unfortunately, you cannot return value(s) from stored procedure however, this feature is supported in upcoming release of product, which is going to release shortly.

To get around the issue, add one more command in your stored procedure, insert 'Insert into' statement to store the return values in some temporary table. After Database:Run Stored Procedure command, you can insert 'Execute SQL Statement' command to retrieve the return values from same temporary table.

Text format of stored procedure would appear something like this,

create or replace procedure ProcParam(a in number, b in number)
is

begin

UPDATE productTable set qty = a where orderid = b;

INSERT INTO product_temp (qty, orderid)
SELECT qty, orderid
FROM productTable
WHERE orderid= b;

end;

Hope that helps.