New Features and Bug Fixes in Version 5.03 of ODBCExpress
=========================================================
New Features
------------
New features introduced since the previous version:
1. Driver profiles are added to allow usage of driver specific settings.
TDriverPtr record:
TDriverPtr = ^TDriverRec;
TDriverRec = record
//description of the driver as returned by the CurrentDriver method
Desc: String;
//the precision (parameter size) values for the following data types
PS_SQL_CHAR,
PS_SQL_VARCHAR,
PS_SQL_LONGVARCHAR,
PS_SQL_BINARY,
PS_SQL_VARBINARY,
PS_SQL_LONGVARBINARY,
PS_SQL_DECIMAL,
PS_SQL_NUMERIC,
PS_SQL_TYPE_TIMESTAMP: SQLUINTEGER;
//the scale (decimal digits) values for the following data types
DD_SQL_DECIMAL,
DD_SQL_NUMERIC,
DD_SQL_TYPE_TIMESTAMP: SQLSMALLINT;
end;
A default list of driver records with settings are automatically added. The
"Desc" fields of this driver list are as follows:
//the default settings used by all drivers
Default
//some modified settings for drivers shipped with MDAC
Microsoft SQL Server;sqlsrv32.dll
Oracle;msorcl32.dll
Access;odbcjt32.dll
dBase;odbcjt32.dll
Paradox;odbcjt32.dll
Visual FoxPro;vfpodbc.dll
FoxPro;odbcjt32.dll
//some modified settings for drivers shipped with the following DBMS's
SQL Server;sysybnt.dll
Adaptive Server Anywhere;dbodbc6w.dll
Informix;iclit09a.dll
InterBase;iscdrv32.dll
A number of methods allow you to manipulate this driver list if necessary.
THdbc class:
//clears the driver list and adds the default list of drivers as above
procedure RefreshDrivers;
//clears the driver list
procedure ClearDrivers;
//adds a new driver to the list, e.g. AddDriver(CurrentDriver)
function AddDriver(ADriver: String): TDriverPtr;
//removes a driver from the list, e.g. RemoveDriver(CurrentDriver)
procedure RemoveDriver(ADriver: String);
//returns the record for a driver, or nil if not found, e.g.
//GetDriver(CurrentDriver)
function GetDriver(ADriver: String): TDriverPtr;
//returns the description of the current driver
function CurrentDriver: String;
//checks if the current driver matches any driver description in the
//provided array
function IsDriver(ADrivers: array of String): Boolean;
The record returned by AddDriver contains default values as contained by the
Default driver record, so you only need to change the record fields which
are different from the default, e.g.:
with AddDriver(CurrentDriver)^ do
begin
PS_SQL_CHAR:= 2000;
PS_SQL_TYPE_TIMESTAMP:= 25; //19+Fractional
DD_SQL_TYPE_TIMESTAMP:= 5; //Fractional
end;
The global, non-connection specific Fractional variable, which where used to
calculate the above timestamp precision and scale, are now replaced by the
above connection specific option.
These driver profiles fixes a range of problems such as "driver not
capable", "invalid precision" and "dynamic sql error" errors with ODBC
drivers such as the dBase and Informix ones.
2. An overloaded BindParam method with an additional parameter SqlType is added
to the THstmt. This allows ODBCExpress to fall back on using the SqlType
parameter when the more accurate SQLDescribeParam function, which return
this and other values, fails. The other values mentioned are then
supplemented by the driver profiles. Some ODBC drivers, which support the
SQLDescribeParam function, fail when the query are too complex (sub-selects,
unions, etc), preventing you from using certain dynamic parameters in these
queries. The new BindParam method (invisible to normal THstmt and
TOEDataSet usage) solves this problem.
THstmt class:
//existing BindParam method - uses SQLDescribeParam
procedure BindParam(Param: SQLUSMALLINT;
ParamType: SQLSMALLINT;
ParamValue: SQLPOINTER); overload;
//new overloaded BindParam method
procedure BindParam(Param: SQLUSMALLINT;
ParamType: SQLSMALLINT;
ParamValue: SQLPOINTER;
SqlType: SQLSMALLINT); overload;
//existing BindParamCore method - does not use SQLDescribeParam
procedure BindParamCore(Param: SQLUSMALLINT;
ParamType: SQLSMALLINT;
ParamValue: SQLPOINTER;
SqlType: SQLSMALLINT);
The new overloaded BindParam method should be used in favour of both the
existing BindParam and BindParamCore methods (unless you are using these two
methods to force the usage or non-usage of the SQLDescribeParam function).
3. Ignore flags are separated for insert and update operations with the
TOEDataSet. This allows you to select a set of fields to ignore during
insert operations and to select a different set of fields to ignore during
update operations.
TTarget class:
//set the primary columns
procedure PrimaryCols(Cols: array of String;
ColCount: Integer); overload;
//calling this method is equivalent to calling both of the following two
//methods
procedure IgnoreCols(Cols: array of String;
ColCount: Integer); overload;
//sets the columns to ignore during insert operations
procedure IgnoreInsertCols(Cols: array of String;
ColCount: Integer); overload;
//sets the columns to ignore during update operations
procedure IgnoreUpdateCols(Cols: array of String;
ColCount: Integer); overload;
//similar overloaded methods without the ColCount parameter
procedure PrimaryCols(Cols: array of String); overload;
procedure IgnoreCols(Cols: array of String); overload;
procedure IgnoreInsertCols(Cols: array of String); overload;
procedure IgnoreUpdateCols(Cols: array of String); overload;
//sets and returns the target table
property TargetTable: String;
//sets and returns the primary and ignore columns
property PrimaryColNames: TStringList;
property IgnoreInsertColNames: TStringList;
property IgnoreUpdateColNames: TStringList;
4. A property EmptyFieldToNull has been added to the TOEDataSet components,
which is False by default. In the past when a field value was not set, an
attempt was made to insert null values for these fields during positional
insert and update operations. Now when a field value is not set, provision
for the default value of a field is made during inserts and updates. You
can revert to inserting null values by setting EmptyFieldToNull to True.
5. A set property NoRowsAffected are added to the THstmt and TOEDataSet
components which allows you to control whether exceptions must be raised
when no rows are affected by a positional insert, update or delete operation
(level 1 to level 3 SQL Generation). It can be one or more of the following
values:
nrByInsert - raise an exception when no rows are affected by a positional
insert operation. This should not be necessary to set since
exceptions are normally raised when an insert fails.
nrByUpdate - raise an exception when no rows are affected by a positional
update operation.
nrByDelete - raise an exception when no rows are affected by a positional
delete operation. Normally you won't be interested in this
scenario, since the row is already deleted.
nrByRefresh - raise an exception when no rows are affected by a refresh
operation. Not being able to refresh a row is normally not
a serious problem.
By default the set only contains the nrByUpdate value. When used together
with the TOEDataSet and cached updates, the OnUpdateError event will be
called if implemented. In this case, as well as any other time when using
level 3 SQL Generation, it tries to find a row to update based on the
primary columns (which might be set using the Target property). In other
words you can simulate some of the BDE update mode property values as
follows:
upWhereAll - mark all the fields as primary using the target property
upWhereChanged - not possible to simulate this value
upWhereKeyOnly - mark only the primary key fields as primary using the
target property (or leave it up to the primary key
detection functionality)
6. 64 bit integer support is added to both the THstmt and TOEDataSet
components.
THstmt class:
//parameter bind methods
procedure BindInt64(Param: SQLUSMALLINT;
var ParamValue: Int64);
procedure BindInt64ByName(ParamName: String;
var ParamValue: Int64);
procedure BindInt64s(Param: SQLUSMALLINT;
var ParamValue: array of Int64);
procedure BindInt64sByName(ParamName: String;
var ParamValue: array of Int64);
//column retrieval methods
property ColInt64[Col: SQLUSMALLINT]: Int64;
property ColInt64ByName[ColName: String]: Int64;
property CellInt64[Col, Row: SQLUSMALLINT]: Int64;
property CellInt64ByName[ColName: String;
Row: SQLUSMALLINT]: Int64;
7. Some changes have been made to the TOECatalog component:
- A UniqueKey property has been added as an alternative to the PrimaryKeys
property. It returns the optimal set of columns that uniquely identifies
a row in the table:
[,]...
This string can be parsed by the new ParseUniqueKey method.
TCatalogTable class:
property PrimaryKeys: TStrings;
property UniqueKey: String;
TOECatalog class:
procedure ParseUniqueKey(UniqueKey: String;
ColumnNames: TStrings);
- A new property ForeignReferences returns the inverse foreign keys than
returned by the existing ForeignKeys property. While the ForeignKeys
property returns a list of foreign keys in the specified table, the
ForeignReferences property returns the foreign keys in other tables that
refer to the primary key in the specified table:
;[.];
This string can be parsed by the existing ParseForeignKey method.
TCatalogTable class:
property ForeignKeys: TStrings;
property ForeignReferences: TStrings;
TOECatalog class:
procedure ParseForeignKey(ForeignKey: String;
var ColumnName, ForeignOwner,
ForeignTable, ForeignColumn: String);
8. Some changes have been made to the TOESchema component:
- Two methods LoadIndexes and DropIndexes are added, which allows you to
load or drop all the indexes associated with a specified table.
TSchemaTable class:
procedure LoadIndexes;
procedure DropIndexes(IgnoreErrors: Boolean);
- Buttons which allow you to load and drop defined tables and views are also
added to the TOESchema editor. This means you can perform the mentioned
operations at design time for testing purposes.
9. The MoveRow and ExchangeRows methods of the TExtendedGrid have been
implemented.
TExtendedGrid class:
procedure MoveRow(Index, NewIndex: Longint);
procedure ExchangeRows(Index1, Index2: Longint);
Bug Fixes
---------
Bug fixes included since the previous version:
1. The following problems with the handle components are fixed:
a. When ExecAsync was enabled, the correct errors were not retrieved when
an exception occurred during execution.
b. The functionality used to detect the primary key of a table has been
modified to cover cases where this functionality failed with certain
ODBC drivers, such as the Oracle 8 ODBC drivers.
c. Connection resolution failed in cases where the handle component, which
detected the connection failure, was destroyed before connection
resolution was applied.
d. An "invalid descriptor index" problem when fetching multiple blobs with
certain ODBC drivers has been fixed. This problem occurred when trying
to re-fetch a blob that was already fetched, returning the above error
instead of the ignored "no data found" error. Blobs will now not be
re-fetched when the RowSetSize is 1.
e. A connection was not automatically established when using the
informational methods and properties of the THdbc component (such as
GetFunction, GetInfoString, Core, etc).
f. An "invalid string or buffer length" error occurred with level 3 SQL
Generation when both the primary and ignore flags of a field was
selected.
g. The TStringTrimming value stNone has been renamed to stTrimNone, since
it clashed with the TSortType value of the same name.
2. The following problems with the TDataSet descendants are fixed:
a. Automatic setting of the required and read-only flags of fields,
depending on whether a field is nullable and updatable respectively,
have been removed.
b. The OnDeleteError, OnEditError and OnPostError events did not fire,
since these events only fire on EDatabaseError exceptions. To get this
working the EODBCExpress exception is now derived from the
EDatabaseError exception instead of the default exception class
Exception.
c. The default parameter type (if it cannot be detected automatically) was
not ftUnknown. It now allows the parameter type to be automatically set
when assigning a variant value to the parameter.
d. A memory leak occurred when using multiple results.
e. Date and time return parameters did not work correctly.
f. There was some problems with retrieving binary-type fields (including
SQL_GUID type fields).
g. Deleting all the rows with CachedUpdates enabled and then calling the
CancelUpdates method caused a "bookmark not found in cache" error.
h. A problem with master-detail relationships, where a non-null (bound)
parameter in the detail dataset at design-time caused this parameter not
to be updated at run-time, when a master record was selected.
3. The following problems with the visual components are fixed:
a. The TExtendedGrid's column widths were not synchronized when getting or
setting either the ColWidths or Columns.Width properties.
b. The titles of the cells which form part of fixed columns in the top row of
the TExtendedGrid were incorrectly displayed when there was no fixed rows.
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. MDAC 2.1 is available from Microsoft and tested with ODBCExpress. If fixes
a lot of MDAC 2 ODBC driver problems, as well as problems with the MDAC 2
ODBC Driver Manager, such as the problem which did not allow you to
re-connect a THdbc to an ODBC driver (without terminating the THdbc) after
it was connected to a different ODBC driver.
3. The missing package file with the extension .DCP is now included with the
other package files.