Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

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

by Tux (Abbot)
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

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


      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?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://989527]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (4)
As of 2018-03-24 07:27 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (297 votes). Check out past polls.