Morfik Database Guide - Using Stored Procedures in Morfik
From Morfikwiki.com
This topic will provide you will a general overview of how to use stored procedures in order to simplify the implementation of some application features. In this particular case we will see how to use executable stored procedures to clear log tables and how to use selectable stored procedures to execute action queries at the same time we retrieve data for the application.
It is not the goal of this work to be an all inclusive guide to writing stored procedures. It is intended as a guide on how to use stored procedures from Morfik AppsBuilder and thus will focus on the usage of the procedures instead of on their creation.
Also treated in this chapter is how to provide visual feedback to the end user while a long running server side process is being executed.
Contents |
[edit] Two Kinds of Stored Procedures
There are essentially two kinds of stored procedures: executable and selectable. This topic covers the usage of both of these kinds of procedures from within a Morfik build web application.
The usage scenarios for these procedures were created as to complement and fit in nicely with the MorfikCMS sample application. Morfik AppsBuilder, at a first glance does not offer any support for using or calling stored procedures, be them executable or selectable. This impression is, however, illusory.
Developers who are familiar with stored procedures can actually use both types of stored procedures to enhance their applications as we will see shortly.
[edit] Using Executable Procedures
Executable stored procedures, as their name suggests are designed to execute some specific action. In our MorfikCMS sample application executable stored procedures have been introduced to clear the AccessLog table. Two Web Methods have been added to the MorfikCMS sample project to show the usage of the stored procedures. Both Web Methods are invoked from a single Form which has been added to the Site Management portion of the Website.
[edit] The ManageLog Form
The ManageLog Form is quite simple. It offers options to clear the AccessLog table in two manners: up to a specific date, or completely. Figure 1 shows the ManageLog Form at design time in the Morfik AppsBuilder Workspace.
Figure 1 – ManageLog Form at design time in the Morfik Workspace.
Each of the buttons, shown in Figure 1, invokes a different Web Method one of which takes single parameter which represents the date up to which the log should be cleared. In Listing 1 you can see two event handlers which call the two supporting Web Methods. You will notice that both of the event handlers call a procedure called StartWorking. This will be addressed further ahead in this chapter, in greater detail.
Listing 1 – Browser side code for the ManageLog Form.
Unit ManageLog;
Interface
Type
ManageLog = Class(Form)
Image1 : Image;
HeaderGradient : Image;
TextLabel1 : TextLabel;
ClearAllBtn : Button;
Container1 : Container;
ClearUpToBtn : Button;
ClearUpToDate : DateTimeEdit;
Procedure ClearUpToBtnClick(Event: TDOMEvent); Message;
Procedure ClearAllBtnClick(Event: TDOMEvent); Message;
Private
{ Private declarations }
Public
{ Public declarations }
End;
Implementation
Uses SystemUtilities;
Procedure ManageLog.ClearUpToBtnClick(Event: TDOMEvent);
Begin
StartWorking;
RunWebMethod('ClearLogUpTo', '"AccessTime='+ClearUpToDate.DateTime+'"');
End;
Procedure ManageLog.ClearAllBtnClick(Event: TDOMEvent);
Begin
StartWorking;
RunWebMethod('ClearAccessLog', '');
End;
End.
[edit] The ClearAccessLog WebMethod
The first of the two Web Methods which was created for the ManageLog Form was ClearAccessLog. This is the simplest of the two WebMethods which were created to clear the log table simply because it just erases all the content of the AccessLog table. This is achieved by executing a stored procedure called "ClearAccessLog", just as the Web Method itself.
Listing 2 shows the full code for the server side portion of the ClearAccessLog Web Method.
Listing 2 – Server side code for the ClearAccessLog WebMethod.
Unit ClearAccessLog;
Interface
Type
ClearAccessLog = Class(WebMethod)
Private
{ Private declarations }
Public
{ Public declarations }
Procedure Execute; override;
End; ['Published=False'];
Implementation
Procedure ClearAccessLog.Execute;
Var
SQLCommand : TSQLCommand;
Begin
SQLCommand := DefaultDBConnection.CreateSQLCommand(
'EXECUTE PROCEDURE "ClearAccessLog"');
Try
SQLCommand.Prepare;
SQLCommand.Execute;
Finally
SQLCommand.Free;
End;
End;
End.
Notice in the code for the Execute method of the ClearAccessLog Web Method that through the use of the EXECUTE PROCEDURE statement we are essentially invoking the ClearAccessLog stored procedure in the same way we would invoke another SQL language statement which does not return any data. This code is very close to what has been presented in Chapter 4, for the LogAccess method of the AuthenticateUser WebMethod.
In the following code snippet you can see the full source code for the ClearAccessLog stored procedure which is used by this Web Method.
SET TERM ^ ;
CREATE PROCEDURE "ClearAccessLog"
as
begin
delete from "AccessLog";
end^
SET TERM ; ^
Please, observe that this is an extremely simple stored procedure. In fact it would not be necessary at all to use a stored procedure to execute this task as it is composed of a single statement which basically deletes all rows from the AccessLog table. It is important to note that the complexity of the stored procedure does not, in any way, affect the way it is called from within a Morfik application.
[edit] The ClearLogUpTo WebMethod
The ClearLogUpTo WebMethod does essentially the same as the ClearAccessLog, but restricted to the time period predating a specific date. The major difference is that this WebMethod invokes a different stored procedure, also homonymous to the Web Method, which requires as a parameter the date up to which the log should be cleared.
Listing 3 shows the source code for the server side code for the ClearLogUpTo WebMethod.
Listing 3 – Server side code for the '''ClearLogUpTo 'WebMethod.
Unit ClearLogUpTo;
Interface
Type
ClearLogUpTo = Class(WebMethod)
AccessTime : String;
Private
{ Private declarations }
Public
{ Public declarations }
Procedure Execute; override;
End; ['Published=False'];
Implementation
Uses SystemUtilities;
Procedure ClearLogUpTo.Execute;
Var
SQLCommand : TSQLCommand;
Begin
SQLCommand := DefaultDBConnection.CreateSQLCommand(
'EXECUTE PROCEDURE "ClearLogUpTo"(:"AccessTime")');
Try
SQLCommand.Prepare;
SQLCommand.ParamByName('AccessTime').AsDateTime := AccessTime;
SQLCommand.Execute;
Finally
SQLCommand.Free;
End;
End;
End.
Note in Listing 3 that the ClearLogUpTo stored procedure takes a parameter and that it is supplied through the use of the ParamByName method.
Figure 2 – Code completion list of "Data type" properties for the parameter object.
The parameter passed on to the stored procedure has been sent to the server through a parameter called AccessTime which was declared for the WebMethod and supplied in the call from the ManageLog Form as shown in the ClearUpToBtnClick event handler in Listing 1.
Observe in the following code snippet that the code for the ClearLogUpTo stored procedure is quite similar to that of the ClearAccessLog procedure, with the addition of the parameter which is used in the WHERE clause of the Delete statement.
SET TERM ^ ;
CREATE PROCEDURE "ClearLogUpTo" ("AccessTime" timestamp)
as
begin
delete from "AccessLog"
where ("AccessTime" < :"AccessTime");
end^
SET TERM ; ^
As in the previous case, this is a very simple stored procedure which could be substituted by the simple execution of the same Delete statement, which the procedure is executing, directly from the application.
[edit] Visual Feedback for Long Processes
In adding the two Web Methods which clear the AccessLog table, the MorfikCMS sample application also introduces a way to visually give feedback to the application/website’s end users while a long running process is being executed. In our sample case the clearing of the log never really takes long as only a few rows exists at any one time in the AccessLog table. In a real world situation, however, such a table might contain millions of rows and the operation to empty it might run for a little longer. By just invoking the Web Method the process is started but the end user has no way of knowing when the process ends. The first thing that immediately comes to mind when we think presenting some visual feedback to the user is to imagine that we will display some visual message, but the question of where soon becomes an issue. The user might have navigated away from the Form which started the process so we have no way of knowing precisely where to display the message. It can be done with a popup message when the process finishes, but the user might not tie in the message with the action, depending on how long the process has been running for.
The path taken in the MorfikCMS sample project was to block the user’s session in the website while the process is in execution. This is approach should be used sparingly as it might annoy certain users and preferably in portions of the application which are used for administrative purposes as in this case.
In the MenageLog Form’s browser side code, shown in Listing 1, in each of the event handlers which invoke WebMethods, prior to invoking the Web Methods, there is a call to a global procedure called StartWorking. The following code snippet shows the implementation of this procedure.
Procedure StartWorking;
Begin
IsWorkInProgress := true;
XApp.OpenForm('WorkProgress', 'POPUP', '"modal=true", "border=none"');
End;
The StartWorking procedure sets a global variable called IsWorkInProgress and then opens the WorkProgress Form in popup modal mode.
Figure 3 – The WorkProgress Form at design time in the Morfik Workspace.
Figure 4 – The WorkProgress Form at runtime in Internet Explorer.
The WorkProgress Form, shown in Figure 3, displays a message indicating that a "generic" operation is being executed and uses an animated GIF image to provide the impression of the work being done, as shown in Figure 4.
Both the ClearAccessLog and the ClearLogUpTo Web Methods in their browser side portions invoke the StopWorking procedure which sets to false the global variable IsWorkInProgress to false. When the next OnTimer event is processed by the timer control in the WorkProgress Form, it closes itself automatically freeing the user to navigate again.
[edit] Using Selectable Procedures
The use of selectable stored procedures is even simpler than the use of the executable ones. A selectable procedure behaves, essentially, as a table in a select Statement. This, which is actually a feature of Morfik’s built-in Firebird database allows us to freely use stored procedures in creating Morfik stored queries.
In the MorfikCMS sample application a stored query was added with the name GetOneArticleCount. This query differs from the GetOneArticle query which was being used to get the data of a specific article, when a user clicks on an article to see its full text, in that it retrieves the data from a stored procedure.
The following code snippet shows the SQL language code of the GetOneArticleCount stored query.
SELECT * FROM "Article_SEL"(:PARAMID)
"Article_SEL" in this case is the name of a stored procedure the definition of which can be seen in the following code snippet.
SET TERM ^ ;
CREATE PROCEDURE "Article_SEL" (lookupid integer)
returns (
"Id" integer,
"Title" varchar(64),
"Summary" varchar(250),
"Body" blob sub_type 1 segment size 80,
"DateCreated" timestamp,
"DatePublished" timestamp,
"IsPublished" smallint,
"SectionId" integer,
"CreatedBy" integer,
"ViewCount" integer)
as
begin
for select "Id",
"Title",
"Summary",
"Body",
"DateCreated",
"DatePublished",
"IsPublished",
"SectionId",
"CreatedBy",
"ViewCount"
from "Article"
where "Id" = :LOOKUPID
into :"Id",
:"Title",
:"Summary",
:"Body",
:"DateCreated",
:"DatePublished",
:"IsPublished",
:"SectionId",
:"CreatedBy",
:"ViewCount"
do
begin
update "Article" Set "ViewCount" = "ViewCount"+1
where "Id" = :"Id";
suspend;
end^
SET TERM ; ^
The code for this procedure was generated automatically through the use a Firebird administration utility and the altered to include an update to the Article table where the value of the ViewCount column is incremented by one. This allows the monitoring of how many times a specific article has been selected for viewing by an end user/visitor.
In effect the main difference of using this stored procedure and a simple query is that every time this procedure is invoked the article it returns gets an update in the number of times it was viewed. This kind of access statistics can be used to implement, for example, a list of the most popular articles.
Since we are dealing with a query which behaves exactly like a query which draws its data directly from a table, and which has the same names for its output parameters as the columns of the original table, all that is required it to replace the name of the data source for the ViewOneArticle Form in order to start counting views.
| Note | |
 | It is, also, necessary to add the ViewCount column to the Article table. This field should be defined as a Number(Integer) column. This change has to be done prior to the creation of the stored procedure or else it will not compile correctly as it references it. |
[edit] Wrapping it up
Though creating complex stored procedures was beyond the scope of this chapter, Morfik makes using them quite easy. Executable stored procedures can be used to delegate database intensive operations directly to the database and selectable stored procedures can be used to execute some action every time they recover some data.
