I have to migrate an old Delphi 4/BDE/Firebird 1.5. As a first step, I would like to convert it under Delphi XE7/BDE/Firebird 1.5. When all or most of migration bugs will be corrected I'll update Firebird and may later change the BDE.
But I have trouble with a simple use of a TStoredProc with doesn't return any values in his params list after an ExecProc.
Under Delphi 4, to test on a new project, I put a TDatabase and a TStoredProc component. In the FormShow, I wrote this simple code:
if not Database1.Connected then
Database1.Connected := True;
with VerifUser2 do
begin
DatabaseName := Database1.DatabaseName;
Params[0].AsInteger := 1;
Params[1].AsString := 'nico';
Params[2].AsString := '';
ExecProc;
ShowMessage(ParamByName('IP_USER').AsString);
end;
The good value, '10061' for this test, is shown using ParamByName('IP_USER') or Params[3].
I do the same thing under Delphi XE7, new form, put the two components and the connection with my Firebird database seems good and I chose a stored procedure at design time.
But at run time I always have '0' either using ParamByName or Params[3].
Using FireDac, I can read the good value but as I have a lot of BDE components and a lot of code I don't like to have to convert Delphi codes and change the BDE layer at the same time.
Update 1: As asked, here is the SQL code of the procedure:
ALTER PROCEDURE PROC_VERIF_USER (
XID_MASTER Integer,
XLOG Varchar(20),
XPASS Varchar(13) )
RETURNS (
IP_USER Integer,
IP_PROFIL Integer,
LIB_USER Varchar(50),
LOG_USER Varchar(20),
PASS_USER Varchar(13),
ERR_MSG Varchar(100) )
AS
Declare variable vexclu char(1);
Declare variable vitmp integer;
begin
ip_user = 0;
Err_Msg = '';
FOR SELECT
SO.IP_SOCIETE,
SO.SOCIETE_CLE,
SO.SOCIETE_EAN13,
SO.SOCIETE_RAISON_SOCIALE,
SO.SOCIETE_EXCLU,
SO.ID_CLASSE_PRINCIPALE
FROM SOCIETE SO
WHERE
(SO.ID_MASTER = 1) AND
(SO.IDS_SOCIETE_TYPE = 'U') AND
(SO.SOCIETE_CLE = CHARUPPER(:xlog)) AND
(SCOMPARE(SO.SOCIETE_EAN13, :xpass)=0)
PLAN (SOCIETE INDEX (FIX_SOCIETE_CLE))
INTO :ip_user, :log_user, :pass_user, :lib_user, :vexclu, :ip_profil
DO
BEGIN
IF ((Err_Msg = '') and (icompare(ip_profil,0)<>0)) THEN
BEGIN
SELECT F_SUBSTR(CHR(13)||:XID_MASTER||CHR(13),CHR(13)||OBJET_LIBELLE||CHR(13))
FROM OBJET
WHERE IP_OBJET = :ip_profil
INTO :vitmp;
if (vitmp >- 1) then Err_Msg = '%s : Utilisateur non autorisé dans cette société ';
END
IF (Err_Msg <> '') THEN
BEGIN
ip_user = 0;
EXIT;
END
END
IF (ip_user=0) THEN
Err_Msg = '%s : Utilisateur ou mot de passe inconnu';
end^
As this works under Delphi 4/BDE and Delphi XE7/FireDac I don't think that the trouble could come from this but you never know.
Update 2 : I have added a suspend in my procedure and use a BDE TQuery with an SELECT * FROM PROC_VERIF_USER(:master,:username,:password) and i read the data with FieldByName() and it works...
I really have a trouble with the BDE TStoredProc component...
I'm going to try an other procedure of my database just to be sure that the trouble don't come from the procedure.
Thanks.
