SQL and Perl

[James Vahn]

The simplist things.. Anyone see my mistake? The next block of text
is a CSV file called "data.csv" and the next is a Perl script to read
read it. The last block is the error I'm getting.


partno,title,author
001555789,"PROGRAMMING PERL",WALL
40015152345,"LEARNING BASH",NEWMAN


#!/usr/bin/perl -w
use DBI;
use DBD::CSV;
$dbh = DBI->connect("DBI:CSV:f_dir=.;csv_sep_char=\\,");
$sth = $dbh->prepare("SELECT * FROM data.csv");
$sth->dump_results();
$sth->execute();
while ( @row = $sth->fetchrow_array ) { print "@row\n" }
$sth->finish();
$dbh->disconnect();
__END__


0 rows (1: Attempt to fetch row from a Non-SELECT statement)
DBD::CSV::st dump_results failed: Attempt to fetch row from a
Non-SELECT statement [for Statement "SELECT * FROM data.csv"]
at ./CSV.pl line 9.

--

[Dave Laird]

Good evening, James...

Post by James Vahn
The simplist things.. Anyone see my mistake? The next block of text
is a CSV file called "data.csv" and the next is a Perl script to read
read it. The last block is the error I'm getting.
partno,title,author
001555789,"PROGRAMMING PERL",WALL
40015152345,"LEARNING BASH",NEWMAN
#!/usr/bin/perl -w
use DBI;
use DBD::CSV;
$dbh = DBI->connect("DBI:CSV:f_dir=.;csv_sep_char=\\,");
$sth = $dbh->prepare("SELECT * FROM data.csv");
$sth->dump_results();
$sth->execute();
$sth->finish();
$dbh->disconnect();
__END__
0 rows (1: Attempt to fetch row from a Non-SELECT statement)
DBD::CSV::st dump_results failed: Attempt to fetch row from a
Non-SELECT statement [for Statement "SELECT * FROM data.csv"]
at ./CSV.pl line 9.

Huh. Are you sure data.csv is actually a comma-delimited file using \\ as
your delimiter? Second thought would be based on my knowledge of MySQL
which uses a INPUT statement to accomplish the same thing. Then you would
use a "SELECT * FROM <file name>" statement to print the rows, I think. I
don't think you're supposed to be able to SELECT * FROM using a delimited
file, but I could be wrong.

Dave

[James Vahn]

Post by Dave Laird
Huh. Are you sure data.csv is actually a comma-delimited file using \\
as your delimiter?

It is as posted, using commas as the delimiter. I did solve the problem
though, and it's the oldest pitfall in the world of scripting: Perl's
DBD::CSV module *requires* MSDOS-style CRLF line endings..! It's been
a full 5 years since my last project and I had completely forgotten.

This is part of the routine I'm using to read a larger file in order to
remove the manufacturer's cruft from it. Note the recent addition of
"\r\n" at the end:
$row[1] =~ s/\"/\"\"/g;
print "$row[0],\"$row[1]\",$row[2]\r\n";

I know, complete gibberish. Lovely stuff, isn't it. The dilemma was in
being able to read the original file, but not the result. Invisible
hobgoblins at work. <chuckle>

Post by Dave Laird
I don't think you're supposed to be able to SELECT * FROM using a
delimited file, but I could be wrong.

We can use very complicated SELECT statements on CSV files, it works
fine. The disadvantage seems to be that it reads the entire file into
memory at connect time. Processing CSV is slow but it does have other
advantages. The nice thing is that you can easily convert the script to
connect to ODBC, MySQL, Postgres and Oracle(?) without significantly
changing the code.


--

[Dave Laird]

Good afternoon, James....

Post by James Vahn
It is as posted, using commas as the delimiter. I did solve the problem
though, and it's the oldest pitfall in the world of scripting: Perl's
DBD::CSV module *requires* MSDOS-style CRLF line endings..! It's been
a full 5 years since my last project and I had completely forgotten.

ARGHHH! I see it right from the beginning. However, the DBD I routinely
work with works fine with just a LF. Go figure. ;-|

Post by James Vahn
This is part of the routine I'm using to read a larger file in order to
remove the manufacturer's cruft from it. Note the recent addition of
$row[1] =~ s/\"/\"\"/g;
print "$row[0],\"$row[1]\",$row[2]\r\n";
I know, complete gibberish. Lovely stuff, isn't it. The dilemma was in
being able to read the original file, but not the result. Invisible
hobgoblins at work. <chuckle>

God, I hate glitches like that. You can spend hours or even days glaring
at code and never see the error, despite the fact it is right there in
front of you. It's like doing DNS and leaving out a semi-colon. <sigh>
FWIW, there is a little utility I've used that takes dissimilar data and
turns it into a comma-delimited text file,

Post by James Vahn
We can use very complicated SELECT statements on CSV files, it works
fine. The disadvantage seems to be that it reads the entire file into
memory at connect time. Processing CSV is slow but it does have other
advantages. The nice thing is that you can easily convert the script to
connect to ODBC, MySQL, Postgres and Oracle(?) without significantly
changing the code.

...and now we can use SQL statements against a running copy of dBase, even
they have entered the 20th century database world. FINALLY!

Dave