Recent

Author Topic: [SOLVED] Component to call a Stored Procedure ( Firebird )  (Read 9372 times)

finalist

  • Jr. Member
  • **
  • Posts: 84
[SOLVED] Component to call a Stored Procedure ( Firebird )
« on: September 20, 2011, 10:13:07 pm »
I need to call a Stored Procedure into a Firebird Database.
Please suggest me about a Lazarus component.
« Last Edit: September 22, 2011, 04:01:45 pm by finalist »

Lacak2

  • Guest
Re: Component to call a Stored Procedure ( Firebird )
« Reply #1 on: September 22, 2011, 07:46:33 am »
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

  • Jr. Member
  • **
  • Posts: 84
Re: Component to call a Stored Procedure ( Firebird )
« Reply #2 on: September 22, 2011, 08:28:54 am »
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

  • Full Member
  • ***
  • Posts: 173
Re: Component to call a Stored Procedure ( Firebird )
« Reply #3 on: September 22, 2011, 09:36:42 am »
UIB and ZeosLib is what you want.

ZeosLib does not support Oracle stored procedure.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Component to call a Stored Procedure ( Firebird )
« Reply #4 on: September 22, 2011, 11:03:06 am »
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 ?

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...
« Last Edit: September 22, 2011, 11:11:13 am by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Arbee

  • Full Member
  • ***
  • Posts: 223
Re: Component to call a Stored Procedure ( Firebird )
« Reply #5 on: September 22, 2011, 11:39:25 am »
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.
1.0/2.6.0  XP SP3 & OS X 10.6.8

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: Component to call a Stored Procedure ( Firebird )
« Reply #6 on: September 22, 2011, 11:54:21 am »
zeosLib has TZStoredProc component, I think that's what you need.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Component to call a Stored Procedure ( Firebird )
« Reply #7 on: September 22, 2011, 12:01:26 pm »
A 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?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

finalist

  • Jr. Member
  • **
  • Posts: 84
Re: Component to call a Stored Procedure ( Firebird )
« Reply #8 on: September 22, 2011, 04:01:19 pm »
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 ..