UniDAC

UniDAC Basics

Introduction

Universal Data Access Components (UniDAC) is a powerful library of nonvisual cross-database data access components for Delphi, C++Builder and Lazarus(Free Pascal). The UniDAC library is designed to help programmers develop faster and cleaner cross-database applications. UniDAC is a complete replacement for standard database connectivity solutions and presents an efficient native alternative to the Borland Database Engine and dbExpress for access to Oracle, SQL Server, MySQL, InterBase, Firebird, SQLite, DB2, Microsoft Access, Advantage Database Server, Adaptive Server Enterprise, DBF, NexusDB, and other databases (using ODBC provider).

UniDAC is based on the well-known Data Access Components from Devart such as ODAC, SDAC, MyDAC, IBDAC, and PgDAC.

This article provides an overview of the concepts and tasks you will apply when you work with UniDAC.

Connecting to the Database

Connecting to the Database in Design-Time

For UniDac component using you have to do following steps:


TUniConnection editor


TUniConnection editor

Connecting to the Database at Run-Time

Set the TUniConnection parameters and open it at run-time. The following example shows how to do this:


UniConnection1: TUniConnection;
...
UniConnection1.ProviderName := 'Oracle';
UniConnection1.Username := 'scott';
UniConnection1.Password := 'tiger';
UniConnection1.Server := 'ORA1020';
UniConnection1.SpecificOptions.Values['Schema'] := 'SCOTT';
UniConnection1.Open;

Each line in the SpecificOptions property has the following format: <OptionName>=<Value>. You can add options using the Add method:



UniConnection1.SpecificOptions.Add('Schema=SCOTT');

But it is better to use the Values property of TStrings because this property does not add a new line if an option with the same name already exists. Instead it replaces the text after '=' with a new value.

To close the connection use the Close method:



UniConnection1.Close;

You should link all the providers that you use in the application. To link a provider, add its unit to the USES list. For Oracle add the OracleUniProvider to USES:



uses   ..., OracleUniProvider;

The provider unit can be easily added by help of the UniDAC Providers palette page. Select this page, find the OracleUniProvider component and drop it on the form. IDE will add the corresponding unit to USES automalically if it is not added yet.

Selecting Data

The TUniQuery and TUniTable components allow you to select data. To do it, drop TUniQuery component into the form. For data selecting you have to establish a connection to the database. You need to set the Connection property for most components. If there is a TUniConnection component into the form, UniDAC automatically sets the Connection property to this component.

For the TUniQuery you need to set the SQL property. Double click the TUniQuery component to open the TUniQuery editor. On the first page of the editor you can enter the text for the SQL property.

TUniSQL component is used to execute queries without recordset. The TUniSQL is not a TDataSet descendant like TUniQuery. TUniSQL is a simple component that provides the best performance.

It is used in the same way as the TUniQuery. If you want to define SQL and parameters - use TUniSQL editor at design-time. You can define SQL and parameters at run-time too. To execute query you have to assign a value for the SQL property and call the Execute method.

If you connect to the SCOTT sample schema, you can enter:



SELECT * FROM emp

to select data from the EMP table.


TUniQuery editor

Click the OK button to save changes and close the editor. To execute the query, you can change the Active property to True in Object Inspector, or call the Open method in your program:



UniQuery1: TUniQuery;
...
UniQuery1.Connection := UniConnection1;
UniQuery1.SQL.Text := 'SELECT * FROM emp';
UniQuery1.Open;

The Displaying Data


Drop TDataSource and TDBGrid components into the form to see data from TUniQuery. You can use standard TDataSource from the Data Access palette page or TUniDataSource component from the UniDAC page. These components have same functionality but TUniDataSource sets the DataSet property automatically.

Set the DataSet property of TDataSource to UniQuery1 (if it is not set automatically). Then set the DataSource property of TDBGrid to DataSource1. If the Active property of UniQuery is True, DBGrid will display data.


Form with grid

To close the TUniQuery use its Close method or set its Active property to False.

UniQuery with data always has a current record. Current record is changed while you move across the DBGrid.

Current record can be changed programmatically by help of the First, Last, Next, Prior, Locate, and LocateEx methods of the TUniQuery.


Working with Fileds


The TUniQuery has a Fields collection containing one TField object for each field in your query. You can get a reference to the TField object by field number or by using FieldByName method:



UniQuery1.Fields[0];
UniQuery1.FieldByName('EMPNO');

TField object can read data from the current record. Use a Value property of TField or typed properties like AsInteger, AsString, etc.

For example, you can copy data from the TUniQuery to a TMemo component using the following code:



var
  Empno: integer;
  Ename: string;
begin
  Memo1.Lines.Clear;

  UniQuery1.Open;
  UniQuery1.First;

  while not UniQuery1.Eof do begin
    Empno := UniQuery1.FieldByName('EMPNO').AsInteger;
    Ename := UniQuery1.FieldByName('ENAME').AsString;

    Memo1.Lines.Add(IntToStr(Empno) + ' ' + Ename);
    
    UniQuery1.Next;
  end;

  UniQuery1.Close;
end;

The Next method sets the Eof property of TUniQuery to True if it cannot move to the next record because there are no more records.

The TUniQuery creates and destroys fields dynamically when you open and close the query. Sometimes you need to create persistent fields generated with the form. To create persistent fields, right click TUniQuery component and select Fields Editor from the context menu. Fields Editor window will be opened. Right click inside the Fields Editor window and select Add all fields from the menu. Now you will see the list of fields in the window.


Fields Editor

Fields are created as the components on the form. IDE adds corresponding variable of form class for each field. You can rewrite the previous code example using the persistent field variables:



...
  while not UniQuery1.Eof do begin
    Empno := UniQuery1EMPNO.AsInteger;
    Ename := UniQuery1ENAME.AsString;

    Memo1.Lines.Add(IntToStr(Empno) + ' ' + Ename);
    
    UniQuery1.Next;
  end;
...

We recommend use TUniTable to select data from one table. You don't need to write SQL statement for TUniTable. You set the TableName property and TUniTable automatically generates SQL statement to get data from this table.

Drop the TUniTable into the form and double-click the component to open TUniTable editor. You can enter value for the TableName property and for OrderFields and FilterSQL properties in the editor.


TUniTable editor

When OrderFields and FilterSQL properties are empty, TUniTable generates simple SQL statement like



SELECT * FROM emp

If you set values for OrderFields or FilterSQL, corresponding ORDER BY or WHERE clauses will be added to the statement.

Executing Queries

TUniQuery can be used not only for selecting data but for executing any queries supported by database server.

For example, you can change records in the EMP table by using the TUniQuery with UPDATE statement. Drop the TUniQuery component on the form and double click it to open the editor. Enter the following text for SQL:


UPDATE emp SET sal = sal + 1 WHERE empno = 10

The query can be executed at design-time from the editor using the Execute button. To execute the query at run-time, call the Execute method of TUniQuery.


UniQuery1.Execute;

Parameters

Queries don't use fixed values in "SET" or "WHERE" clause in general. For example, your program can get the new values for "SAL" and "EMPNO" fields from the user.

You can use parameters for this purpose:


UPDATE emp SET sal = :sal WHERE empno = :empno

Parameters are marked using ':' (colon) and parameter name.

Values of the parameters can be set at run-time, and the server replaces parameter names with the values during the query execution.

After the query with parameters was defined into the SQL tab of the TUniQuery editor, go to the Parameters tab. Here you have to set DataType and ParamType for each parameter


TUniQuery editor - Params

At run-time you can access the parameters by number or by name using the Params collection of TUniQuery.


UniQuery2.Params[0];
UniQuery2.ParamByName('SAL');

Use the following code to execute query with parameters:


UniQuery2.ParamByName('SAL').AsFloat := 100;
UniQuery2.ParamByName('EMPNO').AsInteger := 10;
UniQuery2.Execute;

Each parameter is substituted only by single value in the SQL statement.

Macros

Any part of statement (table name, for example) can be changed dynamically with macros. The macros are marked with '&' (ampersand) and macro name:


SELECT * FROM &macro1

The macros are accessed by number or name from the Macros collection of TUniQuery component in your program code.


UniQuery3.Macros[0];
UniQuery3.MacroByName('MACRO1');

The value of a macro can be set by the Value property of a TMacro. For example:


UniQuery3.MacroByName('MACRO1').Value := 'emp';

or


UniQuery3.MacroByName('MACRO1').Value := 'emp ORDER BY ename';

Editing Data

All of the datasets components described above are editable. Call the Edit method to begin editing. Call the Post or Cancel method to finish editing. If you call Post, the changes are passed to the database server. If you call Cancel, changes will be revoked.


UniQuery1.Edit;
UniQuery1.FieldByName('HIREDATE') := Now;
UniQuery1.FieldByName('SAL') := 1000;
UniQuery1.Post;

Database Controls like TDBGrid or TDBEdit allow user for data editing.

A new record can be inserted by the Insert or Append method. The Append method adds record to the end of dataset. The Insert method inserts record in the current position. After one of these methods is called, you should assign values to the fields and call the Post method:


UniQuery1.Append;
UniQuery1.FieldByName('EMPNO') := -1;
UniQuery1.FieldByName('ENAME') := 'NEW EMP';
UniQuery1.FieldByName('HIREDATE') := Now;
UniQuery1.FieldByName('SAL') := 2000;
UniQuery1.Post;

To delete record in the current position, call the Delete method.

UniDAC executes "INSERT", "UPDATE", or "DELETE" statement to apply changes to the database.

Debugging

UniDAC can show SQL statements in dialog window before execution. Set the Debug property of TUniQuery to True to see SQL statements of your query. For profiling in real-time you have to add the UniDacVcl unit to the USES list. Then run the application. You see the SELECT statement at startup. Try to edit a record, add a new record, and delete this record. You will see the corresponding update statements in the Debug window.

Updating table property

If more than one table is specified in the query, UniDAC allows you to update data only in one table. Fields from other tables become read-only. For example, change the SQL property of UniQuery1 to the following:


SELECT e.*, d.dname
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno

Now you can edit all the fields except the last field DNAME.

UpdatingTable property contains a name of the table that will be updated.

UniDAC uses the first table specified after "SELECT" or the first table pointed after "FROM" as default updating table, depending from the current data provider.

If your query contains several tables, it is recommended to always set the UpdatingTable property to the table you want to edit.

General field information

UniDAC requires information about key fields of the updating table to generate "WHERE" clause of "UPDATE" and "DELETE" statements. Some servers like SQL Server return this information when a query is executed. Oracle and other database servers do not return information about key fields, so UniDAC performs the additional query to the database to get key fields. You can set the KeyFields property of TUniQuery manually. In this case an additional query is not executed.

Complex queries

If you set a complex query to the SQL property, UniDAC may not be able to generate the correct update statements. Or you need custom SQL statements to apply changes to the database (for example, you can apply changes using stored procedures instead of "INSERT", "UPDATE", and "DELETE" statements). You can use the SQLInsert, SQLUpdate, and SQLDelete properties of TUniQuery to set custom update statements. If you double-click one of these properties in Object Inspector, the Update SQLs page of the TUniQuery editor is opened.

A field value in the update queries can be referenced by the parameter with the same name as field name. For example, use the following statement in the SQLUpdate property to save changes to "ENAME" and "SAL" fields.


UPDATE emp SET ename = :ename, sal = :sal
WHERE empno = :empno

Old parameters

You can reference to an old value of the field by adding "OLD_" prefix to the parameter name. For example, if user can change value of EMPNO field, you need to use the old value of this field in the "WHERE" condition:


UPDATE emp SET empno = :empno, ename = :ename, sal = :sal
WHERE empno = :OLD_empno

TUniQuery editor - Update SQL

SQL generator

For simple SQL-queries SQL properties can be updated automatically on the SQL generator tab. Go to the SQL Generator page of the query editor. If your query has several tables in the "FROM" clause, select table to update in the Table Name combobox. You can select statement types to be generated, key fields, and data fields.

Click the Generate SQL button. The update statements are generated and the editor changes the current page to Update SQLs. Now you can make changes in the generated statements.

Using stored procedures

Stored procedure can be used in the update statements. The procedure for insert is similar to following (example for Oracle):


CREATE OR REPLACE PROCEDURE DEPT_INSERT
  (pDNAME VARCHAR, pLOC VARCHAR)
AS
BEGIN
  INSERT INTO DEPT (DNAME, LOC) VALUES (pDNAME, pLOC); 
END;

An SQL statement for stored procedure call can be written manually or created by generator. Go to the Stored Proc Call Generator page, select the stored procedure name, select the statement type and click the Generate button.


TUniQuery editor - SQL Generator

Executing Stored Procedures

TUniStoredProc allows you to execute a stored procedure.


TUniStoredProc editor - SQL

To call the procedure at run-time use the Execute method. You may also set the stored procedure name and generate SQL statement for calling the stored procedure at run-time. Call the PrepareSQL method to generate SQL statement for stored procedure. After that Params collection is filled, and you can assign values to the parameters.


UniStoredProc1.StoredProcName := 'DEPT_INSERT';
UniStoredProc1.PrepareSQL;
UniStoredProc1.ParamByName('PDNAME').AsString := 'DEPT 1';
UniStoredProc1.ParamByName('PLOC').AsString := 'California';
UniStoredProc1.Execute;

Creating Master/Detail Relations

Imagine that you have two tables, and second table has a field (foreign key) that references the primary key of the first table. For example, the "SCOTT" sample schema in the Oracle database has "DEPT" and "EMP" tables. "DEPT" contains the list of departments, and "EMP" contains the list of employes. "DEPT" table has DEPTNO primary key. "EMP" also has the DEPTNO field. This field references the "DEPT" table and contains a number of the department where an employee works.

If you have two TUniQuery or TUniTable components, you can link them in a master/detail relation. The detail dataset shows only records corresponding to the current record in the master dataset.

For example, drop two TUniTable components on the form. Set the Name property of the first table to "DeptTable", and TableName property to "Dept". Set the Name property of the second table to "EmpTable", and TableName property to "Emp". Set the Active property of both tables to True.

Drop two TUniDataSource components on the form, set their names to "DeptDS" and "EmpDS", and link them to the corresponding tables. Then drop two TDBGrid components and link them to the corresponding data sources.

Set the MasterSource property of EmpTable to "DeptDS". Double-click the MasterFields property of EmpTable in Object Inspector. It will open the editor for linking fields between details and master. Select the DEPTNO field in both left and right list and click the Add button. Click the OK button to close the dialog.

Now EmpTable displays only employes from the current department. If you change the current record in DeptTable, EmpTable is automatically refreshed and displays another employes.

When you set MasterSource for TUniTable or TUniQuery, its SQL is automatically modified. Fields that you linked are added to the WHERE condition:


SELECT * FROM EMP
WHERE DEPTNO = :DEPTNO

The parameter value is set from the corresponding field of the master dataset, then the query is executed. When you change the current record in the master, the parameter value in the detail is changed, and the detail query is reexecuted.

Text parameters, corresponding to the master fields, can be added to the SQL text manually. In this case you don't need to set the MasterFields property, just set the MasterSource property. UniDAC sets values for parameters automatically if the master dataset has fields with the same name.

When the current record in the master is changed, the detail query is reexecuted each time. You can avoid this by using local master/detail. Set Options.LocalMasterDetail to True for TUniTable or TUniQuery. In this case parameters are not added to the detail query. This query is executed only one time and returns all records. UniDAC filters these records locally to display only records corresponding to the master record.

Unified SQL

Unified SQL includes special directives, macros, literals, and functions. You can use Unified SQL to write SQL statements that are independent from used provider and database. There are several ways to do it. First way is using connection macros and IF directive. UniDAC automatically defines the macro that corresponds to the selected provider in this way. For example, if you select Oracle provider, Oracle macros is defined. If you want to use "EMP1" table for Oracle and "EMP2" table for SQL Server, you can assign the following to the SQL property of TUniQuery:


{if ORACLE}
SELECT * FROM EMP1
{else}
{if SQLSERVER}
SELECT * FROM EMP2
{else}
SELECT * FROM EMP
{endif}
{endif}

To define macros at design-time, open the TUniConnection editor and select Macros page. Fill Name and Value boxes at the bottom of the page. Then press the Add button. You can use the added macro in IF directive or directly in SQL statements.


Macros editor

For example, if you define macro "EMP_TABLE" with value "EMP", you can write the following SQL statement:


SELECT * FROM {EMP_TABLE}

The several macros with the same name but different value and conditions can be defined. Condition is the name of another macro. If the macro, specified in condition, is enabled, the current macro is also enabled and its value replaces the macro name in SQL statements. If the macro specified in condition is not enabled, the current macro is not enabled also.

The macros corresponding to the providers in Condition can be used. For example, you can add two more macros with name "EMP_TABLE": one with Value = EMP1 and Condition = ORACLE, another with Value = EMP2 and Condition = SQLSERVER. In this case the query


SELECT * FROM {EMP_TABLE}

is equivalent for the query with IF directives from the first example.

The Macros collection of TUniConnection can be used for macros adding at run-time:


UniConnection1.Macros.Add('EMP_TABLE', 'EMP');
UniConnection1.Macros.Add('EMP_TABLE', 'EMP1', 'ORACLE');
UniConnection1.Macros.Add('EMP_TABLE', 'EMP2', 'SQLSERVER');

Unified SQL defines unified literals for date, time and timestamp values. For example:


SELECT * FROM emp WHERE HIREDATE > {date '1982-01-15'}

For Oracle, this statement is converted to the following:


SELECT * FROM emp WHERE HIREDATE > TO_DATE('1982-01-15', 'YYYY-MM-DD')

Unified SQL supports also functions. Functions are marked in SQL statements using 'fn' keyword. For example,


SELECT {fn TRIM(EName)} FROM emp

evaluates to


SELECT TRIM(EName) FROM emp

it is the counterpart in the DBMS. But in MS SQL Server there is no single corresponding function, so the expression evaluates to


SELECT LTRIM(RTRIM(EName)) FROM emp


The treated article presented general definition of UniDAC components and them usage. For detailed information please look UniDAC documentation. The UniDAC documentation includes an useful articles and a detailed reference of all UniDAC components and classes.


If you want to download trial version of UniDAC, please visit https://www.devart.com/unidac/download.html. For information about getting the UniDAC, visit the How to Order section. If you have a question about UniDAC or any other Devart product, contact sales@devart.com.


© 1997-2020 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback