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
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
Last edited: