Stored procedures in Firebird/Interbase

Posted on 2006-05-17
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
    LVL 6

    Expert Comment

    You can visit 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 (Open Source Software).
    IBExpert (It is very good but commercial work)
    You can write store procedure for databse normally.

    Author Comment

    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

    creating stored procedures in your vb code is unnecessary and not a good idea, use ibexpert
    LVL 6

    Expert Comment

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

    Assisted Solution

    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.

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

    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:

    SET TERM ^;
    CREATE PROCEDURE SP_NewInvoiceItemInsert
          ( 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;
    SET TERM ;^

    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
    LVL 6

    Accepted Solution

    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 to download some document and read it over.
    About SQL in FB/IB you can read 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

    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.

    LVL 6

    Expert Comment

    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 (
    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

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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now