Hope springs eternal...

[drndrn]

Ok I know, I'm kind of late.. the year is 2011 and yet I am also
working in Delphi 7
and encountered similar problems.

So.. for whoever it may concern...
The problem I presume is bad translations of the parameters form
ORACLE to the ADO component
(You must use ADO if you're running ORACLE >=9.01i instead of the much
stable BDE)

The Quick Fix ?
If you are declaring field Number in Oracle make sure you limit it to
NUMBER(9) so it is, the size of an integer I guess. Because any
greater value doesn't translate very well in Delphi. Then all is left
to do is fiddle with the parameter type (I found that for my case it
should be BCD go figure ??? )

In my example I was connecting EhLib MemTable with the EhLib AdoDriver
which in turn used an ADODB Conection aaaand was all connected with
regular DB DataSource
Delphi version 7

(Kill me please ! )

From all of the google in the world there was one document that helped
me the most.
And for the sole interest of me (in case I need it again) or for the
next poor fool in the year 2016
and is working in this ancient technology
i will copy it here,

<<<<<begin copy>>>>>>
Accessing Oracle using ADO in Delphi

Little, if any, of the Oracle literature on the this subject mentions
Delphi. Delve into Metalink and you will find several examples of
using ADO to access Oracle data from VB. The excellent white paper,
“Oracle Provider for OLE DB” by Jenny Besaw (30-November-2000)
available to Metalink subscribers, for example, exclusively uses VB to
demonstrate important aspects of using ADO.

There are useful Metalink references (for example document refs:
90351.1, 90016.1, 115096.1), but aside from the occasional nod in the
direction of C++, Delphi doesn’t get a mention. I can’t believe I am
the only person around who believes the Delphi IDE to be one of the
best on the marketplace. Oracle clearly like the Borland way of doing
things – after all JDeveloper is based on Delphi’s stable mate,
JBuilder. So, what could be so wrong in wanting to use Delphi to build
applications which access Oracle?

Well, nothing. There may not be much literature around, but it can be
done! The ADO encapsulation offered in Delphi is simplicity itself to
master, and, with a little perseverance, it can be made to interact
with Oracle. However, the lack of regular support means there is a
steep learning curve. Whilst talking in general terms about some of
the key issues regarding the use of ADO, this article aims to reduce
that curve a little.

So what is ADO?

Microsoft ActiveX® Data Objects (ADO) is a high-level object-oriented
interface to OLE DB data which presents the applications programmer
with a set of objects, including a database independent recordset.

Before the performance gurus amongst you begin to complain about
having extra layers of abstraction, we should recognise that there are
many occasions when using native Oracle drivers may not be
appropriate:

 An application which accesses different database platforms
simultaneously
 An application which needs has the ability to be used with any
customer-defined database back end
 In an environment where (expensive) C++ programmers with low-level
database experience are hard to acquire, but where Delphi or VB skills
are less hard to find

In any case, the performance overheads involved here are not in the
same order of magnitude as in the bad old days of early ODBC drivers!

Despite this being a Microsoft “standard”, which will therefore be
worrisome to many developers, the other benefit of using ADO is that
you can assume that the database access layer is already in place,
provided your clients are running MS Windows, because ADO and OLE DB
are supplied by Microsoft and installed with Windows. No need to worry
about installing extras like the Borland Database Engine (BDE) on
client machines together with your application. An ADO-based
application requires that ADO 2.1 be installed on the client
computer.

General ADO Programming Model

In Figure 1 the ADO object model is to the left of the provider and
italics under the ADO object names are the Delphi components which
contain these objects.

Creating a separate TADOConnection component is not a requirement as
you can specify the connection information on a Command item. However,
this is not often to be recommended because, with a TADOConnection,
you can control the attributes and conditions of a connection for
every command object in the application. Having created a Connection,
the developer can set properties to control several important aspects
of the connection including the cursor location (client or server) and
connection timeout. Furthermore, using the Connection’s associated
methods, it is possible to implement transaction processing and
retrieve metadata about the database to which this component connects.

The two main Command objects used for returning datasets are TADOQuery
and TADOStoredProc. The former allows the developer to set the SQL
property (a list of strings) to any valid SQL query. If this is a
SELECT, then a recordset will be returned. For commands not returning
data, such as DDL, or calls to Stored Procedures which don’t have OUT
parameters, the TADOCommand can be used to reduce the overhead of
using a dataset component.

A TADOStoredProc can return a recordset by declaring the OUT parameter
as a REF CURSOR. Alternatively a single value OUT can be picked up
from the Tparameters property after setting the appropriate IN
parameters and calling the ExecQuery method . This technique of
returning data to an application is examined in the section on stored
procedures, below.

OLE DB

Real world information systems are increasingly made up from a series
of different technologies. Organisations may have both Mainframes and
client-server systems, each with different databases and applications
on them. Furthermore, there is often a need for the PC desktop to be
the presenter of information.

OLE DB responds to this scenario by setting out a specification for a
set of low-level data access interfaces to work against many types of
data store in a standard way. It is possible to call OLE DB methods
directly, but the ADO encapsulation hides the low-level complexities
and lets the developer concentrate on application building.

The first choice to be made by the developer is which provider to use.
When they launched OLE DB, Microsoft made a developer kit available so
that database vendors, and others, could create their own OLE DB
providers. However, they also provided an OLE DB provider specifically
for Oracle.

Oracle have since produced their own OLE DB providers. Use of an
Oracle provider is preferred because it is a native OLE DB provider
and gives access to Oracle-specific database features, such as support
for LOBs, PL/SQL stored procedures, and REF CURSORs. Whilst Client
needs to be 8i or higher, you can use OLE DB to access data from
Oracle databases of version 7.3.4 or higher. Only one version of
OraOLEDB can exist on a machine at a time, and, because it is COM-
based, OLE DB is unable to cope with multiple Oracle homes.

Connecting

In Delphi the ADO Connection object is encapsulated by the
TADOConnection component. By using a single ADOConnection you can
provide access to the database for all your ADO queries and stored
procedures.

A connection string is made up of several items, separated by semi-
colons:

Provider=OraOLEDB.Oracle.1;Password=a_password;Persist Security
Info=True;User ID=a_user;Data Source=sms7;Extended
Properties="plsqlrset=1"

The PLSQLRSet property is not well documented. It is an “extended
property” of the connection data type, and its default value is zero.
However, if you wish to return datasets from stored procedures the
value should be one. On top of it being less than well documented,
failure to set this property results in a rather unhelpful error
(ORA-06550: Wrong number of type of arguments in call) that gives the
novice developer little clue that the problem is with the connection
string!

Delphi is happy for you to use a Data Link (.UDL) file instead of a
connection string. Not hard-coding the connection string at design
time has obvious advantages in terms of flexible deployment. In theory
you could write your application code such that it gets its connection
information from a file in a specific location on the client machine,
and then not care what provider the client was using. In practice,
testing your application against any provider it is likely to
encounter is essential, as there are differences in implementation
between providers.

A UDL file is a Microsoft Data Link file. The extension is
automatically recognised by Windows, and the a new UDL can be created
in Windows98 by selecting File, New, Data Link File from Explorer,
which launches a wizard to help the user fill in the required
information. NT does have the association, but you can’t use File, New
to create a new UDL. Instead, use your editor to create an empty file
and give it the UDL extension. Then right click on that file in
Explorer, and select Properties. This will launch the same wizard.

Fill in the required information and save the UDL. The default place
to save this is Program Files\Common Files\System\OLE DB\Data Links.
If you fail to give the full path when declaring the file name, the
application will look in this directory for the file.

Steps to Creating a connection:

Create the UDL file: Select the provider from the first tab. Use
Oracle Provider for OLE DB, subject to the comments earlier. Then fill
in the Connection tab, and click the TEST button. Here we have an
example of connecting to an Oracle database called SMS7, as a user
called A_user:

Do not forget to set the “plsqlrset=1” (by double clicking “Extended
Properties” on the All tab) if you intend to return record sets from
stored procedures:

In Delphi: Create an Object of type TADOConnection. Start by building
a data module to hold the database access objects. From the ADO tab of
the Component Palette, add an ADOConnection, then press F11 to edit
the properties:

Beware of setting Connect:=True at design time. Delphi rather
unhelpfully bombs out without warning when you open the form with an
active Connection Object on it if that connection cannot actually be
made for any reason. You then need to resort to using a text editor to
manually change the property value in the .DFM file, before restarting
Delphi.

The best solution is to set this property at runtime, perhaps as part
of the OnCreate processing for the form. In addition, if this is right
for your particular application, you could log the user in, saving
them from having to log in directly to Oracle. If you explicitly log
in for the user the ADOConnection.LoginPrompt property should be set
to False.

In figure 5 the connection is made at runtime when the data module is
created, by calling the Open method of the PesonnelConnection object.
A message dialogue is displayed to the user if the connection fails
because the call to Open is made from the safety of a TRY block. Not
dissimilar to the PL/SQL EXCEPTION block, the developer can use the
EXCEPT block to handle all exceptions, or specific ones. In this case
any exception will generate the message to the user.

Now we have a connection, we can begin to work our database. The
examples that follow return data to a simple Delphi form in a variety
of ways. There are arguments about whether or not to use stored
procedures to return data, but they will not be aired here. Instead, I
just give examples of using both queries and stored procedures, and
leave the debate to another time.

Recordsets

Recordset objects are at the heart of ADO. The Recordset holds a set
of rows (or records) and columns (or fields) which can contain
selected data that the user can manipulate. It can communicate with
the underlying database to allow live editing, deletes and updates.
However, don’t forget that the limits of what you are able to do can
be set by the functionality of the data provider, rather than by the
ADO model itself.

Data Types

The fact that different databases and programming languages deal with
data types in no standard fashion is often the biggest single headache
for database professionals, and using Delphi with Oracle is no
exception. As usual, dates can be troublesome. By making good use of
the TO_DATE (Oracle) and DateToStr (Delphi) functions we can easily
get round this problem by passing Varchar and String representations
of dates between the environments.

What you might not expect to be a problem, however, is the simple
INTEGER. Unfortunately, even numbers can be a problem. The crux of the
problem here is Oracle’s preference for storing integers as
NUMBER(38). Delphi gets confused when it is passed an integer which is
so large and tries to help by assuming that the value is Binary Coded
Decimal (BCD). When you see the error displayed in Figure 6, you know
this is your problem.

Figure 6 was generated by just letting the default actions take place
using an ADOTable to access the Oracle table defined in Figure 7.

There are two ways around this problem. If you are creating the
underlying database as part of the application build, then explicitly
define the size of the number in the create statement:

CATALOGUE_NUMBER NUMBER(9) NOT NULL

This simple step means that you can use integers in your application
and have no problems dealing with what Oracle passes over.

However, if you are less fortunate, and are building an application
sitting on top of a live database in which some columns have been
defined as NUMBER, you need to alter the way Delphi treats Oracle
data. Instead of allowing Delphi to generate recordset fields
automatically, use the fields editor to create a persistent field
object of the same name as the offending NUMBER field, and amend the
FieldType to TVariantField. However, arithmetic operations cannot be
performed on numbers accessed through a TVariantField object. If this
is required, one solution is to create a client-side calculated field
(again using the fields editor) and then convert the variant when an
OnCalcFields event occurs (Cat_Int in Figure 8).

Tables and Queries

The simplest way to create a recordset from an Oracle table is the
TADOTable. By setting the TableName property, by default, you
effectively get a “Select * from tablename” recordset which is
editable. If the user amends the recordset the source database is
updated and committed. By default, OraOLEDB is in an autocommit mode.
Setting ReadOnly:=True prevents live editing. TableName can be
selected from a dropdown list of all tables visible to the connection
if you set the Connection property to point at a valid ADOConnection
object. In figure 9 we are using the PersonnelConnection we created
earlier.

When wanting to return a recordset based on an SQL SELECT, use the
TADOQuery component. You can actually write any valid SQL statement in
the SQL property, including DDL. However, the TADOCommand component
is preferred for any SQL which will not result in a recordset as it is
a simple component and does not carry the overhead of being a dataset.

Many properties are shared by ADOTable and ADOQuery (Figure 9). In
general, however, the ADOQuery component should not be used to allow
the user to edit data. If the recordset is as a result of a join, the
recordset will be Read Only anyway with some providers.

WDEQD

As an example of ADO functionality being limited by the provider, the
LockType property makes it look as if you can opt to use pessimistic
locking. Indeed, you can actually set that property :=ltPessimistic,
but Oracle Provider for OLE DB would treat it just the same as the
default ltLockOptimistic. LockType values supported are: ReadOnly,
BatchOptimistic, and Optimistic. Similarly, setting CursorType to
ctOpenKeyset or ctDynamic is not supported by Oracle Provider for OLE
DB.

Generally the default settings for these two properties are fine, but
for performance reasons you may consider changing them. One early
decision to be made is where the cursor processing should take place.
The default is for it to be at client-side. This is usually preferred
as it allows flexibility to manipulate the recordset in ways not
supported by the Server. However, in the case of large recordsets
which may make a client PC struggle, you should set CursorLoction to
clUseServer.

If the cursor location is UseClient you must use a Static cursor which
takes a copy of the rows for local use. If your cursor is located at
the server, you may use a ForwardOnly cursor which will improve
performance but, as the name suggests, it constrains the application’s
ability to navigate the dataset.

In both cases here the default value for MaxRecords of zero is set,
which means there is no limit to the number of records returned.
Setting this to a positive value to prevent too much data being
returned to the client can be useful, but more useful still is the
CacheSize property. This defines how many rows to retrieve at one time
into local memory. Set this to 50, for example, and when the dataset
is first activated the provider puts the first 50 rows into local
memory. As the row pointer is moved through the recordset, the
provider retrieves the data from the local memory buffer. Statement-
level read consistency is maintained so that data brought to the local
buffer from the server excludes any changes made to it by concurrent
users.

In the ADOQuery object inspector, double-clicking the SQL property
pops up a code editor to help create a list of strings containing the
SQL which will generate a recordset (see Figure 10).

Dynamic SQL can be generated using the parameter property. In the
example, the value of :dc can be bound at runtime, allowing the user
the potential to select a value. The parameter dc has been defined in
the Object Inspector. The value of 1.5 has been set, but a user-
provided value could be used. The code in Figure 11 takes the value a
user has entered into an edit box (named eCost) and then re-queries
the database by calling the OPEN method.

Stored Procedures

Stored procedures are a feature of many RDBMS, and the ADOStoredProc
Delphi component allows the developer to use server-side PL/SQL
procedures and packages and make use of existing business rules.

Mastering parameters is the key to the successful use of
ADOStoredProcs. For each procedure the developer should know how many
parameters there are, in what order they appear in the parameter list
definition, which direction they take (IN, OUT and INOUT are
supported), and what type they are.

In Figure 12 we have created an ADOStoredProc which calls the Oracle
stored procedure called InsertACD. We have created 3 parameters which
will get passed to the server. PTitle and pCost are IN, and will be
used in an INSERT, and pMaxVal is an OUT. Figure 13 shows the
procedure definition:

Note that the naming of the parameters on ADOStoredProc object is
irrelevant: the OUT parameter is called pMaxVal in the ADO object, but
pMaxCost in the PL/SQL. Parameters are passed by position, not by
name. The snippet of code in Figure 14 demonstrates the setting of the
IN parameters and the picking up of the OUT parameter. ADOStoredProc
parameters can be referred to using the ParamByName method, or by
directly picking the parameter number (the first parameter being 0).
Both techniques are used here.

When ExecProc is called the PL/SQL procedure is run and a new row is
inserted into the CD2 table. The highest daily_cost is then bound to
the OUT parameter and that is picked up by the final line of code
which places the value in a text box on a Form.

Because Oracle Provider for OLE DB allows procedures to have an
argument of REF CURSOR type, using the TADOStoredProc component
enables the developer to return read-only recordsets from stored
procedures. There is no predefined datatype for REF CURSOR in the OLE
DB specification so you do not bind this parameter. Instead, the
recordset is automatically created after a successful call to OPEN,
and an ADOStoredProc component then acts like any other recordset-
based component.

On the Oracle side of the equation we now need to define a REF CURSOR
type. Using a weakly typed cursor gives the flexibility to return
different types of row with the same cursor, though that makes the
process more prone to error. In the example below we create a weak
type in the package head, called CD_Cursor. That type is then used by
an OUT parameter in the procedure CDLIST. Alternatively, we could have
defined all the cursors we will use in an application in one CURSORS
package.

In the body, the call to OPEN pList FOR, followed by a SELECT
statement, gives us our required cursor, which is an OUT parameter.
However, we can simply treat the ADOStoredProc as a recordset after
the call to Open. Figure 16 demonstrates this by calling the Last
method to position the current record pointer at the last record of
the recordset. This also illustrates runtime creation of parameters,
using
CreateParameter(paramname,paramtype,paramdirection,paramsize,paramvalue)
with code, rather than the Object Inspector. By using data-aware
controls, through a TDatasource, these records could be displayed to
the user, perhaps in a grid, or a listbox.

Creating editable data using stored procedures is more complicated.
One relatively simple solution is to copy the generated recordset into
a local memory-only table, and allow the user to manipulate that by
attaching a TDatasourse to it. Once the user has finished, you can
then call a stored procedure to carry out the updates, inserts and
deletes.

Errors

Reference to the ADO programming model (Figure 1) reveals that there
is an Error object. In fact, handling the error object is something
that Delphi does do very badly. There is next to no documentation, and
what little there is tries to frighten the user off with comments
like: “Using the Errors Collection object directly is not recommended
unless you are familiar with connection object operations. Consult the
Microsoft Data Store SDK help for specific information on using ADO
Errors Collection objects”.

Maybe so, but it is nice to have some control over what you hit your
user with when things go wrong. In actual fact
ADOConnection.Errors.item has a property called Description which is
very useful. In the snippet below (Figure 17) there is an example of
simply capturing the returned error in part of a TRY block exception
handler. In this case the stored procedure InsertACD is fired. If it
fails, the MessageDlg function is called to pop up a message to the
user which warns that the commit has failed and includes the error
message returned to ADO by Oracle.

Conclusion

Although the fact that ADO is a Microsoft initiative may put many
developers off, there is much to be said in its favour. The complex
OLE DB calls are nicely hidden by the ADO encapsulation, and a
developer can rapidly develop data-aware applications which sit on
many different data sources, including non-relational databases and
spreadsheets.

As usual, Borland’s version is easy to use, particularly if you are
already a Delphi user. ADO functionality comes with Delphi
Professional Version 6. A 60 day trial is currently downloadable
(http://www.borland.com/delphi/tryitnow.html). That said, nothing
covered in this article is Delphi specific. Whatever your preferred
IDE, using ADO can help application builders create data-aware RAD
solutions quickly, and effectively.

Author ©Peter Lake

<<<<End Copy>>>

And for those In 2016 I have one word for you :
RUN!