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.

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


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


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


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


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


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


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

Querying an External Firebird Connection

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.


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


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

ODBC Connectors

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.


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


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


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


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


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


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


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


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


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


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


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


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


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

Querying databases with ODBC Connection

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.


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

Using ODBC DSN Data Sources

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.


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

Accessing External Databases Through Code

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.



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.




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.


Related Topics

Back to top