DBI:mysql

[Dalin]

Good morning all,
I'm getting this error "DBD::mysql::st execute failed: called with one bind variables when 4 are needed at prog line 87, CSV2 line 17. Here is the code as it stands...

#!/usr/bin/perl -w
#
#
use strict;
use DBI qw(:sql_types);


####
# Set global dbase connection info
#
#
####

my $dsn="dbi:mysql:database=sfiemsmerc";
my $user="root";
my $passwd="";

my $dbh = DBI->connect($dsn, $user, $passwd, { RaiseError =>1, PrintEr
+ror => 1, AutoCommit => 0 });
$dbh->trace(1);

my @files = <*.dump>;
foreach my $file (@files) {
    my $table = "$file";
    $table =~ s/\.dump//g;

####
# Delete current data from hobby table
# Separate connection!!
#
####

my $delete00 = $dbh->prepare("DELETE FROM $table");
$delete00->execute();

$dbh->commit();

####
# create and prepare insert connection and action
# call q_marks to insert "?" into $stmnt
# 
#
####

$dbh = DBI->connect($dsn, $user, $passwd, { RaiseError => 1, PrintErro
+r => 1, AutoCommit => 0 });

$dbh->trace(1);


my $csvfile="$file";
open (CSV2,"$csvfile")|| die "Cannot open $csvfile, $!";
while (<CSV2>) {
    chomp;
    if ( /rows$|^$table/ ) {
        next;
    }else{
        s/'//g;
        my (@data) = split /,/;
        chomp @data;
        my $stmnt = "INSERT INTO $table VALUES ()";
        $stmnt = q_marks($stmnt,\@data);

        my $insert00 = $dbh->prepare("$stmnt");
        
        my $count = @data;
        my @values;
        for (my $i = 0;$i <= $count - 1;++$i) {
            my $word = "$data[$i]";
            unshift(@values,$word);
            }
        $count = @values;
        if (! $count ) {
            next;
        }else{
            my $values = join(',',reverse(@values));
            chomp $values;
            #print "$values\n";
            $insert00->execute("$values");
            }
        }

####
# Close and commit dbase changes
#
#
####

close CSV2|| die "Cannot close $csvfile, $!";
$dbh->commit();
$dbh->disconnect();
}

####
# 
#
####
#unlink $csvfile|| die "Cannot remove $csvfile, $!";
#unlink $csv2file|| die "Cannot remove $csv2file, $!";
}

my $date = localtime time;
print "$date:hobby_update:Update Complete\n";

sub q_marks {
    my($stmnt,$data) =@_;
    my @marks;
    foreach (@$data) {
        unshift(@marks,"?");
        }
    my $line = join(',',@marks);
    $stmnt =~ s/(\()(\))/$1$line$2/;
    return $stmnt;
    }

There are four data fields that need to be inserted. The data files are comma-delimited, one row per line, "'" qualified. I'm stripping the "'" qualifier. Do I need to bind_param the fields? If so, what would be a good way to do that? Any suggestions anyone has would be greatly appreciated. Thanks, Dalin Where ever there is confusion to be had... I'll be there.

Edit kudra, 2002-04-16 Added readmore

[ferrency]

In q_marks() you're adding one "?" bind placeholder for each element of the $data hash.

But, in a snippet from your while loop:

}else{
    my $values = join(',',reverse(@values));
    chomp $values;
    #print "$values\n";
    $insert00->execute("$values");
}

You're only calling execute() with one value: a string containing a comma-separated list of the elements of @values.

Try this instead:

} else {
    $insert00->execute(@values);
}

Hope this helps...

Alan

[Dalin]

If I attempt to use just @values in the execute, then mysql complains of incorrect sql syntax... mostly due to unescaped characters like "'". The code works beautifully if I use just one data file and hand type the placeholders and the array elements into the insert statements in the code... that though... would take me forever.

[ferrency]

In that case, I suggest that you post a log transcript of the actual query being prepared and executed, and the error messages you receive, both with execute(@values) and with execute($values). This would make it a lot easier to see what's going wrong.

Alan

[cyberconte]

Yes, placeholders will help your code structure a lot. a simple example...

 my $sth_md5=$dbh->prepare("SELECT MD5(concat(?,?,?,?))");
...
...
$rv = $sth_md5->execute($computer,$share,$fullpath,$entry);

based on a scanning script i have, this computes the md5 sum based on 4 variables. (You would probably want to ue Digest::MD5, but this works for an example) I can also use this multiple times over with different variables before ->finishing.
However i note that you're only doing this once, so if performance is an issue, "do" is the way to go. Its just placeholders can make code a little cleaner sometimes

Second, try running debug level 2. i find thats the minimum debug i need to see the queries go in and figure out whats really going on.

Finally, i notice you open 2 connections to the databae without disconnecting the first one. Are you doing this on purpose or for a reason?

Update: Just reminded...heh... one of my biggest problems was DBI inserting as the wrong type - aka inserting without ''s because it thought it was an int, etc. using -> bind_params might be your best bet. The best way to find out if this is the case is to set debug level to 2, and see the query its auctally pasing in ^_^

[Dalin]

Ooops... yeah, I should've been disconnecting that first one. I set the trace to level 2 and that is helping somewhat. I did the bind_param on the data...

for (my $i = 1;$i <= $count;++$i) {
      if ( $values[$i -1] =~ /\d+ ) {
             $insert00->bind_param($i,$values[$i - 1],SQL_INTEGER);
      }else{
             $insert00->bind_param($i,$values[$i - 1]);
             }

But I get the same results.

[peppiv]

I would try using placeholders. Especially when you know that you're inserting four data fields every time. Makes it easy. Check out this

peppiv

[Dalin]

If you check out the q_marks sub, you'll see that I'm inserting the proper amount of place holders for each data line. the $dbh->trace is confirming that the sub is working correctly. I'm not including column names in the insert statement because each data line has the proper amount of fields for each table I'm inserting into. I know this because the data files are direct dumps from a previous dbase.