Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

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

by PrincessofPERL (Initiate)
on Jun 12, 2014 at 02:09 UTC ( #1089635=perlquestion: print w/ replies, xml ) Need Help??
PrincessofPERL has asked for the wisdom of the Perl Monks concerning the following question:

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.

Comment on DBD::CSV::st execute failed. No such file or directory at C:/Perl64/lib/DBD/File.pm line 565
Select or Download Code
Re: DBD::CSV::st execute failed. No such file or directory at C:/Perl64/lib/DBD/File.pm line 565
by Anonymous Monk on Jun 12, 2014 at 03:36 UTC
    Try using absolute paths,
    use Path::Tiny qw/ path /; path( "point.csv" )->realpath;
      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
Re: DBD::CSV::st execute failed. No such file or directory at C:/Perl64/lib/DBD/File.pm line 565
by Tux (Monsignor) on Jun 12, 2014 at 06:18 UTC

    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.


    Enjoy, Have FUN! H.Merijn
      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;
        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

        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 });

        Enjoy, Have FUN! H.Merijn
      Forgot to reply with the versions
      DBD::CSV 0.26 DBD::ODBC 1.23 DBD::SQLite 1.29 DBI 1.609
      These are *constraints*

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1089635]
Approved by boftx
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (8)
As of 2014-08-20 13:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (113 votes), past polls