Delphi Oracle OCI Components (DOCI) v1.5
=========================================
CONTENTS
========
Features
Contacts
Introduction
License
Common description
Methods and Properties
Examples
FEATURES
--------
- DOCI components do not require BDE! There are no problems with different BDE versions.
- DOCI components can work with Oracle versions 8..21c.
- There is a powerful TDataSet descendant - TOraSQL. It implements all capabilities
of TDataSet and adds new.
- There is a complete implementation of ORACLE BLOB and CLOB column datatypes.
- There is a capability for stored procedures execution.
- There is a capability for PL/SQL code execution without stored procedures creating.
- Number of records to be fetched from Oracle server per one network roundtrip can be customized
(additional performance on large tables).
- There are fast and powerful memory tables (TMemoryDataSet, TAMemoryDataSet).
- There are many nice classes for working with dynamic arrays to store and handle all types
of data (search functions are implemented using assembler, therefore is extremely fast!)
CONTACTS
--------
Components are tested and work good with Delphi 7 and later (till Delphi 12.1).
My contact e-mails:
lasersquard@yahoo.com
lasersquad@tut.by
INTRODUCTION
------------
Components were developed to get 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.
You can buil either 32bit component binaries or 64bit. Both of them work well.
If you have installed 64bit Oracle Client on your laptop - buil 64bit version of DOCI components.
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.
All recomendations and suggestions are welcome.
If you find a bug please drop me a letter. I will try to correct and send you a fixed version.
COMMON DESCRIPTION
------------------
The following data types are supported at the moment:
String,
Boolean,
Float,
Date,
Time,
DateTime,
Integer,
Int64,
SmallInt,
Word,
Currency,
BLOB,
CLOB;
These data types are supported in fields and in parameters.
Float и все Integer-like - everything is simple.
Boolean - as NUMBER(1) - 1 byte. Zero value = False, nonzero = True.
String - в памяти - as ANSI string of fixed size.
Date - stored as TTimeStamp.Date(Integer) - days since 1.01.0001.
Time - stored as TTimeStamp.Time(Integer).
DateTime- stored as TTimeStamp.
Cached updates are not implemented yet. If you need these features, send me
a letter.
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 in case TDataSet compatibility is not necessary,
for example when we don't want to represent results in TDBGrid. You can retrieve an
any row via record number as index. During one network roundtrip TAOraSQL fetches
FetchCount number of records. When you work with large tables you should increase
FetchCount property to get higher performance. By default FetchCount is 100 which
corresponds middle sized tables.
Method Open doesn't actually fetch any data. To retrieve data method ReadAll or OpenAll
or ReadRecord should be called:
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.
TOraSQL - is the wrapper over TAOraSQL for compatibility with TDataSet. It is quite similar
to TQuery and TStoredProc standard components. You can provide SQL query statement
and fields' definitions (at runtime and designtime). The usage of TOraSQL is 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.
ReadAll - fetches all records returned by SQL statement.
OpenAll - combines Open and ReadAll.
VGoto(RecordNum) - retrieves all records until the specified RecordNum.
Records are fetched by portions of FetchCount size.
TAOraUpdateSQL - is an object providing INSERT, DELETE, UPDATE functionality. The respective
SQL statements can be generated automatically using standard Delphi SQL query editor
or set manually to the properties: DeleteSQL, InsertSQL, ModifySQL.
TAOraUpdateSQL must be linked to TOraSQL.
DOCI Files:
DynamicArrays.pas - the set of fast and usefull 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 - 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 don't need to implement huge number of InternalXXX methods.
DataSetQuery.pas - 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 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 - some constants and definitions for OCI calls.
OraError.pas - includes class EOraError derived from Exception.
dOCIMessages.pas - supplies resource strings 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 dOCI7Package.dpk press "Compile" and then "Install".
Components should appear on "Data Access" page of Delphi components palette.
That's all.
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 open SELECT queries you should call Open/Close, 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 abit 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.
Download Latest Version
delphioci-1.5.zip (1.1 MB)
