How to compare data from an ODBC source

SQL Data Examiner 2022 natively supports SQL Server, Oracle, PostgreSQL, MySQL and Access databases. However, you may need to compare data from a source not natively supported by the program.

Detailed list of supported data sources →

In this case, starting from SQL Data Examiner 2015, the program allows you to connect to any data source for which there is an ODBC driver.

Note: ODBC data sources are supported in the Рrofessional edition of the SQL Data Examiner only.

Limitations when comparing with ODBC sources

Because the universal code is used when connecting to ODBC data sources, the program cannot load FOREIGN KEYs (each version of each database requires different scripts to get this information), which complicates data synchronization (you need to manually adjust the table order during synchronization so that there are no FK violations; besides, you cannot disable FK, TRIGGERSs and UNIQUE constraints during synchronization).

Also, if the tables use specific types for a particular DBMS, then in some cases there may be problems with reading data from these columns.

Thus, we recommend working with native database support whenever possible, and using ODBC source support only when necessary.

32-bit (x86) and 64-bit (x64) drivers

ODBC drivers can be 32-bit or 64-bit. It is important that the architecture of the program (x86 or x64) matches the architecture of the driver.

By default, on 64-bit systems, the program runs in 64-bit mode. However, for some (especially legacy) data sources, only 32-bit drivers exist. To use these drivers, you need to run the 32-bit version of the program: Tools > Run SQL Data Examiner (x86)...

Connecting to an ODBC data source

Start a new data comparison (click the New... button on the toolbar) and specify More Data Sources > Custom ODBC Data Source as Source and/or Target type.

sqlde-odbc.png

To connect to an ODBC data source you should specify a Data Source Name (DSN) or connection string. Please note that SQL Data Examiner does not configure an ODBC data source and uses specified DSN or connection string "as is".

Configuring a DSN

Open ODBC Data Source Administrator

start-odbc-data-sources.png

Either 64-bit or 32-bit (see above).

Configure DSN

  • Go to the User DSN or System DSN tab
  • Click Add...
  • Scroll through the list of drivers in the appeared dialog to locate the required driver and then click Finish
  • The configuration dialog appears
  • Follow the wizard and configure selected driver
  • Test the configured DSN on the last step

odbc-data-sources.png

Configuring a connection string

Here you can find connection strings for most data sources:
https://www.connectionstrings.com

Sample connection strings

We have tested SQL Data Examiner with some ODBC data sources, however we did not test the program with all available ODBC drivers and do not plan to do it. There are some sample connection strings.

Data Source ODBC Driver Tested connection string
SQLite 3.x SQLite3 ODBC Driver DRIVER={SQLite3 ODBC Driver}; Database=c:\Databases\SQLite\TestDb;
Firebird 2.5.2 Firebird/InterBase(r) driver DRIVER={Firebird/InterBase(r) driver}; UID=SYSDBA; PWD=masterkey; DBNAME=c:\Databases\Firebird\FIREBIRDDB.GDB;
Interbase 11.0.3 InterBase ODBC driver DRIVER={InterBase ODBC driver}; UID=SYSDBA; PWD=masterkey; DIALECT=3; CLIENT=C:\Program Files\Embarcadero\InterBase\bin\gds32.dll; DBNAME=127.0.0.1/3051:Demodb;
SYBASE 12.1 SQL Anywhere 12 Driver={SQL Anywhere 12}; Database=demo; Uid=myUsername; Pwd=myPassword;
SQLBase 11.7 Gupta SQLBase 11.7 Driver={Gupta SQLBase 11.7}; ServerName=Server1; Database=ISLAND;
IBM DB2 10.5.0 IBM DB2 ODBC DRIVER Driver={IBM DB2 ODBC DRIVER}; Database=SAMPLE; UID=myDomain\myUsername; PWD=myPassword
Pervasive 11.30 Pervasive ODBC Client Interface Driver={Pervasive ODBC Client Interface}; ServerName=localhost; DBQ=DEMODATA; TransportHint=TCP:SPX;
PostgreSQL 9.3.0 PostgreSQL Driver={PostgreSQL}; Server=192.168.0.10; Port=5432; Database=myDataBase; Uid=myUsername; Pwd=myPassword;
Ingres 10.1.0 Ingres II DRIVER={Ingres II}; SERVER=(LOCAL); DATABASE=demodb;
Paradox 5.x Microsoft Paradox Driver (*.db) Driver={Microsoft Paradox Driver (*.db )}; DriverID=538; Fil=Paradox 5.X; DefaultDir=c:\Databases\Paradox\; Dbq=c:\Databases\Paradox\; CollatingSequence=ASCII
  • Product:  SQL Data Examiner
  • Edition:  Professional
  • Version:  2015 and later
  • Published:  2022-05-17
  • Last Upadted:  2022-05-17


Do not see what you are looking for?
Please submit a support request