New Features and Bug Fixes in Version 1.20 of ODBCExpress
=========================================================
New Features
------------
1. Addition of .ParamByName and .ColByName methods to THstmt. Instead of
specifying question marks as parameters for a SQL statement (although this
is still allowed), you can now make use of the standard colon-identifier
(e.g. :SNo) to indicate a parameter in a SQL statement. These parameters
are parsed out into the ParamNames stringlist, and ParamByName(identifier)
will return the corresponding parameter number to be bound. The ColNames
stringlist contains a list of the column names for the current result set
and .ColByName(identifier) will return the column number of the column to
access, given a column name.
2. A number of methods are now provided to simplify parameter binding. For
example, instead of using Hstmt.BindParam(3, SQL_C_SLONG, @Number), you can
now use Hstmt.BindInteger(3, Number). To illustrate ParamByName, you can
instead use Hstmt.BindInteger(ParamByName('Age'), Number), or
Hstmt.BindIntegerByName('Age', Number).
3. A number of methods are also now provided to simplify the referencing and
changing of data in a result set. For example, instead of using
Number:= Integer(ColValue(3)^), you can now use Number:= ColInteger[3]. To
change a value, instead of using ToValue(s, @ColValue(2), SQL_C_CHAR) and
ColSize[2]:= Length(s) to change for example a string value, you can now in
one step change the value using ColString[2]:= s. To illustrate ColByName,
you can instead use ColString[ColByName('Name')]:= s, or
ColStringByName['Name']:= s.
4. Bookmarks have been implemented and the method FetchBookmark allows you to
retrieve the bookmark of the current row in the result set and later return
to that row, for example Number:= Hstmt.Bookmark, and you can later use
Hstmt.FetchBookmark(Numver) to return to that row.
5. A postional insert method .DoInsert is also now provided to allow you to
insert records at the data source, in the same way you would use DoUpdate
to update a record.
6. The addition of a BlobSize property to THstmt. Some ODBC drivers (e.g. the
SQLAnywhere and Oracle drivers) aren't able to return the size of a blob
column, before it is retrieved. Since ODBCExpress creates memory on the fly
before blob columns are retrieved, this obviously represents a problem if
the blob size to be retrieved is larger than the default amount of memory
allocated. By setting the blobsize property large enough (e.g.
BlobSize = Max(DataLength(BlobCol)) ), enough memory will be allocated to
retrieve the blob in one go.
7. Automatic detection of supported functionality of a data source is now used
to switch between Level2 and lower level ODBC calls. For example, in v1.1
you couldn't use Hstmt.BindParam(3, SQL_C_SLONG, @Number) with Access (or
most other flatfile databases), but had to use
Hstmt.BindParamCore(3, SQL_C_SLONG, @Number, SQL_INTEGER) to skip Level 2
functionality. Now Hstmt.BindInteger(3, Number) works in both cases.
8. A new combobox type component, TOEDataSource, is added which enumerates
available ODBC data sources.
9. The default ODBCError error message is now fully descriptive, i.e. you
don't have to trap the error to get a descriptive error message displayed.
Also, error handling is also improved in this version. An instance of the
exception class EODBC is created for each ODBC error that occurs, and each
instance can be used to retrieve the error information specific to the
instance, instead of the Henv.Error object. For example, you would trap
and display an error as follows:
on E: EODBC do
E.Diplay;
10. You are now able to use the Hstmt object as a high speed, multi-row
fetching component! This means that in a single fetch statement you can
fetch any number of rows (specified through the new RowSetSize property)
from the backend, and all the rows are fetched using a single fetch call to
the backend! Additional methods are provided to cater for this, for
example CellInteger[Col, Row] instead of ColInteger[Col]. The Cell methods
allows you to access column number Col of row number Row in the current
RowSet. ColInteger[Col] will be equivalent to CellInteger[Col, 1]. You
can also assign to the Cell methods, which means that you can change a
large number of rows before calling the DoUpdate method to update all of
them to the database at the same time (this is similar to the BDE's cached
updates, but applies only to the current RowSet, therefore being of a
greater granularity, which greatly improves concurrency).
11. Since multi-row manipulation with the Hstmt object can now be done by the
developer, we can now replace the Hdbc property of the grid and listbox
with a Hstmt property. Multi-row functionality has always existed in the
Hstmt object and is utilized by the grid and listbox, but was hidden from
the developer. This prevented us from exposing the Hstmt object
encapsulated by a grid or listbox.
12. In 32bit Access and other flatfile databases that supports the
functionality, you can now manipulate blobs larger than 64k, just as easily
as for large SQL databases. Blobs can also be updated using the DoUpdate
method in databases that support the 'UPDATE..WHERE CURRENT OF '
SQL syntax, such as MS SQLServer.
13. Formatting of both editable and non-editable columns are now done at Hstmt
level. For example, you would format column 3 of type double to two
decimal places as follows:
Hstmt.FormatStyle:= fsFloat;
Hstmt.FormatMask:= '#.##';
Three format styles fsFloat, fsDateTime and fsCustom are provided, and the
format mask specified in each case corresponds to the format masks passed
to the FormatFloat, FormatDateTime and Format methods of Delphi
respectively.
14. The OEGrid now contains a parallel structure, which allows you to specify
non-visible columns. It also has two new properies, AutoSize and AutoTitle
(both true by default). Respectively they specify that a column with no
title must be automatically sized and named.
15. The Hdbc object has a new LoginTimeOut property and the Hstmt object a new
QueryTimeOut property to allow you to specify timeouts for respectivily
connections and query processing.
16. The OCL is now splitted into the ODBC Class Library (OCL), which only
contains the non-visual classes and the ODBC Visual Component Library
(OVCL), which contains the visual data-aware components. This is done with
the intention to optimize projects which doesn't make use of the visual
components.
17. Build-in ODBC driver functionality testing is now done in ODBCExpress to
allow for non-critical ODBC functions to be automatically skipped if they
are not supported by a specifoc ODBC driver.
18. Full support for stored procedures are now provided. Previously
ODBCExpress only supported stored procedures which could have input
parameters and may return a result set, for example:
with Hstmt do
begin
SQL:= '{CALL TestInProc(?)}';
Prepare;
BindParam(1, InParam);
Execute;
while FetchNext do
ShowMessage(ColString[1]); //display results
end;
Now you can also use stored procedures with output and input/output
parameters. For example:
with Hstmt do
begin
SQL:= '{CALL TestInOutProc(?)}';
Prepare;
ParamType:= SQL_PARAM_INPUT_OUTPUT;
BindParam(1, InOutParam);
Execute;
ShowMessage(InOutParam); //display result
end;
Bug Fixes
---------
The following bugs, which existed in version 1.10, is now fixed in
version 1.20:
1. The "nil parent" bug, which didn't allow the non-visual classes Henv, Hdbc
and Hstmt to accept nil as a parent on creation. The following statements
are now all valid statements:
Henv:= THenv.Create(nil);
Hdbc:= THdbc.Create(nil);
Hstmt:= THstmt.Create(nil);
2. For databases that support positional updates of blobs, changes in the
TOEImage component sometimes were not updated to the database when
.DoUpdate was called. The .DoUpdate method will now correcly update the
TOEImage component to the database.
3. Only long text fields (> 254 characters) worked with TOEMemo. Now all
database text fields are allowed in TOEMemo, even if they are limited to
254 characters or less.
4. The .DoRefresh method didn't refresh the values displayed in single-record
OE visual components (e.g. TOEEdit). It now works for all the OE visual
components.
5. When a cell in a row of the grid to be updated was empty, an access
violation occured when .DoUpdate was called. Now empty cells are written a
null columns to the database when .DoUpdate is called.
6. Some other small bugs not documented here have been fixed during the
development of version 1.20.