Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

DBD::CSV::prepare() problems

by polelo (Initiate)
on Aug 24, 2012 at 12:50 UTC ( #989524=perlquestion: print w/ replies, xml ) Need Help??
polelo has asked for the wisdom of the Perl Monks concerning the following question:

I use perl DBD::CSV to process and correlate information contained in several csv files received from an external computer,on a daily interval. The incoming file names are case sentive for uniqueness. On MS Windows platforms the DBD::CSV::prepare() command identifies each file correctly and processes it without dying/exiting. However when the same processing script is ported to UNIX/LINUX O/S it reports that the files cannot be found in the specied OS path; the file names are exactly the same as listed in the MS Windows O/S.

On looking at one of the error message (DBD::CSV::st execute failed: Cannot open /in/17_trx_22aug2012_0405.csv:) I noticed that the file names,e.g "17_trx_22aug2012_0405" appear(s) in lowercase on the error report, instead of "17_Trx_22Aug2012_0405", which is how it was associated with a tablename before the prepare() function.

Your help will be much appreciated on the following questions: 1) Is it possible that the prepare() method is converting each file name to lowercase, and this might not be a problem on Windows, but will be on UNIX/LINUX O/S? 2) How can I instruct the prepare() not to lowercase the literals of the DBD::CSV table association? 3) How can I view the prepared SQL (after the prepare() function)?

Comment on DBD::CSV::prepare() problems
Re: DBD::CSV::prepare() problems
by Tux (Monsignor) on Aug 24, 2012 at 13:13 UTC
    1. SQL::Statement deals with table-names case-insensitive.
    2. Show us how you connect to the "database" and how you specify file/table names
    3. Printing a prepared statement should be doable with say $sth->{Statement};
    4. We need to know what versions you use for DBI, Text::CSV_XS, SQL::Statement, and DBD::CSV

    Enjoy, Have FUN! H.Merijn

      Dear Tux

      Thanks for your reply

      this is how I connect to the CSV database

      use DBI; ... #MS Windows path to files my $fpath="G:\\in"; #LINUX server paths #my $fpath="/users/excel/in"; my $csvfile="Trx_24Aug2012_0409.csv"; my $cellh = DBI->connect("DBI:CSV:f_dir=$fpath;csv_eol=\n;"); #associate csv file with tablename my $fname=$fpath.'/'.$csvfile; $cellh->{'csv_tables'}->{'CELLS'} = {'file' =>$fname}; my $sql="select * from CELLS"; my $stcellh = $cellh->prepare($sql) or die "$DBI::errstr\n"; $stcellh->execute() or die "$DBI::errstr\n"; my $colunmcount = $stcellh->{NUM_OF_FIELDS}; print "$colunmcount\n\n"; $stcellh->finish();

      Thanks again

      Polelo

        Hello Polelo

        I tried with my FreeBSD but it says nothing for filename. No error message, no warnings.

        #!/usr/bin/perl use strict; use warnings; use DBI; my $dsn="dbi:CSV:"; my $dbh=DBI->connect($dsn, "","",{ f_encoding => "utf8", f_dir => "./", })or die DBI->errstr; $dbh->{csv_tables}->{t1} = { 'file' => '17_Trx_22Aug2012_0405.csv', 'eol' => "\n", }; my $sth; $sth=$dbh->prepare("select * from t1"); $sth->execute; while( my $r = $sth->fetchrow_hashref){ print "$r->{item1},$r->{item2}\n"; } $sth->finish; $dbh->disconnect; #print version print "perl version =$]\n"; for (qw/DBI Text::CSV_XS SQL::Statement DBD::CSV/){ print "$_ ==>".$_->VERSION."\n"; }
        versions of mine. How is yours?
        perl version =5.012002
        DBI ==>1.615
        Text::CSV_XS ==>0.85
        SQL::Statement ==>1.33
        DBD::CSV ==>0.35
        

        Because you use the file => $filename approach, you do not use the case-insensitiveness from SQL::Statement.

        I do not think you should define csv_eol

        When on Windows *and* other OS's, do not use $dir."/".$file, but use File::Spec.

        When in doubt about casing, use something like

        chdir $f_path; foreach my $file_name (glob "*.*") { lc $file_name eq lc $csvfile or next; $csv_file = $file_name; last; } my $dbh = DBI->connect ("dbi:CSV:", undef, undef, { f_dir => $f_path, f_ext => ".csv/r", f_encoding => "utf-8", f_schema => undef, RaiseError => 1, PrintError => 1, }); $cellh->{csv_tables}{cells} = { file => $csv_file }; my $sth = $sbh->prepare ("select * from cells"); $sth->execute; print "fields: @{[@{$sth->{NAME_lc}}]}\n";

        untested, but should work.


        Enjoy, Have FUN! H.Merijn

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (15)
As of 2015-07-06 21:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (83 votes), past polls