DBD::CSV::st execute failed. No such file or directory at C:/Perl64/lib/DBD/File.pm line 565

[PrincessofPERL]

Greetings, This is my first post here. I have been lurking many of the discussions on this site and YES I have searched this site, GOOGLE, etc. for hours on hints on how to solve my problem. I hope to be expert level some day and give back to the PERL community. I've been spinning the wheels on this problem. Here is my error message. The referenced file is located in the same directory as my Perl script.

D:\...>afs_filter.pl
DBD::CSV::st execute failed:
Execution ERROR: Cannot open afs: No such file or directory at C:/Perl
+64/lib/DBD/File.pm line 565
.

 [for Statement "select * from AFS where ID_DEVTYP like 'INTELI%' AND 
+ID_POINT like 'AUTO%'"] at D:\...\afs_filter.pl line 53.
DBD::CSV::st fetchrow_hashref failed: Attempt to fetch row without a p
+receeding execute () call or from a non-SELECT sta
tement [for Statement "select * from AFS where ID_DEVTYP like 'INTELI%
+' AND ID_POINT like 'AUTO%'"] at D:\...\afs_filter.pl line 60.

Version - Which Cannot Be Updated/Modified, it HAS to stay the same :(

This is perl, v5.10.1 built for MSWin32-x64-multi-thread
(with 2 registered patches, see perl -V for more detail)

Copyright 1987-2009, Larry Wall

Binary build 1007 [291969] provided by ActiveState http://www.ActiveSt
+ate.com
Built Jan 27 2010 14:12:21

Here is My Code

use warnings;
use strict;
use DBI;

# Create connection string to database point.csv (output file from sca
+daexport.pl)
my $dbh = DBI->connect ("dbi:CSV:csv_auto_diag=1", {
      f_dir   => ".",
      f_ext   => ".csv/r",
      f_enc   => "utf-8",
      file    => "point.csv",
      });

# Associate our csv file with the table name "AFS" and include custom 
+column names
$dbh->{csv_tables}->{AFS} = 
{
      file      => "point.csv",
      col_names => [qw( RECORD SUBSCRIPT ID_SUBSTN CO_SUBSTN AREA_SUBS
+TN ID_DEVTYP ID_DEVICE NAME_DEVICE AREA_DEVICE ID_MEAS ID_POINT SITE_
+POINT AREA_POINT )],
};

# Define and Execute SQL to select INTELI and AUTO points
my $sth = $dbh->prepare ("select * from AFS where ID_DEVTYP like 'INTE
+LI%' AND ID_POINT like 'AUTO%'");
$sth->execute;

I do not feel comfortable posting the sensitive file contents of point.csv but can give you the format with data replaced by letters

RECORD,SUBSCRIPT,ID_SUBSTN,CO_SUBSTN,AREA_SUBSTN,ID_DEVTYP,ID_DEVICE,N
+AME_DEVICE,AREA_DEVICE,ID_MEAS,ID_POINT,SITE_POINT,AREA_POINT
a,b,c,d,e,f,g,h,i,j,k,l,m
.
.

I would appreciate any specific/detailed guidance or code to try/correct the issue.

[Tux]

You forgot user and password arguments to connect, so your f_ext attribute was never seen. Now DBD::CSV does only looks for a file named point or POINT and ignores point.csv or POINT.CSV

I see you use both f_ext => ".csv/r" and csv_tables. That is, given the content of the file point.csv having a header line that matches what you stated, useless. Try again without the declaration of csv_tables:

use warnings;
use strict;
use DBI;

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
      f_ext      => ".csv/r",
      f_encoding => "utf-8", # f_enc is not a supported attribute
      });

my $sth = $dbh->prepare ("select * from POINT where ID_DEVTYP like 'IN
+TELI%' AND ID_POINT like 'AUTO%'");
$sth->execute;

csv_auto_diag => 1 is default on since DBD::CSV-0.30

f_dir => "." has always been the default

The SQL statement should be able to allow both point and POINT as table name in the select statement. You however did not mention the versions of DBI, DBD::CSV and SQL::Statement.

[PrincessofPERL]

Hi Merijn, Thanks for your reply. The version of PERL that I have to work with is perl5 (revision 5 version 10 subversion 1) configuration: ActivePerl Build 1007 291969:Compiled at Jan 27 2010 14:12:21 . . There is now some error in referencing the hashref to print to output

Use of uninitialized value in concatenation (.) or string at .. line 5
+5.

Here is my revised code

use warnings;
use strict;
use DBI;


# Create connection string to database point.csv 
my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
      f_ext      => ".csv/r",
      f_encoding => "utf-8",
      });

my $sth = $dbh->prepare ("select * from point where ID_DEVTYP like 'IN
+TELI%' AND ID_POINT like 'AUTO%'");
$sth->execute;

# Create AFS CSV with Columns
open (DAT_OUTPUT,">AFS.csv");
print DAT_OUTPUT "RECORD,SUBSCRIPT,ID_SUBSTN,CO_SUBSTN,AREA_SUBSTN,ID_
+DEVTYP,ID_DEVICE,NAME_DEVICE,AREA_DEVICE,ID_MEAS,ID_POINT,SITE_POINT,
+AREA_POINT\n";

# Cycle through SQL results on ROW basis and print to AFS CSV file
while (my $row = $sth->fetchrow_hashref) 
{
      print DAT_OUTPUT "$row->{RECORD},$row->{SUBSCRIPT},$row->{ID_SUB
+STN},$row->{CO_SUBSTN},$row->{AREA_SUBSTN},$row->{ID_DEVTYP},$row->{I
+D_DEVICE},$row->{NAME_DEVICE},$row->{AREA_DEVICE},$row->{ID_MEAS},$ro
+w->{ID_POINT},$row->{SITE_POINT},$row->{AREA_POINT}\n";
}
# Close file    
close DAT_OUTPUT;

[Tux]

f_ext was added in DBI-1.608 and DBD::CSV-0.25, so that should work. f_encoding however was added in DBI-1.611, so that will not.

You did not mention the version of SQL::Statement, which is the SQL parser for DBD::CSV and essential for almost any statements more complicated than select foo from bar;. You use LIKE.

If you want to use literal key names, you can almost never rely on its casing the way you do. You should tell DBI explicitly to return your key names in upper case or lower case when using fetchrow_hashref. Better yet, do not rely on this at all

The case insensitiveness for table names should work as of DBD::CSV-0.25, so you should be safe there too.

Use a CSV module to output your data!

use warnings;
use strict;
use DBI;
use Text::CSV_XS;

# Create connection string to database point.csv 
my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
    # CSV specific attributes
    f_ext              => ".csv/r",
    f_encoding         => "utf-8",
    # DBI attributes
    RaiseError         => 1,
    PrintError         => 1,
    ChopBlanks         => 1,
    ShowErrorStatement => 1,
    FetchHashKeyName   => "NAME_uc", # You want uc (I prefer lc)
    });

my $sth = $dbh->prepare ("select * from point where ID_DEVTYP like 'IN
+TELI%' AND ID_POINT like 'AUTO%'");
$sth->execute;
my @columns = @{$sth->{NAME_uc}};

# Create AFS CSV with Columns
my $csv = Text::CSV_XS->new ({ binary => 1, eol => "\r\n" });
open my $fh, ">", "AFS.csv" or die "AFS.csv: $!";
$csv->print ($fh, \@columns);

# Cycle through SQL results on ROW basis and print to AFS CSV file
while (my $row = $sth->fetchrow_hashref) {
    $csv->print ($fh, [ @{$row}{@columns} ]);
    }
# Close file    
close $fh;

With Text::CSV_XS-1.07 you can even simplify that to:

use Text::CSV_XS qw( csv );
:
:
my $sth = $dbh->prepare ("select * from point where ID_DEVTYP like 'IN
+TELI%' AND ID_POINT like 'AUTO%'");
$sth->execute;

csv (out => "AFS.csv", in => sub { $sth->fetchrow_hashref });

[poj]

Because you have SELECT * .. the column names in the hash are probably lower case. You can add a print join " ", keys %$row line to check this. To fix it I would define the column names in the SELECT and use fetchrow_array like this

#!perl
use warnings;
use strict;
use DBI;

my @col = qw(RECORD      SUBSCRIPT ID_SUBSTN CO_SUBSTN 
             AREA_SUBSTN ID_DEVTYP ID_DEVICE NAME_DEVICE 
             AREA_DEVICE ID_MEAS   ID_POINT  SITE_POINT AREA_POINT);
my $cols = join ",",@col;

# Create connection string to database point.csv 
my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
      f_ext      => ".csv/r",
      f_encoding => "utf-8",
      });

my $sth = $dbh->prepare ("SELECT $cols FROM point 
                          WHERE ID_DEVTYP LIKE 'INTELI%' 
                            AND ID_POINT  LIKE 'AUTO%'");
$sth->execute;

# Create AFS CSV with Columns
open DAT_OUTPUT,'>','AFS.csv' or die "Could not open AFS.csv : $!";
print DAT_OUTPUT $cols."\n";

# Cycle through SQL results on ROW basis and print to AFS CSV file
while (my @row = $sth->fetchrow_array) {
  print DAT_OUTPUT (join ",",@row)."\n";   
}
# Close file    
close DAT_OUTPUT;

poj

[PrincessofPERL]

Forgot to reply with the versions

DBD::CSV        0.26
DBD::ODBC       1.23
DBD::SQLite     1.29
DBI     1.609

These are *constraints*

[Anonymous Monk]

These are *constraints*

Those are all very old... looking for bugs in outdated modules most of which aren't on CPAN anymore is ... lonely work :) Good luck to you, obligatory :) Yes, even you can use CPAN, A Guide to Installing Modules, Top 11 (GOOD) reasons not to use someone else's Modules, Top Seven (Bad) Reasons Not To Use Modules

[Anonymous Monk]

Try using absolute paths,

 use Path::Tiny qw/ path /;
path( "point.csv" )->realpath;

[PrincessofPERL]

Howdy, Just wanted to follow-up with you and let you know I tried that bit of code but due to constraints I cannot upgrade my version of PERL or modules :( perl, v5.10.1 Build 1007 Error: Can't locate Path/Tiny.pm in @INC Thanks for your response