[fpc-pascal] Reading MDB (MS-Access) data under Linux

[Graeme Geldenhuys]

Hi,

I need to write a data conversion app. Has anybody read MDB data under
Linux yet, using SqlDB? I saw there was a ODBC database component, and
searched by Ubuntu repository. I installed 'iodbc', 'libiodbc2' and
'libiodbc2-dev'. The 'iodbc' package came with a GTK ODBC Admin app, but
I have no clue how ODBC works.

* Anybody that can confirm that I can read MDB files via SqlDB's ODBC
component.

* Any pointers and URL's on how to setup ODBC under Linux (Ubuntu)?


Any help or pointers would me much appreciated.


Regards,
- Graeme -

[Jim]

Post by Graeme Geldenhuys
Hi,
I need to write a data conversion app. Has anybody read MDB data under
Linux yet, using SqlDB? I saw there was a ODBC database component, and
searched by Ubuntu repository. I installed 'iodbc', 'libiodbc2' and
'libiodbc2-dev'. The 'iodbc' package came with a GTK ODBC Admin app, but
I have no clue how ODBC works.
* Anybody that can confirm that I can read MDB files via SqlDB's ODBC
component.
* Any pointers and URL's on how to setup ODBC under Linux (Ubuntu)?
Any help or pointers would me much appreciated.
Regards,
- Graeme -

Hi Graeme,

I suspected there's no ODBC driver for Access under Linux... but:
There is a tool to read mdb files, have only tried it once or so:
http://mdbtools.sourceforge.net/
Don't think it's an ODBC driver though.... hmmm apparently the docs
http://mdbtools.sourceforge.net/faq.html#what
tell me I'm wrong ;)
aptitude search mdb
returns these promising results:
libmdbodbc
mdbtools
The
unixodbc
package also shows up.
(on an old Ubuntu 7.10 machine)

Apparently you have to install unixODBC. IIRC there are 2 free ODBC
driver managers under Linux: unixODBC and iODBC.
I've used one or the other in the past, wasn't that hard to get working,
IIRC you had to edit some text/ini files that specified which driver (=
the mdbtools ODBC driver in your case) you wanted to use.
Then setup a ODBC data source more or less like in Windows...

Might be a good idea to remove iodbc etc to lessen scope for confusion.

Good luck,

[Michael Van Canneyt]

Post by Graeme Geldenhuys
Hi,
I need to write a data conversion app. Has anybody read MDB data under
Linux yet, using SqlDB? I saw there was a ODBC database component, and
searched by Ubuntu repository. I installed 'iodbc', 'libiodbc2' and
'libiodbc2-dev'. The 'iodbc' package came with a GTK ODBC Admin app, but
I have no clue how ODBC works.

This is not enough. You must install a ODBC driver for access database.
The MDBTools contain one; install "libmdbodbc" (it will install other mdb tools).

You must make a DSN in the ODBC configuration that defines your database.
Install unixodbc-bin and run the (graphical) odbcconfig tool.
It's pretty straightforward from then on.

Post by Graeme Geldenhuys
* Anybody that can confirm that I can read MDB files via SqlDB's ODBC
component.

On windows it can.

Post by Graeme Geldenhuys
* Any pointers and URL's on how to setup ODBC under Linux (Ubuntu)?

There is little to set up:
Set up the DSN, and point the TODBCConnection to the DSN you set up.
Then run your query.

But be careful, the mdb tools SQL abilities are rather limited.
Don't expect to be able to run just about any SQL query.

If you just need SELECT * FROM TABLE, then that should work fine.

Michael.

[Bo Berglund]

On Mon, 1 Nov 2010 13:00:23 +0100 (CET), Michael Van Canneyt

Post by Michael Van Canneyt

Post by Graeme Geldenhuys
Hi,
I need to write a data conversion app. Has anybody read MDB data under
Linux yet, using SqlDB? I saw there was a ODBC database component, and
searched by Ubuntu repository. I installed 'iodbc', 'libiodbc2' and
'libiodbc2-dev'. The 'iodbc' package came with a GTK ODBC Admin app, but
I have no clue how ODBC works.

This is not enough. You must install a ODBC driver for access database.
The MDBTools contain one; install "libmdbodbc" (it will install other mdb tools).
You must make a DSN in the ODBC configuration that defines your database.
Install unixodbc-bin and run the (graphical) odbcconfig tool.
It's pretty straightforward from then on.

Post by Graeme Geldenhuys
* Anybody that can confirm that I can read MDB files via SqlDB's ODBC
component.

On windows it can.

Post by Graeme Geldenhuys
* Any pointers and URL's on how to setup ODBC under Linux (Ubuntu)?

Set up the DSN, and point the TODBCConnection to the DSN you set up.
Then run your query.
But be careful, the mdb tools SQL abilities are rather limited.
Don't expect to be able to run just about any SQL query.
If you just need SELECT * FROM TABLE, then that should work fine.

Just tacking on the other MS database MSSQLServer:
Is it possible to also work with MS SQLServer databases via ODBC on
FPC from Linux? Obviously the SQLServer must be running on Windows so
this is about network access to the database engine.
When I make data sources in Windows for these databases I do not use
ODBC but a native SQLServer driver, is such available also on Linux?

Bo Berglund

[Andreas Schneider]

Post by Bo Berglund
Is it possible to also work with MS SQLServer databases via ODBC on
FPC from Linux? Obviously the SQLServer must be running on Windows so
this is about network access to the database engine.
When I make data sources in Windows for these databases I do not use
ODBC but a native SQLServer driver, is such available also on Linux?
Bo Berglund

I only know of a unixodbc driver called FreeTDS. I successfully
connected to a MSSQL Server 2008 from my FPC application on Linux, using
unixodbc, freetds and sqldb :)

Best Regards,
Andreas Schneider.

[Artur Stuczynski]

Post by Andreas Schneider
I only know of a unixodbc driver called FreeTDS. I successfully
connected to a MSSQL Server 2008 from my FPC application on Linux, using
unixodbc, freetds and sqldb :)

I can confirm this setup (FreeTDS+unixodbc) only I accessed SQL Server
from Linux via Zeos instead of sqldb.

Artur

[Graeme Geldenhuys]

Post by Bo Berglund
Is it possible to also work with MS SQLServer databases via ODBC on

I would imagine the only obstacle in that would be to find a ODBC MS-SQL
Server driver for Linux. EasySoft has one listed, but I don't know if it's
for Linux and if it's commercial/free.

http://www.easysoft.com

As for accessing the MDB file via ODBC under Linux. No matter what I tried,
I couldn't connect to it. I installed all the mentioned apps and packages,
but no luck. I did find a MDB File Viewer app though, so I opened my MDB
file, and simply exported the tables I need to CSV files. My app will
process them from there...



Regards,
- Graeme -