Delphi Direct ORACLE Access Components Files

Delphi Oracle OCI Components (DOCI) v1.6  
=========================================

Please contact author in case of questions. 
Any recomendations, suggestions and bug reports are welcome. 
   lasersquard@yahoo.com 
   lasersquad@tut.by


INTRODUCTION
------------

DOCI components are developed for geting direct access to Oracle server from Delphi 7 and later.

They use standard OCI (Oracle Call Interface) library. 
To use components you need Oracle Client installed on your computer.

Depending on your needs you can build either 32bit binaries or 64bit ones.
If you have installed 64bit Oracle Client you need to build 64bit version of DOCI components (because oci.dll from Oracle will be 64bits).
32bit DOCI components will not work with 64bit Oracle Client and vise versa.
You can have both 32bit and 64bit Oracle CLients installed on your laptop simultaneously.


FEATURES
--------

- DOCI components do not require BDE! There are no problems with different BDE versions.
- DOCI components can work with Oracle versions starting from version 8.
- There is a fully functionsl TOraSQL component - descendant of TDataSet. It implements all capabilities of TDataSet and adds new.
- Ccomplete implementation of ORACLE BLOB and CLOB column datatypes.
- Easy way to call stored procedures from your Delphi code.
- Possibility to execute parts of PL/SQL code without creating stored procedures.
- Number of records to be fetched from Oracle server per one network roundtrip can be customized (increase performance for large tables).
- There are fast and powerful tables in memory. TMemoryDataSet - compatible with Delphi DataSource and DBGrid, TAMemoryDataSet - table like storage of data.
- There are many nice classes for working with dynamic arrays to store and handle all Delphi data types 
  (search functions are implemented using assembler, therefore they are extremely fast)
- Sample applications show how to use DOCI components. Very helpful. 


DESCRIPTION
-----------

Project structure

```
delphioci/
├── examples/
│   ├── BkQuery/         # Shows how to run query in background
│   ├── ClientDataSet/   # Use DOCI components together with ClientDataSet 
│   ├── DBErrors/        # How to catch and handle DB errors
│   ├── EditableDataSet/ # Editable grid that post its changed into oracle database
│   ├── MemoryDataSet/   # How to use component that stores its data in memory
│   └── OraTypes/        # Load various system info from Oracle database
├── src/                 # all source files are here
├── tests/               # tests for DOCI components 
└── olddpk               # old .dpk files for compiling DOCI under old Delphi version 
```

DOCI package includes the following components
----------------------------------------------

TOraDB 
Establishes Oracle server connection, manages transactions. Other classes (such as TAOraSQL and TOraSQL) use this 
component to connect to the database. All OCI calls are concentrated inside this component.

TAOraSQL
Immediately makes OCI calls (SQL queries or parts of PL/SQL code). It is a fully functional component and can be 
used even in case TDataSet compatibility is not necessary. For example when we don't want to show results in TDBGrid. 
You can retrieve an any row by record number as an index. During one network roundtrip TAOraSQL fetches number of 
records specified in FetchCount property. When you work with large tables you may increase FetchCount value to get 
higher performance. By default FetchCount is 100 which corresponds middle sized tables. 
Method Open in this component doesn't actually fetch any data. To retrieve all records you can call ReadAll or OpenAll. 
Call ReadRecord to read up to specified number of records.
ReadAll - fetches all records returned by SQL statement.
OpenAll - combines Open and ReadAll. 
ReadRecord(RecordNum) - retrieves all records until the specified RecordNum. Records are fetched by portions of FetchCount size each.


TOraSQL
It is the wrapper over TAOraSQL and descendant of TDataSet. It is quite similar to TQuery and TStoredProc standard components. 
You can provide SQL query statement and fields' definitions (at runtime and design time). The usage of TOraSQL is very similar 
to Delphi standard database components. By default, TOraSQL reads as many records as TDBGrid requires. To read more records call 
OpenAll, ReadAll or VGoto. VGoto(RecordNum) - retrieves all records until the specified RecordNum. Records are fetched by portions 
of FetchCount size.

TAOraUpdateSQL
It is an component for providing database update functionality (INSERT, DELETE, UPDATE). Required SQL statements can be 
generated automatically using standard Delphi SQL query editor or set manually using the properties: DeleteSQL, InsertSQL, 
ModifySQL. TAOraUpdateSQL must be linked to TOraSQL. 


DOCI Files
----------

DynamicArrays.pas 
The set of fast and useful classes to work with dynamic arrays that are used by many other components. Also this unit can 
be employed solely as providing many powerful features. It does not depend on other DOCI units. 
                         
VirtualDataSet.pas
It is descendant of TDataSet that implements all required InternalXXXX methods of TDataSet. It is easier and more convenient 
to inherit your components from TVirtualDataSet because you need to override just several methods instead of huge number 
of InternalXXX ones. 

DataSetQuery.pas
It is descendant of TVirtualDataSet which implements 90% functionality of backend components. TDataSetQuery class is very 
convenient for developers as by overriding just few methods you can create good component for accessing any data 
(for example MemoryTable and OraSQL).

OraDB.pas - includes TOraDB class as a connection to Oracle DB and session manager. The analog in Delphi - TDatabase.

AOraSQL.pas - includes TAOraSQL, TAOraField, TAOraParam which are used by TAOraSQL.

OraSQL.pas - includes the main component TOraSQL.

OraDefines.pas - all required constants and definitions for OCI calls.

OraError.pas - includes class EOraError derived from Exception.

DOCIMessages.pas
String resources for error messages and other needs. Resource strings can be of any language. If you need messages on your 
language just create a copy of DOCIMessages_English.lang file, rename it accordingly, and translate strings in this file to 
your language. To include .lang file into DOCI build define appropriate lang symbol in DOCI.inc file and include your .lang
file into DOCIMEssages.pas similar as it is done with DOCIMessages_English.lang. You are done! 


INSTALLATION
------------

Open package DOCIPackage.dpk press "Compile" and then "Install".
Components should appear on "Data Access" page of Delphi components palette.
That's it.

For old Delphi versions .dpk files can be found in olddpk/ directory.

    
Methods and Properties
----------------------

Below are the major methods and properties.

TOraDB - session/transaction management(as TDatabase in Delphi)
   Properties:
     Active - open/close Oracle server connection
     DBLogin, DBPassword - user name and password 
     DBServer - SQL*Net connection string
   Methods:
    Open, Close - connect/disconnect.
    StartTransaction,
    CommitTransaction,
    RollbackTransaction - transaction control

  TOraSQL - queries to Oracle Server (descendant TDataSet)
   Properies :
     Database - TOraDB instance
     SQL      - query text (as usual parameters begin from ':')
     Params   - list of parameters. 
     FetchCount - for SELECT queries - the number of records to be fetched per one network roundtrip.
     Prepare  - perform preparation steps before SQL statement execution. 
                Useful in case of multiple query execution with different parameters values. 
                Not mandatory.
     UnPrepare - cancels prepare call. Not mandatory. 
     
    It is recommended to get access to data using the following methods:
     GetFieldValue - get field value. Much faster than FieldByName('').As... but returns a wrong 
                     value if a record being edited.
     GetFieldHArray - get pointer on array of values for the specified field.
     GetFieldNullHArray - get pointer on array of tags (boolean values that can be NULL or NOT NULL).
     ReadAll - fetch all records to the client.
     OpenAll - combines Open ad ReadAll calls.
     VGoto(RecordNum) - fetch records until the RecordNum record number is loaded from server.

  TAOraUpdateSQL - queries to edit (update, delete, modify) data
  The following three properties can contain a parts of PL/SQL code with variable and parameter
  definitions (parameters should begin from ':') 
    DeleteSQL - the SQL statement for delete data.
    InsertSQL - the SQL statement for inserting data.
    ModifySQL - the SQL statement for modify data.



 Work with BLOBs:

  If you work with TOraSQL you can use standard TDataSet methods to get access to BLOB fields.
   
  For writing BLOB data you must specify "FOR UPDATE" in SQL query. See ORACLE documentation 
  for additional information.


 TAOraSQL has some functions for direct access to BLOB fields:
    GetLobLength - get the size (in bytes) of defined BLOB field.
    ReadBlob     - read a piece of data from BLOB field to the local buffer variable.
    WriteBlob    - write data from a buffer to BLOB field.
    ReadBlobToStream - read all BLOB data into stream (TStream).
    WriteBlobFromStream - write data from stream (TStream) to BLOB field.


Examples
--------

Some very simple examples here.
For more complex examples see Examples folder.

  1.
   Get cursor from StoredProc:

    BEGIN :Result := GetData(12); END;
   
   Param Result has datatype ftCursor.

  2.
   For SELECT queries you should call Open/Close method, for the others - ExecSQL. 

   If you wish select some fields from one record of table (explicit query) you can use params:

    BEGIN SELECT Name, Index INTO :Name, :Index FROM Table1 WHERE ID = 6; END; 

   and get result as

    OraSQL.ParamByName('Name').AsString;
    OraSQL.ParamByName('Index').AsInteger;

   This works a bit faster than  

    SELECT Name, Index FROM Table1 WHERE ID = 6 

Additional examples can be found in "examples" folder.
doc/DOCI_DAQ.txt file contains answers to frequently asked questions.

 Best regards,
    Andrey Romanchenko. lasersquard@yahoo.com, lasersquad@tut.by.