MySQL: trouble inserting an array with some undefined members into a table using a set number of placeholders

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

I am importing data from a ':' separated file into a MySQL database. I have split the different elements into an array, but my INSERT statement results in the following error:

DBD::mysql::st execute failed: called with 26 bind variables when 24 a
+re needed at csv2sql.pl line 18, <GLEANDATA> line 193.
[download]

I know that some fields in the text file are empty (e.g. 'foo:bar::bash'). The split should result in an array like this:

("foo","bar","","bash")
[download]

Instead, DBI seems to ignore the "" and only attempts to insert foo, bar, and bash.

How do I insert everything, including undefined values?

Perl:

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:db;host=host.com", 'user', 'pw')
    or die ("Could not connect: " . DBI->errstr );

open GLEANDATA, 'gleandata.csv' or die "Could not open file\n";
my @row;
my $sth;
my $line;
while (<GLEANDATA>) {
    $line = $_;
    my @row = split(/:/, $line);
    $sth = $dbh->prepare("INSERT INTO glean_data (gleaner, contact_nam
+e, title_position, city, county, phone, email, dates_called, possible
+_donation, notes_comments, followup_needed, mailing_address, physical
+_address, crops, specific_crop, estimated_quantity, estimated_labor, 
+possible_glean_dates_times, ladders_to_lend, picking_bags_to_lend, bi
+ns_or_boxes, existing_relationship, status, time_of_year_to_call) VAL
+UES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    $sth->execute(@row);
}

close GLEANDATA or die "Could not close file\n";

$dbh->disconnect;
[download]

Just so you know, there are 24 columns I am trying to insert into.

SQL:

CREATE TABLE glean_data (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    gleaner VARCHAR(50),
    contact_name VARCHAR(50),
    title_position     VARCHAR(50),
    city VARCHAR(50),
    county VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(50),
    dates_called VARCHAR(200),
    possible_donation VARCHAR(150),
    notes_comments VARCHAR(5000),
    followup_needed VARCHAR(50),
    mailing_address VARCHAR(100),
    physical_address VARCHAR(100),
    crops VARCHAR(150),
    specific_crop VARCHAR(150),
    estimated_quantity VARCHAR(150),
    estimated_labor VARCHAR(150),
    possible_glean_dates_times VARCHAR(500),
    ladders_to_lend VARCHAR(150),
    picking_bags_to_lend VARCHAR(150),
    bins_or_boxes VARCHAR(50),
    existing_relationship VARCHAR(150),
    status VARCHAR(150),
    time_of_year_to_call VARCHAR(50),
    PRIMARY KEY (id)
)
[download]

Thank you for your help.

Comment on MySQL: trouble inserting an array with some undefined members into a table using a set number of placeholders Select or Download Code

Replies are listed 'Best First'.
Re: MySQL: trouble inserting an array with some undefined members into a table using a set number of placeholders by moritz (Cardinal) on Mar 16, 2009 at 23:15 UTC
I know that some fields in the text file are empty (e.g. 'foo:bar::bash'). The split should result in an array like this: `("foo","bar","","bash")` [download] It should. But does it? Use Data::Dumper to verify it. Instead, DBI seems to ignore the "" and only attempts to insert foo, bar, and bash. I've never seen DBI doing anything like this, and I've just tried it with an example database, it inserted the empty string just fine. Most likely the error is somewhere else in your script.	[reply] [d/l]
Re: MySQL: trouble inserting an array with some undefined members into a table using a set number of placeholders by lostjimmy (Chaplain) on Mar 17, 2009 at 00:59 UTC
The error `DBD::mysql::st execute failed: called with 26 bind variables when 24 are needed` looks like a good clue. Are you sure the lines you are splitting have 24 fields and not 26? Check how many elements are in the array with `print scalar @row`, and, as moritz already said, use Data::Dumper to see what that array contains.	[reply] [d/l] [select]
Re: MySQL: trouble inserting an array with some undefined members into a table using a set number of placeholders by graff (Chancellor) on Mar 17, 2009 at 02:16 UTC
As others have suggested, your data probably contains more colons than you expect on some lines, and a field like "notes_comments" probably has a decent chance of containing colons as data. Try this one-liner on your "gleandata.csv" file, and see what you get: `perl -ne '$n=tr/://;$h{$n}++; END{ print "$h{$_} lines have $_ colons\n" for(sort{$a<=>$b} keys %h)}' < + gleandata.csv` [download] (You should put that on the shell command line as a single line -- I just broke it up to avoid the bothersome "+" in the node display.) If there were only one field that contained colons as data, and you could figure out how to make that field come last on each line, you could try doing your split like this: `my @row = split(/:/, $line, 24);` [download] That way, only 24 elements will be returned, and any "extra" delimiters will just be kept inside the 24th element. But a better approach would be: confirm that appropriate quotes and/or escapes are used in the file when fields contain delimiter characters as data, and parse the file with Text::CSV or Text::xSV, OR use a delimiter character that never shows up as field data (tab is usually good) OR condition field contents as needed to replace delimiter characters inside the field data with "safe" alternatives (e.g. semi-colon or comma or hyphen instead of colon) (updated last bullet point in hopes of making it clearer)	[reply] [d/l] [select]
Re: MySQL: trouble inserting an array with some undefined members into a table using a set number of placeholders by ruzam (Curate) on Mar 17, 2009 at 01:27 UTC
'notes_comments' Are you sure there aren't any extra ':' in those comments (or any other data field for that matter) ?	[reply]
Re^2: MySQL: trouble inserting an array with some undefined members into a table using a set number of placeholders by dwstultz (Initiate) on Mar 18, 2009 at 02:17 UTC
That is a very good question. I tried different delimiters to no avail. The problem ended up being that I was a MORON and the spreadsheet that was the source of the CSV file had some hidden columns outside my view and so the numbers of columns didn't match up. I have all the data imported now. Thank you everyone who responded.	[reply]
Re: MySQL: trouble inserting an array with some undefined members into a table using a set number of placeholders by bichonfrise74 (Vicar) on Mar 17, 2009 at 02:00 UTC
Can you show us some records in the CSV file?	[reply]
Re: MySQL: trouble inserting an array with some undefined members into a table using a set number of placeholders by bradcathey (Prior) on Mar 17, 2009 at 02:26 UTC
I usually employ MySQL's very own `LOAD DATA LOCAL INFILE` when loading a CSV file: `LOAD DATA LOCAL INFILE 'gleandata.csv' INTO TABLE as_contacts FIELDS T +ERMINATED BY ":" OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\r\ +n";` [download] Unless I'm missing something... —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply] [d/l] [select]
Re: MySQL: trouble inserting an array with some undefined members into a table using a set number of placeholders by roboticus (Chancellor) on Mar 17, 2009 at 21:56 UTC
dwstultz: Times are often expressed with a pair of colons, like HH:MM:SS, which would give you the two extra field values... ...roboticus	[reply]