Native Database Connectivity using Zeos Package

Morfik's Zeos Package provides native database connectivity to database servers such as MySQL, Microsoft SQL Server, Postgres Oracle and SQL Lite through native connection libraries or, in case of Microsoft SQL Server, through OLEDB SQL Native Client .

Morfik's first database package built on this architecture is the Zeos Native Database Connectivity package. Being based on Zeos Lib it provides a universal layer for connecting to the various databases, including MySQL, MS SQL, Postgress ,Oracle and SQL Lite through native connection libraries or, in case of Microsoft SQL Server, through OLEDB SQL Native Client . Zeos Lib has been developed for many years and has reached a certain level of maturity and industrial strength. It’s compatible with FreePascal and supports both Windows and Linux platforms.

To use Morfik's Zeos Package simply add the package to the project by utilizing the ‘Used Packages‘ command on the Project ribbon (Figure 1), or drag the package file onto the application main client area. Once added, the package registers a number of Service connectors which are accessible from Database Connectors two on the Project ribbon when the Home View is active.

Figure 1: Home Ribbon

The first version of the Zeos database package comes with MSSQL and MySQL connectors only; other connectors will be developed after the release of Morfik 3.

The Data Connector wizard provides a step-by-step procedure to connect to an external data source (Figure 2). Database server-specific fields such as database location, user name and password (Figure 3) must be specified before a list of available Tables and Views (Figure 4) can be selected.

Figure 2: Database Connectors Wizard

Figure 3: Database Location

Figure 4: Database Objects

Once the wizard has finished, external tables can be used in exactly the same way as internal ones: they support all operations such as editing table data, defining queries based on the tables and writing data-related event handlers. Altering table structures, such as adding or removing fields, specifying indexes, etc., must be done using tools that come with database server. For more information regarding working with tables please refer to this article.

Figure 5: Working with external Table

In order to connect to the MySQL database server, a native connection library needs to be installed both on the machine Morfik is running and on the server where the XApp will be deployed. Connection libraries can be downloaded from database server vendor sites, for example the MySQL connection library can be downloaded here.

The PATH environment variable should include the path to the library or alternatively the library should be copied to a commonly used path such as Windows\System32.

Once the package is installed, Morfik IDE loads the package dll; all database-related objects are instantiated and maintained in the context of the package dll and the IDE works with it through common interfaces which make no assumption regarding a specific database implementation. Uninstalling the package releases this interface and removes all external tables associated with the connection.

Source code of Zeos package is compiled into the XApp providing run-time database connectivity.

Runtime programming for an external database server can be done through Morfik Abstract database layer which allows instantiating and opening connections, executing queries and retrieving data, as seen in the sample code below.

FX Code

Uses
    SystemCatalog;
 
Procedure WebMethod1.Execute;
Var
    Connector  : TDatabaseServiceConnector;
    Connection : TDatabaseServiceConnection;
    Recordset  : TAnyDBRecordset;
Begin
    Connector := Catalog.GetServiceConnectorByID('Zeos MySQL1') As TDatabaseServiceConnector;
    Connection := Connector.CreateConnection;
    Try
        Recordset := Connection.CreateDBRecordset('Select * from help_category', '');
        Recordset.Open;
        While Not Recordset.Eof Do
        Begin
            Debugout(Recordset.FieldByName('name').AsString);
            Recordset.Next;
        End;
    Finally
        Connection.Free;
    End;
End;

Public Class WebMethod1
    Inherits WebMethod
    Public Overrides Sub Execute
        Dim Connector As TDatabaseServiceConnector
        Dim Connection As TDatabaseServiceConnection
        Dim Recordset As TAnyDBRecordset
 
        Connector = Catalog().GetServiceConnectorByID("Zeos MySQL1") As TDatabaseServiceConnector
        Connection = Connector.CreateConnection()
        Try
            Recordset = Connection.CreateDBRecordset("Select * from help_category", "")
            Recordset.Open()
            While Not Recordset.EOF()
                DebugOut(Recordset.FieldByName("name").AsString)
                Recordset.[Next]()
            End While
        Finally
            Connection.Free()
        End Try
    End Sub
 
End Class

public class WebMethod1 : WebMethod
{
    public override void Execute()
    {
        TDatabaseServiceConnector Connector;
        TDatabaseServiceConnection Connection;
        TAnyDBRecordset Recordset;
 
        Connector = Catalog().GetServiceConnectorByID("Zeos MySQL1") as TDatabaseServiceConnector;
        Connection = Connector.CreateConnection();
        try
        {
            Recordset = Connection.CreateDBRecordset("Select * from help_category", "");
            Recordset.Open();
            while (!Recordset.EOF())
            {
                DebugOut(Recordset.FieldByName("name").AsString);
                Recordset.Next();
            }
        }
        finally
        {
            Connection.Free();
        }
    }
}