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

ok, there's progress thanks to you. I'm using ODBC. Quoting field names does work indeed.

There still remain one problem; the query returns only a handfull of rows instead of the 2000 expected. Which brings me back to this 'illegal character' thing I was refering to in my first post.

As the data fetching occurs, something must go wrong somewhere and stop the process. I'll have to identify the offending column.

Can you explain why trying to order the result set fails? 

$query = $dbh->column_info('','',$table,'');
$column_info = $query->fetchall_arrayref();
for $i ( 0 .. $#{$column_info} ) {
   $fields .= qq| "$column_info->[$i][3]",|
}
chop $fields;
$sql = qq|SELECT $fields FROM $table ORDER BY "REF"|;
$query = $dbh->prepare($sql) or die "could not prepare $sql : $DBI::er
+rstr\n";
$query->execute  or die "could not execute $sql  : $DBI::errstr\n";
$result = $query->fetchall_arrayref();

[download]

This returns nothing. And does not produce any error neither.

H

Replies are listed 'Best First'.
Re^3: Fetching data from Access using DBI
by mje (Curate) on Aug 11, 2009 at 09:19 UTC

    I fail to see the point of using column_info to obtain all column names then using them in the select - surely that is "select *". Try to simplify the sql and code you are using to locate the problem. Start by changing the SQL to qq/select * from $table ORDER by "REF"/ and if that does not work check the $table and quoting of $table etc. Try to find the smallest uncomplicated SQL which works and the smallest change that stops it working.

[reply]

      my using column_info is just experimental: I'm learning to use this interface.

      See also my reply to bart.

      H
[reply]
Re^3: Fetching data from Access using DBI
by bart (Canon) on Aug 11, 2009 at 10:03 UTC

    I don't really know what's going on, but I do get the impression that something is crashing: be it MS Access, ODBC itself, or DBD::ODBC.

    This appears to be a bit out of our league, perhaps you should take this to the DBI-users mailing list.

    Didn't you say just select * from mytable order by "REF" is also problematic? Doesn't that imply that the column REF is, at the very least, part of the problem?

    To test the other columns, I wouldn't try them fetching them all at once, but instead, one at a time, each in its own query, and see which queries produce problems.

[reply]
[d/l]
[select]

      well, I'm thinking maybe it's a bug

      I've tried different queries: 

      my $sql0 = qq|SELECT "MARQUE" FROM $table|;
my $sql1 = qq|SELECT "DESC1" FROM $table|;
my $sql2 = qq|SELECT "DESC2" FROM $table|;
my $sql3 = qq|SELECT "PRIX" FROM $table|;
my $sql4 = qq|SELECT "REF" FROM $table|;
my $sql5 = qq|SELECT "TAILLE", "COULEUR" FROM $table|;
my $sql6 = qq|SELECT "REF" FROM $table LIMIT 5|;
my $sql7 = qq|SELECT "DESC1" FROM $table ORDER BY "REF"|;
my $sql8 = qq|SELECT "REF" FROM $table ORDER BY "PRIX"|;

      [download]

      sql0, 1, 3, 4, 5 return all records

      sql2 produces a 500 internal server error (so there is a problem in one or more rows in the "DESC2" column)

      sql6, 7 and 8 return nothing

      Like you said, I do not think now that the problem is perl-related. It's either DBI or DBD::ODBC

      H
[reply]
[d/l]

        500 internal server error sounds awfully like an HTTP error and not an ODBC one. What happens when you run sql2 from a command prompt within a simple perl script? What error is raised/output? When we see what actual error you are getting we might be able to help.

        With sql 6/7/8 it would probably be useful to know the schema and possibly a DBD::ODBC trace which you can get by setting (and exporting if UNIX) DBI_TRACE to "15=x.log" then the trace goes to x.log. But you need to simplify further to a straight forward perl script run from the command line. BTW, you did run DBD::ODBC's test suite to MS Access didn't you?

        What versions of DBI and DBD::ODBC are you using? and what platform are you running this on.

[reply]
Re^3: Fetching data from Access using DBI
by Anonymous Monk on Aug 13, 2009 at 11:57 UTC
    On MS Access 2002/2003 I type a request like this : SELECT Table.field FROM Table; You may try it...Good luck
[reply]
      Sorry ... SELECT Table . Field FROM Table; Without the spaces
[reply]
        Double sorry ... SELECT Table . \ Field \ FROM Table;
[reply]

In Section Seekers of Perl Wisdom