The Morfik Zeos package contains classes that support direct connections to client-server databases through native connectors, removing the need to use ODBC. This package is based upon the Open source ZeosLib code which you can find here. This package offers native connections for MSSQL, MySQL and PostgreSQL databases.

package-view.png
[ View Live Demo]
package-down.png


Getting started

To use the Morfik Zeos package simply add the package to the project by utilizing the “Used Packages” command on the project ribbon (see Figure 1 Used Packages Command), or simply drag the package file onto the application main client area. Once added there will appear new connection options in the Data Connectors dialogs which are discussed below.


security-fig1.png
Figure 1: Used Packages Command


Using the New Data Connectors

Once the Zeos package has been added to a project, starting the Data Connectors wizard will add the MSSQL and MySQL options seen in Figure 2 Data Connector Options.


1.png
Figure 2: Data Connector Options


Selecting the Zeos Connector for MSSQL

To create a new connector for MSSQL, highlight the MSSQL option above, select the Add radio button and click the Next button. The build connection dialog will appear as seen in Figure 3 Build the Connection String.

2.png
Figure 3: Build the Connection String

Clicking on the Build button brings up a fairly standard dialog box seen in Figure 4 Build the Connection String Step 2.


3.png
Figure 4: Build the Connection String Part 2


After completing the dialog box and testing the connection, saving the build string and clicking the Next button, a dialog box containing a list of tables available will be displayed for selection. Check the ones to be available in the application. In Figure 5 Table Selection all the available tables in the standard MSSQL Pubs sample database are selected.


4.png
Figure 5: Table Selection


In the next step, provide a name for the connection and proceed to Finish the wizard. After completing these steps, the tables will have been added to the project manager and will be available as any other table would be. (See Figure 6 Tables in the Project Manager)


5.png
Figure 6: Tables in the Project Manager


Creating a Query

Adding a new visual query is standard except for the option to select the connector to use before proceeding to design the query. The same is true in creating a passthrough query but, for those used to Firebird, field and table names are not case-sensitive so double-quotes are not generally needed.

Using Zeos Connectors at Runtime

When accessing the database in code at runtime, add the SystemCatalog to the Uses clause and then select the MSSQL service connector by the name given to it when created in the wizard, as seen in the sample code below:

FX Code

Procedure wm_GetTitles.Execute;
Var
    Connector  : TDatabaseServiceConnector;
    Connection : TDatabaseServiceConnection;
    Recordset  : TAnyDBRecordset;
    sql        : String;
    s          : TStringList;
Begin
    sql := 'SELECT "titles"."title" as title ' +
           'FROM (("authors" INNER JOIN "titleauthor" ON ' +
           '("authors"."au_id"="titleauthor"."au_id")) ' +
           'INNER JOIN "titles" ON ("titleauthor"."title_id"="titles"."title_id")) ' +
           'WHERE "authors"."au_id" = :"prm"';
    Connector := Catalog.GetServiceConnectorByID('Zeos MSSQL1') As TDatabaseServiceConnector;
    Connection := Connector.CreateConnection;
 
    Try
        Recordset := Connection.CreateDBRecordset(sql,'');
        Recordset.Prepare;
        Recordset.ParamByName('prm').AsString := au_id;
        Recordset.Open;
        s := TStringList.Create;
        While Not Recordset.Eof Do
        Begin
            s.Add(Recordset.FieldByName('title').AsString);
            Recordset.Next;
        End;
        s.Delimiter := '|';
        au_id := s.DelimitedText;
    Finally
        Connection.Free;
    End;
End;


See Also

Back to top