DBMS Notes CompareData

Some of the databases and drivers tested with CompareData.

Basic DBMS metadata support is provided using ODBC catalog functions whereas extended DBMS metadata support provides additional features such as comparing triggers by accessing the DBMS information schema.

DBMS Version Driver Ver Test
DB2* 11.05 IBM DB2 ODBC Driver - DBCOPY1 11.05.6000.1809 Fail
IBM DB2 ODBC Driver - fIBMDBCL1
DBISAM 4.24 DBISAM 4 ODBC Driver 4.24.00.01 Pass
DBMaker 4.2 DBMaker Driver 4.02.00.4721 Pass
FairCom c-treeSQL Server V8.14 c-treeSQL ODBC Driver 8.24.05.29 Pass
FileMaker Pro 19.2.1 FileMaker ODBC 19.00.05.00 Pass
Firebird* 2.0 Firebird/InterBase(r) driver 2.00.05.156 Pass
FrontBase 4.2 FrontBase ODBC 1.20.00.85 Fair
1.20.00.91 Pass
Google BigQuery* 2 Simba ODBC Driver for Google BigQuery 2.03.03.1005 pass
Informix IDS* 14.10 IBM INFORMIX ODBC DRIVER (64-bit) 4.50.00.18020 Pass
Ingres 9.2* 9.2.0 Ingres 9.2 3.50.920.90 Fair
InterBase* 7.5 Firebird/InterBase(r) driver MT 2.00.00.136 Pass
Linter 6.0.8 Linter 6.0 Driver 3.00.61.00 Pass
MariaDB* 10.06 MariaDB ODBC 3.1 Driver 3.01.13.00 Pass
Microsoft Access 2003 - 2019 Microsoft Access Driver (*.mdb, *.accdb) 16.00.13328.20334< Pass64
Microsoft Excel 2003 - 2019 Microsoft Excel Driver (*.xls, *.xls,...) 16.00.13328.20334 Pass32/64
Microsoft SQL Server* 2005 - 2019 ODBC Driver 17 for SQL Server 17.06.01.01 Pass64
Mimer SQL 10.01.06 MIMER 10.01.06.01 Pass64
MySQL* 8.0.26 MySQL ODBC 8 ANSI Driver 08.00.26.00 Fair
MariaDB ODBC 3.1 Driver 3.01.13.00 Pass
OpenLink Virtuoso 4.5 OpenLink Virtuoso (4.5) 4.50.29.04 Fair
Oracle* 19c Oracle in OraDB19Home 19.00.00.00 Pass
DataDirect 8.0 Oracle Wire Protocol 8.00.02.2364 Pass
Pervasive PSQL 9.5 Pervasive ODBC Client Interface 9.50.77.02 Fair
PostgreSQL* 12.0.4 PostgreSQL ODBC Driver(ANSI) 12.02.00.00 Pass
Redshift* 8.0.2 Amazon Redshift (x64) 1.04.27.1000 Pass
Snowflake* 4.43.2 SnowflakeDSIIDriver 2.22.03.00 Pass
SAP Adaptive Server Enterprise* 16 DataDirect 5.2 Sybase Wire Protocol 5.20.00.13 Fair
Adaptive Server Enterprise 16.00.04.00 Fair
SAP Advantage Database Server 12 Advantage StreamlineSQL ODBC 12.00.00.00 Pass64
SAP Hana 4.0.7 HDBODBC 2.07.21.17475 Pass64
SAP MaxDB 7.6 SAP DB 7.06.00.00 Pass
SAP SQL Anywhere* 17 Adaptive Server Anywhere 17 17.00.10.6089 Pass
SQLite* 3.32.3 SQLite3 ODBC Driver 0.9998.00.00 Fair
Text Files - DataDirect 5.2 TextFile (*.*) 5.20.00.20 Pass
Microsoft Text Driver (*.txt; *.csv) 16.00.13328.20334 pass
Teradata V2R5.0 Teradata 3.02.00.02 Fair
Passpass Fairfair/issues Failed failed/not supported
*Extended metadata support

DB2

IBM DB2 ODBC DRIVER - DB2COPY1 11.05.6000.1809 fail
IBM DB2 ODBC DRIVER - IBMDBCL1 11.05.6000.1809 pass
SQL Error. Native Code: 0, SQLState: IM004, Return Code: -1
[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
    

Application can no longer connect to DB2 using IBM Data Server Driver for ODBC after application code page update to UTF-8. Driver does not work with UTF-8 clients on Windows. Evaluate using DataDirect ODBC driver for DB2.


DBISAM

DBISAM 4 ODBC Driver 4.24.00.01 Pass

Tables are not listed in a Tables/Views comparison unless Remote Client/Server mode is used to connect to the database.


Firebird

Firebird/InterBase(r) driver 2.00.05.156 Pass

Driver installer is distributed with CompareData and driver is used by CompareData to connect to its embedded repository.

SQL Error, SQLState: 08004, Return Code: -1
[ODBC Firebird Driver]Unavailable Database
SQL Error. Native Code: -902, SQLState: 08004, Return Code: -1
[ODBC Firebird Driver]File Database is not found

Only one application can connect to the the embedded Firebird database at any one time. Note, however, that an application can have multiple connections to the same embedded database as long as all these connections are from the same instance of the application.

Solution

Verify that no other applications are connected to the embedded repository database.  You can use Windows Task Manager to scan for cmpdata.exe to ensure that only one instance of the application is connected to the embedded repository.

This error may also be due to another application using the embedded Firebird client (fbclient.dll) or if the database file does not exist.


FrontBase

FrontBase ODBC driver 1.20.00.85 Fair

FrontBase has a fix build for the driver that addresses miscellaneous issues.


Google BigQuery

Simba ODBC Driver for Google BigQuery 2.03.03.1005 Pass

Very slow performance with DML prepared statements.


Ingres 9.2

Ingres 9.2 3.50.920.90 ODBC Driver Fair

SQLForeignKeys returns rows for another table with the same name as the requested table (but with different owner). Will be fixed in the next release of the Ingres ODBC driver.


Microsoft Access

32-bit vs 64-bit

A Microsoft Access installation on Windows is either 32-bit or 64-bit version. On 64-bit Windows either 32-bit or 64-bit version of Access may be installed. MS Access ODBC driver 32-bit or 64-bit version may be installed matching the MS Access installation bitness (but not both).

If MS Access installation is 32-bit, then use ODBC Administrator 32-bit version to set up MS Access ODBC data sources and you have to use the 32-bit version of CompareData.

If MS Access installation is 64-bit, then use ODBC Administrator 64-bit version to set up MS Access ODBC data sources and you have to use the 64-bit version of CompareData.

You can set up MS Access ODBC data sources by running either the 32-bit or 64-bit version of the ODBC Administrator app, that matches the bitness of the MS Access installation, from Windows Start screen or from CompareData Tools menu.

Both 32-bit and 64-bit versions of CompareData may be installed side-by-side, but only 32-bit or 64-bit Access ODBC data sources are available or can be set up (matching the MS Access installation bitness).


MySQL

MySQL ODBC 8 ANSI Driver 08.00.26.00 Fair

Issue with SQLColAttribute SQL_DESC_TYPE_NAME where driver memory bug may result in a crash or junk data.


Pervasive

PervasiveSQL v9.5 Driver 9.50.77.02 Fair

SAP Adaptive Server Enterprise

DataDirect 5.2 Sybase Wire Protocol 5.20.00.13 Fair
Adaptive Server Enterprise 16.00.04.00 Fair

Various issues. Until these issues are fixed, it is recommended to use DataDirect Wire driver for Sybase instead.

Extended Metadata Support

In order to get resultset description for triggers and stored procedures/functions you must set the 'Enable Dynamic Prepare' option for the ODBC data source (Connection tab in the driver set up dialog)


SAP Hana

HDBODBC 2.07.21.17475 Pass
SQL Error. Native Code: -10719, SQLState: HY000, Return Code: -1
[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;-10719 Connect failed (invalid SERVERNODE '')

Add SERVERNODE parameter in CompareData data source setup Driver-specific parameters box. Example, SERVERNODE=192.168.194.23:39015

You can get the SERVERNODE parameter value from the ODBC Administator data source setup screen for the DSN by clicking the Test Connection button and copying the value from the Server Host:port: box.


SAP MaxDB

SAP DB Driver 7.06.00.00 Pass
SQL Error. Native Code: -4008, SQLState: HY000, Return Code: -1
[SAP AG][SDBODBC DLL][SAP MaxDB] General error;-4008 POS(1) Unknown user name/password combination

Enter userID and password all upper case when setting up application data source or leave password blank to have the driver prompt you for the password.


Text Files

Microsoft Text Driver (*.txt; *.csv)pass

Please see schema.ini for information about setting up text driver.

[Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only.

Driver does not allow text file names without extension or file extension is not allowed by default Q245407