TSQLDataDriverEh is a universal DataDriver that can interact with server using SQL commands. TSQLDataDriverEh have five objects of the TSQLCommandEh type: SelectCommand, DeleteCommand, InsertCommand, UpdateCommand, GetrecCommand. Each object holds SQL expressions and parameters to execute command to get table data, delete record, insert record, update record and get one record. To execute commands TSQLDataDriverEh calls global event - DefaultSQLDataDriverResolver.OnExecuteCommand. You should write this event to execute SQL expressions on the server and, if need, return DataSet to read data. When TSQLDataDriverEh is used as provider of data, it takes records from DataSet created in DefaultSQLDataDriverResolver.OnExecuteCommand event and writes them in TMemTableEh. On the measure of the requirement it takes given current record, writes them in TMemTableEh and goes over to following record. When TSQLDataDriverEh is used as handler of changes, it takes a changed record from TMemTableEh, and call DefaultSQLDataDriverResolver.OnExecuteCommand event sending DeleteCommand, InsertCommand or UpdateCommand as a parameter. 

Using TSQLDataDriverEh it is possible change a type of the access to data. Suffice it to rewrite global event - DefaultSQLDataDriverResolver.OnExecuteCommand. 

TSQLDataDriverEh has a SpecParams property of TStrings type. You may use it to write the special values, which you can use in the DefaultSQLDataDriverResolver.OnExecuteCommand event. This event also is used by TServerSpecOperationsEh object when DefaultSQLDataDriverResolver.ServerSpecOperations property is assigned. List of special values depends of the type of TServerSpecOperationsEh object and values is filled similarly of description in the "Characteristic TXXXDataDriverEh.SpecParams" section. 

DefaultSQLDataDriverResolver.ServerSpecOperations property has a TServerSpecOperationsEh type. This object is intended to process the special operations before or after ExecuteCommand procedure is performed. TServerSpecOperationsEh is a base class for classes TOracleSpecOperationsEh, TMSSQLSpecOperationsEh, TInterbaseSpecOperationsEh, TInfromixSpecOperationsEh, TDB2SpecOperationsEh, TSybaseSpecOperationsEh and TMSAccessSpecOperationsEh. Each Of these objects can process a SpecParams property in particular to get values of the autoincrement fields that is generated by the server. On the name of the class it is possible to define a type of the server, for which class is intended. 

For full functioning of TSQLDataDriverEh it is necessary to write DefaultSQLDataDriverResolver.OnExecuteCommand event to execute queries on the server and assign DefaultSQLDataDriverResolver.ServerSpecOperations property by the object that inherited from TServerSpecOperationsEh class. Assigning a ServerSpecOperations property makes sense, if you execute operations of the insertion in tables that have autoincrement field (or sequence objects). 

The typical code to adjust working of TSQLDataDriverEh can be look as follows:

// Below code adjusts working of  TSQLDataDriverEh components in the whole Application to access
// InderBase server via BDE Engine
uses … DataDriverEh, BDEDataDriverEh;
type
  TMainForm = class(TMainForm)
    Database1: TDatabase;
    SQLDataDriverEh: TSQLDataDriverEh;
…
procedure TMainForm.FormCreate(Sender: TObject);
begin
  DefaultSQLDataDriverResolver.OnExecuteCommand := OnExecuteSQLCommand;
  DefaultSQLDataDriverResolver.ServerSpecOperations := TInterbaseSpecOperationsEh.Create;
end;

procedure TMainForm.FormDestroy(Sender: TObject);
begin
  DefaultSQLDataDriverResolver.ServerSpecOperations.Free;
  DefaultSQLDataDriverResolver.ServerSpecOperations := Nil;
end;

function TMainForm.OnExecuteSQLCommand(SQLDataDriver: TCustomSQLDataDriverEh;
  Command: TCustomSQLCommandEh; var Cursor: TDataSet; var FreeOnEof,
  Processed: Boolean): Integer;
begin
  Result := DefaultExecuteBDECommandEh(SQLDataDriver, Command,
    Cursor, FreeOnEof, Processed, Database1.DatabaseName);
end;

TSQLDataDriverEh is a base class for TBDEDataDriverEh, TIBXDataDriverEh, TDBXDataDriverEh and TADODataDriverEh. These objects overwrite ExecuteCommand procedure and them can execute SQL expressions on the server and if need returns DataSet to read data. When SQL commands is called, it creates DataSet with type of corresponding type of the access to data. For TBDEDataDriverEh it is a TQuery, for TIBXDataDriverEh it is a TIBXQuery and so on. Furthermore, TBDEDataDriverEh, TIBXDataDriverEh, TDBXDataDriverEh can define TServerSpecOperationsEh object automatically. For TADODataDriverEh it is need to assign DefaultSQLDataDriverResolver. ServerSpecOperations because ADO technology does not allow to define a type of the server. 

TSQLDataDriverEh has the next events:

Property TXXXDataDriverEh.SpecParams. 

Property SpecParams kept a list of parameters and values. TXXXDataDriverEh use them when performing SQL expressions. Value of each parameter have to be wrote in the separate line in the format ‘PARAMETER_NAME =VALUE’. Depending on the type of the server (InterBase, Oracle, MSSQL, Informix) SpecParams can contain the following parameters:

• On interaction with InterBase server:
  • ‘GENERATOR’ - defines a name of the InterBase generator. DataDriver uses this parameter to get current value of generator after the insertion of new record.
  • ‘GENERATOR_FIELD’ - defines a name of the field, which will be assigned current value of the generator after the insertion of new record.
  • ‘AUTO_INCREMENT_FIELD’ - defines a name of the field, which DataDriver will set AutoIncremet type. It is used on making a structure of the internal array of record.

• On interaction with Oracle server:
  • ‘SEQUENCE’ - will assign a name of the field Oracle sequences. DataDriver uses this parameter for the reception of the current value of the sequence after the insertion of new record.
  • ‘SEQUENCE_FIELD’ - will assign a name of the field, which will be assigned current value of the sequence after the insertion of new record.
  • ‘AUTO_INCREMENT_FIELD’ - defines a name of the field, which DataDriver will set AutoIncremet type. It is used on making a structure of the internal array of record.

• On interaction with MSSQL server:
  • ‘AUTO_INCREMENT_FIELD’ - defines a name of the field, which DataDriver will set AutoIncremet type. It is used on making a structure of the internal array of record.

• On interaction with Informix server:
  • ‘AUTO_INCREMENT_FIELD’ - defines a name of the field, which DataDriver will set AutoIncremet type. It is used on making a structure of the internal array of record.

Example of the list of parameters for InterBase server: 

GENERATOR=EMP_NO_GEN 

GENERATOR_FIELD=emp_no 

AUTO_INCREMENT_FIELD=emp_no 

SQL expression for the insertion of record must contain EMP_NO_GEN generator. 

INSERT INTO 

employee (EMP_NO, FIRST_NAME) 

VALUES 

(:EMP_NO_GEN, :FIRST_NAME) 

As TSQLDataDriverEh as one of the TBDEDataDriverEh, TIBXDataDriverEh, TDBXDataDriverEh or TADODataDriverEh components (we will name them as TXXXDataDriverEh) allow to work with database without big number of tunings. 

For TXXXDataDriverEh it is sufficiently to set database object property (For TBDEDataDriverEh it is a Database property of TDatabase type). 

TSQLDataDriverEh does not have database object property. To force it works is sufficiently to write DefaultSQLDataDriverResolver.OnExecuteCommand event once to execute queries on server, and assign DefaultSQLDataDriverResolver.ServerSpecOperations property by the object of TServerSpecOperationsEh type to process some specific server operations. DefaultSQLDataDriverResolver is not visual object, so you have to assign an event and property in the program, for example in the OnCreate event of your main form. It is possible to change access Engine to database quickly when you use TSQLDataDriverEh. It is sufficiently rewrite global event - DefaultSQLDataDriverResolver.OnExecuteCommand only. 

TSQLDataDriverEh and TXXXDataDriverEh works very similar at design-time. When TSQLDataDriverEh component editor is opening you can choose one of design-time types of the database accesses that built-over BDE, ADO, IBX and DBX engines. It is not necessarily that the access engine that you use at design-time will be same as you write in DefaultSQLDataDriverResolver.OnExecuteCommand event.

Double click on TSQLDataDriverEh or TXXXDataDriverEh component opens a dialog editor of TSQLDataDriverEh component. At a design-time TSQLDataDriverEh and TXXXDataDriverEh always use design time copy of object that work with database (it is TDatabase object for DBE engine). If where are not any object was created the system will offer to create a new object. Type of dialog of creating DB Object depend of type of TXXXDataDriverEh. For TSQLDataDriverEh it show dialog where you can choose a type of the access to server (Engine) and service of data processing of server (DB Service). Type of the access defines a engine to access the data and can be one of the following types: BDE, IBX, DBX or ADO. For ADO also need to define "Service a data processing", class that can get a list of DB objects from server. If object that work with database already has been create before then system will offer to choose one of the existing. 

Window of editing TSQLDataDriverEh have a next controls:

1. Tree list of DB objects. Tables, Views, Procedures, Functions etc. It depends of server type.
2. Lower window of elements of the current object in the tree of objects.
3. Output grid of result of executing the Select expressions.
4. Page of Select expression (TSQLDataDriverEh.SelectSQL)
5. Pages expressions for Inserting, Updating, Deleting, Requesting one record.
6. Page to fill SpecParams property and parameters of dynamic SQL building.

TMemTableEh - TXXXDataDriverEh - TXXXConnectionProviderEh 

New components TXXXConnectionProviderEh provide a single point of connection to the database. TADOConnectionProviderEh for ADO, TDBXConnectionProviderEh for DBExpress etc. for each method of data access component of your ConnectionProvider. ConnectionProvider he does not join, but has built-in component InlineConnection (for ADO is TADOConnection, for DBExpress TSQLConnection, etc.) and additional properties, a reference to the standard data access component of the same type (for example if you want to lay TADOConnection separately on form). In component TXXXConnectionProviderEh you also specify the type of server data (MSAccess, SQLServer, Oracle, InterBase, etc.). Some ConnectionProvider automatically determine the type of server (for example, TIBXConnectionProviderEh this will always be the server 'InterBase'). The type of server components defines additional parameters for the database server. For example, on the type of server component TXXXDataDriverEh defines the operating mode with auto increment fields. In TXXXConnectionProviderEh can specify different connection objects at the Design-Time and Run-Time, thereby dividing the ways you can connect to the database during development and in the period of the program. The component also allows you to specify access TADOConnectionProviderEh path to MSAccess database in Design-Time line relative file location. A relative path starts from the location of the form file, which has a component TADOConnectionProviderEh. 

For example: 

ADOConnectionProviderEh1.InlineConnection.ConnectionString = 

'Provider = Microsoft.Jet.OLEDB.4.0; 

Data Source =% PROJECT_PATH% \ ..\ Data \ DBTest.mdb; 

Persist Security Info = False' 

When you open the form in Design-Time component replaces the macro "% PROJECT_PATH%" to the path where the dfm form file is. This allows you to move the project to different computer and at different location without changing the ways of access to the database file. 

Demo: See Demo project using cords TMemTableEh-TADODataDriverEh-TADOConnectionProviderEh in your library - <EhLib Dir\Demos\DataDriver.ADO.SimpleDemo> 

Demo: See Demo project using cords TMemTableEh-TBDEDataDriverEh-TBDEConnectionProviderEh in your library - <EhLib Dir\Demos\DataDriver.BDE.SimpleDemo> 

Demo: See Demo project using cords TMemTableEh-TDBXDataDriverEh-TDBXConnectionProviderEh in your library - <EhLib Dir\Demos\DataDriver.DBX.SimpleDemo> 

Demo: See Demo project using cords TMemTableEh-TIBXDataDriverEh-TIBXConnectionProviderEh in your library - <EhLib Dir\Demos\DataDriver.IBX.SimpleDemo>

SQL expression in SQLDataDriverEh commands (ADODataDriver, DBXDataDriver, ...) can contain Macro variables. Before executing the SQL statement the Macro variables are replaced by the values assigned to them and actual SQL statement is formed, which is transmitted for execution. 

For example the expression “select * from %table_name%” contains a macro variable "%table_name%". If this variable contains the value of "Country", a real expression that will be transferred to the execution will be the next 'select * from Country'. 

You can type in SQLDataDriverEh.SelectSQL, UpdateSQL, DeleteSQL, InsertDQL, GetrecSQL commands any expression that may contain a combination of SQL statements and macro variables. 

In the collection of the elements SQLDataDriverEh.MacroVars.Macros it is necessary to create macro- elements and give them names according to the variables inscribed in the SQL command. At design-time you can enter the values of macro variables, or leave them blank. 

At run-time before the opening MemTableEh connected to SQLDataDriverEh you can set the value of the macro variable:

  ADODataDriverEh1.SelectSQL.Text := ‘select * from %table_name%’
  ADODataDriverEh1.MacroVars.Macros['%table_name%'] := ‘Country’;
  MemTableEh1.Open;
…
 if Conditions
  then ADODataDriverEh1.MacroVars.Macros['%table_name%'] := ‘table1’
  else ADODataDriverEh1.MacroVars.Macros['%table_name%'] := ‘table2’;

Use SQLDataDriverEh.FinalSelectSQL, FinalUpdateSQL, FinalInsertDQL, FinalGetrecSQL properties to access the actual SQL expression. 

Demo: See a Demo project of using macro variables in the folder of the library – <EhLib Dir\Demos\DataDriver.Macros>

There are two special macro variables in SQLDataDriverEh. Their names are stored in the properties:

SQLDataDriverEh.MacroVars.SpecMacros.FilterMacroName
SQLDataDriverEh.MacroVars.SpecMacros.SortOrderMacroName

First macro variable «FilterMacroName» is the name that will be used when the filter is in DBGridEh when preferences contain the following values:

• DBGridEh.STFilter.Local = False
• DBGridEh SQLDataDriverEh connected to the scheme - DBGridEh.DataSource.MemTableEh. SQLDataDriverEh.
• SQLDataDriverEh.SpecMacrosUsages contains value smuUseFilterMacroEh.

If smuUseFilterMacroEh not exist in SQLDataDriverEh.SpecMacrosUsages, filtering through the change SQL statements will be executed under the scheme described in EhLib users guide section - Sorting and filtering data in DBGridEh. 

Default FilterMacroName contain values '% Filter%'. 

The formation of the variable FilterMacroName affect property values in SpecMacrosUsages:

• SmuFilterAsWhereClauseEh value specifies that in the event of non-null value to filter the string value of this macro variable will generate the following rule 'where Filter Expression'. In this case SQLDataDriverEh.SelectSQL can be written as 'select from Country% Filter%'. If filtering is coming from DBGridEh is empty (show all data) SQLDataDriverEh.FinalSelectSQL then made the following expression 'select from Country' (explanatory variables% Filter% 'is an empty string). If the expressions for filtering input from DBGridEh will not empty, the variable% Filter% 'has the value' WHERE Filter Expression '.
• SmuFilterWithANDPrecedingEh value specifies that in the event of non-null value to filter the string value of this macro variable will generate the following rule 'AND Filter Expression'.
• If smuFilterAsWhereClauseEh and smuFilterWithANDPrecedingEh SpecMacrosUsages absent in the string value of the filter will be formed as is, without additional prior expression 'Filter Expression'

Second macro variables «SortOrderMacroName» is the name that will be used when applying sorting DBGridEh when preferences contain the following values:

• DBGridEh.SortLocal = False
• DBGridEh SQLDataDriverEh connected to the scheme - DBGridEh.DataSource.MemTableEh. SQLDataDriverEh.
• SQLDataDriverEh.SpecMacrosUsages contains value smuUseSortOrderMacroEh.

If smuUseSortOrderMacroEh not exist in SQLDataDriverEh.SpecMacrosUsages, sorting through change SQL expression will be executed under the scheme described in EhLib users guide section - Sorting and filtering data in DBGridEh. 

Default FilterMacroName contain values '% Filter%'. 

The formation of the variable SortOrderMacroName affect property values in SpecMacrosUsages:

• SmuSortOrderAsOrderByClauseEh value specifies that in the event of non-null value to sort the string value of this macro variable will generate the following rule 'ORDER BY 3, 7, ...'. In this case SQLDataDriverEh.SelectSQL can be written as 'SELECT FROM Country% SortOrder%'.
• SmuSortOrderWithCommaPrecedingEh value specifies that in the event of non-null value to sort the string value of this macro variable will generate the following rule ', 3, 7 ...'. In this case SQLDataDriverEh.SelectSQL can be written as 'SELECT FROM Country ORDER BY 5% SortOrder%'.
• If smuSortOrderAsOrderByClauseEh smuSortOrderAsOrderByClauseEh and not in SpecMacrosUsages, the string value of the filter will be created as is, without additional prior expressions '3, 7, ... '. In the absence of information on the sorting will be set empty string value.

Demo: See a Demo project of using macro variables in the folder of the library – <EhLib Dir\Demos\DataDriver.SpecMacros> 

Properties of the TADOConnectionProviderEh Component:

Events of TADOConnectionProviderEh component:

EhLib User Guide