http://qs321.pair.com?node_id=460097

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


    Dave
    Code On!
  • Replies are listed 'Best First'.
    Re: DBI reports too many bind variables
    by davidrw (Prior) on May 24, 2005 at 17:52 UTC
      This is the culprit:
      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 '\|';

        my @player_rec = split '\|';

        Although that fixes the problem, it's still bad style to write a regex as a string. Use /\|/ instead of '\|' here. Note that split / / and split ' ' in fact ARE different!

        Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }

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

      Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }

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

      Dave
      Code On!