Creating and Calling Firebird Stored Procedures
From Morfikwiki.com
Contents |
[edit] Introduction
This tutorial aims at describing the steps needed to create a simple Stored Procedures within a Morfik Project and call it through a Webmethod. This tutorial has only been tested with projects that use the default Firebird database server, which is shipped with the AppsBuilder. You can find information about working with Stored Procedures and external SQLServer here.
[edit] Benefits of using Stored Procedures
The following are quoted from the Firebird Book by Helen Borrie (Chapter 28, page 565):
- Modular design: All applications that access the same database share stored procedures, thus centralizing business rules, reusing code, and reducing the size of the applications.
- Streamlined maintenance: When a procedure is modified, changes propagate automatically to all applications without the need for further recompiling on the application side, unless changes affect input or output argument sets.
- Improved performance: Execution of complex processing is delegated to the server, reducing network traffic and the overhead of operating on external sets.
- Architectural economy: Client applications can focus on capturing user input and managing interactive tasks delegating complex data refinement and dependency management engine.
- Extra functionality: Tricky accessing and messaging of the data that cannot be achieved with regular SQL usually can be managed with one or a suit of stored procedure.
[edit] Example
To get started we need to create a table in the database. This one is called "MY_TABLE" with two fields, "FIELD1" of Text type and "FIELD2" of Number type.
The following store procedure inserts a new record with a simple 'HelloWorld' message and number 200 into the table every time called.
CREATE PROCEDURE DO_DUMMY_INSERT
AS
BEGIN
INSERT INTO MY_TABLE (FIELD1, FIELD2) VALUES ('HELLO WORLD', 200);
END;
To use a stored procedure we need to first define it within the database. In Morfik we can use the Pass Through query designer. We use it because it lets us manually execute code against the database. The image below shows the selected item is the Pass Through query designer.
After defining the Stored Procedure we can call it by executing the following SQL statement in another Pass Through query.
EXECUTE PROCEDURE DO_DUMMY_INSERT;
Calling Stored Procedures is like calling a Select statement. So it means that we should be able to call it through the TSQLCommand class which is used for executing DML statements.
Unit WebMethod1;
Interface
Type
WebMethod1=Class(WebMethod)
Private
{ Private declarations }
Public
{ Public declarations }
Procedure Execute; override;
End; ['Published=False'];
Implementation
Procedure WebMethod1.Execute;
Var
Command : TSQLCommand;
SQL : String;
Begin
SQL := 'EXECUTE PROCEDURE DO_DUMMY_INSERT';
Command := DefaultDBConnection.CreateSQLCommand(SQL);
Try
Command.Prepare;
Command.Execute;
Finally
DefaultDBConnection.DestroySQLCommand(Command);
End;
End;
End.
Above example demonstrates creating TSQLCommand to execute the "DO_DUMMY_INSERT" stored procedure from a WebMethod which is callable from the browser.
