TmyStoredProc output parameters

[bluewwol]

Hi;

I am calling a stored procedure from my delphi code as below:-
My procedure is being executed, the data is being committed to the DB, when I execute the code manually from the mySQL command line it executes and returns all 4 of my parameters, however in my Delphi code (below) the results are unassigned. When I step through the parameters I see the named parameters, but not their values?

Any ideas?

Delphi 2010 mydac ver 7.5.10




<snip>
with spLogon do
begin
ParamByName('in_Email').AsString := Trim(input.eMail);
ParamByName('in_Password').AsString := Trim(input.password);
ParamByName('in_IPaddress').AsString := Trim(input.ipAddress);
ParamByName('in_Browser').AsString := Trim(input.browser);
Execute;
// result.status := ParamByName('o_ClientStatus').AsString;
// result.userID := ParamByName('o_ClientUser').AsInteger;
// result.token := ParamByName('o_ClientToken').AsString;
result.clientID := ParamByName('Result').Value;

if assigned(spLogon.Params) then
begin
for Idx := 0 to spLogon.Params.Count - 1 do
begin
if (spLogon.Params[Idx].ParamType = ptOutput) then
spLogon.Params[Idx].Value := spLogon.ParamByName(spLogon.Params[Idx].Name).Value;
end;
end;

end;
</snip>

[Dimon]

We cannot reproduce the problem. Please provide us the script you used to create your stored procedure, as well as the SQL query for its calling.

[bluewwol]

My Stored Procedure is created as follows:-

I am not creating any SQL code to explicitly call the procedure, I am simply creating the component, assigning the connection, assigning the parameters, executing the procedure (from the delphi component and attempting to read from the components output parameters. The values of these parameters is NULL.


<stored Proc>
CREATE PROCEDURE `firstrespond`.`LoginClient`(
IN in_Email VARCHAR(128),
IN in_Password VARCHAR(45),
IN in_SessionID CHAR(25),
IN in_IPaddress VARCHAR(16),
IN in_Browser VARCHAR(256),
OUT o_ClientUserStatus CHAR(1),
OUT o_ClientUser INT,
OUT o_ClientToken VARCHAR(64),
OUT o_FirstName VARCHAR(64),
OUT o_LastName VARCHAR(64),
OUT o_ClientName VARCHAR(96),
OUT o_ClientUserValid datetime,
OUT o_ClientUserLastLogin datetime,
OUT o_ClientStatus CHAR(1),
OUT o_ClientType CHAR(1),
OUT o_ClientValid datetime,
OUT Result int)
BEGIN
DECLARE iEmail VARCHAR(128);
DECLARE iPwd VARCHAR(45);
DECLARE iStatus CHAR(1);
DECLARE iToken VARCHAR(64);
DECLARE iAcctNbr INT;
DECLARE iUserID INT;
DECLARE CUValid datetime;
DECLARE CULastLogin datetime;
DECLARE CStatus CHAR(1);
DECLARE CType CHAR(1);
DECLARE CValid datetime;
DECLARE CName VARCHAR(96);

SET Result = -1;
SET o_ClientUserStatus = 'Z';
SET o_ClientToken = '00000';
SET o_ClientUser = -1;
SET o_FirstName = '.';
SET o_LastName = '.';
SET o_ClientName = '.';
SET o_ClientUserValid = '2000/01/01';
SET o_ClientUserLastLogin = '2000/01/01';
SET o_ClientStatus = '.';
SET o_ClientType = '.';
SET o_ClientValid = '2000/01/01';
SET o_ClientName = '.';


SELECT CU.idClientUser, CU.Clients_AcctNbr, CU.Email, CU.Password, CU.Status, CU.SecToken, CU.ValidTo, CU.LastLogin, C.Status, C.ClientType, C.ValidTo, C.ClientName,
CU.FirstName, CU.LastName
INTO iUserID, iAcctNbr, iEmail, iPwd, iStatus, iToken, CUValid, CULastLogin, CStatus, CType, CValid, CName, o_FirstName, o_LastName
FROM ClientUser CU
JOIN Clients C ON CU.Clients_AcctNbr = C.AcctNbr
WHERE CU.Email = in_Email;

IF iPwd IS NULL THEN
SET Result = -2;
ELSE
IF BINARY iPwd = BINARY in_Password then
SET Result = iAcctNbr;
SET o_ClientUserStatus = iStatus;
SET o_ClientToken = iToken;
SET o_ClientUser = iUserID;

SET o_ClientUserValid = CUValid;
SET o_ClientUserLastLogin = CULastLogin;
SET o_ClientStatus = CStatus;
SET o_ClientType = CType;
SET o_ClientValid = CValid;
SET o_ClientName = CName;

UPDATE ClientUser SET LastLogin = NOW() WHERE idClientUser = iUserID;
END IF;
END IF;
INSERT INTO `firstrespond`.`LoginArchive` (`Email`, `IPAddr`, `LoginResult`, `LoginAttempt`, `Status`, `Browser`, `ClientUserID`, `Token`, `SessionID`)
VALUES (in_Email, in_IPaddress, Result, NOW(), iStatus, in_Browser, iUserID, iToken, in_SessionID);

END
</Stored Proc>

[Dimon]

Please, try to download the latest MyDAC build (8.2.6) and check if this problem still exists.