dthacker has asked for the wisdom of the Perl Monks concerning the following question:
I've been away from perl for too long, and I'm missing something silly.
I have a series of text files that I'm reading and
changing each line to pipe-delimited
writing to a new file
inserting into a database table
The pipe de-limiting and writing to a separate file are going fine. But I'm missing something critical on the insert. I'm using an example from gmax's DBI Recipe Tutorial. After I read in the record, I pipe-delimit it and then split it into an array. I then attempt to insert the array into the database, where things go badly. Code and error messages are below.
#!/usr/bin/perl
use strict;
use DBI;
#configurables
my $workdir = "./work";
my $roster = "";
my $dbh = DBI->connect("DBI:mysql:database=esms",
'username',
'password',
) or die "Can't connect to database";
my @fields = qw (player_name player_age player_nat player_prs player_s
+t
player_tk player_ps player_sh player_stamina
player_aggression player_kab player_tab player_pab
player_sab player_games player_sav player_ktk player_
+kps
player_sht player_gls player_dpoints player_inj playe
+r_sus
player_fit);
my $fieldlist = join ", ", @fields;
my $field_placeholders = join ", ", map {'?'} @fields;
my $insert_query = qq{
INSERT INTO employees ( $fieldlist )
VALUES ( $field_placeholders )};
my $sth= $dbh->prepare( $insert_query );
opendir DIR, $workdir;
@files = grep /ja/, readdir DIR;
closedir DIR;
foreach (@files) {
$roster = $_;
print"now processing $roster\n";
open (RF,"./work/$roster");
open (UNL,">./work/$roster.unl");
while (<RF>) {
chomp;
next if m/^Name/;
next if m/----/;
print "$_\n";
$_ =~ s/\s+/|/g;
my @player_rec = split '|';
if ($sth->execute(@player_rec)) {
print "Worked, cool";
}
else {
print "Didn't work, uncool, error is: $DBI::errstr";
}
print UNL "$_\n";
}
close(RF);
close(UNL);
}
Resulting Errors:
Didn't work, uncool, error is: called with 71 bind variables when 24 a
+re neededZ_Otyusyo|24|ire|R|3|5|11|5|55|21|300|300|300|300|0|0|0|0|0|
+0|0|0|0|0|100|
DBD::mysql::st execute failed: called with 74 bind variables when 24 a
+re needed at ./planb.pl line 45, <RF> line 17.
Didn't work, uncool, error is: called with 74 bind variables when 24 a
+re neededP_Choonduaga|25|cam|R|3|7|10|7|57|35|300|300|300|300|0|0|0|0
+|0|0|0|0|0|0|100|
DBD::mysql::st execute failed: called with 77 bind variables when 24 a
+re needed at ./planb.pl line 45, <RF> line 18.
Didn't work, uncool, error is: called with 77 bind variables when 24 a
+re neededV_Itroidro|19|ita|LC|3|5|10|7|42|24|300|300|300|300|0|0|0|0|
+0|0|0|0|0|0|100|
DBD::mysql::st execute failed: called with 76 bind variables when 24 a
+re needed at ./planb.pl line 45, <RF> line 19.
Didn't work, uncool, error is: called with 76 bind variables when 24 a
+re neededK_Ogoejheb|23|jap|C|3|9|9|8|45|28|300|300|300|300|0|0|0|0|0|
+0|0|0|0|0|100|
and so on.......
Janitored by holli - added readmore
tags
Edit. Fixed Tutorial Link dthacker
Re: DBI reports too many bind variables
by davidrw (Prior) on May 24, 2005 at 17:52 UTC
|
my @player_rec = split '|';
If you add a print join ":", @player_rec; in there you'll see that it split up every single character -- this is because the pipe is treated as a special regex character (behaves like OR), so if you truly need a pipe (as in this case), it just simply needs to be escaped:
my @player_rec = split '\|';
| [reply] [d/l] [select] |
|
| [reply] [d/l] [select] |
|
ah. yes -- thanks for explicitly noting that (i actually had /\|/ in my head but that part got lost somewhere between brain and fingertips..) -- it definitely is an important disctinction (if anyone reading this doesn't know the difference please read preldoc -f split carefully--it's well worth the read).
| [reply] [d/l] [select] |
Re: DBI reports too many bind variables
by Juerd (Abbot) on May 24, 2005 at 17:47 UTC
|
my @player_rec = split '|';
print join '|', @player_rec;
See what happens. Note that split's first argument is either a string containing a single chr(32) (space), or a regex. Your string is seen as a regex. The regex matches either nothing or nothing, and thus matches at every position.
Consider DBIx::Simple, by the way.
| [reply] [d/l] |
|
| [reply] |
Re: DBI reports too many bind variables
by injunjoel (Priest) on May 24, 2005 at 17:53 UTC
|
Greetings all,
With just a quick glance at your code I would suggets changing your line:
my @player_rec = split '|';
to
my @player_rec = split /\|/;
since split takes a regular expression pattern rather than a string, unlike join.
If that doesn't help you can also limit the amount of elements the split returns to you by using the
split /PATTERN/,EXPR,LIMIT
form of the function.
-InjunJoel
"I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo
| [reply] [d/l] [select] |
Re: DBI reports too many bind variables
by dbwiz (Curate) on May 24, 2005 at 20:29 UTC
|
In addition to the useful advice of escaping your pipe character,
I would also advise you to code defensively. i.e. check your
data before passing it to the "execute" method.
my @player_rec = split /\|/;
if (scalar(@player_rec) == scalar(@fields))
{
if ($sth->execute(@player_rec)) {
print "Worked, cool";
}
else {
print "Didn't work, uncool, error is: $DBI::errstr";
}
}
else
{
warn "incorrect number of fields at line $.\n";
warn sprintf("expected: %d but found %d\n",scalar(@fields), scala
+r(@player_rec));
}
| [reply] [d/l] |
Re: DBI reports too many bind variables
by dthacker (Deacon) on May 24, 2005 at 18:04 UTC
|
Thank you all, I see the error, and will look at the suggested modules. Lesson learned here: Make no assumptions that you've actually transformed the data correctly.
| [reply] |
|
|