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

Question

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

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 :

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'

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 :

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

Answer 1

TUniSQL dosn´t return a data set, only can excecute SP (without return).

You can use TUniQuery:

var
 ql : TUniQuery;
 sentence : String;
begin
 ql:=TUniQuery.Create(nil);
 ql.Connection := UniConnection1;
 sentence:='CALL invite_user(''UserName'', ''recCode'', ''[email protected]'', ' +
           '''inviter'', @fErr, @fMsg);' +
           'SELECT @fErr, @fMsg;';
 ql.SQL.Add(sentence);
 ql.Open;
 Memo1.Lines.Add(ql.FieldByName('@fErr').AsString);
 Memo1.Lines.Add(ql.FieldByName('@fMsg').AsString);
 ql.Close;
 ql.DisposeOf;
end;

In the SP ;

SET fErr = 1;
SET fMsg = 'Not Found !';

The @fErr, @fMsg in my code are local variables.