How can i execute a stored procedure

[Ati]

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]

Does the SPTEST1 return a result set ?

[RudieD]

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]

No. Here is the Code of the SP:

Code: [Select]

BEGIN
  UPDATE TEST1
  SET TEXT = :text
  WHERE (ID = :ID);
END

[RudieD]

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]

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]

Yipiehhh,

if i use

Code: [Select]

qrmain.sql.text:='execute procedure SPTEST1(:ID,'''+edsp.Text+''')';

then it works.  Thank you for your help.

[RudieD]

Enjoy !!!

[ciapek]

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