DBMS Notes

Some of the databases and drivers tested with CompareData. Basic DBMS metadata support is limited to functionality available using ODBC catalog functions whereas extended DBMS metadata support adds support for additional features such as comparing triggers by accessing the DBMS information schema.

DBMS DBMS Ver Driver Driver Version Test
4D v11 4D v11 ODBC Driver 11.03.00.00 Failed
Adabas D 13.01 Adabas D 13.01.01.00 Pass
Advantage Database Server 9.1 Advantage StreamlineSQL ODBC 9.10.00.09 Fair
ANTs Data Server 3.6 ANTS ODBC Driver 3.00.00.00 Fair
Birdstep RDM Server 7.0 Birdstep ODBC for RDM Server 7.0 7.00.701.00 Fair
Caché 5.0 InterSystems ODBC 5.00.5802.00 Failed
Easysoft ODBC-JDBC Gateway 1.03.00.01 Pass
2009.1.2 InterSystems ODBC35 2009.01.02.602 Fair
Derby*, JavaDB 10.5.3.0 IBM DB2 ODBC DRIVER 9.01.00.356 Pass
Daffodil 4.1 DaffodilDBDriver(*.ddb) 1.00.00.01 Failed
Easysoft ODBC-JDBC Gateway 1.03.00.01 Pass
DB2 UDB* 8.1.10.812 IBM DB2 ODBC Driver 8.01.10.812 Pass
9 9.01.00.356 Pass
9.07.100.177 PassPass
DBISAM 4.24 DBISAM 4 ODBC Driver 4.24.00.01 Pass
DBMaker 4.2 DBMaker Driver 4.02.00.4721 Pass
EnterpriseDB* 8.1 Release 2 EnterpriseDB 8.1 8.01.04.19 Pass
PostgreSQL ANSI 8.02.03.00 Pass
FairCom c-treeSQL Server V8.14 c-treeSQL ODBC Driver 8.24.05.29 Pass
FileMaker Pro 8 DataDirect 32-BIT SequeLink 5.4 1.15.12.00 Fair
Firebird* 2.0 Firebird/InterBase(r) driver 2.00.00.151 PassPass
FrontBase 4.2 FrontBase ODBC 1.20.00.85 Fair
1.20.00.91 Pass
HSQLDB 1.8.0.7 Easysoft ODBC-JDBC Gateway/HSQLDB JDBC driver 2.00.00.00 Fair
Informix IDS* 10.0 IBM Informix ODBC Driver 2.90.00.12335 PassPass
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
Matisse 8.3.2 Matisse DBMS 8.03.02.00 Failed
MaxDB 7.6 SAP DB 7.06.00.00 Pass
Microsoft Access 2003 Microsoft Access Driver (*.mdb) 4.00.6200.00 Pass
2010 Microsoft Access Driver (*.mdb, *.accdb) 14.00.4760.1000 Pass
Microsoft Excel 2003 Microsoft Excel Driver (*.xls) 4.00.6200.00 Pass
2010 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) 14.00.4760.1000 Pass
Microsoft SQL Server* 2000 SQL Server 2000.85.1132.00 Pass
2005 SQL Native Client 2005.90.1399.00 PassPass
2008 SQL Server Native Client 10.0 2007.100.1600.22 PassPass
2012 SQL Server Native Client 11.0 2011.110.2100.60 PassPass
Mimer SQL 9.2 MIMER 9.02.05.02 Pass
MySQL* 5.0 MySQL Connector/ODBC 3.5 3.51.29.00 PassPass
MySQL Connector/ODBC 5.1 5.01.09.00 FailedFailed
MySQL ODBC 5.2a/w Driver 5.02.02.00 PassPass
Ocelot 3.02 OCELOT 3.02.12.31 Pass
OpenLink Virtuoso 4.5 OpenLink Virtuoso (4.5) 4.50.29.04 Fair
Oracle* 9i Oracle ODBC Driver 9.02.00.65 Pass
10g Oracle in XE 10.02.00.02 Fair
10.02.00.03 PassPass
DataDirect 5.2 Oracle Wire Protocol 5.20.00.48 Pass
11g Oracle in <oracle 11 home> 11.01.00.06 Pass
Pervasive PSQL 9.5 Pervasive ODBC Client Interface 9.50.77.02 Fair
PrimeBase 4.2 SNAP PRIMEBASE 4.02.05.08 Failed
PostgreSQL* 8.4 PostgreSQL ANSI 8.04.01.0100 Pass
SOLID Embedded Engine 4.5 SOLID ODBC Driver 4.5 - (ANSI) 4.50.00.68 PassFailed
SQL Anywhere* 9 Adaptive Server Anywhere 9.0 9.00.02.3137 Pass
SQLBase 9.0.1 Gupta SQLBase 9.00.01.13768 Fair
SQLite* 3.6.23.1 SQLite3 ODBC Driver 0.86.00.00 FairFailed
3.7.9 0.93.00.00 FairFair
Sybase* 12.5 DataDirect 5.1 Sybase Wire Protocol 5.10.00.81 Pass
OpenLink Lite for SQL Server (TDS) (32-bit) 5.20.76.00 Fair
15 DataDirect 5.2 Sybase Wire Protocol 5.20.00.13 Fair
Adaptive Server Enterprise 15.00.00.50 Failed
15.0.0.152 Fair
Text Files - DataDirect 5.2 TextFile (*.*) 5.20.00.20 Pass
Microsoft Text Driver (*.txt; *.csv) 4.00.6304.00 Fair
Teradata V2R5.0 Teradata 3.02.00.02 Fair
Passpass  Fairfair/issues Failedfailed/not supported (32-bit, 64-bit)
*Extended metadata support

DB2

IBM DB2 ODBC Driver 8.01.10.812 Pass
SQL0443N with "SYSIBM:CLI:-805" when invoking a CLI catalog function after upgrading to DB2 UDB Version 8.1 FixPak 10

You must follow post FixPak installation instructions. See flash  or section 1.3.5 in the FixPak Readme for detailed instructions.

IBM DB2 ODBC Driver 9.07.100.177 Pass
SQL Error. Native Code: 0, SQLState: IM004, Return Code: -1
[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

A workaround is to run the application as an administrator in order to connect to DB2 using the ODBC driver in Windows 7.


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.


FileMaker Pro

DataDirect 32-BIT SequeLink 5.4 1.15.12.00 Fair

Driver crashes saving synchronized data. Fixed in newest releases of DataDirect ODBC driver.


Firebird

SQL Error, SQLState: 08004, Return Code: -1 [ODBC Firebird Driver]Unavailable Database

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.

If you want to have multiple applications connect to CompareData repository database at the same time, then you need to run Firebird as a Server and set up an ODBC data source for the repository database. You can download Firebird server from www.firebirdsql.com

Firebird/InterBase(r) driver 2.00.00.151 Pass

This driver is distributed with CompareData and is used by CompareData to connect to its embedded repository. You can use this driver to compare/synchronize Firebird databases.


FontBase

FrontBase ODBC driver 1.20.00.85 Fair

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


HSQLDB

[Easysoft][ODBC-JDBC Gateway][HSQL Database Engine]out of memory
[Easysoft][ODBC-JDBC Gateway][HSQL Database Engine]Java heap space

Issues with Java VM running out of memory when retrieving/synchronizing tables with large data.


Informix

IBM Informix ODBC Driver 2.90.00.12335 Pass
[Informix][Informix ODBC Driver]Driver does not support this function.

Driver does not support SQLTables SQL_ALL_CATALOGS


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.


Matisse

Matisse DBMS 8.03.02.00 Failed

Not supported. Matisse ODBC driver is ODBC-level 2.50 rather than level 3.5 required by CompareData.


MaxDB

SAP DB Driver 7.06.00.00 Pass

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


Microsoft Access 2003

Microsoft Access Driver (*.mdb) 4.00.6200.00 Pass

MySQL

MySQL Connector/ODBC 5.01.07.00 FailedFailed
MySQL Connector/ODBC 5.01.08.00 FailedFailed
MySQL Connector/ODBC 5.01.09.00 FailedFailed

OpenLink Virtuoso 4.5

OpenLink Virtuoso (4.5) 4.50.29.04 Fair

Various issues with synchronizing data.


Oracle

Oracle ODBC driver 9.02.00.65 Pass

Poor performance with deletes, inserts, and updates sql statements. If you are not going to write LOBs to Oracle you can improve the driver performance by unchecking the 'Enable LOBs' checkbox in the Oracle tabpage of the ODBC driver data source setup dialog.

Issue with parameter bind offset not getting used except for the first execution of a prepared sql statement.  To avoid this issue, prepared statements are not used with this Oracle ODBC driver (SQORA32.DLL).

[Oracle][ODBC][Ora]ORA-03127: no new operations allowed until the active operation ends

Oracle ODBC driver 9.x error, use ODBC Data Source Administrator to configure the data source ODBC driver. From the driver Workaround tab check 'Force Retrieval of Longs' option.

Oracle in <oracle_home> 10.02.00.02 Fair

Poor peformance with catalog functions: SQLStatistics, SQLForeignKeys and SQLTablePrivileges (severe).

Oracle in <oracle_10_home> 10.02.00.03 Pass
Oracle in <oracle_11_home> 11.01.00.06 Pass
 SQLState: IM003.  Specified driver could not be loaded due to system error 127 (Oracle in XE)

You attempted connect to an Oracle Database 10g Express Edition but you are already connected to another Oracle database using a different driver that has the same dll name as the Oracle 10g Express Edition driver dll (SQORA32.dll).

Solution: use the same driver when setting up ODBC data source for connecting to both Oracle databases.

For example, your client machine connects to an Oracle 9i Enterprise server but you also downloaed and installed Oracle 10g Express Edition on your client machine and you want to connect to both servers from CompareData at the same time:

  1. Use the Oracle 9i ODBC driver when setting up both ODBC connections
  2. Copy the Oracle 10g Express Edition database section XE from C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora and paste it at the end of the Oracle home tnsnames.ora file of the Oracle driver you want to use for both connections, for example, C:\oracle\ora92\network\admin\tnsnames.ora
ORA-04020: deadlock detected while trying to lock object (application appears hung using repository Wizard)

Option to create public synonyms for repository tables was selected when the repository was created but option was not selected when repository was dropped leaving invalid repository public synonyms. To fix this issue drop the invalid repository public synonyms by running drop_synonym_xxx_ora.sql script located in Sql\Oracle folder of the application installation folder.


Pervasive

PervasiveSQL v9.5 Driver 9.50.77.02 Fair

PostgreSQL

Multiple Servers on the Same Machine using Different Ports

CompareData uses the dbms name, server name, database name, and login id to identify a data source. If you have two data sources for two different servers running on the same machine where the two servers are set up on different ports but both ODBC data sources servers are set up as 'localhost', CompareData will identify the two data sources as equivalent if you use the same login id for both data sources.

Workaround for now is to use a different login id or an equivalent but different server name, such as 'localhost' and '127.0.0.1'.


PrimeBase

SNAP PRIMEBASE 4.02.05.05 Failed

Driver ODBC level is 2.01 rather than level 3.5 required by CompareData.


SQLBase

Gupta SQLBase 9.00.01.13768 Fair

Sybase

DataDirect 5.1 Sybase Wire Protocol 5.10.00.81 Pass
Return code 100. No Data Found for an Insert Statement

Insert fails with return code 100 on executing an insert statement if the insert statement is a prepared statement and the previous execution of the statement encountered a server error.  Workaround:  in the driver ODBC setup window select 2 - Full for the Prepare Method in the Performance tab.

OpenLink Lite for SQL Server (TDS)(32-bit) 5.20.76.00 Fair

Issue with synchronizing dates. Month part of date gets decremented by 1.  This is fixed in the latest update to the driver.

When setting the ODBC driver enable the No Quoted Identifers option in the driver setup dialog.

DataDirect 5.2 Sybase Wire Protocol 5.20.00.13 Fair
Adaptive Server Enterprise 15.00.00.50 Failed
Adaptive Server Enterprise 15.0.0.152 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)


Text Files

Microsoft Text Driver (*.txt; *.csv) 4.00.6304.00Fair

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