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.
FINAL Well i found my solution in fact is really simple when you know it...
Replacing the Params[x].AsString
with Params[x].AsAnsiString
works!!
I found this solution a few hours after the Update 2 but didn't have the time to report it. Sorry.
Thanks for you help.