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

Is there a module for pushing an array into mysql rows? I checked CPAN, but nothing looked very promising.

Here is the code Im trying - its failing miserably 
my @COLUMNS;
push ( @COLUMNS, $_)for ( 1 .. $last_column );
my $column_list = map { print "field$_, " } @COLUMNS;
my $placeholders = { print "?, " for ( 1 .. $last_column ) };

my $insert_data = $dbh->prepare(<<SQL);
insert into $filename ($column_list) values ($placeholders)
SQL

foreach (<DATAFILE>)
{
    my (@ROW) = split /,/, $_;
    my $row_list = map { print "\'$_\', " } @ROW;
    $insert_data->execute($row_list);
}

[download]
here is the error 
DBD::mysql::db do failed: You have an error in your SQL syntax; check 
+the manual that corresponds to your MySQL server version for the righ
+t syntax to use near '61 )  VALUES ( 61 )' at line 1 at ./generatefil
+es.pl line 87, <DATAFILE> line 467.

[download]

Thanks in advance for any help

Ted
--
"That which we persist in doing becomes easier, not that the task itself has become easier, but that our ability to perform it has improved."
  --Ralph Waldo Emerson

Replies are listed 'Best First'.
Re: array as mysql db rows
by rnahi (Curate) on Sep 16, 2005 at 22:11 UTC

    Some ready to use solutions to your problem are available at DBI Recipes.

    Check especially the section titled "Inserting a list of lists."

[reply]
Re: array as mysql db rows
by graff (Chancellor) on Sep 16, 2005 at 21:56 UTC
    You haven't shown us the whole script (maybe we should be grateful), but let me suggest the following minor additions and changes to the code that you posted -- try this out and see what happens: 
    #!/usr/bin/perl

use strict;
use DBI;

my $tablename = 'table_name';  # maybe this is really a file name?
my $last_col = 20; # pick a number (any number?) -- you must know the 
+right one

my $colnames = join( ',', map { sprintf( "field%d", $_ ) } 1 .. $last_
+col );
my $placehld = join( ',', map { '?' } 1 .. $last_col );

my $sql = "insert into $tablename ($colnames) values ($placehld)";

print STDERR "Ready to do:\n $sql\n";  # see how that looks

open( DATAFILE, "wheres_the_data" ) or die "wheres_the_data: $!";

my $dbh = DBI->new( blah blah );
my $insert_data = $dbh->prepare( $sql );

foreach (<DATAFILE>)
{
    chomp;
    my (@ROW) = split /,/;
    if ( @ROW != $last_col ) {
        # what do you do if the number of "fields" in ROW
        # is greater than or less than the number of columns
        # in the table?  Think about that, and do it here.
    } else {
        $insert_data->execute( @ROW );
    }
}

    [download]

    So there were a lot of problems with the code you posted, like using "print" where you needed to use "sprintf", not doing "chomp" on the input data file, and really misinterpreting what the DBI man page tells you about how to use placeholders.

    I'm hoping that if you start with this version, you'll get what you want more easily.

[reply]
[d/l]
Re: array as mysql db rows
by InfiniteSilence (Curate) on Sep 16, 2005 at 21:19 UTC
    No local MySQL but with DBI and SQLite this works: 

    #!/usr/bin/perl -w
use strict;
use DBI;
use MIME::Base64;
my $dbh = DBI->connect('DBI:SQLite:dbname=test_blob.db', '', '');

$dbh->do('CREATE TABLE test_blob( a text , b text , Bindata BLOB )');

my $bindata = "ABC" . qq|\x00| . "DEF";
#added
$bindata = encode_base64($bindata);

my $sth1 = $dbh->prepare('INSERT INTO test_blob VALUES(?,?,?)' );
my @ar = qw|foo doog|;
$sth1->execute(@ar, $bindata);

my $sth2 = $dbh->prepare('SELECT Bindata FROM test_blob');
$sth2->execute();

my $row = $sth2->fetch();
my $fetched_data = $row->[0];

$sth2->finish();
$dbh->disconnect;

print decode_base64($fetched_data);
1;

    [download]

    And from the database: 

    .schema test_blob
CREATE TABLE test_blob( a text , b text , Bindata BLOB );
sqlite> select * from test_blob;
foo|doog|QUJDAERFRg==

    [download]

    That code you posted had all sorts of other problems so I grabbed the above from a previous post. Update: Come to think of it, you could replace that SQL creation code with something like: 

    perl -e "$lf = @ARGV;$sql= 'insert into BLAH (' . eval{join ',',@ARGV 
+} . ') VALUES (' . eval{join ',', split//,'?' x $lf} . ')'; print $sq
+l;" DOGS CATS FISH ANIMALS TRIKES KITES
insert into BLAH (DOGS,CATS,FISH,ANIMALS,TRIKES,KITES) VALUES (?,?,?,?
+,?,?)

    [download]

    Celebrate Intellectual Diversity

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom