Database Connectors

Morfik brings to your application development world the concept of the Database Connector. A Database Connector allows your application to have access to data made available by an online service, a database management system (MS SQL, Oracle, Firebird, MySQL, etc) or a standard Internet service such as email. Data Connectors allow you to treat disparate data sources as if they were sets of database tables. In this chapter we will concentrate on the external database related Service Connectors.

As an external database, Firebird would be just one more, except that Morfik uses its built-in native database access layer. So, when working with an external Firebird database you use all the same classes you would use to programmatically access the Primary database, just a different connection.

The first step to connect to any kind of external database is to create a connector for it. This can be done by going to the Home|Data Connectors or the External Data | Database Connectors menu option. This option will bring up the dialog you see on Figure 1.

The difference between choosing the "Data Connectors" and the specific connectors (Firebird, ODBC or WSDL) is the very first step in the wizard where the Data Connectors provides. For the following example we use the "Data connectors" from the External Data Ribbon tab.

Figure 1: First page of the Firebird Connector Wizard

In the dialog that appears in Figure 1 you can see the "External Firebird Data Sources" option selected in the first page of the Data Connector Wizard.

Once you choose to work with an External Firebird Data Source you are taken to the second page of the Wizard in which you must indicate the location and connection parameters for your external database, as you can see in Figure 2.

Figure 2: connecting to an External Firebird Data Source

Once you have successfully connected to your database (you can test the connection by clicking on the "Test Connection" button) you are taken to the third page of the Wizard which will ask you to select which tables you wish to have access to, in this project. The third page of the Wizard can be seen in Figure 3.

To demonstrate how this works, we use the sample Firebird Employee database. In Figure 3 you can see the Employee table for this database is selected. More tables are available and can be selected, if needed. We will see an example with a larger database, when we look at connecting with an MS SQL database.

Figure 3: List of tables in the selected external database with the Employee table selected

On the third page of the Wizard, when you click on the "Advanced" button, it will bring up a window in which you can attribute aliases to the tables you are bringing into the project. This window can be seen in Figure 4. You might want to change the names of data objects to conform to internal corporate policy or to avoid name clashes with objects you have already created within your project.

Figure 4: Window for creating aliases for objects in external databases

Once you have completed the process of choosing the tables with which you work with in your project, you are taken to the fourth page of the Wizard where you are called upon to name the Data Connector you are creating.

Figure 5: Choose a name for the new Firebird connector

After you have gone through these steps the Wizard will be complete and you can exit it by clicking on finish. Once you have gone through the Wizard, you will be able to see a new table in your project. In Morfik external tables are represented in the same manner as tables from the application's default database. If you chose more than one table on running the Wizard, you will have as many new table objects. In Figure 6 you can see a screenshot of the Morfik Workspace with the external table shown.

Figure 6: Morfik Workspace with an external table called Contacts, shown in the tables list

Once the Data Connector has been configured you will be able to use the external tables just about anywhere where you could use a table from the Primary database.

Creating a query against an external database is almost exactly the same as doing it against the Primary database. The main difference is that when your project has a Data Connector configured you will be asked to state which database you wish to query against. This is done by bringing up the window that you can see in Figure 7.

Figure 7: New Query based on the external database table

If you wish to create a query, visually within the Workspace, after you have selected the appropriate database in the window shown in Figure 7, you are taken to the Visual Query Designer, just as when you work with a primary database, as you can see in Figure 8.

Figure 8: Visual Query Designer showing query design based on an external database table

Using Morfik you can connect to any data source that has support for an ODBC driver. This opens up possibilities to connecting to all the major databases, commercial or open source as well as many proprietary data sources.

In order to present you with a complete overview of how to use an external database via ODBC connectivity, we use MS SQL Server as an example. All that we will see in this chapter can be applied to other databases as well.

To connect to a MS SQL Server database, you will follow a procedure which closely mirrors that described in the topics regarding the connection to an external Firebird database. In this case, in starting the Data Connector Wizard, you will choose the "ODBC data sources" option in the first page of the Wizard as shown in Figure 9.

Figure 9: Starting up the Data Connector Wizard in order to create a new ODBC Data Sources

In order to connect to an external MS SQL database, we will need to supply the Connection string. In this example, this will involve creating a new Data Source Name (DSN). Click on Build to go through the steps to create the new DSN.

Figure 10: Build a new Data Source Name

In the "Select Data Source" dialog click on New and you are asked to choose the ODBC driver or data source you will be using which is "SQL Server", as shown in Figure 11.

Figure 11: Select an ODBC driver or a data source to use with this Data Connector

After indicating that you want to work with the Microsoft SQL Server driver and since this is a new DSN, you will be asked to enter details for the new DSN. Give the new DSN a name and click on OK and then Finish to Save, as shown in Figure 12.

Figure 12: Saving the ODBC DSN name

Next you will need to enter the details of the server that runs the MS SQL database and the optional description as shown in Figure 13 and click on Next.

Figure 13: SQL Database server details

Choose how the SQL server should verify the authenticity of the login ID. In this example we choose the SQL Server authentication method and enter the username and password.

Figure 14: Data Connector Wizard requests information about the authenticity method and the username and password

You are now connected to the SQL Server and are asked to choose the actual database on the server with which you want to work and click on Next.

Figure 15: Choosing a database after initial configuration of the ODBC Database Data Connector

Once the database is selected (in this case the Northwind database), the ODBC Microsoft SQL Server setup dialog is displayed where the connection can be tested. Press OK on the "ODBC Microsoft SQL Server Setup" dialog, then OK again on the "Select Data Source" dialog which should already have the name of the new DSN. Finally you are asked to confirm the user name and password to connect to the database before going back to the Data Connector wizard.

Figure 16: Additional confirmation in creating the new MSSQL data source name

Following the above steps, the Morfik Data Connector wizard now includes the full Connection string to continue with connecting to your MS SQL database.

Figure 12a: You have the connection string, you are now ready to select the data objects that will be used in the project

Just as you could change the names of the tables in an application which used the Data Connector to the Firebird external database, you can do so when working with the ODBC data source as well.

In Figure 14 you can see the selection of the desired tables within the Wizard itself and then on Figure 15 you can see the window brought up by clicking on the "Advanced" button. This window allows the aliasing of specific external data objects, as we have seen before.

Figure 14: Choosing which data object will be used in the project

Figure 15: Creating aliases for tables from different origins, which are used in your project

Once you have completed all these steps in the Data Connector you will be, again, expected to name this Data Connector, as shown in Figure 16.

Figure 16: Data Connector Wizard asks for the name of the Connector that is about to be created

Once all this is done and the Wizard has been concluded you will see a new set of tables within the Morfik Workspace as can be seen in Figure 17.

Figure 17: Several external tables listed in your project's table list

Creating a query against an external ODBC database is almost exactly the same as doing it against the Primary database. Once you have selected that you want to work an external database you will be taken to the Visual Query designer. From the Visual Query designer you can work freely to create your query. In Figure 18 you can see a query being visually created, based on four of the selected tables.

Figure 18: Visually creating a complex query on external database tables

Morfik allows you to choose from the ODBC DSN data sources that are configured in your computer or even from DSN configuration files. At the start of the Data Connector Wizard, you have the option of looking up and selecting an existing DSN on your system rather than having to create a new DSN.

Figure 15: System DSN selection page in the Create Data Connector Wizard

All we have seen in this chapter is about accessing the external databases through Morfik’s built-in data-awareness. This is very simple and straight forward. In Morfik, visual, high level, objects see mapped external tables just as if they were tables in the primary database.

You can access the external databases by writing code in a WebMethod for example. In order to do that you must go through the process of getting a connection to them. The code you see in listing 1 and listing 2 should give you a general idea of how to do this. The code in both listings does exactly the same things, using two different Data Connectors, however. The first one shows you how to use a connection to an external Firebird database, while the second one shows you how to do the same thing with an ODBC connection. In this test scenario we used a Microsoft Access external database. All databases that are accessed through the ODBC Data Connector would work with the same source code.

Listing 1 – Server side code of a WebMethod that accesses an external Firebird database.

FX Code

Unit UserLogin_ExtFB;
 
Interface
 
Type
UserLogin_ExtFB = Class(WebMethod)
    UserName     : String;
    Password     : String;
    Authorized   : Boolean;
    UserID       : Integer;
    CompleteName : String;
  Private
    { Private declarations }
  Public
    { Public declarations }
    Procedure Execute; override;
End; ['Published=False']
 
Implementation
 
uses 
   SystemConnectors, 
   SystemConnectorsIBO,   
   SystemCatalog,
   SystemDatabase;
 
Procedure UserLogin_ExtFB.Execute;
Var  
    Connector  : TServiceConnector;
    Connection : TIBOServiceConnection;
    UserRS     : TRecordSet;
    SQLstr     : string;
Begin
    SQLstr := 'SELECT "UserID", "Name" FROM "tbUser_1" where ' +
                '"UserName" = ''' + UserName  + ''' and ' +
                '"Password" = ''' + Password  + '''';
 
    Connector := Catalog.GetServiceConnectorByID('External Firebird');
    If Connector = Nil Then Exit;
 
    Connection := TIBOServiceConnection(Connector.CreateConnection);
    Try
        UserRS := Connection.CreateRecordSet(SQLStr);   
        UserRS.Prepare;
        UserRS.Active := True;
        UserRS.First;   
        Authorized := False;
        UserID := -1; 
        CompleteName := ''; 
        if not (UserRS.Bof and UserRS.Eof) then
        begin
          Authorized := True;
          UserID := UserRS.FieldByName('"UserID"').AsInteger; 
          CompleteName := UserRS.FieldByName('"Name"').AsString; 
        End;
    Finally
        UserRS.Free;
        Connection.Free;
    End;
End;
 
End.

/*
%MA DYNAMIC
*/
Using SystemConnectors
Using SystemConnectorsIBO
Using SystemCatalog
 
Namespace UserLogin_ExtFB
    Public Class UserLogin_ExtFB
        Inherits WebMethod
        Public UserName As String
        Public Password As String
        Public Authorized As Boolean
        Public UserID As Integer
        Public CompleteName As String
 
        ' Public declarations 
        Public Overrides Sub Execute
            Dim Connector As TServiceConnector
            Dim Connection As TIBOServiceConnection
            Dim UserRS As TRecordSet
            Dim SQLstr As String
 
            SQLstr = "SELECT ""UserID"", ""Name"" FROM ""tbUser_1"" where " 
           + """UserName"" = '" + UserName + "' and " + """Password"" = '" + Password + "'"
            Connector = Catalog().GetServiceConnectorByID("External Firebird")
            If Connector = Nothing Then Return
            Connection = Ctype(Connector.CreateConnection(), TIBOServiceConnection)
            Try
                UserRS = Connection.CreateRecordSet(SQLstr)
                UserRS.Prepare()
                UserRS.Active = True
                UserRS.First()
                Authorized = False
                UserID = -1
                CompleteName = ""
                If Not (UserRS.Bof and UserRS.Eof) Then
                    Authorized = True
                    UserID = UserRS.FieldByName("""UserID""").AsInteger
                    CompleteName = UserRS.FieldByName("""Name""").AsString
                End If
            Finally
                UserRS.Free()
                Connection.Free()
            End Try
        End Sub 
 
    End Class
End Namespace

/*
$MA DYNAMIC
*/
using SystemConnectors;
using SystemConnectorsIBO;
using SystemCatalog;
 
namespace UserLogin_ExtFB 
{
    public class UserLogin_ExtFB : WebMethod
    {
        public String UserName;
        public String Password;
        public Boolean Authorized;
        public Integer UserID;
        public String CompleteName;
        // Public declarations 
        public override void Execute()
        {
            TServiceConnector Connector;
            TIBOServiceConnection Connection;
            TRecordSet UserRS;
            String SQLstr;
 
            SQLstr = "SELECT \"UserID\", \"Name\" FROM \"tbUser_1\" where " 
           + "\"UserName\" = '" + UserName + "' and " + "\"Password\" = '" + Password + "'";
            Connector = Catalog().GetServiceConnectorByID("External Firebird");
            if (Connector == null)
                return;
            Connection = (TIBOServiceConnection)(Connector.CreateConnection());
            try
            {
                UserRS = Connection.CreateRecordSet(SQLstr);
                UserRS.Prepare();
                UserRS.Active = True;
                UserRS.First();
                Authorized = False;
                UserID = -1;
                CompleteName = "";
                if (!(UserRS.Bof && UserRS.Eof))
                {
                    Authorized = True;
                    UserID = UserRS.FieldByName("\"UserID\"").AsInteger;
                    CompleteName = UserRS.FieldByName("\"Name\"").AsString;
                }
            }
            finally
            {
                UserRS.Free();
                Connection.Free();
            }
        }
    }
}

Listing 2 – Server side code of a WebMethod that accesses an external ODBC database.

FX Code

Unit UserLogin_ExtAccess;
 
Interface
 
uses 
   SystemCatalog,
   SystemConnectors, 
   SystemConnectorsODBC,
   SystemDatabase,
   SystemDatabaseSQLDB;
 
Type
UserLogin_ExtAccess = Class(WebMethod)
    UserName     : String;
    Password     : String;
    Authorized   : Boolean;
    UserID       : Integer;
    CompleteName : String;
  Private
    { Private declarations }
  Public
    { Public declarations }
    Procedure Execute; override;
End; ['Published=False']
 
 
Implementation
 
Procedure UserLogin_ExtAccess.Execute;
Var  
    Connector  : TServiceConnector;
    Connection : TODBCServiceConnection;
    Query      : TSQLQuery;
    SQLstr     : string;
Begin
    SQLstr := 'SELECT "UserID", "Name" FROM "tbUser_1" where ' +
                '"UserName" = ''' + UserName  + ''' and ' +
                '"Password" = ''' + Password  + '''';
 
    Authorized := False;
    UserID := -1; 
    CompleteName := ''; 
 
    Connector := Catalog.GetServiceConnectorByID('External Access');
 
    If Connector = Nil Then Exit;
 
    Connection := TODBCServiceConnection(Connector.CreateConnection);
    Query := TSQLQuery.Create(Nil);
    Query.Database:= Connection.Connection;
 
    Try
        Query.SQL.Text := SQLStr;
        Query.Prepare;
        Query.Open;
        Query.First;
        if not (Query.Bof or Query.Eof) then
        begin
          Authorized := True;
          UserID := Query.FieldByName('"UserID"').AsInteger;
          CompleteName := Query.FieldByName('"Name"').AsString; 
        End;
    Finally
        Query.Free;
        Connection.Free;
    End;
End;
End.

/*
%MA DYNAMIC
*/
Imports SystemCatalog
Imports SystemConnectors
Imports SystemConnectorsODBC
Imports SystemDatabaseSqlDb
 
Namespace UserLogin_ExtAccess
    Public Class UserLogin_ExtAccess
        Inherits WebMethod
        Public UserName As String
        Public Password As String
        Public Authorized As Boolean
        Public UserID As Integer
        Public CompleteName As String
 
        ' Public declarations 
        Public Overrides Sub Execute
            Dim Connector As TServiceConnector
            Dim Connection As TODBCServiceConnection
            Dim Query As TSQLQuery
            Dim SQLstr As String
 
            SQLstr = "SELECT ""UserID"", ""Name"" FROM ""tbUser_1"" where " 
           + """UserName"" = '" + UserName + "' and " + """Password"" = '" + Password + "'"
            Authorized = False
            UserID = -1
            CompleteName = ""
            Connector = Catalog().GetServiceConnectorByID("External Access")
            If Connector = Nothing Then Return
            Connection = Ctype(Connector.CreateConnection(), TODBCServiceConnection)
            Query = New TSQLQuery(Nothing)
            Query.DataBase = Connection.Connection
            Try
                Query.SQL.Text = SQLstr
                Query.Prepare()
                Query.Open()
                Query.First()
                If Not (Query.BOF Or Query.EOF) Then
                    Authorized = True
                    UserID = Query.FieldByName("""UserID""").AsInteger
                    CompleteName = Query.FieldByName("""Name""").AsString
                End If
            Finally
                Query.Free()
                Connection.Free()
            End Try
        End Sub 
 
    End Class
End Namespace

/*
$MA DYNAMIC
*/
imports SystemCatalog;
imports SystemConnectors;
imports SystemConnectorsODBC;
imports SystemDatabaseSqlDb;
 
namespace UserLogin_ExtAccess
{
    public class UserLogin_ExtAccess : WebMethod
    {
        public String UserName;
        public String Password;
        public Boolean Authorized;
        public Integer UserID;
        public String CompleteName;
        // Public declarations 
        public override void Execute()
        {
            TServiceConnector Connector;
            TODBCServiceConnection Connection;
            TSQLQuery Query;
            String SQLstr;
 
            SQLstr = "SELECT \"UserID\", \"Name\" FROM \"tbUser_1\" where " 
            + "\"UserName\" = '" + UserName + "' and " + "\"Password\" = '" + Password + "'";
            Authorized = False;
            UserID = -1;
            CompleteName = "";
            Connector = Catalog().GetServiceConnectorByID("External Access");
            if (Connector == null)
                return;
            Connection = (TODBCServiceConnection)(Connector.CreateConnection());
            Query = new TSQLQuery(null);
            Query.DataBase = Connection.Connection;
            try
            {
                Query.SQL.Text = SQLstr;
                Query.Prepare();
                Query.Open();
                Query.First();
                if (!(Query.BOF || Query.EOF))
                {
                    Authorized = True;
                    UserID = Query.FieldByName("\"UserID\"").AsInteger;
                    CompleteName = Query.FieldByName("\"Name\"").AsString;
                }
            }
            finally
            {
                Query.Free();
                Connection.Free();
            }
        }
    }
}

It is important to note that when you are using an ODBC connection you do not use the same TRecordSet class that is used when you are accessing a Firebird connection. Instead, you use a class called TSQLQuery which is quite similar, but not identical. The usage is different because Morfik believes that you should have the full power of the underlying data access mechanism at your disposal, not a common subset, if you wish to directly handle data access.

For handling the visual aspects of database interaction, the Morfik Framework does treat all data connections in a similar way, handling the differences for you.

Note in the code, in Listings 1 and 2, that the way to get access to your external database is to get an instance of the Connector you have defined and then, from it, you get a Connection object. The connection objects will be specific for each kind of Connector and the way to use them will be different as the code examples show.

All databases that are accessed through the ODBC connector will be accessed in the same manner.

• Database Connectors
  
• Main Page