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