Perl DBI and mySQL

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

Hi PerlMonks,

A quick question for those of you familiar with Perl DBI module. The basics of the code is pulling a table out of an obsolete database and putting it into a new mySQL database.

I'm approaching this using a set of arrays -- basically, pulling one row at a time from the old table and putting it into the new & linking it with the column names.

when I check the lenght of the arrays of both @col and @values in perl -- the result is the same: the number of columns is 10; the number of values is 10,

however when I join the columns and values into strings "," deliminated, and try to INSERT the row into the database, I get a error: DBD::mysql::db do failed: Column count doesn't match value count

here's the code snippet:

while (@tmp = $hi->fetchrow_array()) {
        push(@col, $tmp[0]);
    }

while (@org = $sth->fetchrow_array()) {
        my $i;
        for ($i = 0; $i <= 9 ; $i++) {
            push(@values, $org[$i]);
        }
    }
    my $cc = join(", ", @col);
    my $vv = join(", ", @values);
    print "\n\n $vv \n $cc";
    my $sql_2 = qq{INSERT INTO mfa.genome ($cc) VALUES ("$vv")};

    my $now = $DBHandle->prepare( $sql_2 );
    $DBHandle->do($sql_2);
[download]

There is some strange syntax in the old table to begin with, a column including both ";" and "." could this choking the mySQL?

many thanks,
jperlq

Comment on Perl DBI and mySQL Download Code

Replies are listed 'Best First'.
Re: Perl DBI and mySQL by kyle (Abbot) on Feb 02, 2008 at 19:49 UTC
In the SQL you're using, you have your values in one big quoted string (`VALUES ("a,b,c...")`) instead of as individual values (`VALUES ("a", "b", "c")`). What you really should do is use placeholders for this. `my $cc = join q{,}, @col; my $qmarks = join q{,}, map { '?' } @values; my $sql_2 = qq{INSERT INTO mfa.genome ($cc) VALUES ($qmarks)}; $DBHandle->do( $sql_2, undef, @values );` [download] A few more things stand out here. You seem to be populating `@col` with only the first column of every row you fetch from `$hi` (whatever that is). Is that really what you meant? I guess you can see from your value of `$cc` later if it's right. You push into `@values`, but I don't see that it gets cleared anywhere. The way you're adding to `@values` is very not Perlish. You could do the same thing as: `push @values, @org[0..9]` With DBI, you can `do()` without `prepare()`. (`prepare()` is for when you will later `execute()`.) The `@values` you had were going to be passed unquoted to the database. This means that if the values themselves had what looked like SQL code in them, that could be executed. Using placeholders takes care of that, but you can also use the `quote()` method on a database handle. According to the indents, your SQL stuff is happening inside the `while` loop, but according to the braces, it's outside. Be sure you're looping over what you want to.	[reply] [d/l] [select]
Re: Perl DBI and mySQL by Joost (Canon) on Feb 02, 2008 at 19:45 UTC
`qq{INSERT INTO mfa.genome ($cc) VALUES ("$vv")};` [download] That will expand into something like `INSERT INTO mfa.genome (col1,col2,col3) VALUES ("val1,val2,val3")` [download] Note that "val1,val2,val3" is ONE string value, unless the values contain special characters that break the SQL. As hinted above, use something like `my $values = join(",",map { $dbh->quote($_) } @values); my $sql = qq{INSERT INTO mfa.genome ($cc) VALUES ($values)}` [download] "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l] [select]
Re^2: Perl DBI and mySQL by jperlq (Acolyte) on Feb 04, 2008 at 20:49 UTC
Many thanks, the trick `my $values = join(",",map { $dbh->quote($_) } @values);` [download] worked well. I will have to read up on both the ",map" part of the join statement you supplied and the $dbh->quote($_) function of DBI :) Thank you	[reply] [d/l]
Re: Perl DBI and mySQL by snopal (Pilgrim) on Feb 02, 2008 at 19:41 UTC
quote() is your friend. Update: Also, this: `qq{INSERT INTO mfa.genome ($cc) VALUES ("$vv")};` [download] Parses out to this: `INSERT INTO mfa.gnome (field1, field2) VALUES ("value1, value2")` [download] The quotes confuse MySQL.	[reply] [d/l] [select]