How to return parameter from mysql store procedure (1 Viewer)

[mstres]

I created store procedure in mySql db. SP have statements with update several tables and I passing string as output parameter from Error Handler inside Store procedure.
If Transactions is done with out any error after Submit transaction I set this string to "Done" for example.
When I call SP from mySQL and use Select @output parameter, I have string result as "Done" or same "Error"
When I call SP from Access 2003 using qrydef.execute I have system error with ODBC failing.
By the way SP working OK with out any output parameters.
Code from Access 2003:

Public Sub DoSendToBCR_Trans(inBus_Type, inE_Rate, inWO_Number, inStatus, inAccount_Exec, inNo_Sites, inBandwidth, myeng, myNotes, myuser, myoutstatus_message)
On Error GoTo Err_Proc
Dim qDef As QueryDef

Dim strStatusMessage As String
Set qDef = CurrentDb.CreateQueryDef(tmpQryName)
qDef.Connect = gblConnectString
qDef.sql = "CALL DoSendToBCR_Trans" & _
"(" & inBus_Type & ",'" & inE_Rate & "', " & inWO_Number & ", " & inStatus & ",'" & inAccount_Exec & "', " & inNo_Sites & ", " & inBandwidth & ",' " & myeng & "',' " & myNotes & "',' " & myuser & "',' " & myoutstatus_message & "')"
qDef.ReturnsRecords = True
qDef.Execute
strStatusMessage = myoutstatus_message
DisplayCustMsgBox (strStatusMessage)
Set qDef = Nothing
Exit_proc:
Exit Sub
Err_Proc:
DisplayCustOkMsgBox Err.Description
Resume Exit_proc
End Sub

Store Procedure from MySql:

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`DoSendToBCR_Trans`$$
CREATE DEFINER=`mysqladmin`@`%` PROCEDURE `DoSendToBCR_Trans`(IN myBus_Type Int, IN myE_Rate VARCHAR(10), In myWO_Number double, In myStatus Int, in myAccount_Exec varchar(50), in myNo_Sites int, in myBandwidth int, in myeng VARCHAR(50), in myEngNotes mediumtext, In myuser vARCHAR(15), out status_message varchar(50))
BEGIN
DECLARE EXIT HANDLER
FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
begin
ROLLBACK;
set status_message = 'Error occurred terminating ';
end;
SET autocommit=0;
Start transaction;
INSERT INTO Financials_Tbl (`Customer Name`, `Capital Outlay`, Bus_Type, `E-Rate`, `Business Case`, Notes, Product, Market, WO_Number, `Status`, `Account Exec`, `Vertical Market`, SDE, RCL, Job_Category_Id, No_Sites, Bandwidth)
SELECT WO_Tbl.Billing_Name, WO_Tbl.`Capital Outlay`, myBus_Type , myE_Rate, WO_Tbl.`Business Case`, WO_Tbl.`WO Description`, WO_Tbl.Service, WO_Tbl.Market, WO_Tbl.WO_Number, myStatus , myAccount_Exec, WO_Tbl.Sales_Channel_ID, WO_Tbl.SDE_ID, WO_Tbl.RCL, WO_Tbl.Job_Category_Id, myNo_Sites , myBandwidth
FROM WO_Tbl
WHERE (((WO_Tbl.WO_Number)= myWO_Number));
SET @new_job_id=(SELECT LAST_INSERT_ID());
Insert into `bcr_log_tbl`(`BCR_ID`, `User`, `Action`, `WO_Number`)
Values(@new_job_id, myeng, 19, myWO_Number);
Update `WO_Tbl` Set `BCR_Created` = -1, `Date Closed` = Now() , `Status` = 'Closed', `Eng_Notes` = myEngNotes
WHERE (((`WO_Tbl`.`WO_Number`)= myWO_Number));

Insert into Audit_Tbl(`WO`, `Actual_User`, `Engineer`, `Actiontype`, `Department`, `Action`)
Values(myWO_Number, myuser, myeng, 'Status Change', 'Engineering', 'Closed' );
commit;
set status_message = 'Done';
END$$
DELIMITER ;

Thank you
Mike

 

[MSAccessRookie]

I created store procedure in mySql db. SP have statements with update several tables and I passing string as output parameter from Error Handler inside Store procedure.
If Transactions is done with out any error after Submit transaction I set this string to "Done" for example.
When I call SP from mySQL and use Select @output parameter, I have string result as "Done" or same "Error"
When I call SP from Access 2003 using qrydef.execute I have system error with ODBC failing.
By the way SP working OK with out any output parameters.
If any body have any idea i will be appreciate.
Mike

I am familiar with two possibilities for handling this.

1. If you must have a Stored Procedure, then defining a parameter as OUT or INOUT will allow the Stored Procedure to return a value to the calling environment.
2. Another (perhaps better) option, would be using a Stored Function as opposed to a Stored Procedure, and passing the value back to the calling environment.

The link below gives examples.

http://www.roseindia.net/mysql/mysql5/stored-procedures-and-functions.shtml

 

[mstres]

Thank you for your help.
Unfortunately, I could not see web site(security reason)
If you can give me example how to call Stored Function in MYSQL from Access DB or
how to call Store Procedure with return parameters. Will be grate!!!
Every think is working when I call Stored Procedure or Stored Function from Query Builder in MySQL.
Thank you very much for your help
Mikhail

 

[MSAccessRookie]

As you are unable to see the website that I provided, try to see if you can find some of your own. I found that one as the first one returned by the following Google Search:

MY-SQL Stored Procedures and Functions

 

[mstres]

All this examples using ADO, my application using DAO.
VBA in Access and Access DB tables link to MySql DB
This is reason for using qrydef
Thank you for your time
Mike