Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Re: DBD::CSV::prepare() problems

by Tux (Monsignor)
on Aug 24, 2012 at 13:13 UTC ( #989527=note: print w/ replies, xml ) Need Help??


in reply to DBD::CSV::prepare() problems

  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


Comment on Re: DBD::CSV::prepare() problems
Download Code
Replies are listed 'Best First'.
Re^2: DBD::CSV::prepare() problems
by polelo (Initiate) on Aug 24, 2012 at 20:02 UTC

    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

      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

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

        Go ahead and use / on every OS you have

      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
      

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (11)
As of 2015-07-08 06:35 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 (94 votes), past polls