Morfik Database Guide - Working with Database Connections
From Morfikwiki.com
This topic will provide you will a general overview of how to work with database connections in code. Most of the work that goes into creating a Morfik database driven application is actually done through visual programming and very little configuration/parameterization code. There are, however, situations in which you are still required to write some database access and manipulation code, even when working with a tool such as Morfik AppsBuilder which was designed specifically for creating database applications.
As we will see in this topic, even in these situations the actual amount of code that must be manually written is small and relatively easy to understand.
Contents |
[edit] User Authentication
A good example of when you have to directly access a database programmatically is when you have to perform user authentication against a data stored in a database. Of course, other authentication methods can be used, but it is fairly common for database driven web applications to use their own databases to store user access information. This is precisely the case of our sample project MorfikCMS.
As a home made implementation of a Content Management System (CMS) it makes sense for the MorfikCMS project to use its database to store information about its users. Initially only basic identification information is stored for each user and its sole purpose is to differentiate between general users and users who have to right alter the site’s content. For the sake of simplicity we will treat all identified users as being authorized to perform any changes to the site’s data, though a role based authorization scheme would not be to hard to implement. It would, however, add very little information on how to access the database that is not already covered by our simpler example.
For our purposes then we will be validating the user’s login credentials against the data saved in the UserCatalog table of our database.
Table 1 shows the structure of the UserCatalog table.
| Table 1 – Definition for table UserCatalog | ||
| Field | Type | Size |
| Id | AutoNumber (Integer) | - |
| FullName | Text | 64 |
| Text | 64 | |
| Password | Text | 20 |
It was exactly due to the frequency in which this kind of solution is used that User Authentication was chosen to exemplify how to write code to directly access a database.
[edit] Databases are Server Side
All database access code is executed on the server side of a Morfik AppsBuilder application. The reason is simple: the databases cannot be accessed directly from the browser due to security constraints. This means that even if you are creating a desktop application with Morfik and running both the server and browser on the same computer, you will still have to write all database access code on the server side of the application.
When you need information from a database in application logic of your browser side code the way to get it is through a WebMethod call, which essentially means that most of the code you will ever write to access a database will be within the server side class of a WebMethod.
[edit] Database Access with WebMethods
Creating a WebMethod with Morfik AppsBuilder is a very simple endeavor. For general examples, be sure to look up chapter 8 of the Morfik Developer’s Handbook which covers this in some detail.
Since Morfik AppsBuilder was designed to work with databases, Web Methods have been engineered to make it easy to access them. From within the server side class of a Web Method you have direct access to your XApp’s default database connection and can invoke any database commands without even having to worry about establishing a connection to the database, since that is already taken care of.
| Note | |
 | The Server side class of a Web Method has a member variable called DefaultDBConnection which is the application’s default database connection and which can be freely used without the need to worry about creating and/or closing a new connection. |
In order to be useful, a Web Method which does some database access, must be able to return the retrieved information to its invoker. This is particularly useful in our situation where we want to send two bits of information which we expect will identify the user and get back whether or not those are a match to a previously registered user. This exchange of information between the browser and server components of your XApp is done entirely through the WebMethod’s parameters.
[edit] Web Method Parameters
A Web Method can have both in and out parameters. This means that not only it can receive information but give some of its own in return. In our sample case we are working with a WebMethod which has five parameters: 2 "in" parameters (AUserName and APassword) and 3 "out" parameters (CheckedOk, FullName and UserId). In Figure 1 you can see those parameters in the Web Method Designer of Morfik AppsBuilder.
Figure 1 – Parameter definition for the AuthenticateUser WebMethod.
In this particular example the AuthenticateUser Web Method takes what is essentially the username and password as input and replies with the information if these could be verified and if so the user’s full name and id in the system’s database.
In Listing 1 you can see the complete source code for the server side portion of the AuthenticateUser Web Method. Notice that all authentication logic is contained within the Execute method of the class.
Unit AuthenticateUser;
Interface
Type
AuthenticateUser=Class(WebMethod)
AUserName : String;
APassword : String;
CheckedOk : Boolean;
FullName : String;
UserId : Integer;
Private
{ Private declarations }
Public
{ Public declarations }
Procedure Execute; override;
Procedure LogAccess;
End; ['Published=False'];
Implementation
Procedure AuthenticateUser.LogAccess;
Var
SQLCommand : TSQLCommand;
Begin
SQLCommand := DefaultDBConnection.CreateSQLCommand('INSERT INTO "AccessLog" ("email","AccessTime") ' +
'VALUES (:EMAILPARAM,:ACCESSTIMEPARAM)');
Try
SQLCommand.Prepare;
SQLCommand.ParamByName('ACCESSTIMEPARAM').AsDateTime := Now;
SQLCommand.ParamByName('EMAILPARAM').AsString := AUserName;
SQLCommand.Execute;
finally
SQLCommand.Free;
end;
end;
Procedure AuthenticateUser.Execute;
var
UserCatRS: TRecordSet;
Begin
UserCatRS := DefaultDBConnection.CreateRecordSet('SELECT "Id", "FullName", "email", "Password" FROM "UserCatalog" '+
'WHERE LOWCASE("UserCatalog"."email")=LOWCASE(:EMAILPARAM)');
try
UserCatRS.ParamByName('EMAILPARAM').AsString := AUserName;
UserCatRS.Prepare;
UserCatRS.Active := True;
UserCatRS.First;
CheckedOk := False;
If Not UserCatRS.Eof then
begin
if (UpperCase(APassword) = UpperCase(UserCatRS.FieldByName('Password').AsString)) then
begin
CheckedOk := true;
FullName := UserCatRS.FieldByName('FullName').AsString;
UserId := UserCatRS.FieldByName('Id').AsInteger;
LogAccess;
end;
end;
finally
UserCatRS.Free;
end;
End;
End.
All the parameters of a Web Method are represented as member variables of both the server and browser side classes. This makes reading and setting these parameters extremely easy. In our case we want to use the two "in" parameters of the Web Method as parameters to a SQL Select command that we will run against the UserCat table of the project’s own database and retrieve some other information in the three "out" parameters.
[edit] Reading data with RecordSets
In order to recover data from the database through code we need to have a RecordSet object. This object is instantiated from the TRecordSet class and should be used to execute "Select" commands against the Firebird database as these commands actually return a set of records which match the specified criteria.
The following code snippet shows how a TRecordSet (called UserCatRS) object can be instantiated from the SQL command string.
UserCatRS := DefaultDBConnection.CreateRecordSet(
'SELECT "Id", "FullName", "email", "Password" '+
'FROM "UserCatalog" '+
'WHERE LOWCASE("UserCatalog"."email")='+
'LOWCASE(:EMAILPARAM)');
In the following line the necessary parameter is passed to the SQL Statement before its execution in the database.
UserCatRS.ParamByName('EMAILPARAM').AsString := AUserName;
Setting the actual parameters of a RecordSet object can be done through a call to the ParamByName method of the class.
In the following snippet the statement is prepared for execution and executed by setting the Active property to true. Once the statement is executed a call to First positions the resulting RecordSet to the first of its records. At this point if the RecordSet does not indicate that it is at the "End of File" position (EOF) the stored and supplied values for the user’s password are compared.
UserCatRS.Prepare;
UserCatRS.Active := True;
UserCatRS.First;
CheckedOk := False;
If Not UserCatRS.Eof then
Begin
If (UpperCase(APassword) =
UpperCase(UserCatRS.FieldByName('Password').AsString)) then
The comparison of the password is done in case insensitive manner through the use of the UpperCase function on both sides. If the password comparison yields a true result, the return, i.e. "out" parameters are set with the values recovered from the table. The actual reading of the fields of the returned records can be done through a call to the RecordSet’s FieldByName method.
Begin
CheckedOk := true;
FullName := UserCatRS.FieldByName('FullName').AsString;
UserId := UserCatRS.FieldByName('Id').AsInteger;
LogAccess;
End;
End;
Finally
UserCatRS.Free;
End;
When the data that we required has been read we can dispose of the RecordSet object through a call to its Free method as seen above.
[edit] Working with SQLCommands
In order to recover data from the database through code we used a TRecordSet object but the TRecordSet class is designed exactly for that: to recover data. There are situations when you need to execute data manipulations which don’t really return any data, such as inserting data into a table. In these situations we will be using the TSQLCommand class. An example of its use can be seen in the LogAccess method of the server side implementation of the AuthenticateUser WebMethod.
The TSQLCommand is designed specifically to execute commands which do not return sets of records. In the following code snippets you can see how a TSQLCommand object is instantiated from a SQL command string, prepared and executed after having its parameters set.
SQLCommand := DefaultDBConnection.CreateSQLCommand(
'INSERT INTO "AccessLog" ("email","AccessTime") ' +
'VALUES (:EMAILPARAM,:ACCESSTIMEPARAM)');
Try
SQLCommand.Prepare;
SQLCommand.ParamByName('ACCESSTIMEPARAM').AsServerDateTime :=
Now;
SQLCommand.ParamByName('EMAILPARAM').AsString := AUserName;
SQLCommand.Execute;
Finally
SQLCommand.Free;
End;
Parameters of the SQLCommand object are set in exactly the same manner as was done with the UserCatRS (RecordSet) object in the the Execute method of the AuthenticateUser WebMethod.
The LogAccess procedure simply inserts a record into the AccessLog table, each time a successful Sign in is executed in the application.
[edit] Receiving Data in the Browser
We have just seen how to recover some data in the server side portion of a Web Method and how to execute action SQL commands which do not return data. In the first of these situations, there will be cases in which the recovered information has to go back to the browser side of your XApp. AS previously mentioned this transfer is achieved by setting the Web Method’s "out" parameters.
The "out" parameters can be read in the browser side portion of a Web Method once the remote procedure call returns from the server. When the call does return the Web Method browser side class’ HandleResponse method is invoked.
In Listing 2 you can see the complete source code for the browser side portion of the AuthenticateUser Web Method.
Listing 1 – Server side portion of the AuthenticateUser Web Method.
Unit AuthenticateUser;
Interface
Type
AuthenticateUser=Class(WebMethod)
AUserName : String;
APassword : String;
CheckedOk : Boolean;
FullName : String;
UserId : Integer;
Private
{ Private declarations }
Public
{ Public declarations }
Procedure HandleResponse; override;
Procedure AuthenticationOk;
Procedure AuthenticationNotOk;
End;
Implementation
Uses Header, Footer, UserSignIn, SystemControls;
const InvalidLogInMessage =
'Username and password do not match. You provided ';
Procedure AuthenticateUser.AuthenticationOk;
Begin
UserIsAuthenticated := true;
CurrentUser := UserId;
Header(XApp.Forms['Header']).UserNameLabel.Caption :=
'Welcome '+ FullName;
Header(XApp.Forms['Header']).SignInOutOption.Caption := 'Sign Out';
Header(XApp.Forms['Header']).Separator1.Visible := true;
Footer(XApp.Forms['Footer']).SiteManagementOption.Visible := true;
Header(XApp.Forms['Header']).UserNameLabel.Visible := true;
UserSignIn(XApp.Forms['UserSignIn']).Close;
End;
Procedure AuthenticateUser.AuthenticationNotOk;
Begin
UserSignIn(XApp.Forms['UserSignIn']).ErrorMessage.Caption :=
InvalidLogInMessage +
UserSignIn(XApp.Forms['UserSignIn']).UserNameEdit.Text;
End;
Procedure AuthenticateUser.HandleResponse;
Begin
If CheckedOK Then
AuthenticationOk()
Else
AuthenticationNotOk()
End;
End.
In this particular case the HandleResponse method just tests to see if the CheckedOk "out" parameter has returned as true or false. If the check was Ok, then the AuthenticationOk method is called, otherwise, the AuthenticationNotOk method is called.
The AuthenticationOk method sets a global variable called UserIsAuthenticated to true and hides or display a few objects. It is the global variable UserIsAuthenticated which is used, through out the application to allow or disallow operations depending on a registered user having been authenticated.
One of the visual elements that the AuthenticationOk method makes visible and whose content it sets is a TextLabel which will hold the user’s full name, also returned from the Server as seen in the following snippet.
Header(XApp.Forms['Header']).UserNameLabel.Caption := 'Welcome '+ FullName;
Header(XApp.Forms['Header']).UserNameLabel.Visible := true;
[edit] Invoking the Authentication Code
As we have seen the user authentication is accomplished through the use of a Web Method. That is the mechanism that we use to send the information to the server and get back a response, but how to we get the information from the user and how do we start the authentication process which will lead to the database lookup we have put together.
[edit] The UserSignIn Form
The UserSignIn Form is invoked from the Header Form when the user/visitor clicks on the "Sign In" link. This Form is displayed as a popup instead of being displayed inline as it should be the single point in which the user focuses during this process. As such the UserSignIn Form is also modal, restricting all interaction with the application/site to itself.
The following code snippet contains the complete event handler for the OnClick event of the SignInOutOption TextLabel.
Procedure Header.SignInOutOptionClick(Event: TDOMEvent); Begin
If Not UserIsAuthenticated Then
OpenForm('UserSignIn', 'POPUP', '"title=Sign In", "modal=true"')
Else
Begin
UserIsAuthenticated := false;
SignInOutOption.Caption := 'Sign In';
UserNameLabel.Visible := false;
Separator1.Visible := false;
Footer(XApp.Forms['Footer']).SiteManagementOption.Visible := false;
End;
End;
Notice that after determining that a user is not currently signed in the event handler invokes OpenForm causing the UserSignInOut to popup.
Figure 2 shows the UserSignIn Form in Design mode in Morfik AppsBuilder Workspace. This is a simple Form which is not bound to any data source. All data exchange with the server is done through the AuthenticateUser Web Method which we have previously reviewed.
Figure 2 – The UserSignIn Form in design mode in the Morfik Workspace.
Once the UserSignIn Form is displayed and the user inputs his data the SignInBtn Button control invokes the RunWebMethod command in its OnClick event handler.
The following snippet contains the event handler for the OnClick event of the SignInBtn button control and the DoAuthenticate procedure it invokes.
Procedure UserSignIn.DoAuthenticate;
Begin
RunWebMethod('AuthenticateUser','"AUserName='+ UserNameEdit.Text +
'", "APassword=' + PasswordEdit.Text + '"');
End;
Procedure UserSignIn.SignInBtnClick(Event: TDOMEvent);
Begin
DoAuthenticate;
End;
The DoAuthenticate procedure as seen in the previous code snippet invokes the RunWebMethod command to execute the AuthenticateUser Web Method passing as its parameters the data entered in the two TextEdit controls of this Form.
Figure 3 – The UserSignIn Form at runtime, displayed as a popup.
After invoking the AuthenticateUser Web Method execution the event handler for the SignInBtn button closes the popup Form as the execution will resume in the browser side part of the Web Method once the server replies to the call.
The CancelBtn button in the UserSignIn Form only closes the Form if clicked as no further action is required.
[edit] Wrapping it up
Interfacing with the database from code in a Morfik application is not a complex task and is normally done in the server side portion of a Web Method where the code to execute pure action queries and data selection queries is quite similar.
