Stored procedures in Firebird/Interbase

Posted on 2006-05-17
Medium Priority
Last Modified: 2013-12-09

I've been using MS Access for 2 years as the database in my VB.NET applications.

I've decided now to use Firebird or Interbase.

Can someone provide me with code to build and use, from within VB.NET, stored procedures for insert, update and delete records in these databases.

Best regards
Question by:Ghanisen
  • 4
  • 2
  • 2
  • +1

Expert Comment

ID: 16706082
You can visit http://sourceforge.net/project/showfiles.php?group_id=9028 to download firebird-net-provider. In the VB.NET application you can access firebird/interbase database same as MS Access. If you want to learn how to write SQL to process data in database I think you should read the document.
Client tool to play with Firebird/InterBase database is IBConsole http://info.borland.com/devsupport/interbase/opensource/#ibconsole (Open Source Software).
IBExpert (It is very good but commercial work) http://www.ibexpert.com/
You can write store procedure for databse normally.

Author Comment

ID: 16706743
Thank you for this information but I already have done all that and also downloaded EMS SQL Manager 2005 that allows to d a lot.

But my question is how can I create stored procedures in a Firebird/Interbase database in code from within my VB.NET application.

LVL 19

Expert Comment

ID: 16706871
creating stored procedures in your vb code is unnecessary and not a good idea, use ibexpert
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Expert Comment

ID: 16706923
Create stored procedures is execution a SQL
Please read SQL reference  
LVL 10

Assisted Solution

kacor earned 750 total points
ID: 16711919
Hi Ghanisen,

in InterBase you can create a stored procedure using the command CREATE PROCEDURE with the following syntax:

CREATE PROCEDURE ProcedureName InputParameterList
      RETURNS OutputParameterList

ProcedureName conforms the InterBase naming convention and is unique within the database.

The easiest way to execute a procedure is: EXECUTE  PROCEDURE ProcedureName InputParameterList.

If you'd like to execute a procedure in an InterBase application (in other procedure or trigger) the syntax is:

EXECUTE  PROCEDURE  ProcedureName InputParameterList
     RETURNING_VALUES ParameterList

UPDATE example

In this example assume that a row was inserted or deleted. As input parameter gets the total of the invoice and the table Orders is modified with the new value of StockTotal.

CREATE PROCEDURE SP_StockTotalModify    /*SP - Stored Procedure*/
      ( WhichOrder INTEGER, StockIncr DECIMAL(8,2) )
      UPDATE Orders
            SET StockTotal = StockTotal + :StockIncr
            WHERE OrderID = :WhichOrder;

In the query the variable WhichOrder is used in the WHERE condition and the variable StockIncr in the SET command. If the change increases the number is positiv, if decreases, negativ .
INSERT example

This example inserts a new row in the table InvoiceItems to an existing order. It calls the procedure SP_StockTotalModify above and transfers to the procedure the incrementation:

      ( WhichOrder INTEGER, WhichStockItem CHAR(10),
        UnitPrice DECIMAL(8,2), Quantity INTEGER )
      DECLARE VARIABLE InterimTotal DECIMAL(8,2);
      /* stores the result of the interim calculation */
      InterimTotal = UnitPrice * Quantity;
      /* insert new row */
      INSERT INTO InvoiceItems
            VALUES( :WhichOrder, :WhichStockItem, :UnitPrice, :Quantity);
      /* calls an other procedure to modify the table containing the primary key */
      EXECUTE PROCEDURE SP_StockTotalModify
            :WhichOrder, :InterimTotal;

In this procedure the UnitPrice is multiplied by the Quantity, and the result will be stored in a variable. In the next step a new row is inserted in a normal way. At the end the value of the variable is transmitted to an embedded procedure to modify the sum value in the table Orders.

In this case you can see one advantage of the stored procedure: more operation is replaced by one called procedure.

I hope this will be sufficient but if you need more help write here.

wbr Janos

Accepted Solution

phuocnh earned 750 total points
ID: 16714056
I think kacor gave you some details to manipulate in FB/IB databse. However, it is not enough for you to write an application using FB/IB database. I thinks you should visit here http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_download_documentation to download some document and read it over.
About SQL in FB/IB you can read http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_60_sqlref. You should remember that this document for IB version 6.0 or FB version 1.0x. Now IB version 7.5 and FB version 2.0 so they change very much. You should read change log document to get new features.
I hope you become an expert on IB/FB soon
Phuoc H. Nguyen

Author Comment

ID: 16716317
Hi Kacor and phuocnh,

Thanks to both of you and I really appreciate your efforts.

Before posting this question I had already roamed the internet and found many document including the ibphoenix ones.

If I'm asking how to create procedures from within VB.NET apps, that is for writing patches that may drop or add procedures to databases silently, i.e. without user intervention. Therefore IbExpert, EMS SQL 2005 etc. are not an option.

With Access databases, I do write or drop stored procedures in the database from within my VB.NET apps. I'd like to be abale to do the same, that is execute de CREATe procedure, from within my VB.NET apps.


Expert Comment

ID: 16716599
I think the above hints make you understand how to solve your question. Play the IB/FB database same as Access database but different SQL (CREATE PROCEDURE statement,DROP PROCEDURE statement).
Now I show you clearer:
1. Install Visual Studio .NET 2005.
2. Install Firevird version 2.0
3. Install Firebird-net-provider (http://prdownloads.sourceforge.net/firebird/FirebirdClient-2.0-RC4.exe?download)
4. Register Firebird items (FBCommand, FBCommandBuilder, FBConnection,FBDataAdapter,FBDataConnectionUIControl)
+Right click on ToolBox > Choose Items >"Choose Toolbox Items" windows appears >In ".NET Framework Components" tab, select all items starting with Firebird namespace > click on "OK" button.
+Now on Toolbox pane, you can see the above Firebird items appear.
5. Start a new VB.NET appication.
6. Drag and drop FBConnection  named FBConnection1 on the form, right click on it to change its properties and make connection to Firebird database.
7. Drag and drop FBCommand on form, right click on it to change its properties.
+CommandText property: You enter the CREATE PROCEDURE statement you want to create a store procedure.
+CommandType property: You select "StoredProcedure".
+Connection property: You select FBConnection1.
8. Now in the code you just call to execute the FbCommand1.
That's all
Phuoc H. Nguyen
LVL 19

Expert Comment

ID: 16717459
please note: firebird 2.0 is still beta, so you may run into problems, 1.5.3 is the most recent release

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question