Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

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 ( #1089653=note: print w/ replies, xml ) Need Help??


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

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


Comment on Re: DBD::CSV::st execute failed. No such file or directory at C:/Perl64/lib/DBD/File.pm line 565
Select or Download Code
Re^2: DBD::CSV::st execute failed. No such file or directory at C:/Perl64/lib/DBD/File.pm line 565
by PrincessofPERL (Initiate) on Jun 17, 2014 at 18:37 UTC
    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
        Thank you for your response. Here is a Challenge Your code works in v5.10.1 Binary build 1007 291969 but not Binary build 1006 291086 My former code worked in Build 1006 but not 1007. point.csv does exist.. in the same directory Here is my error message.. still peculiar to a file open issue. The File.pm is ~2009 on the 1007 build and ~2008 on the 1006 build.
        C:\....>afs_filter.pl Execution ERROR: Cannot open point: No such file or directory at C:/P +erl64/lib/DBD/File.pm line 579. . DBD::CSV::st fetchrow_array failed: Attempt to fetch row without a pre +ceeding execute() call or from a non-SELECT statement [for Statement +"SELECT RECORD,SUBSCRIPT,ID_SUBSTN,CO_SUBSTN,AREA_SUBSTN,ID_D EVTYP,ID_DEVICE,NAME_DEVICE,AREA_DEVICE,ID_MEAS,ID_POINT,SITE_POINT,AR +EA_POINT FROM point WHERE ID_DEVTYP LIKE 'INTELI%' AND ID_POINT LIKE + 'AUTO%'"] at C:\....afs_filter.pl line 53.
        Any ideas? Thanks

      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
Re^2: DBD::CSV::st execute failed. No such file or directory at C:/Perl64/lib/DBD/File.pm line 565
by PrincessofPERL (Initiate) on Jun 17, 2014 at 18:58 UTC
    Forgot to reply with the versions
    DBD::CSV 0.26 DBD::ODBC 1.23 DBD::SQLite 1.29 DBI 1.609
    These are *constraints*

        Greetings! Once again Thank you for your detailed replies and guidance. I have had some challenges with my PERL project. My code solution had to work with PERL v5.10.1 Build 1006, and another with Build 1007 (at the time I had no idea IT installed a different Build version on server #2). I had developed, tested, and validated on my project on Build 1006. It was to my surprise that the Build 1006 solution I made did not work on a server running Build 1007. The error message was the following:

        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

        That message first appeared on the 2007 Build but NOT 2006 when I used this original code for my solution:

        # 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;

        After your advice I had found a solution to my delight that worked on the 1007 build. But that self-esteem was very short lived when I discovered to my surprise that code did not work on the older 1006 build. Here is that code reference that I changed and worked on Build 1007 but lost compatibility with Build 1006

        my @col = qw(RECORD SUBSCRIPT ID_SUBSTN CO_SUBSTN AREA_SUBSTN ID_DEVTY +P 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;

        So I was stuck with two solutions. One set of scripts that worked with Build 1006, and one set that worked with Build 1007. I think it has been a bit of luck, following some of your suggestions, and experimentation that I have reached a happy medium that works with two different versions of the File.pm module from build 1006 to 1007. This is how I tricked File.pm on both Build 1006 and 1007 to accept my file. May not be pretty.. but it works.

        my @col = qw(RECORD SUBSCRIPT ID_SUBSTN CO_SUBSTN AREA_SUBSTN ID_DEVTY +P 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 (output file from sca +daexport.pl) my $dbh = DBI->connect ("dbi:CSV:", undef, undef, { f_encoding => "utf-8", }); # Define and Execute SQL to select INTELI and AUTO points my $sth = $dbh->prepare ("select $cols from point.csv where ID_DEVTYP +like 'INTELI%' AND ID_POINT like 'AUTO%'"); $sth->execute;

        and my hash solution now works with this. Here is the entire solution

        use warnings; use strict; use DBI; my @col = qw(RECORD SUBSCRIPT ID_SUBSTN CO_SUBSTN AREA_SUBSTN ID_DEVTY +P 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 (output file from sca +daexport.pl) my $dbh = DBI->connect ("dbi:CSV:", undef, undef, { f_encoding => "utf-8", }); # Define and Execute SQL to select INTELI and AUTO points my $sth = $dbh->prepare ("select $cols from point.csv where ID_DEVTYP +like 'INTELI%' 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;

        I've been recently told that my client has yet another server with perl 5.8.8 where this needs to run and they will not consider upgrading because of risk. I'm going to cross my fingers and do a shaman dance hoping this will work on an even older version of code. Cheers!

        And thank-you for your contributions to File.pm, I noticed your name in the file header.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (20)
As of 2014-10-30 15:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (208 votes), past polls