[SOLVED] Component to call a Stored Procedure ( Firebird )
lazarus / 16-Databases

----------------------------------------------------------------------
finalist · 2011-09-20 20:13

I need to call a Stored Procedure into a Firebird Database.
Please suggest me about a Lazarus component.

----------------------------------------------------------------------
Lacak2 · 2011-09-22 05:46

AFAIk you can use TSQLQuery and set:
 SQLQuery1.SQL.Text:='execute procedure xxx'
 SQLQuery1.ExecSQL;

It depends if your procedure is selectable (returns result set) or
not.

----------------------------------------------------------------------
finalist · 2011-09-22 06:28

I use a Stored Procedure because I need a return result.
The Stored Procedure calls a Generator to get a value for field named
ID - primary key for the Table.
I need to know the value of ID immediately after inserting a new
record.
Is it possible that ?

----------------------------------------------------------------------
jixian.yang · 2011-09-22 07:36

UIB and ZeosLib is what you want.

ZeosLib does not support Oracle stored procedure.

----------------------------------------------------------------------
BigChimp · 2011-09-22 09:03

Quote from: finalist on September 22, 2011, 08:28:54 amI use a Stored
Procedure because I need a return result.
The Stored Procedure calls a Generator to get a value for field named
ID - primary key for the Table.
I need to know the value of ID immediately after inserting a new
record.
Is it possible that ?

In that case, try treating the stored procedure as a regular query,
e.g. (of the top of my head) SELECT resultvar FROM storedproc;
i.e. don't use .execsql but .open...

----------------------------------------------------------------------
Arbee · 2011-09-22 09:39

A stored procedure that returns a result is actually called a User
Defined Function (UDF).

As far as I remember you must use the UDF where you otherwise would
use a field name, so if the UDF is called "GetID":

Code: [Select]SELECT getID() FROM table

Mind you ... I haven't checked any specific Firebird peculiarities. 
This is based on what I know from DB2.

----------------------------------------------------------------------
tatamata · 2011-09-22 09:54

zeosLib has TZStoredProc component, I think that's what you need.

----------------------------------------------------------------------
BigChimp · 2011-09-22 10:01

Quote from: Arbee on September 22, 2011, 11:39:25 amA stored procedure
that returns a result is actually called a User Defined Function
(UDF).
...
Mind you ... I haven't checked any specific Firebird peculiarities. 
This is based on what I know from DB2.
Hmmm, no in Firebird that's not the right terminology: a UDF in
Firebird is external code compiled to a library (dll/so) that indeed
returns a result. Invocation is the same as you indicated.

In Firebird, a stored procedure can IIRC either return a result set, a
scalar (one single value) or nothing at all.
In the last case, use execute. In the former two cases, IIRC in SQLDB
you can treat it as a regular query, i.e.
Code: [Select]SELECT resultvar FROM storedprocname
@tatamata: does the Zeosdb TZStoredProcedure provide functionality for
return values as well?

----------------------------------------------------------------------
finalist · 2011-09-22 14:01

Thank you very much !
I have success using a regular TSQLQuery with 'SELECT resultvar FROM
storedproc'
Thats because I am using SQLdb components ....
It is possible to be a working solution using TZStoredProc with Zeos
Access components, but I did not test this way ..