How do I connect do data sources using ODBC instead of jdbc?

CloverDX Platform
1

Is this even possible? For example, we get data in all sorts of formats. Dbase, Btrieve, Pervasive, DataFlex and proprietary databases. None of these are listed in the Available drivers list in Clover. And I don’t see anyway to use ODBC.

For example, I have an MS Access database that I want to test with, but I cannot seem to be able to open the database. It is in Access97 format. I created a connection using the sun.jdbc.odc.JdbcOdbcDriver and it validates. However, when I go to create a metadata, I get a Connection error popup with message:

[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented

I don’t know if this is referring to CloverETL or to ODBC in general. The mdb file is password protected and I don’t have any way to remove that password as we don’t run Access97.

This is just an example of what we may run into and I need to know if Clover can handle these types of databases.

Thanks,
Scott

2

Connecting to ODBC data sources is supported through JDBC-ODBC bridge driver - many DBs without JDBC driver works well.

Not all JDBC functionality is always supported - depends on the ODBC driver mostly. In case of MS Access the call which Clover uses to get list of tables in DB (catalog) is not supported by the ODBC driver - hence the error message.

Some of the DBs you listed are directly supported by Clover - dBase/FoxBase/FoxPro - see DBFDataReader doc
The JDBC driver for Pervasive is already included in Clover. DataFlux - there is ODBC driver, not sure about JDBC.

As for the MS Access and other DBs connected through the JDBC-ODBC bridge - the DBInputTable/DBOutputTable will work. Getting the metadata extracted may be more work - you can create the metadata manually or use the analyzeDB utility supplied with CloverETL Engine.

Following would be the usage of the utility to extract metadata from table “orders” from already configured ODBC source “northwind” (MS Access demo DB).

analyzeDB.bat -dbDriver sun.jdbc.odbc.JdbcOdbcDriver -dbURL jdbc:odbc:northwind -q "select * from orders"

The analyzeDB utility is in “/bin” subdir of CloverETL Engine installation - downloadable for example for SF.net CloverETL Engine OSS

Just grab that and unzip the cloverETL.rel-3-2-0.zip. However there seems to be some problem with the Windows version of the analyzeDB.bat - try to use the one attached to this post instead.

3

Thanks very much for the information.

Scott

4

Solved:Using MS Access Driver in CloverETL with Office 2007 on Windows 7 64Bit

I had to install the 64 Bit MS Access Driver, but System first did not accept, as Office 2007 (32) is installed. Found the solution here:

http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/

Then used this connection String:
jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=