[mdb-dev] Problems to connect to mdb-file with unixODBC and mdbtools driver in Perl

[Sebastian Reinhardt]

Hi,
In past I have used mdb-tools- command line utils for reading data from
an mdb-file. Now I try to write an perl script by using DBD::ODBC and
libmdbodbc- driver.
But I can connect to mdb-file, if I put the database information in my
"~/.odbc.ini"- file ,only!
If I use this "ini-file"- input with this connect line:
my $dbh = DBI->connect('DBI:ODBC:MDBDSN', $user, $pass, { RaiseError => 1});
in my Perl script, I can connect to the file.
If I try to connect to the mdb-file with:
my $dbh =
DBI->connect('DBI:ODBC:DRIVER={MDBToolsODBC};database=/mnt/lvm/SNR_Soft/Kivitendo_Import/in/kramp/Kramp.dat'
$user, $pass, { RaiseError => 1});
it fails with this error:
DBI
connect('DRIVER={MDBToolsODBC};database=/mnt/lvm/SNR_Soft/Kivitendo_Import/in/kramp/Kramp.dat','',...)
failed: [unixODBC]Could not find DSN in connect string (SQL-08001)

This is my odbc.ini:
----------
[MDBDSN]
Description = MDBDSN
Driver = MDBToolsODBC
Database = /mnt/lvm/SNR_Soft/Kivitendo_Import/in/kramp/Kramp.dat
Servername = localhost
Port = 5432
----------

So why it fails without the ini file? The ini-file is no solution,
because I have to be able to put in different files!

Next problem is, that "mdb-tables" shows this tables contained in mdb- file:
------------------------------------------------
mdb-tables in/kramp/Kramp.dat
KATALOG Pricelist RC_01012011 Schnelldreher Stand T_Rabatt_codes
T_RC_neu T_upd_RC WEBLINKS
------------------------------------------------
But, if I read out the tables with "my @tables = $dbh->tables();" (with
connected data file by using odbc.ini), I get:
------------------------------------------------
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿMSysObjectsÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿMSysACEsÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿMSysQueriesÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿMSysRelationshipsÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿADD_CHCHF_DATAÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿDEL_DATAÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿKATALOGÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿMSysAccessObjectsÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿMSysIMEXColumnsÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿMSysIMEXSpecsÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿPricelistÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_upd_1050_Fÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_UPD_ARTIKEL_Berndÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_upd_manniÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_upd_Produktfÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_upd_Rabattcodeÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_upd_RCÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_upd_rc_01012011ÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_upd_schnelldreherÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_WEBLINKSÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿRC_01012011ÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿSchnelldreherÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿStandÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿT_Rabatt_codesÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿT_RC_neuÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿT_upd_RCÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿUPD_KATALOGÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿWEBLINKSÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿADD_AGEEUR_DATAÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿGET_DATA_AGEEUR_2007ÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿMSysAccessXMLÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_upd_Produktf_Ers_duÿÿÿÿÿÿ
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿQ_Update_Manni_Schneeÿÿÿÿÿÿ
------------------------------------------------

So, what is wrong? Can anyone help me? I have to read out more then
250000 data lines for parsing. Using command line tools with an Perl
script (sytem call inside the script), this process takes up to 4 days
(2xQuad-core server, no lame duck!)! Not really usable. I hope with the
mdbtools- driver it is much more faster.....


Is there any documentation about the libmdbodbc- driver available?

[Jean-Michel Vourgère]

Post by Sebastian Reinhardt
Hi,
In past I have used mdb-tools- command line utils for reading data from
an mdb-file. Now I try to write an perl script by using DBD::ODBC and
libmdbodbc- driver.
But I can connect to mdb-file, if I put the database information in my
"~/.odbc.ini"- file ,only!
my $dbh = DBI->connect('DBI:ODBC:MDBDSN', $user, $pass, { RaiseError => 1});
in my Perl script, I can connect to the file.
my $dbh =
DBI->connect('DBI:ODBC:DRIVER={MDBToolsODBC};database=/mnt/lvm/SNR_Soft/Kivitendo_Import/in/kramp/Kramp.dat'
$user, $pass, { RaiseError => 1});
DBI
connect('DRIVER={MDBToolsODBC};database=/mnt/lvm/SNR_Soft/Kivitendo_Import/in/kramp/Kramp.dat','',...)
failed: [unixODBC]Could not find DSN in connect string (SQL-08001)
----------
[MDBDSN]
Description = MDBDSN
Driver = MDBToolsODBC
Database = /mnt/lvm/SNR_Soft/Kivitendo_Import/in/kramp/Kramp.dat
Servername = localhost
Port = 5432
----------
So why it fails without the ini file? The ini-file is no solution,
because I have to be able to put in different files!

You need a valid DSN. This is "not optimal" I agree... :/
The good news is that you can override the database.

So
connect('DRIVER={MDBToolsODBC};DSN=MDBSN;Database=/mnt/lvm/SNR_Soft/Kivitendo_Import/in/kramp/Kramp2.mdb','',...)
might work.

Note the capital D in Database.

[Sebastian Reinhardt]

Post by Jean-Michel Vourgère
You need a valid DSN. This is "not optimal" I agree... :/
The good news is that you can override the database.
So
connect('DRIVER={MDBToolsODBC};DSN=MDBSN;Database=/mnt/lvm/SNR_Soft/Kivitendo_Import/in/kramp/Kramp2.mdb','',...)
might work.
Note the capital D in Database.

Thanks for Your advise. Is it the capital letter "D" only. Once again,
small mistake with huge effect.
Unfortunatally I solved this issue in an different way by using an ASCII
file with fixed row width.
But I will keep it in mind for next time...Thanks