DBD::CSV malfunction?

[Mark]

On the DBD::CSV doc page at CPAN.org, I am referred to two links at the
end of it for further support. Both links give me a 404 page.

I'm running into a problem with CSV in that it is not pulling data from
a file. Below is my code, a "head" of my data (in the file
"data/location"), and the error message I get (the first one indicates a
possible secondary problem in that $sth->errstr is not set even though
an error is detected).

I have experience using DBI for other code involving databases such as
PostgreSQL and Informix, but this problem with CSV is perplexing me.

I've tried putting .csv suffix on my file, and replaced all semicolons
with commas, too. But none of this seems to matter.

Anyone have any ideas??

Mark
___________________________________________

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbstr = 'DBI:CSV:f_dir=data';
my $dbh = DBI->connect($dbstr,undef,undef,{ChopBlanks => 1,PrintWarn =>
1,PrintError => 0}) or warn "Cannot connect to: $dbstr: " .
DBI->errstr;

my $sql = "SELECT * FROM location ORDER BY lid;";
my $sth = $dbh->prepare("$sql") or die "Cannot prepare SQL: $sql -- " .
$dbh->errstr;

if(defined($sth)) {
$sth->execute() or warn "Unable to execute on $dbstr: $sql -- "
. $sth->errstr; # this execute statement is line 13 ...
# ... that causes the error
}

exit;
__END__


[oper@na_dell7-tir scripts]$ head data/location
lid;county;coe;cpm;detail;elev;hdatum;hsa;hu;lat;lon;lremark;lrevise;name;network;rb;rfc;sbd;sn;state;waro;wfo;wsfo;type;des;det;post;stntype;tzone
06C;Unk;;;at;0.0;;LOT;;41.9844444444444;88.0980555555556;;;Chicago/Schaumburg;Unk;;Unk;;;IL;;LOT;;;;;1;;CST6CDT
07S;Unk;;;at;;;Unk;;47.9667;117.433;;;;Unk;;Unk;;;XX;;Unk;;;;;0;ASOS;EST5EDT
0A6;Unk;;;at;;;Unk;;35.2;81.15000000000002;;;;Unk;;SERFC;;;XX;;Unk;;;;;0;ASOS;EST5EDT
0V1;Unk;;;at;;;Unk;;43.7333;103.617;;;;Unk;;Unk;;;XX;;Unk;;;;;0;ASOS;EST5EDT
0Y7;Unk;;;at;;;Unk;;40.6333;93.90000000000001;;;;Unk;;Unk;;;XX;;Unk;;;;;0;ASOS;EST5EDT
0Z0;Unk;;;at;;;Unk;;66.0667;162.767;;;;Unk;;Unk;;;XX;;Unk;;;;;0;ASOS;EST5EDT
12N;Unk;;;at;;;Unk;;41.0167;74.7333;;;;Unk;;Unk;;;XX;;Unk;;;;;0;ASOS;EST5EDT
14G;Unk;;;at;0.0;;CLE;;41.33333333333334;83.16111111111111;;;Fremont;Unk;;OHRFC;;;OH;;CLE;;;;;1;;EST5EDT

[oper@na_dell7-tir scripts]$ ./testcase.pl
Use of uninitialized value in concatenation (.) or string at
../testcase.pl line 13.
Unable to execute on DBI:CSV:f_dir=data: SELECT * FROM location ORDER BY
lid; -- at ./testcase.pl line 13.

 

[A. Sinan Unur]

On the DBD::CSV doc page at CPAN.org, I am referred to two links at
the end of it for further support. Both links give me a 404 page.

I'm running into a problem with CSV in that it is not pulling data
from a file. Below is my code, a "head" of my data (in the file
"data/location"), and the error message I get (the first one indicates
a possible secondary problem in that $sth->errstr is not set even
though an error is detected).

Mark, first off, I would like to thank you very much for reading and
following the posting guidelines, and making it easy for us to try and
help.

my $dbstr = 'DBI:CSV:f_dir=data';
my $dbh = DBI->connect($dbstr,undef,undef,{ChopBlanks => 1,PrintWarn
=>
1,PrintError => 0}) or warn "Cannot connect to: $dbstr: " .
DBI->errstr;

You might want to format things in a little nicer way:

my $dbh = DBI->connect($dbstr,
undef, undef, {
ChopBlanks => 1,
PrintWarn => 1,
PrintError => 0
}) or warn "Cannot connect to: $dbstr: " . DBI->errstr;

my $sql = "SELECT * FROM location ORDER BY lid;";

This is the problem. I do not think you can use 'ORDER BY' with DBD::CSV.

Sinan

 

[DJ Stunks]

This is the problem. I do not think you can use 'ORDER BY' with DBD::CSV.

I'm going to have to go ahead and kind of, disagree with you there.

Versions:
DBD::CSV 0.22
SQL::Statement 1.14
Perl 5.8.7 build 813 for MSWin32-x86-multi-thread

From DBD::CSV:

The level of SQL support available depends on the version of
SQL::Statement installed. Any version will support *basic*
CREATE, INSERT, DELETE, UPDATE, and SELECT statements. Only
versions of SQL::Statement 1.0 and above support additional
features such as table joins, string functions, etc. See the
documentation of the latest version of SQL::Statement for details.

From SQL::Statement:

SELECT <select_clause>
<from_clause>
[<where_clause>]
[ ORDER BY ocol1 [ASC|DESC], ... ocolN [ASC|DESC]] ]
[ GROUP BY gcol1 [, ... gcolN] ]
[ LIMIT [start,] length ]

Observe:

C:\tmp>cat fruity
fruit date
orange 20050125
grape 20060220
apple 20051223
tomato 20050128

C:\tmp>cat tmp2.pl
#!/usr/bin/perl

use strict;
use warnings;

use Date::Manip;
use DBI;

my $file = 'fruity';

my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_sep_char=\t");
my $sth = $dbh->prepare("select * from $file order by date");
$sth->execute or die "Could not execute! $DBI::errstr";

while (my @row = $sth->fetchrow_array) {
printf "%-6s %d\n", @row;
}

__END__

C:\tmp>tmp2.pl
orange 20050125
tomato 20050128
apple 20051223
grape 20060220

-jp

 

[DJ Stunks]

Anyone have any ideas??

Mark
___________________________________________

[oper@na_dell7-tir scripts]$ ./testcase.pl
Use of uninitialized value in concatenation (.) or string at
./testcase.pl line 13.
Unable to execute on DBI:CSV:f_dir=data: SELECT * FROM location ORDER BY
lid; -- at ./testcase.pl line 13.

Hey Mark,

I tried your script and I agree, I can't quite seem to get it to work
that way. It seems to have an issue reading the column headings from
the first line of the file. Might be a bug? If so, I would encourage
you to contact the author and get it resolved. CPAN works best when we
all work together

However, I did get it to work by including the column names in the
script as outlined in the docs. I sorted by lat just to show it was
working - your file was already ordered by lid.

Let me apologize in advance for the word wrap. I'm not really sure
what the best solution for big lines like those are... (both
usenet-wise and in general)

Observe:

C:\tmp>cat tmp
06C;Unk;;;at;0.0;;LOT;;41.9844444444444;88.0980555555556;;;Chicago/Schaumbu-rg;Unk;;Unk;;;IL;;LOT;;;;;1;;CST6CDT
07S;Unk;;;at;;;Unk;;47.9667;117.433;;;;Unk;;Unk;;;XX;;Unk;;;;;0;ASOS;EST5ED-T
0A6;Unk;;;at;;;Unk;;35.2;81.15000000000002;;;;Unk;;SERFC;;;XX;;Unk;;;;;0;AS-OS;EST5EDT
0V1;Unk;;;at;;;Unk;;43.7333;103.617;;;;Unk;;Unk;;;XX;;Unk;;;;;0;ASOS;EST5ED-T
0Y7;Unk;;;at;;;Unk;;40.6333;93.90000000000001;;;;Unk;;Unk;;;XX;;Unk;;;;;0;A-SOS;EST5EDT
0Z0;Unk;;;at;;;Unk;;66.0667;162.767;;;;Unk;;Unk;;;XX;;Unk;;;;;0;ASOS;EST5ED-T
12N;Unk;;;at;;;Unk;;41.0167;74.7333;;;;Unk;;Unk;;;XX;;Unk;;;;;0;ASOS;EST5ED-T
14G;Unk;;;at;0.0;;CLE;;41.33333333333334;83.16111111111111;;;Fremont;Unk;;O-HRFC;;;OH;;CLE;;;;;1;;EST5EDT

C:\tmp>cat tmp2.pl
#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Text::Table;

my $file = 'tmp';
my @columns = split /;/,
'lid;county;coe;cpm;detail;elev;hdatum;hsa;hu;lat;lon;lremark;lrevise;name;network;rb;rfc;sbd;sn;state;waro;wfo;wsfo;type;des;det;post;stntype;tzone';

my $dbh = DBI->connect("DBI:CSV:")
or die "Could not connect! $DBI::errstr";

$dbh->{'csv_tables'}->{'countries'} = {
'eol' => "\n",
'sep_char' => ";",
'quote_char' => undef,
'escape_char' => undef,
'file' => $file,
'col_names' => \@columns
};

my $sth = $dbh->prepare("select lid,lat,lon,tzone from countries order
by lat");
$sth->execute or die "Could not execute! $DBI::errstr";


my $tb = Text::Table->new(split /,/,'lid,lat,lon,tzone');
while (my @row = $sth->fetchrow_array) {
$tb->add(@row);
}
print $tb;

__END__

C:\tmp>tmp2.pl
lid lat lon tzone
0A6 35.2 81.15000000000002 EST5EDT
0Y7 40.6333 93.90000000000001 EST5EDT
12N 41.0167 74.7333 EST5ED-T
14G 41.33333333333334 83.16111111111111 EST5EDT
06C 41.9844444444444 88.0980555555556 CST6CDT
0V1 43.7333 103.617 EST5ED-T
07S 47.9667 117.433 EST5ED-T
0Z0 66.0667 162.767 EST5ED-T

HTH,
-jp

 

[xhoster]

my $dbstr = 'DBI:CSV:f_dir=data';
my $dbh = DBI->connect($dbstr,undef,undef,{ChopBlanks => 1,PrintWarn =>
1,PrintError => 0}) or warn "Cannot connect to: $dbstr: " .
DBI->errstr;

You ask us for help without even asking DBI for help first? That is
insulting.

PrintError => 1

my $sql = "SELECT * FROM location ORDER BY lid;";

valid SQL does not have a trailing ;

Xho