UniDAC 基础 英文 原文【就不传其中的图片了】

Universal Data Access Components

Send comments on this topic

UniDAC Basics

Top Previous Next

要看译文戳这里：

• 《UniDAC 基础》 【菜头】中文 翻译 (全)

·Introduction

·Connecting to the Database

·Selecting Data

·Executing Queries

·Editing Data

·Executing Stored Procedures

·Creating Master/Detail Relations

·Unified SQL

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:

·Create an empty application that will be used to work with UniDAC components. SelectFile | New | VCL Forms Application from the Delphi menu.

·Find UniDAC page on the component palette and dropTUniConnection component on the form.

·Set the main properties of TUniConnection using TUniConnection editor. Double click theTUniConnection component on the form to open the editor.

·Select a provider name corresponding to your database from theProvider drop-down combobox. For example, select Oracle for connecting to an Oracle database.

·Enter the following connection parameters: user name, password, server, database, and port into the editor. Some of connection parameters are not used, depending on the selected provider. For Oracle you need to enter user name, password, and server, for example. Server is a TNS alias name of an Oracle database. You can select value forServer from the drop-down list or enter it manually.

 

 

·Click the Connect button. If the connection is established successfully the editor closes automatically.

·Open the editor again by double-clicking theTUniConnection component and select theOptions page. Here you can enter some options specific to the provider. Schema is a useful option for an Oracle database. We will use objects from the "SCOTT" sample schema in this example. So, enter "SCOTT" as a value forSchema.

 

 

Connecting to the Database at Run-Time

Set theTUniConnection 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 theSpecificOptions property has the following format: <OptionName>=<Value>. You can add options using theAdd method:

 

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

 

But it is better to use theValues 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 theClose method:

 

UniConnection1.Close;

 

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

 

uses   ..., OracleUniProvider;

 

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

Selecting Data

TheTUniQuery andTUniTable components allow you to select data. To do it, dropTUniQuery component into the form. For data selecting you have to establish a connection to the database. You need to set theConnection property for most components. If there is aTUniConnection component into the form, UniDAC automatically sets theConnection property to this component.

For theTUniQuery 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. TheTUniSQL is not a TDataSet descendant likeTUniQuery.TUniSQL is a simple component that provides the best performance.

It is used in the same way as theTUniQuery. If you want to define SQL and parameters - useTUniSQL 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 theExecute method.

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

 

SELECT * FROM emp

 

to select data from the EMP table.

 

 

Click theOK button to save changes and close the editor. To execute the query, you can change theActive property to True in Object Inspector, or call theOpen 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 fromTUniQuery. You can use standard TDataSource from the Data Access palette page orTUniDataSource component from the UniDAC page. These components have same functionality butTUniDataSource sets the DataSet property automatically.

Set the DataSet property of TDataSource toUniQuery1 (if it is not set automatically). Then set the DataSource property of TDBGrid to DataSource1. If theActive property ofUniQuery isTrue, DBGrid will display data.

 

 

To close theTUniQuery use itsClose method or set itsActive property toFalse.

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 theFirst,Last,Next,Prior,Locate, andLocateEx methods of theTUniQuery.

 

Working with Fileds

 

TheTUniQuery 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 aValue property of TField or typed properties likeAsInteger,AsString, etc.

For example, you can copy data from theTUniQuery 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;

 

TheNext method sets the Eof property ofTUniQuery to True if it cannot move to the next record because there are no more records.

TheTUniQuery 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 clickTUniQuery component and selectFields Editor from the context menu.Fields Editor window will be opened. Right click inside theFields Editor window and selectAdd all fields from the menu. Now you will see the list of fields in the window.

 

 

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 useTUniTable to select data from one table. You don't need to write SQL statement forTUniTable. You set the TableName property andTUniTable automatically generates SQL statement to get data from this table.

Drop theTUniTable into the form and double-click the component to openTUniTable editor. You can enter value for theTableName property and forOrderFields andFilterSQL properties in the editor.

 

 

WhenOrderFields andFilterSQL properties are empty,TUniTable generates simple SQL statement like

 

SELECT * FROM emp

 

If you set values forOrderFields orFilterSQL, 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 theTUniQuery with UPDATE statement. Drop theTUniQuery 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 theExecute button. To execute the query at run-time, call theExecute method ofTUniQuery.

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 theTUniQuery editor, go to theParameters tab. Here you have to set DataType and ParamType for each parameter

 

 

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

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 ofTUniQuery component in your program code.

UniQuery3.Macros[0];

UniQuery3.MacroByName('MACRO1');

The value of a macro can be set by theValue property of aTMacro. 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 theEdit method to begin editing. Call thePost orCancel method to finish editing. If you callPost, the changes are passed to the database server. If you callCancel, 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.

·Run the test application.

·You can edit any cell in DBGrid linked to TUniQuery. TheEdit method called automatically, when editing starts. ThePost method is called, when another record is selected. To cancel your changes in the current record, press the ESC key.

A new record can be inserted by theInsert orAppend method. TheAppend method adds record to the end of dataset. TheInsert method inserts record in the current position. After one of these methods is called, you should assign values to the fields and call thePost 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 theDelete 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 theDebug property ofTUniQuery to True to see SQL statements of your query. For profiling in real-time you have to add theUniDacVcl 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 ofUniQuery1 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 theUpdatingTable 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 ofTUniQuery manually. In this case an additional query is not executed.

Complex queries

If you set a complex query to theSQL 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 theSQLInsert,SQLUpdate, andSQLDelete properties ofTUniQuery to set custom update statements. If you double-click one of these properties in Object Inspector, theUpdate SQLs page of theTUniQuery 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 theSQLUpdate 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

 

 

SQL generator

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

Click theGenerate SQL button. The update statements are generated and the editor changes the current page toUpdate 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 theStored Proc Call Generator page, select the stored procedure name, select the statement type and click theGenerate button.

 

 

Executing Stored Procedures

TUniStoredProc allows you to execute a stored procedure.

·Drop TUniStoredProc on the form and double-click it.TUniStoredProc editor will be opened.

·Enter the stored procedure name or select it from the list. For example, you can select "EMP_INS" procedure from the previous topic.

·When you move focus to another control or press theCreate SQL button (), the editor creates SQL statement for calling the procedure. You can see it in the box below the stored procedure name.

·If the procedure has parameters, they will be added to the generated SQL statement and to the Params property.

 

 

To call the procedure at run-time use theExecute method. You may also set the stored procedure name and generate SQL statement for calling the stored procedure at run-time. Call thePrepareSQL 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 twoTUniQuery orTUniTable 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 twoTUniTable components on the form. Set theName property of the first table to "DeptTable", andTableName property to "Dept". Set theName property of the second table to "EmpTable", andTableName property to "Emp". Set theActive property of both tables to True.

Drop twoTUniDataSource 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 theMasterSource property ofEmpTable to "DeptDS". Double-click theMasterFields property ofEmpTable 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 theAdd button. Click theOK button to close the dialog.

NowEmpTable displays only employes from the current department. If you change the current record inDeptTable,EmpTable is automatically refreshed and displays another employes.

When you setMasterSource forTUniTable orTUniQuery, itsSQL is automatically modified. Fields that you linked are added to theWHERE 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 theMasterFields property, just set theMasterSource 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. SetOptions.LocalMasterDetail to True forTUniTable orTUniQuery. 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 theSQL property ofTUniQuery:

{if ORACLE}

SELECT * FROM EMP1

{else}

{if SQLSERVER}

SELECT * FROM EMP2

{else}

SELECT * FROM EMP

{endif}

{endif}

To define macros at design-time, open theTUniConnection editor and selectMacros page. FillName andValue boxes at the bottom of the page. Then press theAdd button. You can use the added macro in IF directive or directly in SQL statements.

 

 

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 inCondition 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 ofTUniConnection 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 visithttp://www.devart.com/unidac/download.html. For information about getting the UniDAC, visit theHow to Order section. If you have a question about UniDAC or any other Devart product, [e-mail].

---

---

© 1997-2013 Devart. All Rights Reserved.