Re: [Iodbc-list] mdbtools and iodbc

[<iod...@li...>]

iod...@li... writes:

[snip]
> I can't actually get mdbtools to compile for iodbc at present, but I'm 
> battling away at that!

Worked for me here (Gentoo linux). 

iODBC: ./configure --prefix=/usr/local/stow/iodbc/ --disable-gui
       make
       sudo make install

mdbtools: ./configure --prefix=/usr/local/stow/mdb  \
                      --with-iodbc=/usr/local/stow/iodbc/ 
          make
          sudo make install

test connection: 
          export ODBCINI=$HOME/.odbc.ini
          $EDITOR $ODBCINI

            [ODBC Data Sources]
            mdb = access mdb thing

            [mdb]
            Description = MDBtools access driver
            Driver = /usr/local/stow/mdb/lib/libmdbodbc.so
            Database = /home/tim/temp/junk.mdb

I copied a northwind.mdb into place, ran 
        iodbctest DSN=mdb
and behold, it connects.

> However, using UnixODBC, when I execute on this Linux box (RH9) some Java
> code to access through JDBC-ODBC an mdb database file, (it is code that
> is know to work correctly on Windows), I obtain the following error
> message:
>
> 	[root@home Ex51]# java ex51/QuerySolution
         ^^^^
(erm, do you have to run as root? bit of a risk if your app goes hay-wire.)

> 	***Solution being executed***
> 	...Driver loaded
> 	Problem accessing the database java.sql.SQLException:
> 	[unixODBC][Driver Manager]Data source name not found, and no
> 	default driver specified

This could be a few things, but I would suspect that the driver manager (in
this case, unixodbc) doesn't know the environment variable ODBCINI. You
should try with iodbctest as a first port of call, see if that can make a
connection - then layer jODBC (or PHP, etc) on top of it, but get iodbctest
working first of all.

> After several days of not resolving this problem, I am close to the point
> of giving up, and perish the thought, moving to Windows albeit
> momentarily

Well, having made the connection with iODBC myself, this isn't entirely
necessary. However, the resultant ODBC driver might leave you wondering
about the statements it claims to support - for example, 

 | SQL>create table timtest (str char(255))
 | Error at Line : syntax error near create
 | 1: Couldn't parse SQL
 |  (1), SQLSTATE=08001

I'm not sure why that fails, but it seems to be a parsing problem rather
than read-onlyness (unless it's mapping its error-messages wrong). 

If you find this to be a problem, you could investigate OpenLink's 3-tier
ODBC-Agent connection model, where you make an ODBC connection to the ODBC
driver-manager layer on a remote box, in this case Windows, in order to use
the existing Access ODBC driver. You can find more on that at
<http://www.openlinksw.com/main/product.htm> under "MT Overview".

HTH,

~Tim
-- 
OpenLink Software
Tel: +44 (0) 20 8681 7701
Web: <http://www.openlinksw.com>
Universal Data Access & Data Integration Technology Providers