in reply to Fetching data from Access using DBI

The principle idea behind solving your problem is quoting the field names.

Well, I don't know whether you're using ODBC or ADO to connect from DBI to MS Access, but I can tell you from experience with ODBC: MS Access recognizes a different SQL dialect over ODBC, than its own bastardized SQL in for example Query Builder. In the latter, you quote field names and table names by putting square brackets around them; in ODBC, you're expected to use the SQL-92 standard double quotes. 

select * from "desc"

[download]

Replies are listed 'Best First'.
Re^2: Fetching data from Access using DBI
by hacheb (Novice) on Aug 09, 2009 at 22:24 UTC

    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
[reply]
[d/l]

      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]

      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]
      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]

In Section Seekers of Perl Wisdom