DataSnap technology overview

The DataSnap technology (previously known as MIDAS) allows development of client–server applications, specifically applications using databases, that work via Internet, local network, or on a local PC. For connection, DataSnap allows using the TCP/IP protocol, as well as HTTP and HTTPS. For backward compatibility, COM/DCOM support also remains.

Besides, DataSnap allows development of applications for both PC and mobile devices based on iOS, Android and Windows Phone.

In addition, DataSnap supports secure connection capabilities using JSON, as well as use of filters for data encryption and compression for enhanced security.

This technology also supports the asynchronous method of all client applications notification about changes made on a server.

DataSnap is available and allows multi–tier application development in both Delphi and C++Builder.

Three–tier databases overview

Three–tier architecture involves the following application components:

Client — an interface component (usually graphical), that introduces the first tier, the end–user application itself. The first tier mustn’t be related to the database directly, be loaded by the main business–logic and store application status.

Elementary business logic should be and usually is introduced at the first tier: authorization interface, encryption algorithms, checking for input data validity, simple operations (sorting, grouping, values count) with data already stored on the server.

Application server is represented at the second tier. The second tier includes the major business logic part. And it excludes fragments, that are exported to clients and the ones implemented at the DB server level: stored procedures and triggers.

DB server provides data storing and is introduced at the third tier. Usually, it is a relational or object–oriented DBMS. While the third tier is a database along with stored procedures, triggers, and a scheme describing the application in terms of relational model, the second tier is designed as program interface binding client components with the database application logic.

In the simplest configuration, the application server can be combined with the database server physically on the same computer, to which one or more clients connect via the network.

In the “ideal” configuration, the database server is located on a dedicated computer, to which one or more application servers are connected via the network, to which, in turn, clients are connected.

Application server structure

Application server encapsulates most of the business logic of the distributed application and provides client access to the database.

The main part of the application server is the remote data module.

Firstly, like a normal data module, it is a platform for placement of non–visual data access components and provider components. Connection components, transactions, and components, that encapsulate datasets, placed in the remote data module, provide three–tier applications with connection to the database server. It can be such sets of components as UniDAC, ODAC, SDAC, MyDAC, IBDAC, PgDAC, LiteDAC.

Secondly, the remote data module implements the main features of the application server based on providing customers with the IAppServer interface or its descendant. For this, the remote data module must contain the required number of TDataSetProvider provider components. These components transfer data packages to the client application, or rather to TClientDataSet components, and also provide access to the methods of the interface. Each component encapsulating the dataset designed for transfer to the client must be associated with a provider component.

Client application structure

The client application in the three–tier model should have only a minimal set of necessary functions, delegating the majority of data processing operations to the application server. Above all, the remote client application must provide access to the application server.

The DataSnap connection components are used for this. They provide the IAppServer interface used by provider components on the server side and by TClientDataSet components on the client side for data packages transfer. To work with datasets, the TClientDataSet components are used in the data caching mode.
In their structure a client application and a database application are alike. Client connections to the application server are performed by the DataSnap components. These components interact with the remote data module, included to the server, using the IAppServer interface methods.

Also, in the client application, there can be used additional, defined by a developer, methods of the remote data module interface, inherited from the IAppServer interface. Connection to the application server is provided by MIDAS.DLL, required on the client computer.

Like a normal DB application, the distributed multi–tier application client should contain components encapsulating datasets, that are associated with visual data display components. Obviously, the server dataset should be copied by the client application to a local buffer. Thereat, an effective procedure of data loading by comparably small portions should be used, that allows to significantly reduce the load on transfer channel between application client and server.

Data caching and encryption in the client application is achieved by the specialized component — TClientDataSet, the remote ascendant of which is TDataSet. Besides methods inherited from descendants, the TClientDataSet class encapsulates a number of additional functions, that simplify the data control. To retrieve the server dataset, the TClientDataSet component interacts with the TDataSetProvider component using the IProviderSupport interface methods.

Application server creation using UniDAC

1. Create a new project:

Main menu –> File > New > Other –> Delphi Projects –> DataSnap Server –> DataSnap Server;

• uServer.pas — the main server form;
• uServerMethods.pas — contains server methods;
• uServerContainer.pas — contains elements for the DataSnap server implementation.

After all the performed steps, the server container must contain 5 components. Short description of these components:

• DSServer — controls data transfer and server classes;
• DSServerClass — defines the server class with public methods invoked by the client application;
• DSHTTPService — provides access to the server via the HTTP protocol;
• DSAuthenticationManager — allows users authentication and authorization on connection to the server and invoking of server methods;
• DSTCPServerTransport — provides access to the server via the TCP/IP protocol.

Set the HideDSAdmin property of the TDSServer component to True, that will allow to hide the DSAdmin class administrative server methods.

And set the Autostart property to False (the server mustn’t run automatically).

Set the LifeCycle property of the TDSServerClass component to the Session property, in this case, a unique session is created for each user connection, and as a result, its own class example is provided for each connection.

Create an OnUserAuthenticate event in the TDSAuthenticationManager component, where user will be verified when attempting to connect to the server.

On the main form of the server place components responsible for server settings and start, as well as for displaying short event log: server start/stop, client connection/disconnection. Much more detailed logs can be kept in the real application, but we won’t go into details here. So place the components onto the form as shown in the figure. Before running the server, the ports must be specified, that will listen to the server (and to which the client will connect), for the TCP/IP and HTTP protocols, as well as using encryption and compression should be set for transferring information between the client and server.

Write a handler of the OnClick event for the Start button in the following way:

procedure TfMainServer.btStartClick(Sender: TObject);
begin
  if not ServerContainer.DSServer.Started then begin
    ServerContainer.DSTCPServerTransport.Port := StrToInt(edTCPIPPort.Text);
    ServerContainer.DSHTTPService.DSPort := StrToInt(edHTTPPort.Text);
    ServerContainer.DSServer.Start;
    mStatus.Lines.Add(Format('%s: TCP/IP Started', [TimeToStr(now)]));
    ServerContainer.DSHTTPService.Start;
    mStatus.Lines.Add(Format('%s: HTTP Started', [TimeToStr(now)]));
    btStart.Caption := 'Stop';
  end
  else begin
    ServerContainer.DSHTTPService.Stop;
    mStatus.Lines.Add(Format('%s: HTTP Stoped', [TimeToStr(now)]));
    ServerContainer.DSServer.Stop;
    mStatus.Lines.Add(Format('%s: TCP/IP Stoped', [TimeToStr(now)]));
    btStart.Caption := 'Start';
  end;
end;

On this button click, the selected parameters will be set and both servers will be started, on the second click — servers will be stopped.

In the TDSServer component events, implement the OnConnect and OnDisconnect events to log clients connection/disconnection to/from the server and display the client info.

procedure TServerContainer.DSServerConnect(DSConnectEventObject: TDSConnectEventObject);
var
  ci: TDBXClientInfo;
begin
  ci := DSConnectEventObject.ChannelInfo.ClientInfo;
  fMainServer.mStatus.Lines.Add(Format('Client %s Connected IP: %s, Port: %s', [ci.Protocol, ci.IpAddress, ci.ClientPort]));
end;

procedure TServerContainer.DSServerDisconnect(DSConnectEventObject: TDSConnectEventObject);
var
  ci: TDBXClientInfo;
begin
  ci := DSConnectEventObject.ChannelInfo.ClientInfo;
  fMainServer.mStatus.Lines.Add(Format('Client %s Disconnected IP: %s, Port: %s', [ci.Protocol, ci.IpAddress, ci.ClientPort]));
end;

Since our server is to interact with the DB, we should set up connection with DB using UniDAC, as well as implement queries and procedures for work with data.

The required DB objects should be created on the server, there are scripts for creating and filling the needed objects below. We will use a PostgreSQL DB in this sample, but, since UniDAC is a set of universal components, this project can work with any DB.

CREATE TABLE dept
(
  deptno serial NOT NULL,
  dname character varying(14),
  loc character varying(13),
  cnt integer,
  CONSTRAINT "PK_MASTER" PRIMARY KEY (deptno)
);
 
CREATE TABLE emp
(
  empno serial NOT NULL,
  ename character varying(10),
  job character varying(9),
  mgr integer,
  hiredate timestamp without time zone,
  sal real,
  comm real,
  deptno integer NOT NULL,
  CONSTRAINT "PK_DETAIL" PRIMARY KEY (deptno, empno)
);
 
CREATE TABLE users
(
  id numeric NOT NULL,
  username character(20) NOT NULL,
  passwd character(20) NOT NULL,
  CONSTRAINT pk_users_id PRIMARY KEY (id),
  CONSTRAINT uq_users_name UNIQUE (username)
);
  
CREATE OR REPLACE FUNCTION sp_check_user(a_username character DEFAULT ''::bpchar, a_passwd character DEFAULT ''::bpchar)
  RETURNS boolean AS
$BODY$BEGIN
IF EXISTS (SELECT 1 FROM users WHERE username = a_username AND passwd = a_passwd) THEN
RETURN True;
ELSE
RETURN False;
END IF;
END;
$BODY$
  LANGUAGE plpgsql;
 
CREATE TABLE users
(
  id numeric NOT NULL,
  username character(20) NOT NULL,
  passwd character(20) NOT NULL,
  CONSTRAINT pk_users_id PRIMARY KEY (id),
  CONSTRAINT uq_users_name UNIQUE (username)
);
 
CREATE OR REPLACE FUNCTION sp_check_user(a_username character DEFAULT ''::bpchar, a_passwd character DEFAULT ''::bpchar)
  RETURNS boolean AS
$BODY$BEGIN
IF EXISTS (SELECT 1 FROM users WHERE username = a_username AND passwd = a_passwd) THEN
RETURN True;
ELSE
RETURN False;
END IF;
END;
$BODY$
  LANGUAGE plpgsql;
 
CREATE OR REPLACE FUNCTION sp_dept_modify(a_deptno numeric DEFAULT (-1), a_dname character DEFAULT ''::bpchar, a_loc character DEFAULT ''::bpchar, a_op_type numeric DEFAULT 0)
  RETURNS void AS
$BODY$begin
  case a_op_type
    when 0 then delete from dept where deptno = a_deptno;
    when 1 then insert into dept (deptno, dname, loc) values (a_deptno, a_dname, a_loc);
    else update dept set dname = a_dname, loc = a_loc where deptno = a_deptno;
  end case;
end;$BODY$
  LANGUAGE plpgsql;
 
CREATE OR REPLACE FUNCTION sp_emp_modify(a_empno numeric DEFAULT (-1), a_ename character DEFAULT ''::bpchar, a_job character DEFAULT ''::bpchar, a_mgr numeric DEFAULT 0, a_hiredate timestamp without time zone DEFAULT (now())::timestamp without time zone, a_sal numeric DEFAULT 0, a_comm numeric DEFAULT 0, a_deptno numeric DEFAULT (-1), a_op_type numeric DEFAULT 0)
  RETURNS void AS
$BODY$begin
  case a_op_type
    when 0 then delete from emp where empno = a_empno;
    when 1 then insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (a_empno, a_ename, a_job, a_mgr, a_hiredate, a_sal, a_comm, a_deptno);
    else update emp set ename = a_ename, job = a_job, mgr = a_mgr, hiredate = a_hiredate, sal = a_sal, comm = a_comm where empno = a_empno;
  end case;
end;$BODY$
  LANGUAGE plpgsql;

After creation of DB objects, we should place Universal Data Access Components into the uServerMethods module.

• UniConnection: a component for connection to the DB;
• UniQuery: a component for SQL commands execution and generating the result DataSet;
• UniStoredProc: a component for invoking stored procedures (in some DBs, for example SQLite, stored procedures are not supported, therefore another way of data modifying should be chosen in such DBs);
• PostgreSQLUniProvider: a component implementing interaction with the PostgreSQL server. If using another DB, an appropriate provider should be placed onto the form.

The following properties should be set in UniConnection to connect to the DB:

• Provider: an appropriate DB provider, PostgreSQL in this particular case;
• Server: the name or IP of the PC, on which the PostgreSQL server is installed;
• Port: the PostgreSQL port number, 5432 by default;
• Username, Password: the PostgreSQL user credentials;
• Database: the name of the database on the chosen server, where our objects are created, Postgres by default;

There also can be set additional parameters, such as Schema, Pooling, Protocol, SSL, and others, that depend on the server settings and affect the connection mode. The full description of all the properties and options can be found in the UniDAC documentation.

The tremendous advantage of UniDAC in contrast to other data access components is the fact that UniDAC allows working with the most widespread DBs in the Direct mode, that means no need to install any additional DB clients and use client libraries, since it works directly via the TCP/IP protocol.

After setting all the parameters, to check connection, the Connected property should be set to True — if there is no error, then all connection parameter settings are correct, and we can proceed configuring other components.

The components can be configured with both Object Inspector and Connection Editor, that can be invoked by double–click on the UniConnection component (this applies to other Universal Data Access Components as well).

The following components to be configured are UniQuery components, that run queries to our created tables and generate the result dataset sent to the client. These components has the main property — SQL, in which the SQL query is written. Since we have two tables in the DB, we have to use two UniQuery components.

In the first TUniQuery named uDept write a query to retrieve data from the Dept table:

SELECT * FROM DEPT

In the second TUniQuery named uEmp write a query to retrieve data from the Emp table:

SELECT * FROM EMP WHERE DEPTNO = :DEPTNO

Since the Emp table has Master–Detail relations (one–to–many) with the Dept table, we have to limit the fetch from the Emp table with the data corresponding to the current record in the Dept table. For this, the query contains an additional Where condition, by means of which data will be filtered.

Technically, Master–Detail relationship can be implemented in the client application as well, the question, where the relationship should be implemented (on the client or on the server), depends on the aims and task of a particular application, data volume, quality and size of a data channel, etc.)

To modify data in both tables, we will use stored procedures, which scripts are provided above.

Certainly, in real business applications these procedures are much more complex, however, to demonstrate UniDAC and DataSnap capabilities, these primitive procedures will be enough. For work with stored procedures, the UniStoredProc component is used, where the procedure name must be specified. Place two of such components onto the form with the names usp_dept_modify and usp_emp_modify to modify the Dept and Emp tables respectively. In the StoredProcName property set the procedure names sp_dept_modify and sp_emp_modify respectively. A query to invoke these procedures will be automatically generated, as well as appropriate parameters will be added for each procedure.

In addition, as we decided to implement user authentication when connecting to the application server, we need one more procedure, that will compare the credentials entered by a user with the ones stored on the server. Place one more UniStoredProc component (usp_check_user) onto the form and set its StoredProcName property to sp_check_user. This procedure will be used in the OnUserAuthenticate event for credentials verification — if invalid, user will be refused to connect to the server.

Enter the following code into the OnUserAuthenticate event:

  ServerMethods.usp_check_user.ParamByName('a_username').AsString := User;
  ServerMethods.usp_check_user.ParamByName('a_passwd').AsString := Password;
  ServerMethods.usp_check_user.Execute;
  valid := ServerMethods.usp_check_user.ParamByName('result').AsBoolean;

The parameter has a boolean value and is responsible for authentication. If the result of the sp_check_user procedure execution is True, this means the credentials are correct and the client will connect to the server, if False — no connection will be established.

Now we need to implement methods, that will return the datasets generated in UniQuery to the client, and the methods, that the client will use to modify data. In order for these methods to be available to the client, they must be declared in the Public section of the TServerMethods class, in the uServerMethods module. The following is these methods declaration:

function GetDept: TDataSet;
function GetEmp(DeptNo: integer = -1): TDataSet;
procedure DeptModify(DeptNo: integer = -1; DName: string = ''; Loc: string = ''; op_type: integer = 0);
procedure EmpModify(Empno: integer = -1; EName: string = ''; Job: string = ''; Mgr: integer = 0; HireDate: TDateTime = 0; Sal: double = 0; Comm: double = 0; Deptno:integer = -1; op_type: integer = 0);
procedure EmpModify(Empno: integer = -1; EName: string = ''; Job: string = ''; Mgr: integer = 0; HireDate: TDateTime = 0; Sal: double = 0; Comm: double = 0; Deptno:integer = -1; op_type: integer = 0);

Consider implementation of the methods.

This method executes the query specified in uqDept and returns the generated dataset to the client with data from the Dept table.

function TServerMethods.GetDept: TDataSet;
begin
  uqDept.Close;
  uqDept.Open;
  Result := uqDept;
end;

This method executes the query specified in uqEmpand returns the generated dataset to the client with data from the Emp table. When requesting this method from the client, a corresponding DEPTNO number will be specified to be used in the where condition for filtering employees by a required department.

function TServerMethods.GetEmp(DeptNo: integer): TDataSet;
begin
  uqEmp.Close;
  uqEmp.ParamByName('DEPTNO').AsInteger := DeptNo;
  uqEmp.Open;
  Result := uqEmp;
end;

This method is designed for modifying data in the Dept table, and it invokes the created procedure. To simplify the sample, one procedure is created for each DML (INSERT, UPDATE, DELETE) operation, that will run an appropriate operation dependently on the op_type parameter set in the client when invoking the server method, dependently on what we want to do: add, remove or edit data.

procedure TServerMethods.DeptModify(DeptNo: integer; DName: string; 
  Loc: string; op_type: integer);
begin
  usp_dept_modify.ParamByName('a_deptno').AsInteger := DeptNo;
  usp_dept_modify.ParamByName('a_dname').AsString := DName;
  usp_dept_modify.ParamByName('a_loc').AsString := Loc;
  usp_dept_modify.ParamByName('a_op_type').AsInteger := op_type;
  usp_dept_modify.Execute;
end;

This method is similar to the previous one, but for the Emp table.

procedure TServerMethods.EmpModify(Empno: integer; EName, Job: string;
  Mgr: integer; HireDate: TDateTime; Sal, Comm: double; Deptno, op_type: integer);
begin
  usp_emp_modify.ParamByName('a_empno').AsInteger := Empno;
  usp_emp_modify.ParamByName('a_ename').AsString := EName;
  usp_emp_modify.ParamByName('a_job').AsString := Job;
  usp_emp_modify.ParamByName('a_mgr').AsInteger := Mgr;
  usp_emp_modify.ParamByName('a_hiredate').AsDateTime := HireDate;
  usp_emp_modify.ParamByName('a_sal').AsFloat := Sal;
  usp_emp_modify.ParamByName('a_Comm').AsFloat := Comm;
  usp_emp_modify.ParamByName('a_deptno').AsInteger := Deptno;
  usp_emp_modify.ParamByName('a_op_type').AsInteger := op_type;
  usp_emp_modify.Execute;
end;

In addition, it is necessary to add methods including data encryption and compression. These methods are implemented in the uServerContainer module and are given by the following:

function TServerContainer.AddFilter(ServerTransport: TDSServerTransport;
  Filter: String; Key: String = ''): boolean;
var
  filters: TTransportFilterCollection;
  i: integer;
begin
  Result := False;
  filters := ServerTransport.Filters;
  for i := 0 to filters.Count - 1 do
    if filters.FilterIdList[i] = Filter then
      exit;
  if Filter = 'PC1' then begin
    i := filters.AddFilter(TTransportCypherFilter.Create);
    filters.GetFilter(i).SetParameterValue('Key', Key);
  end
  else if Filter = 'RSA' then begin
    i := filters.AddFilter(TTransportCypherFilter.Create);
    filters.GetFilter(i).SetParameterValue('UseGlobalKey', 'true');
    filters.GetFilter(i).SetParameterValue('KeyLength', '1024');
    filters.GetFilter(i).SetParameterValue('KeyExponent', '3');
  end
  else if Filter = 'ZLibCompression' then begin
    i := filters.AddFilter(TTransportCompressionFilter.Create);
    filters.GetFilter(i).SetParameterValue('CompressMoreThan', '1024');
  end;
  Result := True;
end;

procedure TServerContainer.RemoveFilter(ServerTransport: TDSServerTransport; 
  Filter: String);
var
  i: integer;
begin
  for i := 0 to ServerTransport.Filters.Count - 1 do
    if ServerTransport.Filters.FilterIdList[i] = Filter then begin
      ServerTransport.Filters.RemoveFilter(i);
      exit;
    end;
end;

invoking of these methods occurs in the uServer module when checking appropriate CheckBoxes

procedure TfMainServer.FilterCheckBoxClick(Sender: TObject);
begin
  if not (Sender is TCheckBox) then
    exit;
  if TCheckBox(Sender).Checked then begin
    if not ServerContainer.AddFilter(ServerContainer.DSTCPServerTransport, TCheckBox(Sender).Caption, edPC1Key.Text) then
      MessageDlg(format('Cannot Add %s Filter For TCP/IP, Or Filter Already Exists.', [TCheckBox(Sender).Caption]), mtError, [mbOK], 0);
    if not ServerContainer.AddFilter(ServerContainer.DSHTTPService, TCheckBox(Sender).Caption, edPC1Key.Text) then
      MessageDlg(format('Cannot Add %s Filter For HTTP, Or Filter Already Exists.', [TCheckBox(Sender).Caption]), mtError, [mbOK], 0);
  end
  else begin
    ServerContainer.RemoveFilter(ServerContainer.DSTCPServerTransport, TCheckBox(Sender).Caption);
    ServerContainer.RemoveFilter(ServerContainer.DSHTTPService, TCheckBox(Sender).Caption);
  end;
end;

Assigning of the FilterCheckBoxClick method to the OnClick event of all CheckBoxes

procedure TfMainServer.FormCreate(Sender: TObject);
begin
  cbPC1.OnClick := FilterCheckBoxClick;
  cbRSA.OnClick := FilterCheckBoxClick;
  cbZlib.OnClick := FilterCheckBoxClick;
end;

Application back end development is finished at that. Now the application is to be compiled and built. If there is no errors on compilation and build, we can move to creation of the application front end.

Create VCL Form Application for the client application and add TDataModule, in which place the data access components:

• SQLConnection: TSQLConnection;
• DeptSqlServerMethod: TSqlServerMethod;
• EmpSqlServerMethod: TSqlServerMethod;
• DeptDataSetProvider: TDataSetProvider;
• EmpDataSetProvider: TDataSetProvider;
• DeptClientDataSet: TClientDataSet;
• EmpClientDataSet: TClientDataSet;
• DeptDataSource: TDataSource;
• EmpDataSource: TDataSource;

• TSQLConnection — for connection to the DataSnap server;
• TsqlServerMethod — for sending queries to the server;
• TDataSetProvider — for binding TSqlServerMethod with TClientDataSet;
• TDataSource — for relating the generated dataset with data display visual components (DBGrid);

These components must be bound with each other:

TDataSource–>TClientDataSet–>TDataSetProvider–>TSQLServerMethod–>TSQLConnection

The Driver property of the TSQLConnection component should be set to Datasnap, the other options (protocol, port, host, etc.) will be set dynamically depending on the settings applied by a user.

Place the components responsible for connection settings, data display and editing onto the main form as shown in the figure. Handlers for appropriate events will be implemented further.

For binding of server methods with the client, a module should be created for implementation of DataSnap client classes. For this, we need to run the server application, go to the ClientDM form (TDataModule), open the context menu of the TSQLConnection component and select Generate DataSnap client classes. There will be automatically created a module containing all the methods of the server class available to the client.

Now write the code for an event responsible for client connection/disconnection. In this method, there should be set the connection parameters for the TSQLConnection component, and the server is connected to (the server must run at the moment).

procedure TfMainClient.btConnectClick(Sender: TObject);
begin
  if not DM.SQLConnection.Connected then begin
    DM.SQLConnection.Params.Values['CommunicationProtocol'] := IfThen(rgProtocol.ItemIndex = 0, 'tcp/ip', 'http');
    DM.SQLConnection.Params.Values['HostName'] := IfThen(rgProtocol.ItemIndex = 0, edTCPIPHost.Text, edHTTPHost.Text );
    DM.SQLConnection.Params.Values['Port'] := IfThen(rgProtocol.ItemIndex = 0, edTCPIPPort.Text, edHTTPPort.Text);
    DM.SQLConnection.Params.Values['DSAuthenticationUser'] := edLogin.Text;
    DM.SQLConnection.Params.Values['DSAuthenticationPassword'] := edPaswd.Text;
    DM.SQLConnection.Connected := True;
    btConnect.Caption := 'Disconnect';
  end
  else begin
    DM.SQLConnection.Connected := False;
    btConnect.Caption := 'Connect';
  end;
end;

For both TSQLServerMethod methods (DeptSqlServerMethod and EmpSqlServerMethod) the ServerMethodName property must be set to specify the method, which the component will refer to. Set these properties to TServerMethods.GetDept and TServerMethods.GetEmp respectively.

Now the code for the open dataset event should be written, it is given by:

procedure TfMainClient.btOpenClick(Sender: TObject);
begin
  if not DM.DeptClientDataSet.Active then begin
    DM.DeptClientDataSet.Open;
    DM.EmpSqlServerMethod.ParamByName('DeptNo').AsInteger := DM.DeptClientDataSet.FieldByName('DEPTNO').AsInteger;
    DM.EmpClientDataSet.Open;
    btOpen.Caption := 'Close';
  end
  else  begin
    DM.EmpClientDataSet.Close;
    DM.DeptClientDataSet.Close;
    btOpen.Caption := 'Open';
  end;
end;

In order for records in the client dataset to be modified when moving through the Master DataSet, the OnDataChange event should be implemented for the DeptDataSource component. The client dataset will be reopened in this event and retrieve the data corresponding to the current record in the Master DataSet:

procedure TDM.DeptDataSourceDataChange(Sender: TObject; Field: TField);
begin
  EmpClientDataSet.Close;
  EmpSqlServerMethod.ParamByName('DeptNo').AsInteger := DM.DeptClientDataSet.FieldByName('DEPTNO').AsInteger;
  EmpClientDataSet.Open;
end;

After this, we can run the application, connect to the server, and open the dataset. If everything is done correctly, records from both tables will be displayed in the application.

Besides displaying data, it is also necessary to provide for possibility to modify them (addition, editing, and deletion), these actions will be performed with the stored procedure, the text of which is provided above.

To modify each of the datasets, create editing forms as shown in the figures.

And add our properties for data setting:

    //Dept
    property Deptno: integer read GetDeptno write SetDeptno;
    property DName: string read GetDName write SetDName;
    property Loc: string read GetLoc write SetLoc;

    //Emp
    property EmpNo: integer read GetEmpno write SetEmpno;
    property EName: string read GetEname write SetEname;
    property Job: string read GetJob write SetJob;
    property Mgr: integer read GetMgr write SetMgr;
    property Hiredate: TDateTime read GetHiredate write SetHiredate;
    property Sal: real read GetSal write SetSal;
    property Comm: real read GetComm write SetComm;

To work with these forms in the main application form, it is necessary to implement the methods of invoking these forms, data transfer/retrieving, and result processing. These forms will be invoked as modal, therefore, dependently on the return result (ModalResult), we will either invoke an appropriate procedure or ignore changes.

procedure TfMainClient.btUpdateDeptClick(Sender: TObject);
var
  fModifyDept: TfModifyDept;
  ServerMethodsClient: TServerMethodsClient;
begin
  fModifyDept := TfModifyDept.Create(Self);
  try
    fModifyDept.Deptno := DM.DeptClientDataSet.FieldByName('Deptno').AsInteger;
    fModifyDept.DName := DM.DeptClientDataSet.FieldByName('dname').AsString;
    fModifyDept.Loc := DM.DeptClientDataSet.FieldByName('loc').AsString;
    fModifyDept.ShowModal;
    if fModifyDept.ModalResult = mrOk then begin
      ServerMethodsClient := TServerMethodsClient.Create(DM.SQLConnection.DBXConnection);
      try
        ServerMethodsClient.DeptModify(fModifyDept.Deptno, fModifyDept.DName, fModifyDept.Loc, 2);
        DM.Reopen;
      finally
        ServerMethodsClient.Free;
      end;
    end;
  finally
    fModifyDept.Free;
  end;
end;

procedure TfMainClient.btUpdateEmpClick(Sender: TObject);
var
  fModifyEmp: TfModifyEmp;
  ServerMethodsClient: TServerMethodsClient;
begin
  fModifyEmp := TfModifyEmp.Create(Self);
  try
    fModifyEmp.EmpNo := DM.EmpClientDataSet.FieldByName('Empno').AsInteger;
    fModifyEmp.EName := DM.EmpClientDataSet.FieldByName('Ename').AsString;
    fModifyEmp.Job := DM.EmpClientDataSet.FieldByName('job').AsString;
    fModifyEmp.Mgr := DM.EmpClientDataSet.FieldByName('Mgr').AsInteger;
    fModifyEmp.Hiredate := DM.EmpClientDataSet.FieldByName('Hiredate').AsDateTime;
    fModifyEmp.Sal := DM.EmpClientDataSet.FieldByName('Sal').AsFloat;
    fModifyEmp.Comm := DM.EmpClientDataSet.FieldByName('Comm').AsFloat;
    fModifyEmp.ShowModal;
    if fModifyEmp.ModalResult = mrOk then begin
      ServerMethodsClient := TServerMethodsClient.Create(DM.SQLConnection.DBXConnection);
      try
        ServerMethodsClient.EmpModify(fModifyEmp.EmpNo, fModifyEmp.EName, fModifyEmp.Job, fModifyEmp.Mgr, fModifyEmp.Hiredate, fModifyEmp.Sal, fModifyEmp.Comm, DM.DeptClientDataSet.FieldByName('Deptno').AsInteger, 3);
        DM.Reopen;
      finally
        ServerMethodsClient.Free;
      end;
    end;
  finally
    fModifyEmp.Free;
  end;
end;

procedure TfMainClient.tbInsertEmpClick(Sender: TObject);
var
  fModifyEmp: TfModifyEmp;
  ServerMethodsClient: TServerMethodsClient;
begin
  fModifyEmp := TfModifyEmp.Create(Self);
  try
    fModifyEmp.EmpNo := -1;
    fModifyEmp.ShowModal;
    if fModifyEmp.ModalResult = mrOk then begin
      ServerMethodsClient := TServerMethodsClient.Create(DM.SQLConnection.DBXConnection);
      try
        ServerMethodsClient.EmpModify(fModifyEmp.EmpNo, fModifyEmp.EName, fModifyEmp.Job, fModifyEmp.Mgr, fModifyEmp.Hiredate, fModifyEmp.Sal, fModifyEmp.Comm, DM.DeptClientDataSet.FieldByName('Deptno').AsInteger, 1);
        DM.Reopen;
      finally
        ServerMethodsClient.Free;
      end;
    end;
  finally
    fModifyEmp.Free;
  end;
end;

As the final result, we must get the following:

The running server:

The running client:

The data editor:

This sample demonstrates the basics of three–tier application development using UniDAC and the DataSnap technology. Naturally, real business applications are much more complex and massive, however, when developing them, the same basic methods and techniques as described here are used.

Delphi XE2 allows to develop iOS applications as well as applications for Win 32-bit, 64-bit, and Mac OS. In this article, we will try to explain how to develop iPhone apps in Delphi XE2. However, everything below can be used in application development for iPad and iPod, since they support iOS mobile platform as well.

iOS application development is a little bit different from development of common desktop applications, and it consists of two main stages:

1. Application development in Delphi XE2
2. Application compilation and building in Xcode on Mac OS

This is due to the fact that Delphi does not have a native compiler for iOS platform, and the FPC compiler should be used for compilation.

Also, note that an iOS application can only work with several databases (where no client library is required, or if there is a native client for iOS). This restriction is due to the fact that, according to the Apple policy, iOS applications must not be written using external dynamic libraries, if only they are not built into the iOS SDK or statically linked to the application itself (you can find more detailed information at the Apple site).

So, using Devart Data Access Components, it is possible to work with the following databases in iOS:

1. SQLite (the library is supplied with iOS)
2. MySQL in the direct mode (the client is not required)
3. PostgreSQL in the direct mode (the client is not required)

We have used UniDAC for developing our sample application, and all three databases are used in it.

Also, you can develop DB applications for iOS using the following separate Devart products:

• PgDAC for work with PostgreSQL
• MyDAC for work with MySQL
• LiteDAC for work with SQLite

The process of application development using these products is similar to the one described below, except that you have to use the product-specific connection and query components instead of TUniConnection and TUniQuery listed in our code samples.

Application development in Delphi

At the first stage, a new iOS application can be created using “File -> New -> Other -> FireMonkey HD iOS Application” menu. A new iOS application can be created Then the application is designed similarly to any other application, i.e. you can place visual components on the form, implement application logic, etc.

Our demo form can be seen below:

However, there are several restrictions in Delphi XE2 if your application needs to work with a database:

1. Data-access components can not be placed on the form in design-time and have to be created, configured and released manually in runtime.
2. There are some peculiarities when generating project-relative paths (for example, when setting a database name for the connection) in iOS.
3. There is no possibility to set a link between data-access components and visual controls (for example, like connecting TStringGrid and a dataset with the LiveBindings mechanism in a trivial FireMonkey application). The process of displaying and editing data needs to be implemented manually.

Creating data-access components

So, when developing your iOS application, you first see that all the data-access components on the Tool Palette are not available, as it is shown below:

In order to use UniDAC in our sample application, we have declared two variables in the private section of the form declaration, and then created the TUniConnection and TUniQuery instances in the OnClick event handler of the “Connect” button:

TiForm = class(TForm)
private
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
end;

...

procedure TiForm.btConnectClick(Sender: TObject);
begin
  if not Assigned(UniConnection) then
    UniConnection := TUniConnection.Create(Self);
  UniConnection.ProviderName := 'SQLite';
  UniConnection.Database := GetDatabasePath + edDataBase.Text;
  UniConnection.Connect;
  
  if not Assigned(UniQuery) then
    UniQuery := TUniQuery.Create(Self);
  UniQuery.Connection := UniConnection;
  UniQuery.SQL.Text := 'SELECT ID, Common_Name, Graphic, SpeciesName, Category, Notes  FROM FISH';
  UniQuery.Open;
end;

Setting the database path

As you can see, in the code sample above we have used some GetDatabasePath function when setting the UniConnection.Database property.

The point is that, under iOS, there is no ability to use usual ParamStr(0) function for obtaining the application path (for example, in order to generate a project-relative path to a database). In our sample application, we implemented the GetDatabasePath function as follows:

function GetDatabasePath: string;
{$IFDEF FPC}
var
  Paths : NSArray;
  FileName : NSString;
begin
  Paths := NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, True);
  FileName := Paths.objectAtIndex(0);
  Result := String(FileName.UTF8String) + '/';
{$ELSE}
begin
  Result := ExtractFilePath(ParamStr(0));
{$ENDIF}
end;

When the application runs in Windows, the function will return the application path.

But, when the application runs in iOS, the function will return a path to the special Documents sub-folder located in the application root folder on the iOS device. The Documents sub-folder is automatically created for each iOS application when it is installed. All application data, including the database, has to be placed in this sub-folder.

Unfortunatelly, there is no possibility to include the database file into the project in order to place it into the Documents subfolder automatically when the application is installed. Therefore you will have to copy the database file to the iOS device manually after installing the application.

Retrieving and displaying data

Then, the task is to display the data retrieved by the query in visual controls. As it is described above, there is no possibility in Delphi XE2 to do this in a way like using a TDataSource component in VCL, or LiveBindings in FMX. Developer’s own methods are required to be implemented for each specific case to display and edit data.

In our sample application, we did not use any data-aware components like TDBGrid or TStringGrid, therefore we simply set visual controls properties with the values of the corresponding query fields when the trackbar at the botton of the form changes its position:

procedure TiForm.TrackBar1Change(Sender: TObject);
begin
  if Assigned(UniQuery) and UniQuery.Active then begin
    UniQuery.Locate('ID', VarArrayOf([Round(TrackBar1.Value + 1)]), [loPartialKey]);
    lbCommon_Name.Text := UniQuery.FieldByName('Common_Name').AsString;
    lbSpeciesName.Text := UniQuery.FieldByName('SpeciesName').AsString;
    lbCategory.Text := UniQuery.FieldByName('Category').AsString;
    meDescription.Text := UniQuery.FieldByName('Notes').AsString;
  end;
end;

Data can be displayed in a grid in a similar way. You can loop by the records in a dataset, and enter the values of each field into a corresponding cell of the grid, as follows:

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
  i: Integer;
begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ProviderName := 'SQLite';
    UniConnection.Database := GetDatabasePath + 'fish.db3';
    UniConnection.Connect;
  
    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.SQL.Text := 'Select Category, Common_Name, Notes from FISH';
      UniQuery.Open;
  
      while not UniQuery.Eof do begin
        for i := 0 to UniQuery.Fields.Count - 1 do
          StringGrid1.Cells[i, UniQuery.RecNo - 1] := UniQuery.Fields[i].AsString;
        UniQuery.Next;
      end;
	  
    finally
      UniQuery.Free;
    end;
	
  finally
    UniConnection.Free;
  end;
end;

Data editing also needs to be performed manually, i.e. you should call the corresponding Insert/Update/Delete methods, fill in the fields of a dataset with the right values, and then call the Post method, etc.

Compilation and deployment

The second stage is compilation and deployment of applications directly to the iOS platform.

The stage consists of three steps:

1. Creating an Xcode project for the Delphi project.
2. Compiling the Xcode project on MacOS.
3. Deploying the application to an iOS device.

Creating an Xcode project

In order to compile a Delphi project on MacOS, a special Xcode project has to be created. The project consists of several additional files that are created in the “xcode” subfolder in the root folder of the project.

To create an Xcode project for the existing Delphi project, the dpr2Xcode.exe command-line utility is used. The utility is supplied with Delphi XE2 and located in the Bin folder.

To make the usage of the utility more handy, you can customize the IDE Tools menu.

Open the “Tools-> Configure Tools” menu, press the “Add…” button and fill the properties as follows:

• Title: Export to Xcode
• Program: dpr2Xcode.exe
• Parameters: $PROJECT

Now you can open your iOS project in the IDE and use the “Tools -> Export to Xcode” menu to create an Xcode project. As it described before, after doing this, the “xcode” subfolder will be created in the root folder of the project.

Then, it is nessessary to copy all UniDAC source files into the iOS project root folder, because UniDAC files will also be required by the FPC compiler to build the project.

Compiling the Xcode project on MacOS

Now, the iOS project is ready to be compiled with Xcode on MacOS.

The MacOS system used for compiling must conform to the following requirements:

• Operating system: OS X 10.6 Snow Leopard or OS X 10.7 Lion
• Free Pascal version 2.6.0 installed on MacOS
• Xcode and the iOS SDK installed on MacOS
  (We have verified Xcode versions 3.2.5 and 4.2, and iOS SDK versions 4.2.x, 4.3.x and 5.0)
• the FireMonkey-iOS-XE2.dmg package installed on MacOS
  (The package is required by FPC to compile Delphi projects. It is supplied with Delphi XE2 and located in the FireMonkey-iOS folder)
• an iOS mobile device (iPhone, iPod or iPad) connected to the Mac via USB port
  (If you don’t have one, you can test the application in the iOS device simulator included in Xcode)

So, if you have an appropriate MacOS system, you should copy the project folder to MacOS, open the project in Xcode, then select an appropriate output device in Xcode (a real iOS device or a simulator) and build the project.

When a simulator is chosen as an output device, then after the successfull building, the application will be opened in the simulator directly on the MacOS system.

When a real connected device is chosen as an output device, the application will be automatically transferred and installed on the device, and its label will appear on the device desktop.

Also, since all the source files are transferred to Mac OS, there is a possibility to debug the application directly in Xcode.

Deploying the application to an iOS device

The application deployment process is not the objective of this article, it is widely described in Internet. As mentioned above, you do not have to deploy any additional libraries with an application written using UniDAC, PgDAC, MyDAC, or LiteDAC.

Also note, that in order to develop applications on a real iPhone, iPod, or iPad, you have to sign up for the Apple’s paid iOS Developer Program and configure the device for development purposes. You can find out more about the iOS Developer Program at the iOS Dev Center. Without this license, the application can be only tested in the iOS simulator included in Xcode.

Data Type Mapping is a flexible and easily customizable gear, which allows mapping between DB types and Delphi field types.

In this article there are several examples, which can be used when working with all supported DBs. In order to clearly display the universality of the Data Type Mapping gear, a separate DB will be used for each example.

Data Type Mapping Rules

In versions where Data Type Mapping was not supported, the DAC products automatically set correspondence between the DB data types and Delphi field types. In versions with Data Type Mapping support the correspondence between the DB data types and Delphi field types can be set manually.

Here is the example with the numeric type in the following table of a PostgreSQL database:

CREATE TABLE numeric_types
(
 id integer NOT NULL,
 value1 numeric(4,0),
 value2 numeric(10,0),
 value3 numeric(15,0),
 value4 numeric(5,2),
 value5 numeric(10,4),
 value6 numeric(15,6),
 CONSTRAINT pk_numeric_types PRIMARY KEY (id)
)

And Data Type Mapping should be used so that:

• the numeric fields with Scale=0 in Delphi would be mapped to one of the field types: TSmallintField, TIntegerField or TlargeintField, depending on Precision
• to save precision, the numeric fields with Precision>=10 and Scalе<= 4 would be mapped to TBCDField
• and the numeric fields with Scalе>= 5 would be mapped to TFMTBCDField.

The above in the form of a table:

To specify that numeric fields with Precision <= 4 and Scale = 0 must be mapped to ftSmallint, such a rule should be set:

var
  DBType: Word;
  MinPrecision: Integer;
  MaxPrecision: Integer;
  MinScale: Integer;
  MaxScale: Integer;
  FieldType: TfieldType;
begin
  DBType       := pgNumeric;
  MinPrecision := 0;
  MaxPrecision := 4;
  MinScale     := 0;
  MaxScale     := 0;
  FieldType    := ftSmallint;
  PgConnection.DataTypeMap.AddDBTypeRule(DBType, MinPrecision, MaxPrecision, MinScale, MaxScale, FieldType);
end;

This is an example of the detailed rule setting, and it is made for maximum visualization.Usually, rules are set much shorter, e.g. as follows:

// clear existing rules
PgConnection.DataTypeMap.Clear;
// rule for numeric(4,0)
PgConnection.DataTypeMap.AddDBTypeRule(pgNumeric, 0,      4, 0,     0, ftSmallint);
// rule for numeric(10,0)
PgConnection.DataTypeMap.AddDBTypeRule(pgNumeric, 5,     10, 0,     0, ftInteger);
// rule for numeric(15,0)
PgConnection.DataTypeMap.AddDBTypeRule(pgNumeric, 11, rlAny, 0,     0, ftLargeint);
// rule for numeric(5,2)
PgConnection.DataTypeMap.AddDBTypeRule(pgNumeric, 0,      9, 1, rlAny, ftFloat);
// rule for numeric(10,4)
PgConnection.DataTypeMap.AddDBTypeRule(pgNumeric, 10, rlAny, 1,     4, ftBCD);
// rule for numeric(15,6)
PgConnection.DataTypeMap.AddDBTypeRule(pgNumeric, 10, rlAny, 5, rlAny, ftFMTBcd);

Defining Data Type Mapping Rules in the Design-Time

In addition to the possibility of setting Data Type Mapping in Run-Time, there is also a possibility to set Data Type Mapping in Design-Time with the help of convenient user interface:

Rules order

When setting rules, there can occur a situation when two or more rules that contradict to each other are set for one type in the database. In this case, only one rule will be applied — the one, which was set first.

For example, there is a table in an Oracle database:

CREATE TABLE NUMBER_TYPES
(
 ID NUMBER NOT NULL,
 VALUE1 NUMBER(5,2),
 VALUE2 NUMBER(10,4),
 VALUE3 NUMBER(15,6),
 CONSTRAINT PK_NUMBER_TYPES PRIMARY KEY (id)
)

TBCDField should be used for NUMBER(10,4), and TFMTBCDField – for NUMBER(15,6) instead of default fields:

If rules are set in the following way:

OraSession.DataTypeMap.Clear;
OraSession.DataTypeMap.AddDBTypeRule(oraNumber, 0,     9, rlAny, rlAny, ftFloat);
OraSession.DataTypeMap.AddDBTypeRule(oraNumber, 0, rlAny,     0,     4, ftBCD);
OraSession.DataTypeMap.AddDBTypeRule(oraNumber, 0, rlAny,     0, rlAny, ftFMTBCD);

it will lead to the following result:

But if rules are set in the following way:

OraSession.DataTypeMap.Clear;
OraSession.DataTypeMap.AddDBTypeRule(oraNumber, 0, rlAny,     0, rlAny, ftFMTBCD);
OraSession.DataTypeMap.AddDBTypeRule(oraNumber, 0, rlAny,     0,     4, ftBCD);
OraSession.DataTypeMap.AddDBTypeRule(oraNumber, 0,     9, rlAny, rlAny, ftFloat);

it will lead to the following result:

This happens because the rule

OraSession.DataTypeMap.AddDBTypeRule(oraNumber, 0, rlAny, 0, rlAny, ftFMTBCD);

will be applied for the NUMBER fields, whose Precision is from 0 to infinity, and Scale is from 0 to infinity too. This condition is met by all NUMBER fields with any Precision and Scale.

When using Data Type Mapping, first matching rule is searched for each type, and it is used for mapping. In the second example, the first set rule appears to be the first matching rule for all three types, and therefore the ftFMTBCD type will be used for all fields in Delphi.

If to go back to the first example, the first matching rule for the NUMBER(5,2) type is the first rule, for NUMBER(10,4) – the second rule, and for NUMBER(15,6) - the third rule. So in the first example, the expected result was obtained.

So it should be remembered that if rules for Data Type Mapping are set so that two or more rules that contradict to each other are set for one type in the database, the rules will be applied in the specifed order.

Defining rules for Connection and Dataset

Data Type Mapping allows setting rules for the whole connection as well as for each DataSet in the application.

For example, such table is created in SQL Server:

CREATE TABLE person
( 
 id                INT              NOT NULL  , 
 firstname         VARCHAR(20)          NULL  , 
 lastname          VARCHAR(30)          NULL  ,         
 gender_code       VARCHAR(1)           NULL  , 
 birth_dttm        DATETIME             NULL  ,  
 CONSTRAINT pk_person PRIMARY KEY CLUSTERED (id ASC) ON [PRIMARY] 
)
GO

It is exactly known that the birth_dttm field contains birth day, and this field should be ftDate in Delphi, and not ftDateTime. If such rule is set:

MSConnection.DataTypeMap.Clear;
MSConnection.DataTypeMap.AddDBTypeRule(msDateTime, ftDate);

All DATETIME fields in Delphi will have the ftDate type, that is incorrect. The ftDate type was expected to be used for the DATETIME type only when working with the person table. In this case, Data Type Mapping should be set not for the whole connection, but for a particular DataSet:

MSQuery.DataTypeMap.Clear;
MSQuery.DataTypeMap.AddDBTypeRule(msDateTime, ftDate);

Or the opposite case. For example, DATETIME is used in the application only for date storage, and only one table stores both date and time. In this case, the following rules setting will be correct:

MSConnection.DataTypeMap.Clear;
MSConnection.DataTypeMap.AddDBTypeRule(msDateTime, ftDate);
MSQuery.DataTypeMap.Clear;
MSQuery.DataTypeMap.AddDBTypeRule(msDateTime, ftDateTime);

In this case, in all DataSets for the DATETIME type fields with the ftDate type will be created, and for MSQuery – with the ftDateTime type.

The point is that the priority of the rules set for the DataSet is higher than the priority of the rules set for the whole connection. This allows both flexible and convenient setting of Data Type Mapping for the whole application. There is no need to set the same rules for each DataSet, all the general rules can be set once for the whole connection. And if a DataSet with an individual Data Type Mapping is necessary, individual rules can be set for it.

Rules for a particular field

Sometimes there is a need to set a rule not for the whole connection, and not for the whole dataset, but only for a particular field.

For example, there is such table in a MySQL database:

CREATE TABLE item 
(
 id INT NOT NULL AUTO_INCREMENT,
 name CHAR(50) NOT NULL,
 guid CHAR(38), 
 PRIMARY KEY (id)
) ENGINE=MyISAM;

The guid field contains a unique identifier. For convenient work, this identifier is expected to be mapped to the TGuidField type in Delphi. But there is one problem, if to set the rule like this:

MyQuery.DataTypeMap.Clear;
MyQuery.DataTypeMap.AddDBTypeRule(myChar, ftGuid);

then both name
and guid fields will have the ftGuid type in Delphi, that does not correspond to what was planned. In this case, the only way is to use Data Type Mapping for a particular field:

MyQuery.DataTypeMap.AddFieldNameRule('guid', ftGuid);

In addition, it is important to remember that setting rules for particular fields has the highest priority. If to set some rule for a particular field, all other rules in the Connection or DataSet will be ignored for this field.

Ignoring conversion errors

Data Type Mapping allows mapping various types, and sometimes there can occur the problem with that the data stored in a DB cannot be converted to the correct data of the Delphi field type specified in rules of Data Type Mapping or vice-versa. In this case, an error will occur, which will inform that the data cannot be mapped to the specified type.

For example:

But when setting rules for Data Type Mapping, there is a possibility to ignore data conversion errors:

IBCConnection.DataTypeMap.AddDBTypeRule(ibcVarchar, ftInteger, True);

In this case, the correct conversion is impossible. But because of ignoring data conversion errors, Data Type Mapping tries to return values that can be set to the Delphi fields or DB fields depending on the direction of conversion.

Therefore ignoring of conversion errors should be used only if the conversion results are expected.

UniDAC and Data Type Mapping

When using UniDAC, there often occurs a hard-to-solve situation, when two similar types from the DB have differnt types in Delphi. For greater clarity, there are examples below.

For example, there is a project, which works with two DBs: Oracle and SQL Server. There is such table created in each DB:

Oracle:

CREATE TABLE ITEM_INFO
(
 ID NUMBER NOT NULL,
 CODE VARCHAR2(10)  NOT NULL,
 DESCRIPTION NVARCHAR2(250),
 CONSTRAINT PK_ITEM_INFO PRIMARY KEY (id)
)

SQL Server:

CREATE TABLE item_info
( 
 id                INT              NOT NULL  , 
 code              VARCHAR(10)      NOT NULL  , 
 description       NVARCHAR(250)        NULL  ,         
 CONSTRAINT pk_item_info PRIMARY KEY CLUSTERED (id ASC) 
 ON [PRIMARY] 
)
GO

The problem is due to that, when working with Oracle with the enabled UseUnicode option, both CODE and DESCRIPTION fields will have the ftWideString type, and if the UseUnicode option is disabled, both fields will have the ftString type. For SQL Server, the CODE field will always be ftString, and the DESCRIPTION field will always be ftWideString. This problem arises especially sharply when attempting to create persistent fields, because in this case, when working with one of the providers, an error will always occur. Formerly, the only way to avoid the error was to refuse using of persistent fields in such situations.

For the time being, this problem can be solved rather easily. Data Type Mapping can be set for the Oracle provider:

UniConnection.DataTypeMap.Clear;
UniConnection.DataTypeMap.AddDBTypeRule(oraVarchar2, ftString);
UniConnection.DataTypeMap.AddDBTypeRule(oraNVarchar2, ftWideString);

Or Data Type Mapping can be set for SQL Server:

// for useUnicode = True in the Oracle data provider
UniConnection.DataTypeMap.Clear;
UniConnection.DataTypeMap.AddDBTypeRule(msVarchar, ftWideString);

or:

// for useUnicode = False in the Oracle data provider
UniConnection.DataTypeMap.Clear;
UniConnection.DataTypeMap.AddDBTypeRule(msNVarchar, ftString);

Best wishes from Devart!

This post aimed to describe the main advantages, provided to application developers by the new Data Type Mapping engine. A great amount of efforts and time was spent to make this engine flexible and convenient. It is very much hoped to be estimated by developers and make their application development easy and quick.

RAD Studio XE2 allows creating applications for Mac OS X. But the problem is that RAD Studio XE2 (both Delphi XE2 and C++Builder XE2) can be installed and run only under MS Windows, therefore applications for Mac OS X can be developed under MS Windows. That is why many people may face the problem of deploying applications on Mac OS X. Taking this into account, we decided to write this article that will help our users deal with deploying problem and deploy their applications to Mac OS X successfully. In this article, we will tell about two ways of applications deploying on Mac OS X and the peculiarities that must be taken into account during deploying applications that use Devart Data Access Components and Devart dbExpress drivers.

Using PAServer

The easiest way to deploy applications to Mac OS X is to deploy them using PAServer.

When PAServer is used to debug applications on a remote computer with Mac OS X, it copies executable Mac OS X application packages to the “PAServer_Installation_Directory” directory (where “PAServer_Installation_Directory” is the “/Users/$USER/Applications/Embarcadero/PAServer” directory by default). In the scratch-dir directory PAServer creates directories with names, that consist of the computer name and the remote profile name. For example, if the computer name (where RAD Studio XE2 is run) is “MyPC”, and the remote profile name for the Mac OS X platform is “Mac OS X”, PAServer will create the “PAServer_Installation_Directory/scratch-dir/MyPC-Mac OS X” directory. In this directory, PAServer will create an application package with the name of the project plus the “.app” extension. For example, if your application name is “MyProject”, PAServer will create the “MyProject.app” application package.

You can use PAServer for both debugging your application and for deploying it as well. For this, you should choose Release Build Configuration and run it without debugging (the Run Without Debugging command from the Run menu). Also, before the final deployment, you should clean the application package folder (for example, “MyProject.app”) from all old files that could remain after previous runnings and deployments.

Note: You can choose files to deploy in an application package by selecting “Deployment” from the Project menu in RAD Studio XE2.

Manual

An application package is internally represented by a folder with the following structure:

For Delphi:

For C++Builder:

Note: To view the content of the application package in Mac OS X, you can right click on package and select “Show Package Contents” from the shortcut menu.

All needed files to create the Mac OS X application package are supplied by RAD Studio XE2. To create the Mac OS X application package manually, the following steps must be performed:

• create a FireMonkey Delphi or C++Builder application;
• add the OS X platform to Target Platforms and make it active;
• build the application using Release Build Configuration;
• create a folder with the name that consists of the project name and the “.app” extension, for example, “MyProject.app”;
• create the Contents folder in the MyProject.app folder;
• copy the “MyProject_Directory\OSX32\Release\MyProject.info.plist” file to the Contents folder and rename it to info.plist;
• create the MacOS folder in the Contents folder;
• for Delphi applications: copy
  “RAD_Studio_XE2_Install_Directory\Redist\osx32\libcgunwind.1.0.dylib”
  to the MacOS folder;
  for C++Builder applications: copy
  “RAD_Studio_XE2_Install_Directory\Redist\osx32\libcgunwind.1.0.dylib”,
  “RAD_Studio_XE2_Install_Directory\Redist\osx32\libcgcrtl.dylib” and
  “RAD_Studio_XE2_Install_Directory\Redist\osx32\libcgstl.dylib”
  to the MacOS folder;
• copy the “MyProject_Directory\OSX32\Release\MyProject” file to the MacOS folder;
• create the Resources folder in the Contents folder;
• copy the “MyProject_Directory\OSX32\Release\MyProject.icns” file to the Resources folder.

After performing these steps you can run the MyProject.app application package on Mac OS X.

Specificity of deploying applications that use Devart Data Access Components

Applications using some of the Devart Data Access Components products require database client libraries to function on Mac OS X. Client libraries can be located in public libraries directory (for example, /usr/lib ) or in the MacOS folder of the application package. The table below shows the required client libraries for each particular Devart Data Access Components:

DAC	Mode
	Direct	Client
ODAC	Not required	Oracle client
MyDAC	Not required	libmysql.dylib
IBDAC		for InterBase: libibtogo.dylib for Firebird: libfbclient.dylib
PgDAC	Not required
SQLite (by UniDAC)		libsqlite3.dylib
UniDAC	Correspondent libraries for used data provider

Specificity of deploying applications that use Devart dbExpress drivers

Applications using some of the Devart dbExpress drivers products require database client libraries plus the libmidas.dylib library and the correspondent dbExpress driver library to function on Mac OS X. All required libraries can be located in the public libraries directory (for example, /usr/lib ) or in the MacOS folder of the application package. The table below shows all required libraries for each particular dbExpress driver:

dbExpress driver	Mode
	Direct	Client
dbExpress driver for Oracle	libmidas.dylib libdbexpoda40.dylib	libmidas.dylib libdbexpoda40.dylib Oracle client
dbExpress driver for MySQL	libmidas.dylib libdbexpmda40.dylib	libmidas.dylib libdbexpmda40.dylib libmysql.dylib
dbExpress driver for InterBase & Firebird		libmidas.dylib libdbexpida40.dylib for InterBase: libibtogo.dylib for Firebird: libfbclient.dylib
dbExpress driver for PostgreSQL	libmidas.dylib libdbexppgsql40.dylib
dbExpress driver for SQLite		libmidas.dylib libdbexpsqlite40.dylib libsqlite3.dylib

Many users have encountered the problem when running FireMonkey HD Applications on Mac OS X without 3D hardware HAL. When this application is run, it either freezes or produces the following error:

Runtime error 231 at 000169AD

We have researched this problem and found a solution from Embarcadero. If a Mac OS X computer has no 3D hardware HAL, you need to set the global variable FMX.Types.GlobalUseHWEffects to False. Example:

begin
  FMX.Types.GlobalUseHWEffects := False;
 
  Application.Initialize;
  Application.CreateForm(TForm1, Form1);
  Application.Run; 
end;

However, this solution is not a panacea. Sometimes HD Applications continue to freeze or raise the error even with FMX.Types.GlobalUseHWEffects set to False. In this case, you need to modify the FMX.Filter.pas unit. Do the following:

1. Copy the FMX.Filter.pas and FMX.Defines.inc files from the $(BDS)\source\fmx\ folder to your project folder.
2. In the files copied to your project folder replace the code of the FilterByName and FilterClassByName functions with the following code:

   function FilterByName(const AName: string): TFilter; 
   var
     i: Integer;
   begin
     Result := nil;
     if not GlobalUseHWEffects or (Filters = nil) then // <-- change this line
       Exit;
     for i := 0 to Filters.Count - 1 do
       if CompareText(TFilterClass(Filters.Objects[i]).FilterAttr.Name, AName) = 0
     then
     begin
       Result := TFilterClass(Filters.Objects[i]).Create;
       Exit;
     end;
   end;
   
   function FilterClassByName(const AName: string): TFilterClass; 
   var
     i: Integer;
   begin
     Result := nil;
     if not GlobalUseHWEffects or (Filters = nil) then // <-- change this line
       Exit;
     for i := 0 to Filters.Count - 1 do
       if CompareText(TFilterClass(Filters.Objects[i]).FilterAttr.Name, AName) = 0
       then
       begin
         Result := TFilterClass(Filters.Objects[i]);
         Exit;
       end;
   end;

3. Add the FMX.Filter unit to the USES section of your project:

   program Project1;
    
   uses
     FMX.Forms,
     FMX.Types,
     FMX.Filter, // <-- add unit
     Unit1 in 'Unit1.pas' {Form1};

After such modifications your applications will run successfully on Mac OS X without 3D hardware HAL; however, 3D effects will not be available for it.

Updated:
This trick is not required for Rad Studio XE2 with Update 3 and higher.

Delphi XE2 Overview

Delphi XE2 is the major breakthrough in the line of all Delphi versions of this product. It allows deploying your applications both on Windows and Mac OS platforms. Additionally, it is now possible to create 64-bit Windows applications to fully benefit from the power of new hardware. Moreover, you can create visually spectacular applications with the help of the FireMonkey GPU application platform.

Its main features are the following:

• Windows 64-bit platform support;
• Mac OS support;
• FireMonkey application development platform;
• Live data bindings with visual components;
• VCL styles for Windows applications.

For more information about Delphi XE2, please refer to Delphi XE2 Overview article on Embarcadero website.

Changes in 64-bit Application Development

64-bit platform support implies several important changes that each developer must keep in mind prior to the development of a new application or the modernization of an old one.

General

Delphi XE2 IDE is a 32-bit application. It means that it cannot load 64-bit packages at design-time. So, all design-time packages in Delphi XE2 IDE are 32-bit.

Therefore, if you develop your own components, you should remember that for the purpose of developing components with the 64-bit platform support, you have to compile run-time packages both for the 32- and 64-bit platforms, while design-time packages need to be compiled only for the 32-bit platform. This might be a source of difficulties if your package is both a run-time and a design-time package, as it is more than likely that this package won’t be compiled for the 64-bit platform. In this case, you will have to separate your package into two packages, one of which will be used as run-time only, and the other as design-time only.

For the same reason, if your design-time packages require that certain DLLs be loaded, you should remember that design-time packages can be only 32-bit and that is why they can load only 32-bit versions of these DLLs, while at run-time 64-bit versions of the DLLs will be loaded. Correspondingly, if there are only 64-bit versions of the DLL on your computer, you won’t be able to use all functions at design-time and, vice versa, if you have only 32-bit versions of the DLLs, your applications that are compiled for the 64-bit platform won’t be able to work at run-time.

Extended type

For this type in a 64-bit applications compiler generates SSE2 instructions instead of FPU, and that greatly improves performance in applications that use this type a lot (where data accuracy is needed). For this purpose, the size and precision of the Extended type is reduced:

The following two additional types are introduced to ensure compatibility in the process of developing 32- and 64-bit applications:

Extended80 – whose size in 32-bit application is 10 bytes; however, this type provides the same precision as its 8-byte equivalent in 64-bit applications.

Extended80Rec – can be used to perform low-level operations on an extended precision floating-point value. For example, the sign, the exponent, and the mantissa can be changed separately. It enables you to perform memory-related operations with 10-bit floating-point variables, but not extended-precision arithmetic operations.

Pointer and Integers

The major difference between 32- and 64-bit platforms is the volume of the used memory and, correspondingly, the size of the pointer that is used to address large memory volumes.

At the same time, the size of the Integer type remains the same for both platforms:

That is why, the following code works incorrectly on the 64-bit platform:

Ptr := Pointer(Integer(Ptr) + Offset);

While this code works correctly on the 64-bit platform and incorrectly on the 32-bit platform:

Ptr := Pointer(Int64(Ptr) + Offset);

For this purpose, the following platform-dependent integer type is introduced:

This type helps ensure that pointers work correctly both for the 32- and 64-bit platforms:

Ptr := Pointer(NativeInt(Ptr) + Offset);

However, you need to be extra-careful when developing applications for several versions of Delphi, in which case you should remember that in the previous versions of Delphi the NativeInt type had different sizes:

Out parameters

Some WinAPI functions have OUT parameters of the SIZE_T type, which is equivalent to NativeInt in Delphi XE2. The problem is that if you are developing only a 32-bit application, you won’t be able to pass Integer to OUT, while in a 64-bit application, you will not be able to pass Int64; in both cases you have to pass NativeInt.

For example:

procedure MyProc(out Value: NativeInt);
begin
  Value := 12345;
end; 

var
  Value1: NativeInt;
{$IFDEF WIN32}
  Value2: Integer;
{$ENDIF}
{$IFDEF WIN64}
  Value2: Int64;
{$ENDIF}
begin
 MyProc(Value1); // will be compiled;

 MyProc(Value2); // will not be compiled !!!
end;

If you pass pointers to SendMessage/PostMessage/TControl.Perform, the wParam and lParam parameters should be type-casted to the WPARAM/LPARAM type and not to Integer/Longint.

Correct:

SendMessage(hWnd, WM_SETTEXT, 0, LPARAM(@MyCharArray));

Wrong:

SendMessage(hWnd, WM_SETTEXT, 0, Integer(@MyCharArray));

Replace SetWindowLong/GetWindowLog with SetWindowLongPtr/GetWindowLongPtr for GWLP_HINSTANCE, GWLP_ID, GWLP_USERDATA, GWLP_HWNDPARENT and GWLP_WNDPROC as they return pointers and handles. Pointers that are passed to SetWindowLongPtr should be type-casted to LONG_PTR and not to Integer/Longint.

Correct:

SetWindowLongPtr(hWnd, GWLP_WNDPROC, LONG_PTR(@MyWindowProc));

Wrong:

SetWindowLong(hWnd, GWL_WNDPROC, Longint(@MyWindowProc));

Pointers that are assigned to the TMessage.Result field should use a type-cast to LRESULT instead of Integer/Longint.

Correct:

Message.Result := LRESULT(Self);

Wrong:

Message.Result := Integer(Self);

Assembler

In order to make your application (that uses assembly code) work, you will have to make several changes to it:

• rewrite your code that mixes Pascal code and assembly code. Mixing them is not supported in 64-bit applications;
• rewrite assembly code that doesn’t consider architecture and processor specifics.

You can use conditional defines to make your application work with different architectures.

You can learn more about Assembly code here. You can also look at the following article that will help you to make your application support the 64-bit platform.

Exception handling

The biggest difference in exception handling between Delphi 32 and 64-bit is that in Delphi XE2 64-bit you will gain more performance because of different internal exception mechanism. For 32-bit applications, the Delphi compiler (dcc32.exe) generates additional code that is executed any way and that causes performance loss. The 64-bit compiler (dcc64.exe) doesn’t generate such code, it generates metadata and stores it in the PDATA section of an executable file instead.

But in Delphi XE2 64-bit it’s impossible to have more than 16 levels of nested exceptions. Having more than 16 levels of nested exceptions will cause a Run Time error.

Debugging

Debugging of 64-bit applications in Delphi XE2 is remote. It is caused by the same reason: Delphi XE2 IDE is a 32 application, but your application is 64-bit. If you are trying to debug your application and you cannot do it, you should check that the Include remote debug symbols project option is enabled.

To enable it, perform the following steps:

1. Open Project Options (in the main menu Project->Options).
2. In the Target combobox, select Debug configuration – 64-bit Windows platform.
   

   If there is no such option in the combobox, right-click Target Platforms in Project Manager and select Add platform. After adding the 64-bit Windows platform, the Debug configuration – 64-bit Windows platform option will be available in the Target combobox.
   

3. Select Linking in the left part of the Project Options form.
4. Enable the Include remote debug symbols option.
   

After that, you can run and debug your 64-bit application.

To enable remote debugging, perform the following steps:

1. Install Platform Assistant Server (PAServer) on a remote computer. You can
   find PAServer in the %RAD_Studio_XE2_Install_Directory%\PAServer directory. The
   setup_paserver.exe file is an installation file for Windows, and the setup_paserver.zip
   file is an istallation file for MacOS.
2. Run the PAServer.exe file on a remote computer and set the password that will be used to connect to this computer.
3. On a local computer with Delphi XE2 installed, right-click the target platform that you want to debug in Project Manager and select Assign Remote Profile.
   
4. Click the Add button in the displayed window and input your profile name.
5. Click the Next button, input the name of a remote computer and the password to it (that you assigned when you started PAServer on a remote computer).
6. After that, you can test the connection by clicking the Test Connection button. If your connection failed, check that your firewalls on both remote and local computers do not block your connection, and try to establish a connection once more. If your connection succeeded, click the Next button and then the Finish button. Select your newly created profile and click OK.
   

After performing these steps you will be able to debug your application on a remote computer. You application will be executed on a remote computer, but you will be able to debug it on your local computer with Delphi XE2.

For more information about working with Platform Assistant Server, please refer to this article.

Database-Specific Aspects of 64-bit Development

For each database, the specifics of the 64-bit development is mainly conditioned by the use of particular database client libraries. When our connectivity solutions are used in Direct mode (without involving database client software), the specifics of developing applications depends exclusively on peculiarities of 64-bit platform regardless of the used database.

For example, our PostgreSQL connectivity solutions (PgDAC, UniDAC and dbExpress Driver for PostgreSQL) work with the PostgreSQL database directly. Our connectivity solutions for Oracle and MySQL can be used in Direct mode as well as with the corresponding database client.

For our connectivity solutions, using DBMS client libraries, the following requiements should be met. As Delphi XE2 is a 32-bit application, it can load only 32-bit libraries. So, to connect to the database at design-time, you need to use 32-bit client library, while at run-time you will need the 64-bit client library. For SQL Server, InterBase and Firebird, MySQL (in client mode), and SQLite you need to place the 32-bit client library to the C:\Windows\SysWOW64 directory (if you need to connect to the database at design-time), and the 64-bit client library, used for connecting to the database at run-time, to the C:\Windows\System32 directory. Note that developers of SQLite do not provide a ready driver for x64 platforms, and for x64 applications you need to manually compile the sqlite library (for example, in MS VisualStudio).

Our connectivity solutions for Oracle (ODAC, UniDAC, dbExpress driver for Oracle) use the DEFAULT Oracle Client as standard, so depending on the capacity of the DEFAULT Oracle Client you need to explicitly specify either the 64-bit client for run-time (if the DEFAULT Oracle Client is 32-bit), or the 32-bit client for design-time (if the DEFAULT Oracle Client is 64-bit), or you may explicitly specify both clients – for run-time and for design-time.

When developing cross-platform application using UniDAC to work with the MS Access database, you should remember that it is impossible to install two (32- and 64-bit) drivers on the same system (Microsoft limitation). That is why, if you need to connect to the database at design-time, the 32-bit driver must be installed on the development computer, since Delphi XE2 uses x32 libraries at design-time. If no such connection is needed, you can install the x64 MS Access driver. All the other aspects of x64 and x32 development are identical.

As regards using UniDAC for connecting to other database servers through ODBC, for information on drivers for different platforms and specifics contact their developers.