Mailing lists / lazarus / 2009-03
6 messages · 2 people · –
Reply by email Subscribe to this list Your subscription
Read-only archive. Use j and k to move between messages.
Hi,
I have stored procedure in FireBird:
EXECUTE PROCEDURE ZnajdzKodKreskowy (:Kod, 0, 0, 0, :IleCyfrSprawdzac) RETURNING_VALUES (:Jest, :Zlecenie, :I1, :I2, :I3, :I4, :D, :I5, :S);
I was trying to get returning values to lazarus code, but I couldn't. I find out that there is no StoredProcedure element from Delphi. How could I get this values? I would be pleased if someone wrote an example.
And sorry for my English.
Best regards,
Kamil Walas
Hi,
you must do a "select * from ZnajdzKodKreskowy (:Kod, 0, 0, 0, :IleCyfrSprawdzac)"
The example:
qry01.SQL.Clear;
qry01.SQL.Text:= 'select * from ZnajdzKodKreskowy (:Kod, 0, 0, 0,
:IleCyfrSprawdzac)';
qry01.Params.ParamByName('Kod').AsString:= 'AAA';
qry01.Params.ParamByName('IleCyfrSprawdzac').AsString:= 'BBB'; // I
don't know what type are your paremeters :-)
qry01.Open;
MyResult01:=qry01.FieldByName('Jest').AsString;
MyResult02:=qry01.FieldByName('Zlecenie').AsString;
MyResult03:=qry01.FieldByName('...others...').AsString;
qry01.Close;Have a nice weekend!
Eduardo.
Kamil Walas escribi?:
Hi,
I try before but it didn't work. This procedure work if Ido 'Execute procedure ZnajdzKodKreskowy (:Kod, 0, 0, 0, :IleCyfrSprawdzac) ' but if I do
'SELECT * FROM ZnajdzKodKreskowy (:Kod, 0, 0, 0, :IleCyfrSprawdzac)' I got nothing. It simply doesn't work that way.
Code with:
qry01.SQL.Text:= 'Execute procedure ZnajdzKodKreskowy (:Kod, 0, 0, 0, :IleCyfrSprawdzac) ';
qry01.Open.Active := True;I get erreor: 'Statment cannot be performed on non-select stetment' or something like that and I must do
qry01.ExecSQL;but I don't know how to get returning parameters in that kind of instruction.
Kamil
Eduardo Lopez pisze:
Kamil:
The way is:
select * from ZnajdzKodKreskowy (....
//Asign parameters
qry1.open
//Read the resultsThis "open" the query and you can read the result from the stored procedure (or loop reading it).
If you use 'Execute procedure ...' you must use qry1.ExceSQL because in this case is for Stored Procedures that don't returns result.
Are you tried to do the "select * from ZnajdzKodKreskowy (...." in the SQL query analizer (Flamerobin, IBExperte, etc. etc.)? It works there?
Your Stored procedure is "selectable" ?
(http://firebird-with-delphi.blogspot.com/2007/07/fb-selectable-stored-procedure.html)
I am a newbie too in Lazarus and have the same question some time ago, and this list help me too, so i can confirm that this is the way :-)
Regards
Eduardo.
Kamil Walas escribi?:
I didn't wrote it explicite, I use FlameRobin and in SQL query with SELECT I got nothing but EXECUTE PROCEDURE returns some rows. Using FlameRobin:
'EXECUTE PROCEDURE ZNAJDZKODKRESKOWY('XXXXXXXX',0,0,0,7);' - one row with data
'SELECT * FROM
ZNAJDZKODKRESKOWY('XXXXXXXX',0,0,0,7);' - no rows with data
I don't know how to get result rows from 'EXECUTE ..' in Lazarus.
Execution this code:
SQLQuery1.SQL.Text := 'EXECUTE PROCEDURE
ZNAJDZKODKRESKOWY('+''''+'XXXXXXXX'+''''+',0,0,0,7);';
SQLQuery1.Open;
ShowMessage(SQLQuery1.FieldByName('ZLECENIE').AsString);
SQLQuery1.Close;I got:
'SQLQuery1: Cannot open a non-select statement'
I think the problem is that my procedure is not "selectable" and I don't know to handle with it.
Is there a way to construct a SQL statment to be "selectable" or use different code in Lazarus?
Kamil
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.lazarus.freepascal.org/pipermail/lazarus/attachments/20090328/7ec382d2/attachment-0001.html
I send you a link to an article that explain how to do a stored procedure selectable. I copy/paste the example here:
CREATE PROCEDURE GET_EMP_PROJ( EMP_NO SMALLINT)
RETURNS ( PROJ_ID CHAR(5))
AS
BEGIN
FOR SELECT proj_id
FROM employee_project
WHERE emp_no = :emp_no
INTO :proj_id
DO
SUSPEND;END
The trick is the use of FOR select....
DO
SUSPEND;Try to insert a simple query in this example, create the SP in your database and run it doing a "select * from GET_EMP_PROJ(33)" in Flamerobin.
I remember that FlameRobin has a utility for create selectable stored procedures. Give it a try.
Regards.
Eduardo.
Kamil Walas escribi?: