gps sago gps sago - 1 month ago 5
Perl Question

Why sub routine fails when connecting multiples databases for same tables using perl?




I have attached my part of code where i tried to connect multiple databases to insert same tables for all databases?But am gated with an errors?

CODE:

sub gen_machine_status
{
#print "entered machine status";
$DBH1 = &connect or die "Cannot connect to the sql server \n";
$DBH2 = &connect or die "Cannot connect to the sql server \n";
$DBH1->do("USE rdb_banre_1m;");
$DBH2->do("USE rdb_banre_cur;");
$stmt = "INSERT INTO m_status(time,available,used,busy,closed,reserved,down) VALUES(\"$current_time\",\"$machine{'+'}\",\"$machine{'A'}\",\"$machine{'B'}\",\"$machine{'C'}\",\"$machine{'R'}\",\"$machine{'D'}\")";

my $sth1 = $DBH1->prepare( "select * from users" );
my $sth2=$DBH2->prepare("insert into users where time = (SELECT max(time) FROM users)");

$sth1->execute() or print "Could not insert data";
while(my @row=$sth1->fetchrow_array)

{

#print "@row";
$sth2->execute(@row);
}
$sth1->finish;$sth2->finish;
$DBH1->disconnect();$DBH2->disconnect();
}


Error:

DBD::mysql::st execute failed: called with 7 bind variables when 0 are needed

Answer

The error seems clear. I assume it is generated by this row:

$sth2->execute(@row);

When you pass data to execute() the values are matched to bind points in your compiled SQL statement. Your SQL is compiled in this line:

my $sth2=$DBH2->prepare("insert into  users where time = (SELECT max(time) FROM  users)");

There are no bind points in this statement (most commonly, bind points are indicated with question marks).

You have a compiled SQL statement that contains no bind points and a call to execute() that provides data for seven bind points. That's why you get the error.

In order to fix the error, you would need to compile an SQL statement that includes seven bind points. It might look like this:

my $sth2 = $DBH2->prepare('insert into users
                           (col1, col2, col3, col4, col5, col6, col7)
                           values (?, ?, ?, ?, ?, ?, ?)');

I don't know the name of your columns - so I've made some up. You would need to include the correct column names.

A few other points about your code:

  1. Please don't call subroutines using &. It will confuse you at some point. Please use connect() instead.
  2. The fact that you don't declare $DBH1 and $DBH2 with my makes me wonder if you have use strict in your code. You should always include use strict and use warnings.
  3. You have another SQL statement in $stmt which is never used. And that references a hash called %machine which doesn't exist. Is that intentional?
  4. INSERT ... WHERE ... doesn't make much sense. What are trying to do here?
Comments