How can i execute a stored procedure
lazarus / 16-Databases

----------------------------------------------------------------------
Ati · 2006-08-16 07:53

Hello,

i wrote a simple stored procedure to update a dataset. Now i want to
execute the SP from my program but it doesn´t work. I found an advice
like this "select StoredProcedureName(Insert1,Insert2)". That´s all i
found to this topic. I tried this and nothing happens. What i must add
to my code....TQuery.ExecSql and TQuery.open produce an error.
Here is my code:
Code: [Select]procedure TForm1.Button4Click(Sender: TObject);
var nr:Integer;

begin
  nr:=qrmain.FieldByName('ID').AsInteger;
  dbmain.Close;
  dbmain.Open;
  if dbmain.Connected=true then
  begin
    dbmain.Connected:=false;
  end;
  qrmain.sql.text:='select SPTEST1(:ID,'''+edsp.text+''')';
  qrmain.params.ParamByName('ID').AsInteger:=nr;
  qrmain.Open;
  trmain.Commit;
end;

----------------------------------------------------------------------
RudieD · 2006-08-16 08:58

Does the SPTEST1 return a result set ?

----------------------------------------------------------------------
RudieD · 2006-08-16 09:02

If it doesn't, rather use :
Code: [Select]procedure TForm1.Button4Click(Sender: TObject);
var nr:Integer;

begin
  nr:=qrmain.FieldByName('ID').AsInteger;
  dbmain.Close;
  dbmain.Open;
  if dbmain.Connected=true then
  begin
    dbmain.Connected:=false;
  end;
  qrmain.sql.text:='exec SPTEST1(:ID,'''+edsp.text+''')';
  qrmain.params.ParamByName('ID').AsInteger:=nr;
  qrmain.ExecSQL;
  trmain.Commit;
end;

----------------------------------------------------------------------
Ati · 2006-08-16 09:02

No. Here is the Code of the SP:
Code: [Select]BEGIN
  UPDATE TEST1
  SET TEXT = :text
  WHERE (ID = :ID);
END

----------------------------------------------------------------------
RudieD · 2006-08-16 09:43

Depending on the database you might have to use
Code: [Select]qrmain.sql.text:='exec SPTEST1(:ID,'''+edsp.text+''')';
or
Code: [Select]qrmain.sql.text:='execute
SPTEST1(:ID,'''+edsp.text+''')';
or
Code: [Select]qrmain.sql.text:='SPTEST1(:ID,'''+edsp.text+''')';

and also remember to use qrmain.ExecSQL and not qrmain.Open;

----------------------------------------------------------------------
Ati · 2006-08-16 10:42

Nothing works.. if i use "exec" it produce a EDatabaseError because
"exec" is unknown. If i used "execute" or only "SPTEST1" then is ther
an error because SPTEST1 is unknown??? But it is really the name of
the Procedure. My Login is "SYSDBA" so i have the right´s to execute
the SP.

P.S. i use Firebird 1.5

----------------------------------------------------------------------
Ati · 2006-08-16 10:51

Yipiehhh,

if i use
Code: [Select]qrmain.sql.text:='execute procedure
SPTEST1(:ID,'''+edsp.Text+''')';

then it works.  Thank you for your help.

----------------------------------------------------------------------
RudieD · 2006-08-16 11:15

Enjoy !!!

----------------------------------------------------------------------
ciapek · 2008-11-19 11:10

What i must doing when a wont get paramerter from procedure???