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:
OnExecuteCommand |
write this event to execute SQL expression. You can call TCustomSQLDataDriverEh.DefaultExecuteCommand method to process this event by default. By default TCustomSQLDataDriverEh. DefaultExecuteCommand calls DefaultSQLDataDriverResolver.ExecuteCommand method, which, in turn, call DefaultSQLDataDriverResolver.OnExecuteCommand event. |
OnGetBackUpdatedValues |
write this event to return updated values from server. You can call TCustomSQLDataDriverEh.DefaultGetUpdatedServerValues method to process action by default. TCustomSQLDataDriverEh.DefaultGetUpdatedServerValues call DefaultSQLDataDriverResolver.GetBackUpdatedValues. If it was not processed in DefaultSQLDataDriverResolver then it call InternalGetServerSpecOperations.GetBackUpdatedValues. InternalGetServerSpecOperations returns object of TServerSpecOperationsEh type. |
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:
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:
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:
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:
Second macro variables «SortOrderMacroName» is the name that will be used when applying sorting DBGridEh when preferences contain the following values:
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:
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:
Property name |
Property type |
Description |
Connection |
TADOConnection |
Link to an external component ADOConnection. |
InlineConnection |
TADOInlineConnectionEh |
Built-in component ADOConnection. |
InlineConnection. Connected |
Boolean |
|
InlineConnection. ConnectionString |
WideString |
|
InlineConnection. UseAtDesignTime |
Boolean |
Use InlineConnection in Design-Time. |
InlineConnection. UseAtRunTime |
Boolean |
Use InlineConnection in Run-Time. |
InlineConnection. Attributes |
TxactAttributes |
|
InlineConnection. CommandTimeout |
Integer |
|
InlineConnection. ConnectionTimeout |
Integer |
|
InlineConnection. ConnectOptions |
TConnectOption |
|
InlineConnection. CursorLocation |
TCursorLocation |
|
InlineConnection. DefaultDatabase |
WideString |
|
InlineConnection. IsolationLevel |
TIsolationLevel |
|
InlineConnection. KeepConnection |
Boolean |
|
InlineConnection. LoginPrompt |
Boolean |
|
InlineConnection. Mode |
TConnectMode |
|
InlineConnection. Provider |
WideString |
|
Path |
string |
ReadOnly property contains the path to the project in Design-Time. Is used when specifying paths ConnectionString with the macro% PROJECT_PATH%. |
ServerType |
string |
Name of the server type. In the current version |
Events of TADOConnectionProviderEh component:
Event name |
Event type |
Description |
OnExecuteCommand |
TResolverExecuteCommandEhEvent |
|
OnGetBackUpdatedValues |
TResolverGetBackUpdatedValuesEhEvent |
|
OnGetServerSpecOperations |
TResolverGetServerSpecOperationsEh |
|
OnUpdateRecord |
TResolverUpdateRecordEhEvent |
|
InlineConnectionBeforeConnect |
TNotifyEvent |
|
InlineConnectionAfterConnect |
TNotifyEvent |
|
Copyright (c) 1998-2013. All rights reserved.
|
What do you think about this topic? Send feedback!
|