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.

Using the Zeos Native Database Connectivity Package

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.

ExternalDbRibbon.jpg
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.

ExternalDbWizard.png
Figure 2: Database Connectors Wizard


ExternalDbLocation.png
Figure 3: Database Location


ExternalDbObjects.png
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.

ExternalDbTable.jpg
Figure 5: Working with external Table

Technical Notes

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.

Coding with Zeos database package

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;
    Recordset := Connection.CreateDBRecordset('Select * from help_category', '');
    Try
        Recordset.Open;
        While Not Recordset.Eof Do
        Begin
            Debugout(Recordset.FieldByName('name').AsString);
            Recordset.Next;
        End;
    Finally
        Recordset.Free;
        Connection.Free;
    End;
End;


Calling a MSSQL Stored Procedure With Zeos Package

Using the ZEOS Package you can also make use of specific features of the supported databases, such as Stored Procedures. The code snippets below show how a MSSQL Stored Procedure might be called from the implementation of a Web Method.

FX Code

Implementation
 
Uses SystemCatalog;
 
Procedure WebMethod1.Execute;
Var
    Recordset  : TAnyDbRecordset;
    Connector  : TDatabaseServiceConnector;
    Connection : TDatabaseServiceConnection;
Begin
    Connector  := Catalog.GetServiceConnectorByID('MSSQL1') As TDatabaseServiceConnector;
    Connection := Connector.CreateConnection;
    Recordset  := Connection.CreateDBRecordset('Exec Proc1 @Param1 = :myparam');
 
    Try
       Recordset.Prepare;
       Recordset.ParamByName('myparam').AsString := 'xyz';
       Recordset.Open;
       DebugOut(Recordset.Fields[0].AsString);
       DebugOut(Recordset.Fields[1].AsString);
    Finally 
       Recordset.Free;
       Connection.Free;
    End;
End;
 
End.


The following is the source code for a sample Stored Procedure which would be compatible with the call used the previous example.

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[Proc1]    Script Date: 03/23/2012 12:56:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc1] @Param1 NVARCHAR(50)
AS
SELECT @Param1, CURRENT_TIMESTAMP