in reply to Re^5: Fetching data from Access using DBI
in thread Fetching data from Access using DBI

I run it from the command line on an ubuntu box.

He's the dbi rap: 

henri@henri-desktop:~$ perl -MDBI -e 'DBI->installed_versions;'
  Perl            : 5.008008    (i486-linux-gnu-thread-multi)
  OS              : linux    (2.6.24-19-server)
  DBI             : 1.609
  DBD::mysql      : 4.012
  DBD::Sponge     : 12.010002
  DBD::Proxy      : 0.2004
  DBD::ODBC       : 1.22
  DBD::Multiplex  : 2.09
  DBD::Gofer      : 0.011565
  DBD::File       : 0.37
  DBD::ExampleP   : 12.010007
  DBD::DBM        : 0.03

[download]
what got ran : 
use DBI;
use DBD::ODBC;
use strict;
use warnings;

DBI->trace(DBD::ODBC->parse_trace_flags('odbcconnection|odbcunicode'))
+;

my $dbh = DBI->connect('dbi:ODBC:boutique', {RaiseError => 1, PrintErr
+or => 1} ); # data source set up in /etc/odbc.ini
my $table = "ARTICLES";
my $sql;
my $query;
my $result;
my ($i, $j);

$sql = SELECT "REF" FROM "$table" LIMIT 5|;
print qq~sql : $sql\n\n~;

$query = $dbh->prepare($sql);
$query->execute;
$result = $query->fetchall_arrayref();
for $i ( 0 .. $#{$result} ) {
   print qq~row $i : \t~;
      for $j ( 0 .. $#{$result->[$i]} ) {
         if ($result->[$i][$j] eq ""){
            print qq~NULL\t~;
         } else {
        print qq~$result->[$i][$j]\t~;
     }
      }
      print qq~\n~;
}

$query->finish();
$dbh->disconnect();

[download]
trace ends with : 
sql : SELECT "REF" FROM "ARTICLES" LIMIT 5

Error at Line : syntax error near LIMIT

[download]
the last few lines of /tmp/x.log are : 
    SQLDriverConnect 'boutique', 'HASH(0x8153c28)', 'xxxx'
    SQLConnect 'boutique', 'HASH(0x8153c28)'
Turning autocommit on
DRIVER_ODBC_VER = 
DRIVER_NAME = 
DRIVER_VERSION = 
DBD::ODBC is unicode built : NO
SQL_DBMS_NAME = zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
+zzzzzzzzzz
SQLMoreResults supported: 0
SQLDescribeParam supported: 0
SQLDescribeParam supported: 0
    Processing sql in non-unicode mode
SQLDisconnect=0

[download]
The 500 error I was refering to was partly solved by the command 
export LC_TYPE=fr_FR

[download]

But I got only about 20 rows of of the 2000.

H

Replies are listed 'Best First'.
Re^7: Fetching data from Access using DBI
by mje (Curate) on Aug 12, 2009 at 07:48 UTC

    I believe the syntax in MS Access is "top N" not "limit N".

    What ODBC driver are you using to access MS Access from ubuntu?

    You have only shown a partial trace. After calling connect set TraceLevel to 15 on the connection handle.

[reply]
      Whatever clause (order by, limit, or top) is added to the select, the execution stops with a 'syntax error near said clause

      The driver is configured this way: 

      henri@henri-desktop:~$ cat /etc/odbcinst.ini
[ODBC]
Trace = yes
TraceFile = /tmp/unixodbc_trace.log

[Microsoft Access Driver (*.mdb)] 
Description = MDB tools ODBC driver
Driver = /usr/lib/libmdbodbc.so.0
FileUsage = 1
henri@henri-desktop:~$ cat /etc/odbc.ini
[boutique]
Description = access database
Driver = Microsoft Access Driver (*.mdb) 
Database = /var/www/htdocs/boutique.mdb
Host = localhost

      [download]

      here's the full dbi trace from an execution with a working query (select "ref" from $table) 

      henri@henri-desktop:/tmp$ cat dbitrace.log 
    DBI 1.609-ithread default trace level set to 0x0/15 (pid 11125) at
+ DBI.pm line 273 via access-mdb.pl line 3
    Note: perl is running without the recommended perl -w option
install_method DBI::db::get_info    , flags 0x2a00,
    usage: min 2, max 2, '$info_type'
install_method DBI::db::take_imp_data, flags 0x10000,
    usage: min 1, max 1, ''
install_method DBI::db::disconnect  , flags 0x10c00,
    usage: min 1, max 1, ''
install_method DBI::db::selectrow_array, flags 0x2000,
    usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
install_method DBI::db::tables      , flags 0x2200,
    usage: min 1, max 6, '$catalog, $schema, $table, $type [, \%attr ]
+'
install_method DBI::db::quote_identifier, flags 0x0430,
    usage: min 2, max 6, '$name [, ...] [, \%attr ]'
install_method DBI::db::clone       ,
    usage: min 1, max 2, '[\%attr]'
install_method DBI::db::quote       , flags 0x0430,
    usage: min 2, max 3, '$string [, $data_type ]'
install_method DBI::db::type_info   , flags 0x2200,
    usage: min 1, max 2, '$data_type'
install_method DBI::db::statistics_info, flags 0xaa00,
    usage: min 6, max 7, '$catalog, $schema, $table, $unique_only, $qu
+ick, [, \%attr ]'
install_method DBI::db::selectrow_arrayref, flags 0x2000,
    usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
install_method DBI::db::begin_work  , flags 0x0400,
    usage: min 1, max 2, '[ \%attr ]'
install_method DBI::db::last_insert_id, flags 0x2800,
    usage: min 5, max 6, '$catalog, $schema, $table_name, $field_name 
+[, \%attr ]'
install_method DBI::db::foreign_key_info, flags 0xaa00,
    usage: min 7, max 8, '$pk_catalog, $pk_schema, $pk_table, $fk_cata
+log, $fk_schema, $fk_table [, \%attr ]'
install_method DBI::db::primary_key , flags 0x2200,
    usage: min 4, max 5, '$catalog, $schema, $table [, \%attr ]'
install_method DBI::db::commit      , flags 0x0c80,
    usage: min 1, max 1, ''
install_method DBI::db::ping        , flags 0x0404,
    usage: min 1, max 1, ''
install_method DBI::db::selectall_arrayref, flags 0x2000,
    usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
install_method DBI::db::type_info_all, flags 0x2a00,
    usage: min 1, max 1, ''
install_method DBI::db::do          , flags 0x3200,
    usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
install_method DBI::db::selectcol_arrayref, flags 0x2000,
    usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
install_method DBI::db::prepare_cached, flags 0xa200,
    usage: min 2, max 4, '$statement [, \%attr [, $if_active ] ]'
install_method DBI::db::rows        , flags 0x0004
install_method DBI::db::rollback    , flags 0x0c80,
    usage: min 1, max 1, ''
install_method DBI::db::column_info , flags 0xaa00,
    usage: min 5, max 6, '$catalog, $schema, $table, $column [, \%attr
+ ]'
install_method DBI::db::table_info  , flags 0xaa00,
    usage: min 1, max 6, '$catalog, $schema, $table, $type [, \%attr ]
+'
install_method DBI::db::primary_key_info, flags 0xaa00,
    usage: min 4, max 5, '$catalog, $schema, $table [, \%attr ]'
install_method DBI::db::prepare     , flags 0xa200,
    usage: min 2, max 3, '$statement [, \%attr]'
install_method DBI::db::preparse    
install_method DBI::db::connected   , flags 0x0004,
    usage: min 1, max 0, ''
install_method DBI::db::data_sources, flags 0x0200,
    usage: min 1, max 2, '[\%attr]'
install_method DBI::db::selectall_hashref, flags 0x2000,
    usage: min 3, max 0, '$statement, $keyfield [, \%attr [, @bind_par
+ams ] ]'
install_method DBI::db::selectrow_hashref, flags 0x2000,
    usage: min 2, max 0, '$statement [, \%attr [, @bind_params ] ]'
install_method DBI::dr::default_user,
    usage: min 3, max 4, '$user, $pass [, \%attr]'
install_method DBI::dr::data_sources, flags 0x0800,
    usage: min 1, max 2, '[\%attr]'
install_method DBI::dr::dbixs_revision, flags 0x0004
install_method DBI::dr::disconnect_all, flags 0x0800,
    usage: min 1, max 1, ''
install_method DBI::dr::connect_cached, flags 0x8000, H 3,
    usage: min 1, max 5, '[$db [,$user [,$passwd [,\%attr]]]]'
install_method DBI::dr::connect     , flags 0x8000, H 3,
    usage: min 1, max 5, '[$db [,$user [,$passwd [,\%attr]]]]'
install_method DBI::st::more_results,
    usage: min 1, max 1, ''
install_method DBI::st::blob_read   ,
    usage: min 4, max 5, '$field, $offset, $len [, \$buf [, $bufoffset
+]]'
install_method DBI::st::fetchall_hashref,
    usage: min 2, max 2, '$key_field'
install_method DBI::st::bind_param_inout_array,
    usage: min 4, max 5, '$parameter, \@var, $maxlen, [, \%attr]'
install_method DBI::st::finish      ,
    usage: min 1, max 1, ''
install_method DBI::st::rows        , flags 0x0004
install_method DBI::st::execute_for_fetch, flags 0x5040,
    usage: min 2, max 3, '$fetch_sub [, $tuple_status]'
install_method DBI::st::fetchrow_hashref
install_method DBI::st::_get_fbav   
install_method DBI::st::_set_fbav   , T 6
install_method DBI::st::fetchrow    
install_method DBI::st::fetch       
install_method DBI::st::execute_array, flags 0x5040,
    usage: min 2, max 0, '\%attribs [, @args]'
install_method DBI::st::dump_results,
    usage: min 1, max 5, '$maxfieldlen, $linesep, $fieldsep, $filehand
+le'
install_method DBI::st::bind_param_array,
    usage: min 3, max 4, '$parameter, $var [, \%attr]'
install_method DBI::st::fetchrow_array
install_method DBI::st::execute     , flags 0x1040,
    usage: min 1, max 0, '[@args]'
install_method DBI::st::bind_col    ,
    usage: min 3, max 4, '$column, \$var [, \%attr]'
install_method DBI::st::fetchall_arrayref,
    usage: min 1, max 3, '[ $slice [, $max_rows]]'
install_method DBI::st::fetchrow_arrayref
install_method DBI::st::bind_param_inout,
    usage: min 4, max 5, '$parameter, \$var, $maxlen, [, \%attr]'
install_method DBI::st::bind_columns,
    usage: min 2, max 0, '\$var1 [, \$var2, ...]'
install_method DBI::st::cancel      , flags 0x0800,
    usage: min 1, max 1, ''
install_method DBI::st::blob_copy_to_file,
    usage: min 3, max 3, '$field, $filename_or_handleref'
install_method DBI::st::bind_param  ,
    usage: min 3, max 4, '$parameter, $var [, \%attr]'
install_method DBI::common::parse_trace_flag, flags 0x0404, T 8,
    usage: min 2, max 2, '$name'
install_method DBI::common::errstr  , flags 0x0004
install_method DBI::common::trace_msg, flags 0x0004, T 8,
    usage: min 2, max 3, '$message_text [, $min_level ]'
install_method DBI::common::err     , flags 0x0004
install_method DBI::common::CLEAR   , flags 0x0004
install_method DBI::common::state   , flags 0x0004
install_method DBI::common::_not_impl
install_method DBI::common::NEXTKEY , flags 0x0004
install_method DBI::common::trace   , flags 0x0004,
    usage: min 1, max 3, '[$trace_level, [$filename]]'
install_method DBI::common::debug   , flags 0x0004,
    usage: min 1, max 2, '[$debug_level]'
install_method DBI::common::parse_trace_flags, flags 0x0404, T 8,
    usage: min 2, max 2, '$flags'
install_method DBI::common::private_attribute_info
install_method DBI::common::swap_inner_handle,
    usage: min 2, max 3, '$h [, $allow_reparent ]'
install_method DBI::common::EXISTS  , flags 0x0004
install_method DBI::common::visit_child_handles, flags 0x0404, T 4,
    usage: min 2, max 3, '$coderef [, $info ]'
install_method DBI::common::FETCH   , flags 0x0404
install_method DBI::common::FIRSTKEY, flags 0x0004
install_method DBI::common::set_err , flags 0x0010,
    usage: min 3, max 6, '$err, $errmsg [, $state, $method, $rv]'
install_method DBI::common::DESTROY , flags 0x10004
install_method DBI::common::dump_handle, flags 0x0004,
    usage: min 1, max 3, '[$message [, $level]]'
install_method DBI::common::FETCH_many, flags 0x0404
install_method DBI::common::can     , flags 0x0100
install_method DBI::common::STORE   , flags 0x041c
install_method DBI::common::private_data, flags 0x0004,
    usage: min 1, max 1, ''
install_method DBI::common::func    , flags 0x0006
non-Unicode login6
dbd_db_login6
    SQLDriverConnect 'boutique', 'HASH(0x8153c28)', 'xxxx'
    SQLConnect 'boutique', 'HASH(0x8153c28)'
Turning autocommit on
DRIVER_ODBC_VER = 
DRIVER_NAME = 
DRIVER_VERSION = 
DBD::ODBC is unicode built : NO
SQL_DBMS_NAME = zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
+zzzzzzzzzz
SQLMoreResults supported: 0
SQLDescribeParam supported: 0
SQLDescribeParam supported: 0
    Processing sql in non-unicode mode
SQLDisconnect=0

      [download]

      the query select "desc2" from $table returns the first row and then produces a segmentation fault (or 500 server error if run from a browser). the output in dbitrace.log does not change. Every select on other fields goes fine, only the "desc2" crashes.

      I'll submit the case to the dbi mailing list. For practical purposes, however, I'll try another route, maybe CVS

      When I was testing my odbc setup, I used openoffice base to open, via the odbc driver, the m$ access base. I was able to open the file but oo base crashed when I tried to open the "articles" table. So it appears that something is wrong with the driver. Could that be related to unicode?

      H
[reply]
[d/l]
[select]

        mdbtools is flawed in a number of areas - you are really going to struggle to use this successfully as an ODBC driver. The last release was Jun 18 2004. I strongly suggest that if you absolutely have to access MS Access from a unix machine you consider using an ODBC-ODBC bridge or another commericial driver (as I know of no opensource one that is close to working) - mdbtools is not up to it.

        By all means submit this to the dbi-users list but I am on that list and I maintain DBD::ODBC and I'm not going to spend any time on mdbtools because I've wasted too much time in the past debugging some code to find it is a buggy mdbtools. Believe me, it is not a viable option for accessing MDB files.

[reply]

In Section Seekers of Perl Wisdom