New Features and Bug Fixes in Version 5 of ODBCExpress
======================================================
New Features
------------
New features introduced since ODBCExpress 4.53:
1. Full result set caching is added to the TOEDataSet:
- By setting a single property Cached, you can now easily swap between using
a back-end result set or caching the result set on the front-end.
Typically a front-end cache is used where the result set is relatively
small and where direct user interaction is required.
- Cached updates is also supported and can be set via the CachedUpdates
property. The related ApplyUpdates, CancelUpdates, FlushBuffers and
RevertRecord methods and the UpdatesPending and UpdateStatus properties
are included. Though no UpdateMode property is provided, the update mode
is key-only by default and can be changed to all the fields or a subset of
the fields using the Target property.
- The FetchAll method will fill the cache with all the rows in the result
set. The RecNo property can be used to determine the current row number,
as well as position to a specific row in the result set. You can even use
negative values with the RecNo property to position to a row at an offset
from the end of the result set. Even though RecNo is supported when using
the cache, the IsSequenced property will always return False to avoid
misinterpretation of the property by the virtual TDataSet.
- The OnInsert, OnUpdate and OnDelete events are replaced by the single
OnUpdateRecord event. An OnUpdateError event provides control over rows
which cannot be updated successfully. These events are similar to the
BDE's TQuery ones. The OnRefresh event is also replaced with the
OnRefreshRecord event.
- Blob-deferral functionality in the TOEDataSet is equivalent to the
functionality controlled by the CacheBlobs property of the BDE's TQuery.
- The Hstmt.RowSetSize property is now also provided for use together with
the cache to retrieve multiple rows from the back-end at a time. This is
implemented transparently and does not affect the use of the DataSet's
Fetch methods.
2. The latest ODBC API v3.51 is fully implemented.
3. The following new functionality is added to the handle components:
- The SQL Parser is modified to skip colons inside string literals while
parsing the SQL for parameters. For example, the following SQL statement
will now work as-is under ODBCExpress and does not have to be padded by
double colons where single-colons are required:
UPDATE Files SET FileDate = '01:02:03'
WHERE FilePath = 'c:\programs\myfile.txt' AND FileAttr = :FileAttr
This functionality filters through to the TOEDataSet. Double-colon
padding is not supported anymore.
- Dynamic-length string and binary column and parameter support have been
added. This is not only more efficient on memory usage, but provides
direct support for limited length data types larger than 256 bytes, for
example the Oracle VarChar2 type. These dynamic fields and parameters are
also supported by the TOEDataSet.
- Binding stored procedure parameters by name at the back-end are now
supported for both the THstmt and TOEDataSet. This allows you to declare
stored procedure parameters in any order in the SQL statement, and is
controlled by the BindByName property of the Hstmt.
- A RowCountMethod property is added to allow you to control the way the row
count is determined by the THstmt or the TOEDataSet. It can be set to one
of the following values:
rcFunction: Uses an ODBC function to determine the row count, or one of
the following methods if not supported.
rcCustom: Allows the user to determine the row count via the
OnRowCount event. In a cached TOEDataSet, this option fills
the cache with all the rows in the result set and then
returns the number of rows in the cache. If the OnRowCount
event returns a value < 0, or the OnRowCount event is not
implemented, then one of the following methods are used.
rcSelect: Performs a SELECT Count(*) approximation, or the following
method if not supported.
rcTraverse: Returns an approximation by creating a temporary result set
and counting the rows in this result set by retrieving them.
If ODBCExpress detects (in the above order) that a method is not
supported, it will automatically attempt the following method. This
property is useful for skipping a supported method which does not return
the correct RowCount (sometimes caused by using it together with incorrect
cursor types).
- Two methods ColStream and CellStream have been added which allows you to
retrieve blob fields from the database directly to a file or any other
TStream descendant, without caching them in memory as well. These methods
is of course best used together with blob deferral.
- The THdbc's OnConnect and OnDisconnect events have been replaced by the
BeforeConnect, AfterConnect (the old OnConnect), BeforeDisconnect, and
AfterDisconnect (the old OnDisconnect) events. The THstmt's OnPrepare,
OnExecute and OnFetch events have been replaced by the BeforePrepare,
AfterPrepare (the old OnPrepare), BeforeExecute, AfterExecute (the old
OnExecute), BeforeFetch and AfterFetch (the old OnFetch) events.
- The Close and CloseCursor methods of the THstmt have been made virtual to
allow them to be extended in the same way as the Init and Terminate
methods.
- Alternative DoRefresh functionality is added in addition to the existing
refresh functionality via an ODBC method, which is not always supported or
applicable to all levels of SQL Generation.
4. The following new functionality is also added to the TOEDataSet:
- The virtual FindRecord method is implemented which allows you to use the
FindFirst, FindLast, FindNext and FindPrior methods as well as the Found
property. The performance of these methods are best when used together
with the cache.
- Multiple result set support is added to the TOEDataSet. Calling the
NextResultSet method will initialize processing for the next result set
and close and re-open the DataSet to display the next result set. The
NextResultSet method also provides a method for completing the processing
of stored procedures executed with some ODBC drivers so that the return
and result values of the stored procedures can be accessed.
- Automatically-created parameters now have data types by default (instead
of ftUnknown).
- The TFieldDef.Required property is implemented (in addition to the
DataType, Precision and Size properties).
- Similar properties have been added to the TOEParam object:
DefRequired: Indicates whether a parameter is nullable or not.
DefDataType: Indicates the default data type of a parameter.
DefPrecision: Indicates the scale of a parameter.
DefSize: Indicates the defined size of a parameter.
- If Editable is set to True and an updatable concurrency is required, the
concurrency is changed accordingly.
- Current-row refresh functionality is fully implemented via the Refresh
method. This differs from the BDE's implementation of the Refresh method
which applies to all the visible rows.
5. The following new functionality is added to the TOEQuery:
- The StoredProc property is renamed to StoredProcName to ease porting code
from the BDE's TStoredProc component. The TOEQuery (and TOEDataSet)
component is not only constructed to be as close as possible to the BDE's
TQuery component, but also to incorporate appropriate functionality from
the BDE's TTable and TStoredProc components.
- The TQuery-like CacheBlobs property is added as an alternative to the
Hstmt.BlobDeferral sub-property and the TQuery-like ParamBindMode property
is added as an alternative to the Hstmt.BindByName sub-property.
6. The following new functionality is added to the non-visual components:
- The TOESchema and TOECatalog property naming have been reconciled and
improved:
TSchemaColumn.ColName -> TSchemaColumn.ColumnName
TSchemaColumn.ColType -> TSchemaColumn.DataType
TSchemaColumn.ColSize -> TSchemaColumn.Precision
TSchemaColumn.ColFlags -> TSchemaColumn.Flags
TSchemaColumn.ColDefault -> TSchemaColumn.Default
TCatalogColumn.Name -> TCatalogColumn.ColumnName
TCatalogColumn.TypeName -> TCatalogColumn.DataTypeName
TCatalogColumn.Length Removed
TCatalogColumn.Default Added
TCatalogColumn.Remarks -> TCatalogColumn.Description
TCatalogTable.TableRemarks -> TCatalogTable.Description
TCatalogProcedure.ProcedureRemarks -> TCatalogProcedure.Description
- The methods ParseForeignKey and ParseIndex are added to the TOECatalog to
allow easy parsing of foreign key strings (into ColumnName, ForeignOwner,
ForeignTable and ForeignColumn) and index strings (into IndexName, Unique
and ColumnNames) returned by the TOECatalog.
- A property NameConstraints is added to the TOESchema to allow alternative
SQL Generation for table constraints set up in the component. This allows
generation of "CREATE TABLE" SQL compatible with a wider range of
databases, e.g. file-based databases such as MS Access, dBase, FoxPro and
Paradox. This includes primary key, foreign key, nullability and
uniqueness constraints.
- The TOESchema design-time property editor interface is also improved,
while primary key, foreign key, index and default value information can
now also be retrieved from the database into the property editor.
- The default script extension of the TOESchema is changed from .OES to .SQL
to be more compatible with other SQL scripting tools.
- The TOESetup component is extended to allow you to set the Driver and
Translator sections of the .INF file to be installed. This allows you to
install multiple ODBC drivers using the same .INF file at run-time.
Bug Fixes
---------
The following bugs, which existed in ODBCExpress 4.53, are now fixed in this
version:
1. The implementation of the latest ODBC API v3.51 fixed the string translation
problems which existed with ODBC drivers which already implemented this
version of the API, e.g. the SQL Server 7 ODBC driver.
2. The following problems which existed with the handle components are fixed:
- A blob re-fetching problem occurred with the CellMemory property and a
blob-fetching problem occurred with the CellString property.
- String, binary and date/time precision problems existed with some ODBC
drivers such as dBase and FoxPro.
- The RowsFetched property did not return the correct value for some ODBC
drivers (e.g. MS Access) when a prepared statement was re-executed.
3. The following problems which existed with the TOEDataSet are fixed:
- Deferred blob fetching did not work correctly.
- A stored procedure SQL generation problem when the stored procedure
contained no parameters.
- The TField.OldValue property did not work correctly with blob fields.
- A problem where numeric fields retrieved via some ODBC drivers (e.g.
Oracle) were incorrectly detected as currency fields.
- The design-time parameter editor sometimes caused an "invalid argument to
date encode" error when used together with date/time parameters.
- A problem in which refreshing a row with its current values at the
database did not work if the row was already modified in the DataSet.
- Some unnecessary calls to the Refresh method after a Cancel or Delete
operation were made.
- The MoveAbsolute(0) case was not handled.
- A problem with the initial size of a parameter caused "invalid parameter
size" errors with some ODBC drivers. This problem still exists with some
ODBC drivers in certain cases, but can be avoided or can be attributed to
an ODBC driver implementation problem.
- A problem with the parsing of a table name existed when the table name was
the first element of a new line in the SQL property editor.
- The AutoCalcFields property was not exposed.
4. The following problems which existed with the TOEQuery are fixed:
- A default-value problem where the value of LocateInsert was always True
and could only be set at run-time and not at design-time. The default
value of LocateInsert is now the same as for the TOEDataSet, which is
False instead of True.
5. The following problems which existed with the non-visual components are
fixed:
- A problem in which some ODBC drivers (e.g. SQL Anywhere and Interbase)
returned tables of all types (including system tables), no matter what the
TableType setting of the TOECatalog was. This fixes the tables listed by
the table name property editors of all the other ODBCExpress components as
well.
- A "list index out of bounds" error sometimes occurred when using the
WriteToFile functionality of the TOESchema property editor and an empty
top row sometimes appeared after retrieving tables or views into the
property editor.
Comments
--------
Some important comments on the new version:
1. Please report any problems experienced with this new version to
support@odbcexpress.com so that it can be addressed as soon as possible.
2. Apply the Delphi 4 Update Pack #2.
3. There is a serious threading bug in Delphi 4 caused by the incorrect setting
of the IsMultiThread global variable inside DLLs (with or without OE). This
bug is the cause of access violations and runtime errors in multi-threaded
DLLs (as used by COM servers, ISAPI applications, or any other
multi-threaded DLL).
To solve this problem set the IsMultiThread variable to True in the
begin/end section of your DLL. The reason this problem only occurs in DLLs
and not inside EXEs is because of an if-then statement causing the
IsMultiThread variable to be set correctly in the one case but not in the
other.
4. Auto-increment fields work fine with the TOEDataSet component and will
automatically be ignored as part of the column list during inserts and
updates. If they also form part of the primary key, they will automatically
be used as part of the where-clause of updates and deletes, if necessary.
5. The fields used by LocateInsert are the primary fields by default, but can
be changed to include all the fields or a subset of the fields using the
Target property.
6. Some ODBC drivers will only allow access to return and result values of
stored procedures after making a call to the SQLMoreResults ODBC function,
implemented by the THstmt.MoreResults and TOEDataSet.NextResultSet methods.
7. Closing and re-opening the TOEDataSet sometimes causes incorrect behaviour,
such as empty result sets, with certain ODBC drivers (e.g. Oracle and SQL
Anywhere). If this occurs call the UnPrepare method between the Close and
Open calls. Of course this will cause the SQL statement to be re-prepared
when you re-open the DataSet, but should fix the behaviour problems.