Working with External Databases
From Morfikwiki.com
Contents |
[edit] Morfik Database Connectivity
Database connectivity is a major point in any modern application. Databases have become so ubiquitous that most people do not even stop to think about them. Databases are mainly taken for granted as part of any developer’s arsenal of tools to attack any specific problem.
With that in mind Morfik not only chose to provide a fully integrated relational database server, but to also offer support for connecting to other databases such as MS SQL Server, MySQL and Oracle.
Since database connectivity is essentially a server based task, this topic does not apply to Morfik Browser Application projects. All the features described in this topic apply only to Morfik Web Application projects.
[edit] Primary Databases vs. External Databases
One of the first things to understand in working with external databases in Morfik is what the differences between primary databases and external databases are. Primary databases are always Firebird Databases and these accompany your project from the beginning. Morfik stores some system information in these databases and they are an integral part of how the Morfik framework functions.
An external database is a plain and simple database, with no system tables or any intrinsic use by the Morfik Framework.
[edit] Why Use External Databases?
The most common reason to use an external database would be because you already have it. In this case, even existing Firebird databases would be good candidates as external databases.
Another reason could be adherence to corporate standards. If your company has standardized on another database, Morfik can access it, provided that it has an ODBC driver (native connectivity is under development).
[edit] Database Mashups
Data mashups between different databases is one area in which Morfik really excels at. You could be combining data from a primary Firebird database, with an external MS SQL Server database, with data coming directly from Salesforce.com. In this kind of situation you will, in fact, have not one but several external databases tied to your application.
[edit] External Databases and Service Connectors
Morfik brings to your application development world the concept of the Service Connector. A Service Connector allows your application to have access to data made available by an online service (Salesforce.com, eBay, etc), a database management system (MS SQL, Oracle, Firebird, MySQL, etc) or a standard Internet service such as email. Service 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.
[edit] Working with External Firebird Databases
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.
[edit] Creating a 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 Project|Service Connectors menu option. This option will bring up the dialog you see on Figure 1.
Figure 1 – First page of the Service 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 Service 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 – Second Page of the Service Connector Wizard when 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.
Figure 3 – Third Page of the Service Connector Wizard when connecting to an External Firebird Data Source.
Just to demonstrate how this works, I created a database with a single small table. In Figure 3 you can see this table selected. If more tables where available, you could choose several, or just one as in this case. We will see an example with a larger database, when we look at connecting with an MS SQL database.
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 Service Connector you are creating. You can see this page in Figure 5, where the Connector receives the name Firebird Contacts.
Figure 5 - Fourth Page of the Service Connector Wizard when connecting to an External Firebird Data Source.
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 1.4.x and previous versions, this table would have a special icon of a table with an overlying arrow which indicates that it is an "external" table. In Morfik AppsBuilder R2, 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 Service Connector as been configured you will be able to use the external tables just about anywhere where you could use a table from the Primary database.
[edit] Querying an External 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 Service 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 – Select database window in the Morfik Workspace.
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.
[edit] Working with Different Databases
In order to present you with a complete overview of how to use an external database, I decided to use MS SQL Server as an example. All that we will see in this chapter can be applied to other databases as well.
[edit] Which Databases are Supported?
You can use with Morfik any database that has 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.
[edit] Connecting to an MS SQL Database
In order to connect with an 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 Service 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 Service Connector Wizard in order to create a new
ODBC Data sources
Having chosen the ODBC data source option in the Wizard you are asked to choose the ODBC driver or data source you will be using. This can be seen in Figure 10.
Figure 10 – Select an ODBC driver or a data source to use with this Service Connector.
After indicating that you want to work with the Microsoft SQL Server driver you will be asked to provide connection details, just as is show in Figure 11.
Figure 11a – Service Connector Wizard requests information about the database connection such as username, password and the name of the plane.
Figure 11b.
When you work with MS SQL you are connecting first to the server, then you will be asked to choose the database on the server, with which you want to work, as shown in Figure 12.
Figure 12 – Choosing a database after initial configuration of the ODBC
Database Service Connector.
Figure 12b. – 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 Service Connector to the Firebird external database, you can do so when working with the ODBC data source as well. In order to more clearly illustrate this I have done another example which uses MS SQL Server’s Northwind database in order to have more tables in the database.
In Figure 13 you can see the selection of the desired tables within the Wizard itself and then on Figure 14 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 13 – Choosing which data object will be used in the project.
Figure 14 – Creating aliases for tables from different origins, which are used in your project.
Once you have completed all these steps in the Service Connector you will be, again, expected to name this service connector, as shown in Figure 15.
Figure 15 – Service 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. These tables will show an arrow over table icon as an indication of their being external tables. An example of this can be seen in Figure 16.
Figure 16 – Several external tables listed in your project’s table list.
[edit] Querying an External MS SQL 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 17 you can see a query being visually created, based on four tables.
Figure 17 – Visually creating a complex query on external database tables.
[edit] 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. In Figure 10 you can see a checkbox that gives you the option of looking up the DSNs, instead of just choosing an ODBC driver.
In Figure 18, you can see a page of the Create Service Connector Wizard which allows you to choose a machine DSN configuration for the creation of the connector for the access to your external data base.
Figure 18 – Machine DSN selection page in the Create Service Connector Wizard.
[edit] 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 AppsBuilder, 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 service 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 service connector would work with the same source code.
Listing 1 – Server side code of a WebMethod that accesses an external Firebird database.
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.
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.
[edit] Wrapping it up
Morfik allows connection to external data sources for data access from within your applications and from the Workspace itself. External database connections are handled through the Morfik Service Connectors which are a part of the Morfik Satellite application architecture.
