in reply to Use of uninitialized value in join or string

domaniqs:

I'm guessing that some of your data has embedded quotes and/or commas in it. That has an opportunity to confuse the CSV handler *and* your database handling code. In this case, it appears to be your database handling code.

The difficulty is getting your data properly quoted for use with the database. Luckily, it's such a common difficulty that DBI has support for it built in: Placeholders. Rather than building the value strings into your SQL, you use a question mark as a placeholder. Then, when you execute the statement, you provide an array containing the values for the question marks, in order. So I'd suggest modifying your program to something like:

my $SQL = "INSERT INTO $table VALUES (" 
        . join(",", map { "?" } $csv->fields)
        . ")";
$prep = $db->prepare($SQL) or die "...";
while (my $row = $csv->getline($fh)) {
    $prep->execute($csv->fields);
}

[download]

The first bit build a string with a set of ? placeholders for the fields. It does so by using the map operator to convert the list of fields into a list of question marks, and join to add the commas between then.

Then we prepare the statement for execution. Notice one thing: The prepare statement is *outside* your loop. We can do this because the statement doesn't change for each iteration!

So if your database drivers support precompiling statements (and most do), then you get a speed boost because it only compiles the statement one time! Finally, in the loop we simply execute the statement with a different list of values each time.

Please learn to use placeholders, and you'll find a lot of database work simpler, safer and more reliable.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Replies are listed 'Best First'.
Re^2: Use of uninitialized value in join or string
by domaniqs (Initiate) on Jan 29, 2014 at 12:54 UTC

    Many thanks for explaining how useful placeholders are to me, @roboticus

    I have replaced code:

            while (my $row = $csv->getline($fh)) {
            $prep = $db->prepare("INSERT INTO $table VALUES (\"" . joi
+n('","', $csv->fields()) . "\")")
                    or die "Cannot prepare database " . $db->errstr() 
+. "\n";
            if (!$prep->execute()) { die "Failed to write row " . $db-
+>errstr() . "\n"; }
        }

    [download]

    with the code that you have suggested to me, but the script produces another error:

    DBD::mysql::st execute failed: Column count doesn't match value count at row 1 at ./test.pl line 85, <$fh> line 2.

    and so on for every record of the file to be imported.

    which makes no sense to me, as from my understanding when I use map { expr } list, it evaluates expr for every item in list, so it should update the list automatically, isn't that right?

[reply]
[d/l]
[select]

      Your code snippet here isn't actually using placeholders, so you may not have quite grasped what they are. See how roboticus is using the question marks in his SQL? See how he calls execute with the field list? These are things which you should be doing.

[reply]
[d/l]

        Thank you hippo for pointing this out, however, the snippet I've inserted into my recent post, is the piece of code, that was removed and replaced by the code, that roboticus kindly suggested to me in his post.

        So instead of:

        my $SQL = "INSERT INTO $table VALUES (" 
        . join(",", map { "?" } $csv->fields)
        . ")";
$prep = $db->prepare($SQL) or die "...";
while (my $row = $csv->getline($fh)) {
    $prep->execute($csv->fields);
}

        [download]

        I used:

        my $SQL = "INSERT INTO $table VALUES (" 
        . join(",", map { "?" } $csv->fields)
        . ")";
$prep = $db->prepare($SQL) or die "Cannot prepare database";
while (my $row = $csv->getline($fh)) {
    $prep->execute($csv->fields);
}

        [download]

        It does not change the fact, that I still struggle with this new error:

        DBD::mysql::st execute failed: Column count doesn't match value count at row 1 at ./test.pl line 85, <$fh> line 2.
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom