Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

DBD::CSV issues querying file

by hesco (Deacon)
on Jan 14, 2007 at 08:47 UTC ( #594621=perlquestion: print w/replies, xml ) Need Help??

hesco has asked for the wisdom of the Perl Monks concerning the following question:

I'm working on a test script. Right now the tests are passing because I've mocked out the routine they are testing in order to get the test script working first. I'm encountering errors from my attempts to read a .csv file that I can't find documented in DBD::CSV's documentation.

The first sign of error reads: SQL ERROR: Bad table or column name 'vasample;' has chars not alphanumeric or underscore!, and then it generally breaks down from there.

The field names from the first line are defined as follows:

"random","memberid","address1","address2","county","local","state","zip","zip4"," hod","vasenate","congress","district"

I have a sample file from the client with data and the results she produced doing my module's task manually. I need to loop through that csv file, pull arguments and expected results from each record and use those to test the code I'm working on tonight.

Any ideas would be appreciated.

-- Hugh

UPDATE:

almut takes the prize here. As soon as I deleted that semicolon "in my table name" (who knew?), I got 49 records of data come flooding by. Thanks loads.

My code reads:

#!/usr/bin/perl -w use Test::More tests => 10; use Data::Dumper; use DBI; use lib('lib'); use My::New::Module; my $vasbe = My::New::Module->new(); isa_ok($vasbe,'My::New::Module'); my $dir = "/home/hesco/sb/My-New-Module/"; my $file = 'vasample.csv'; my $table = 'vasample'; my $cols = [qw( random memberid address1 address2 county local state +zip zip4 hod vasenate congress district )]; my $sep = ','; my $dbh = DBI->connect( "DBI:CSV:f_dir=$dir;csv_eol=\n;csv_sep_char=$sep;", {RaiseError=>1}, ); print STDERR "The connection is made.\n"; $dbh->{'csv_tables'}->{'vasample'} = { 'file' => 'vasample.csv', 'col_names' => $cols, }; print STDERR "Our tables are defined.\n"; my $sql = "SELECT memberid, address1, zip, hod, vasenate, congress, di +strict FROM $table;"; my $sth = $dbh->prepare($sql) or die "Cannot prepare query: " . $sql . "|<-->|" . $dbh->errstr(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); print STDERR "We've executed this query: $sql.\n"; print STDERR "Which returned " . $sth->rows() . " records.\n"; while (my $row = $sth->fetchrow_hashref) { print("Found result row: id = ", $row->{'memberid'}, ", zip = ", $ro +w->{'zip'}); } print STDERR " . . . and printed a row for each record.\n"; my %address = ( 'streetnumber' => '', 'streetname' => '', 'zipcode' => '', ); print STDERR "We've now built the \%address hash.\n"; my $result = &match_address( \%address ); END { print "And now the program is terminating.\n"; $sth->finish(); $dbh->disconnect(); } 1; # with subroutines following # STDERR / STDOUT follows: t/12_vasbe_testcases.t 1..10 ok 1 - The object isa My::New::Module The connection is made. Our tables are defined. SQL ERROR: Bad table or column name 'vasample;' has chars not alphanum +eric or underscore! We've executed this query: SELECT memberid, address1, zip, hod, vasena +te, congress, district FROM vasample;. Which returned 1 records. Use of uninitialized value in print at t/12_vasbe_testcases.t line 52. Use of uninitialized value in print at t/12_vasbe_testcases.t line 52. Found result row: id = , zip = . . . and printed a row for each recor +d. We've now built the %address hash. . . . successfully runs the mocked tests. And now the program is terminating.
if( $lal && $lol ) { $life++; }

Replies are listed 'Best First'.
Re: DBD::CSV issues querying file
by almut (Canon) on Jan 14, 2007 at 11:53 UTC
    SQL ERROR: Bad table or column name 'vasample;' has chars not alphanumeric or underscore!

    It probably doesn't like the semicolon in your SELECT statement's table name:

    my $sql = "SELECT ... FROM $table;"; ^

    (just a guess - haven't verified it...)

Re: DBD::CSV issues querying file
by jZed (Prior) on Jan 15, 2007 at 16:55 UTC
    "Has characters not alphanumeric or underscore" is pretty specific, so yep, it's that stray semi-colon. Yes, most SQL batchmode processors require semicolons at the end of statements but that's so they can handle multiple statements, they usually strip off the semicolon before sending the statement to the SQL processor. DBI expects DBDs to not use the statement-terminating semicolon and most DBDs respect that. (Though this may change as DBI becaomes able to handle multiple statements in a single call). For the time being let's be thankful that the DBDs don't let users do things like
    my $sth = $dbh->prepare("SELECT * FROM ?"); $sth->execute(" MyTable2; DELETE * FROM MyTable2 ");
    Sharp-eyed monks will notice at least two places there where a well-behaved DBD will complain.

    update: added missing parens; thanks tanktalus

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://594621]
Approved by Melly
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: (5)
As of 2021-06-24 16:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)












    Results (130 votes). Check out past polls.

    Notices?