DBD::CSV issues querying file

[hesco]

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.

[almut]

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...)

[jZed]

"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