Главная страница | назад





Article #25249: Calling an IB stored procedure using TStoredProc

Problem:
Looking for an example of how to call an InterBase 
stored procedure using Delphi's TStoredProc component.

Solution:
The information in this article applies to:
  * InterBase v4.x
  * InterBase v5.x
  * Delphi 1.x, 2.x, 3.x, 4.x

Here is an example of how to call an InterBase stored 
procedure using Delphi's TStoredProc component.  

The stored procedure retrieves and returns the next value
from a generator.   You must create this stored procedure and
generator in the database.


Creating the generator:
==================
create generator cust_no_gen;

Here is the InterBase Stored procedure:

===============================
create procedure proc1 returns(e_no integer) 
as begin
  e_no=gen_id(cust_no_gen,1);  
end

------------------------------------------------------
------------------------------------------------------------
Here are the steps to accomplish the task in Delphi:
* Put TDatabase, TStoredProc, TButton, TLabel components   
  on the form.

* Set the following properties for TDatabase component:
   >  AliasName to the BDE alias pointing to the database created
   > Fill in DatabaseName to be used by other database components

   > Connected to True

* Set the following properties for TStoredProc 
component:
   > Databasename to the name you set for your TDatabase component
   > StoredProcName to the name of the stored procedure in 
        the database (You can select from drop down list)


* Double click on the TButton component and add the 
   following code in between begin and end.

storedproc1.execproc;
label1.caption:=storedproc1.parambyname('e_no').asstring;

The result would look like this:
=========================
procedure TForm1.Button2Click(Sender: TObject);
begin
storedproc1.execproc;
label1.caption:=storedproc1.parambyname('e_no').asstring;
end;
end.

*  Save the project/form/unit and Run.
*  Click on the Button and the next generated number 
   will be displayed.

Some benefits of getting the gen id first before inserting it:
=============================================
*  The next generated number will be visible, so it is known
    exactly what's inserted.
*  Using trigger to call the generator in Delphi often results
    in error "Record Key Deleted".  Calling the generator using
    TstoredProc is a workaround for this error.

Last Modified: 02-OCT-00