[Lazarus] Stored Procedure and Returning Values

Eduardo Lopez dar_alle at yahoo.com.ar
Sat Mar 28 20:52:17 CET 2009


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ó:
> 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
>
>
> Eduardo Lopez wrote:
>> Kamil:
>> The way is:
>>
>> select * from ZnajdzKodKreskowy (....
>> //Asign parameters
>> qry1.open
>> //Read the results
>>
>> This "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ó:
>>   
>>> 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:
>>>   
>>>     
>>>> 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 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
>>>>>  
>>>>> _______________________________________________
>>>>> Lazarus mailing list
>>>>> Lazarus at lazarus.freepascal.org
>>>>> http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
>>>>>
>>>>>   
>>>>>     
>>>>>       
>>>>>         
>>>> _______________________________________________
>>>> Lazarus mailing list
>>>> Lazarus at lazarus.freepascal.org
>>>> http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
>>>>
>>>>   
>>>>     
>>>>       
>>> _______________________________________________
>>> Lazarus mailing list
>>> Lazarus at lazarus.freepascal.org
>>> http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
>>>
>>>   
>>>     
>>
>> _______________________________________________
>> Lazarus mailing list
>> Lazarus at lazarus.freepascal.org
>> http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
>>
>>   
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Lazarus mailing list
> Lazarus at lazarus.freepascal.org
> http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
>   




More information about the Lazarus mailing list