How to get value of an Output Parameter of a MySQL SP with TUniSQL

[Mahmood_M]

Hi
I have created a Stored Procedure in a MySQL DataBase that have 2 output parameters like this ( for Example ) :

Code: Select all

CREATE PROCEDURE `invite_user`(fUName VARCHAR(15) CHARSET utf8, fRegCode VARCHAR(15) CHARSET utf8, fEmail VARCHAR(30) CHARSET utf8, fInviter VARCHAR(15) CHARSET utf8,
                               OUT fErr INT, OUT fMsg VARCHAR(255) CHARSET utf8)
BEGIN
...
IF (@C = 0) THEN     
 ...
 SET fErr = 0;
 SET fMsg = '';
ELSE
 SET fErr = 1;
 SET fMsg = 'Not Found !';
END IF;
END

I want to execute this SP using TUniSQL and get output value of fErr and fMsg, When I use the SP Call Generator of TUniSQL, it generates this SQL :

Code: Select all

CALL invite_user(:fUName, :fRegCode, :fEmail, :fInviter, @fErr, @fMsg);
SELECT CAST(@fErr AS SIGNED) AS '@fErr', @fMsg AS '@fMsg'

and output parameters are listed in parameters list of TUniSQL as 'fErr' and 'fMsg' ( Parameter type is Output )
But after executing TUniSQL, there is no Value in 'fErr' and 'fMsg'
Execution done without any error, but for example "TUniSQL.ParamByName('fErr' or 'fMsg').AsString" returns an empty String !
I have tried setting parameters in SP like this :

Code: Select all

SET @fErr = 0;

But the problem exists
These parameters are empty too when I use TUniStoredProc instead of TUniSQL !
Is there problem in SP ( problem in setting value of parameters ) ?

I`m using Delphi XE6 and UniDAC 6.1

[ViktorV]

Working with MySQL server, to get an OUT parameter value, you should use the TUniStoredProc component, not TUniSQL. To get the correct value of the OUT parameter, you can use the following code:

Code: Select all

  UniStoredProc.StoredProcName := 'invite_user';
  UniStoredProc.SQL.Text := '';
  UniStoredProc.ExecProc;
  ShowMessage(UniStoredProc.ParamByName('fErr').AsString);

[Mahmood_M]

Thanks
My SP have some other parameters as Input, when I want to assign values of these parameters an error shows : "The Parameter 'MyParam' not found" !

Code: Select all

   UniSP.StoredProcName := 'invite_user';
   UniSP.SQL.Text := '';

   UniSP.ParamByName('fUName').AsString := 'UserName';
   OR
   UniSP.Params.Items[0].AsString := 'UserName';

   UniSP.ExecProc;

* ParamCheck property of TUniStoredProc is True
It seems parameters are recognized after executing !
I have tried to call Prepare procedure of TUniStoredProc after assigning StoredProcName, but I got an access violation at preparation line

how can I set values of input parameters before executing and get values of output parameters after it ?

thanks for your replays ...

[ViktorV]

To have an ability to set the IN parameter of the stored procedure, you should call the TUniStoreProc.PrepareSQL method after the line UniSP.SQL.Text := '';. For example:

Code: Select all

UniSP.SQL.Text := '';
UniSP.PrepareSQL;