Writing code for data manipulation

Notes-M3-48.png This article requires updates for compliance with Morfik 3. Further information might be found on the code for data manipulation talk page. (ML) (27-04-2010)

Morfik allows you to write code on the server side so that you can handle direct data manipulation. This is very useful when you want to update information stored in more than one table at the same time, and when you need to have a recurring process which is not started in response to direct end user action.

All server-side code that can be invoked from the browser in a Morfik application is implemented as a Web Method. This is useful for the implementation of published Web Service interfaces to the logic of your application.

Databases access happens on the server side

All database access code is executed on the server side of a Morfik application; the databases cannot be accessed directly from the browser due to security constraints. Even if you create a desktop application with Morfik and run 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.

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 common for database-driven web applications to use their own databases to store user access information. This is precisely the case of our sample CMS application project.

As a homemade implementation of a Content Management System (CMS) it makes sense for the CMS application 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 the right to 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 too hard to implement. Doing this, however, would add little information to what we already know about accessing databases from 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. As this validation will require server-side coding, we will implement it though the use of Web Methods.

Table 1 shows the structure of the UserCatalog table.

Table 1 – Definition for table UserCatalog
Field Type Size
Id AutoNumber (Integer) -
FullName Text 64
Email Text 64
Password Text 20


The CMS application project also stores information about user sign-in, in a log table. This is a real example of how to create server-side data manipulation code which works with more than one database interaction. In this case the application will check the UserCatalog table to see if the user's credentials are valid and register his sign-in in the AccessLog table.

When you need information from a database in the 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.

Database access with WebMethods

Creating a WebMethod with Morfik is very simple. The article topics on Web Methods cover this in some detail.

Since Morfik 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, as long as you are working with the project's default database, can invoke any database commands without even having to worry about establishing a connection to the database, since that is already taken care of automatically by the Morfik Framework.

Note: The server-side class of a WebMethod 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 WebMethod for database access must return the retrieved information to its invoker. This is particularly useful in our situation where we want to send two bits of information which 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.

WebMethod Parameters

A WebMethod 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: two "in" parameters (AUserName and APassword) and three "out" parameters (CheckedOk, FullName and UserId). In Figure 1 you can see those parameters in the Morfik WebMethod Designer.


5-1.png
Figure 1: Parameter definition for the AuthenticateUser WebMethod.


In this particular example the AuthenticateUser WebMethod takes what is essentially the username and password as input and replies with information about if these could be verified and if so the user’s full name and id in the system’s database. For sake of simplicity the CMS application uses the user's email address for its authentication.

In Listing 1 you can see the complete source code for the server-side portion of the AuthenticateUser WebMethod. Notice that all authentication logic is contained within the Execute method of the class, while the code required to register the authentication request in the database is in the LogAccess method.

Listing 1—Server-side portion of the AuthenticateUser WebMethod.

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 WebMethod are represented as member variables of both the server and browser-side classes representing the WebMethod. This makes reading and setting these parameters extremely easy. In our case we want to use the two "in" parameters of the WebMethod 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.

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:

FX Code

  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.

 

Note: If you need to specify a parameter for a RecordSet as holding a NULL value, you can do so by setting its IsNull property in the same way that the AsString property is being set in the above sample.

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 moves 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.

FX Code

   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.

FX Code

      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.

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 that don’t really return any data, for example inserting data into a table. In these situations we use 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 that 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.

Receiving data in the browser

We have just seen how to recover some data in the server-side portion of a WebMethod 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 WebMethod’s "out" parameters.

The "out" parameters can be read in the browser-side portion of a WebMethod once the remote procedure call returns from the server. When the call does return, the WebMethod 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 WebMethod.

Listing 2—Browser side portion of the AuthenticateUser WebMethod.

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 frmUserSignIn;
 
const InvalidLogInMessage = 'Username and password do not match. You provided ';
 
 
Procedure AuthenticateUser.AuthenticationOk;
begin
  UserIsAuthenticated := true;
  CurrentUser := UserId;
  frmUserSignIn(XApp.Forms['frmUserSignIn']).Close;
  OpenPage('AdminHome', '"openmode=refresh"');
end;
 
Procedure AuthenticateUser.AuthenticationNotOk;
begin
  frmUserSignIn(XApp.Forms['frmUserSignIn']).ErrorMessage.Caption := InvalidLogInMessage +
  frmUserSignIn(XApp.Forms['frmUserSignIn']).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 uses the OpenPage function to invoke a URL that has been previously published, in this case the Admin URL. The global variable UserIsAuthenticated is used throughout the application to allow or disallow operations depending on a registered user having been authenticated.

Invoking the authentication code

As we have seen, the user authentication is accomplished through the use of a WebMethod. That is the mechanism that we use to send the information to the server and get back a response, but how do 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?

The UserSignIn Form

The UserSignIn Form is invoked from the Index Form when the user/visitor clicks on the "Site Maintenance" link displayed in the Form's footer. This Form is displayed as a popup instead of being displayed inline as it should be the single point on 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 SiteManagementOption TextLabel.

Procedure Index.SiteManagementOptionClick(Event: TDOMEvent);
Begin
    If UserIsAuthenticated Then
       OpenPage('AdminHome', '"openmode=refresh"')
    Else
       OpenForm('frmUserSignIn', 'POPUP', '"title=Sign In", "modal=true"')
End;

Notice that after determining that a user is not currently signed in, the event handler invokes OpenForm causing the UserSignIn Form to pop up. If the user had already gone through the sing-in process the event handler would have simply invoked the AdminHome URL.

Figure 2 shows the UserSignIn Form in design mode in the Morfik development environment. This is a simple Form which is not bound to any data source. All data exchange with the server is done through the AuthenticateUser WebMethod which we have previously reviewed.


writing-code-for-database-cms-authentication.png
Figure 2: The frmUserSignIn Form in design mode in the Morfik development environment.


Once the frmUserSignIn 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 above invokes the RunWebMethod command to execute the AuthenticateUser WebMethod, passing as its parameters the data entered in the two TextEdit controls of this Form.


writing-code-for-database-authentication-runtime.png
Figure 3: The frmUserSignIn Form at runtime, displayed as a popup.


After invoking the AuthenticateUser WebMethod execution the event handler for the Site Management link closes the popup form as the execution will resume in the browser-side part of the WebMethod once the server replies to the call.

The CancelBtn button in the UserSignIn Form only closes the Form when clicked, as no further action is required.

Working with databases in background threads

When working with Morfik you are almost always building a server application. This normally means that your application will be running 24/7, without ever stopping. This poses interesting challenges in that you cannot rely on running any cleanup or maintenance code when your application starts or stops. It doesn't. It also means that you need to be very careful in writing your server-side code, as any resource leaks will quickly add up in an application that is running continuously.

One way to work around the fact that you can't count on having cleanup or maintenance code when the application starts or stops is to have code that runs in the background and that executes some actions at specific intervals or when certain conditions are met.

A Morfik application is inherently a multi-threaded application. This means that at any one moment there are several lightweight subprocesses running and executing tasks in response to user requests. When you create WebMethods, the Morfik Framework and the WebMethod designer ensure that you are directed to write your code within a single procedure which runs within the context of the server thread.

When you set out to write code that will be executed continuously in the background you will not have any special assistance from the development environment, but the Morfik Framework does provide the basis you need to create your code without much hassle.

In order to write code that runs in the background of your server application, you will need to create a descendant of the TThread class, defined in the SystemClasses unit. In this descendant class you will have to override the execute method within which you will code the logic that you want to execute in the background.

The following code snippet shows the basic structure of a method which creates a connection to the project's default database and executes a SQL command through that connection. This is a simplified version of a method that is part of an sample listing included in this article.

FX Code

Procedure TDataSync.Log(CodErro: Integer; MsgErro: String; TipoMsg: String);
Var
   strSQL: String;
   SQLCommand: TSQLCommand;
   Conn: TIBOServiceConnection;
Begin
     Conn := XApp.DefaultDBConnector.CreateConnection;
     try 
         strSQL := 'YOUR SQL COMMAND';
         SQLCommand := Conn.CreateSQLCommand(strSQL);
         SQLCommand.Prepare;
         SQLCommand.ParamByName('PARAM1').AsString := TipoMsg;
         SQLCommand.ParamByName('PARAM2').AsString := MsgErro;
         SQLCommand.Execute;
     finally
         SQLCommand.Free;
         Conn.Free;
     end;
End;

Listing 3 shows an entire module to illustrate how to create code that accesses an external database as well as the project's default database. Access to the default database is accomplished within the Log method while access to the external database is done within the Execute method itself.

Listing 3—Server-side code for a module with the implementation of a descendant of the TThread class which executes data access code as a background, lightweight process (Thread).

FX Code

Unit SyncData;
 
Interface
 
Uses SystemDatabaseIBO, SystemServer, SystemClasses, SystemUtilities,
     SystemConnectors, SystemCatalog, SystemDatabase,  SystemConnectorsIBO,
     SystemConnectorsODBC, SystemDatabaseSqlDB;
 
type
  TDataSync = Class(TThread)
    Connection: TODBCServiceConnection;
    Procedure Execute; override;
    Procedure Log(CodError: Integer; MsgError: String; TypeMsg: String);
  end;
 
 
 
Implementation
 
 
{This procedure shows how to connect to the default database.}
 
Procedure TDataSync.Log(CodError: Integer; MsgError: String; TypeMsg: String);
Var
   strSQL, strDateEnd, strDateStart: String;
   SQLCommand: TSQLCommand;
   Conn: TIBOServiceConnection;
Begin
     Conn := XApp.DefaultDBConnector.CreateConnection;
     try
 
         strSQL := 'EXECUTE PROCEDURE "LOG_IMPORT_INS" (:LOG_IMPORT_STATUS, ';
         strSQL := strSQL + ':LOG_IMPORT_TEXT, :LOG_IMPORT_DATE_START, :LOG_IMPORT_DATE_END, ';
         strSQL := strSQL + ':LOG_IMPORT_ERROR ) ';
         SQLCommand := Conn.CreateSQLCommand(strSQL);
 
         SQLCommand.Prepare;
         SQLCommand.ParamByName('LOG_IMPORT_STATUS').AsString := TypeMsg;
         SQLCommand.ParamByName('LOG_IMPORT_TEXT').AsString := MsgError;
 
         strDateStart:= DateTimeToStr(Now, 0);
         strDateEnd := strDateStart;
 
         SQLCommand.ParamByName('LOG_IMPORT_DATE_START').AsString := strDateStart;
         SQLCommand.ParamByName('LOG_IMPORT_DATE_END').AsString := strDateEnd;
         SQLCommand.ParamByName('LOG_IMPORT_COD_ERROE').AsInteger := CodError;
         SQLCommand.Execute;
     finally
         SQLCommand.Free;
         Conn.Free;
     end;
End;
 
 
{This method shows how to establish a connection to an external database.}
Procedure TDataSync.Execute;
begin
   while true do
   begin
      Try
            Connection := TODBCServiceConnection(Catalog.GetServiceConnectorByID('ExtDB').CreateConnection);
 
        Try
 
          {-- Execute DB commands --}
 
          Log(0, 'Updates completed without error!', 'SUCCESS');
 
        Except
 
          Log(-6, 'The update process was interrupted.', 'ERROR');
 
        End;
      Finally
         Connection.Free;
      End;
     Sleep(60000000);
   end;
end;
 
End.


Note: Writing database access code to run as a background thread is not that different from writing the same code to run from a WebMethod. The main difference is that you don't have a ready-to-use database connection as when you are writing code within the scope of the Execute method of a Web Service. In Listing 3 and in the snippet above it you can see samples of the code necessary to create a connection to the project's default database or to an external database.

The same code used to create a connection to an external database in this example can also be used from within a WebMethod.


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 WebMethod where the code to execute pure action queries and data selection queries is quite similar.


Related Topics


Back to top