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
Citus 10.2.3 PostgreSQL ANSI 13.02.00.00 Fair
ClickHouse 21.12.3.32 ClickHouse ODBC Driver (ANSI) 1.01.10.25734 fair
CockroachDB* 21.2.0-beta.4 PostgreSQL Unicode 13.02.00.00 pass
CrateDB 4.6.6 PostgreSQL ANSI 13.02.00.00 fair
Databricks Spark SQL 3.1.2 Simba Spark ODBC Driver 2.6.19.1033 pass
Db2* 11.05 IBM DB2 ODBC Driver - DBCOPY1 11.05.6000.1809 Fail
IBM DB2 ODBC Driver - IBMDBCL1 11.05.7000.1973 Pass
dBASE 5 Microsoft dBase Driver (*.dbf) 10.00.22000.01 Fair
DBMaker 5.4 DBMaker 5.4 Driver 5.04.04.30310 Fair
EnterpriseDB Advanced Server* 14.1 PostgreSQL ANSI 13.02.00.00 pass
Exasol 7.2.1 EXASolition Driver 7.01.07.00 fail
FileMaker Pro 19.2.1 FileMaker ODBC 19.00.05.00 Pass
Firebird* 6.03 Firebird/InterBase(r) driver 2.00.05.156 Pass
Google BigQuery* 2 Simba ODBC Driver for Google BigQuery 2.03.03.1005 pass
Google Sheets 1 CData ODBC Driver for Google Sheets 21.00.8091 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* 14.02 InterBase ODBC driver 1.00.00.101 Pass
InterSystems IRIS 2021.2.0.651 InterSystems IRIS ODBC35 2021.2.0.651 Fair
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, *.xlsx, *.xlsm, *.xlsb) 16.00.14701.20040 Pass32/64
Microsoft SQL Server* 2005 - 2019 ODBC Driver 17 for SQL Server 17.08.0001 Pass64
Mimer SQL 10.01.06 MIMER 10.01.06.01 Pass64
MonetDB 11.41 MonetDB ODBC Driver 11.41.13.01 Pass
MongoDB 5.0.5 MongoDB ODBC 1.4.2 ANSI Driver 1.04.02.00 fair
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 7.2 OpenLink Virtuoso 7.20.3219.01 Fair
Oracle* 19c Oracle in OraDB19Home 19.00.00.00 Pass
Percona Server for MySQL* 8.0.27 MySQL ODBC 8 ANSI Driver 08.00.28.00 Fair
MariaDB ODBC 3.1 Driver 3.01.13.00 Pass
Percona Distribution for PostgreSQL* 14.2 PostgreSQL ANSI 13.02.00.00 Fair
PostgreSQL Unicode 13.02.00.00 Pass
Pervasive PSQL 9.5 Pervasive ODBC Client Interface 9.50.77.02 Fair
PostgreSQL* 14.0.0 PostgreSQL ANSI 13.02.00.00 Fair
PostgreSQL Unicode 13.02.00.00 Pass
Raima Database Manager 15.2.1 Raima Database Manager 15.2 15.02.01.4127 Fail
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 Adaptive Server Enterprise 16.00.04.01 Fair
SAP Advantage Database Server 12 Advantage StreamlineSQL ODBC 12.00.00.00 Pass64
SAP HANA 4.0.7 HDBODBC 2.07.21.17475 Fair
SAP MaxDB 7.6 SAP DB 7.06.00.00 Pass
SAP SQL Anywhere* 17 Adaptive Server Anywhere 17 17.00.10.6089 Pass
SingleStore* 7.05.0011 SingleStore ODBC 1.04 ANSI Driver 1.00.04 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* 17.10 Teradata Database ODBC Driver 17.10 17.10.00.15 Fair
TiDB* 5.3.0 MySQL ODBC 8.0 ANSI 8.00.27.00 pass
YugabyteDB* 11.2-YB-2.8.0.0-b0 PostgreSQL ANSI 13.02.00.00 pass
PostgreSQL Unicode 13.02.00.00 Pass
Passpass Fairfair/issues Failed failed/not supported
*Extended metadata support

CockroachDB

PostgreSQL Unicode 13.02.00.00 pass 
SQL Error. Native Code: 101, SQLState: 08001, Return Code: -1
FATAL:  codeParamsRoutingFailed: missing cluster name in connection string

SQL Error. Native Code: 1, SQLState: 3D000, Return Code: -1
ERROR: database "loyal-rabbit-****.defaultdb" does not exist;
No query has been executed with that handle
[SQL Error]Failed to get number of columns for resultset

Do not prefix database name with cluster name in PostgreSQL ODBC driver data source setup dialog loyal-rabbit-****.defaultdb. Enter cluster name in PostgreSQL ODBC driver data source setup dialog page 3 libpg parameters box. Example:

options=--cluster=loyal-rabbit-1234

Db2

IBM DB2 ODBC DRIVER - DB2COPY1 11.05.6000.1809 fail
IBM DB2 ODBC DRIVER - IBMDBCL1 11.05.7000.1973 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. Db2 for Windows and IBM Data Server Driver for ODBC do not currently support UTF-8 Windows code page.

The most recent available non UTF-8 CompareData version that works with IBM Data Server Driver for ODBC is version 2.5.6.

You can also evaluate using DataDirect ODBC driver for Db2.

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 instance of the application can connect to the embedded Firebird database at the same time.

Verify that no other application is connected to the embedded repository database. You can use Windows Task Manager to look 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.

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.28.00 Fair

Issue with SQLColAttribute SQL_DESC_TYPE_NAME where driver may return junk data for resultset column data type.

Pervasive

PervasiveSQL v9.5 Driver 9.50.77.02 Fair

PostgreSQL

PostgreSQL ANSI 13.02.00.00 Fair

Driver does not convert wide char source data to narrow char on synchronizing data. For example inserting SQL Server nchar data 'abcdefg' results in only 'a' being inserted.

Use PostgreSQL Unicode version of the driver (degrades performance) until this issue is fixed by PostgreSQL.

SAP Adaptive Server Enterprise

Adaptive Server Enterprise 16.00.04.01 test pass

SAP HANA

HDBODBC 2.07.21.17475 Fair 
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.

SingleStore

SingleStore ODBC 1.04 ANSI Driver 1.00.04 pass 
[Resultset Description]no column found for resultset

[Resultset Description]Resultset description is not available for prepared sql statement for data source. Please make sure driver option to dynamically prepare sql statements is set, if applicable to driver, and/or you have permission to view information schema

Open ODBC Administrator to configure the data source and uncheck "Prepare statements on the client side" checkbox in the Miscellaneous Options page of the data source configuration.

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.

YugabyteDB

PostgreSQL ANSI 13.02.00.00 pass 
ERROR: invalid value for parameter "client_encoding": "WIN1252"
DETAIL: Conversion between WIN1252 and UTF8 is not supported.

Testing connection to YugabyteDB in PostgreSQL ANSI ODBC driver data source setup dialog fails. However, CompareData is able to connect to the data source since CompareData is UTF-8 application and does not use Windows locale code page.

It is recommended to use PostgreSQL ANSI version of PostgreSQL ODBC driver with CompareData, when possible, for best performance.