I had attached my following code but I struck with the following errors.Can anyone resolve the reason for the issue?
我附上了下面的代码，但是我犯了以下错误。谁能解决这个问题的原因?
$DBH1->do("USE data_current;");
$stm1="select * from data_1m.wms where time =(select max(time) from wms)";
$stmt2="insert into data_current.wms(time,available,closed,used,busy,reserved,down) values(select * from data_1m.wms where time =(select max(time) from wms)";
my $sth1 = $DBH1->prepare( "$stmt1" );
$sth1->execute($stmt2) or print "Could not insert data";
$sth1->finish;
$DBH1->disconnect();
Following error occurs:
发生错误:
DBD::mysql::st execute failed: called with 1 bind variables when 0 are needed
执行失败:在需要0时用1个绑定变量调用
2 个解决方案
#1
3
I fear I rather confused you with one of my comments yesterday. Here's what I said:
恐怕我把你和我昨天的评论搞混了。这就是我说:
Looks like this has nothing to do with your Perl. Your SQL is returning the wrong data (for reasons that I don't understand). Here's what to do. Open a mysql session on the source database. Write an SQL statement that returns the correct data. Add the
insert into ...clause to the front of that SQL. Execute that SQL from Perl看起来这与您的Perl无关。您的SQL正在返回错误的数据(出于我不理解的原因)。这是要做什么。在源数据库上打开mysql会话。编写一个SQL语句，返回正确的数据。添加插入到…SQL前面的子句。从Perl中执行SQL
I'll try to unpick the misunderstandings here. But, given how long we've been answering questions on this seemingly simple task, I do wonder if it time for you to give up and employ a programmer to do this for you.
我将尽量消除这里的误解。但是，考虑到我们在这个看似简单的任务上已经回答了多长时间的问题，我确实想知道现在是否应该让您放弃并雇佣一个程序员来为您做这件事。
This task has two phases. First you need to identify an SQL statement which selects the data that you interested in. Secondly, you need to convert that select statement into an insert into ... statement and execute that statement from your Perl program.
这个任务有两个阶段。首先，您需要确定一个SQL语句来选择您感兴趣的数据。其次，您需要将select语句转换为insert into…语句并从Perl程序中执行该语句。
For the first stage, you don't need Perl at all. You just need a mysql command line program which is connected to your source database. You then try out a few SQL statements until you find one that gives you the data you need. It looks like you think the statement you need is:
对于第一阶段，您根本不需要Perl。您只需要一个mysql命令行程序，它连接到您的源数据库。然后尝试使用一些SQL语句，直到找到一个提供所需数据的语句。看起来你认为你需要的是:
select *
from data_1m.wms
where time = (select max(time) from wms)
That's great. I would probably make two tweaks to that SQL. I'd explicitly list the columns that I'm selecting and I'd specify the database that the wms table is in. So it would look like this:
太好了。我可能会对SQL做两次修改。我将显式列出正在选择的列，并指定wms表所在的数据库。看起来是这样的:
select time, available, closed, used, busy, reserved, down
from data_1m.wms
where time = (select max(time) from data_1m.wms)
Now we need to convert that to an insert ... statement. That probably looks like this:
现在我们需要将其转换为插入式…声明。大概是这样的:
insert into data_current.wms(time,available,closed,used,busy,reserved,down)
select time, available, closed, used, busy, reserved, down
from data_1m.wms
where time = (select max(time) from data_1m.wms)
(That's slightly different to what you had - you don't need the values keyword in an insert ... select ... statement.)
(这与您所拥有的稍有不同——您不需要在插入中使用值关键字……)选择……声明。)
So that's what you need to run from your Perl program. In fact you can test it from your mysql command line program first to ensure that it does the right thing.
这就是您需要从Perl程序中运行的。实际上，您可以先从mysql命令行程序中测试它，以确保它执行正确的操作。
Now we come to your current problem. And this is where I start to think that you might be better off just paying someone else to do this for you - because this problem is caused by you simply not reading (or not understanding) the basics of DBI.
现在我们来谈谈你目前的问题。这就是我开始认为你最好付钱给别人帮你做这件事的原因——因为这个问题仅仅是因为你没有阅读(或不理解)DBI的基本原理。
Running a query in DBI is a two-stage process. You prepare() the query and that gives you back a statement handle. You then call execute() on that statement handle to actually execute the query. Nowhere in the documentation is it even hinted that you can pass another (new) statement to the execute() method on an existing statement handle. If you want to run another statement then you need to prepare() that statement to get a new statement handle and then run execute() on that statement handle.
在DBI中运行查询是一个两阶段的过程。准备查询并返回一个语句句柄。然后调用语句句柄上的execute()来实际执行查询。在文档中没有任何地方暗示您可以将另一个(新的)语句传递给现有语句句柄上的execute()方法。如果您想运行另一个语句，那么您需要准备()该语句以获得一个新的语句句柄，然后在该语句句柄上运行execute()。
(Passing parameters to execute() is allowed in order to define values that are inserted into "bind points" in your SQL. That's why you got the error that you did. But you're not using bind points.)
(允许传递参数来执行()，以便定义插入到SQL“绑定点”中的值。这就是为什么你会犯这样的错误。但你不用绑定点)
So to do what you wanted to do in your code, you would write code like this:
要在代码中做你想做的事，你可以这样写:
my $stm1="select * from data_1m.wms where time =(select max(time) from wms)";
my $stmt2="insert into data_current.wms(time,available,closed,used,busy,reserved,down) values(select * from data_1m.wms where time =(select max(time) from wms)";
my $sth1 = $DBH1->prepare( $stmt1 );
$sth1->execute() or print "Could not insert data";
$sth1->finish;
my $sth2 = $DBH1->prepare( $stmt2 );
$sth2->execute;
$sth2->finish;
$DBH1->disconnect();
But (as I hope I've made clear above) you don't need to do this as $stmt1 is completely unnecessary. Also (as Chankey points out) it's far easier to run insert ... statements using the do() method than the prepare()/execute() cycle.
但是(我希望我在上面已经说过)您不需要这样做，因为$stmt1完全没有必要。而且(正如Chankey指出的)运行insert更容易…使用do()方法的语句，而不是使用prepare()/execute()循环。
I apologise if my comments yesterday confused you. I really tried to be a clear as possible. But I really think you need to step back and take a couple of deep breaths before continuing with this problem. It is starting to look a bit like you are programming by changing things at random - and that is never a good approach.
如果我昨天的评论让你感到困惑，我向你道歉。我真的尽力想要变得清楚。但我真的认为在继续这个问题之前，你需要退后一步，做几次深呼吸。它开始看起来有点像你通过随机改变事情来编程——这从来都不是一个好的方法。
#2
1
The typical method call sequence for a non-SELECT statement is:
非select语句的典型方法调用序列为:
prepare,
execute,
execute,
execute
Now in your case you are trying to insert data into table, which is non-SELECT statement. So you should follow the same approach.
现在，您正在尝试将数据插入到表中，这是一个非select语句。所以你应该遵循同样的方法。
1) Prepare the statement
1)准备语句
my $sth = $dbh->prepare("INSERT INTO...");
2) Execute the prepared statement
2)执行准备好的语句
$sth->execute(); #pass @bind_values if there are any
Alternatively you could also use do() method.
或者也可以使用do()方法。
$rows_affected = $dbh->do("INSERT INTO ...");
Please go through the documentation of DBI for more details.
请查看DBI的文档以了解更多细节。
DBD::mysql::st execute failed: called with 1 bind variables when 0 are needed
执行失败:在需要0时用1个绑定变量调用
You are getting the above error because you are passing another statement to execute method, whereas execute method accepts only bind values, which are none in your case.
您会得到上面的错误，因为您正在传递另一个语句来执行方法，而execute方法只接受绑定值，在您的例子中没有绑定值。
#1
3
I fear I rather confused you with one of my comments yesterday. Here's what I said:
恐怕我把你和我昨天的评论搞混了。这就是我说:
Looks like this has nothing to do with your Perl. Your SQL is returning the wrong data (for reasons that I don't understand). Here's what to do. Open a mysql session on the source database. Write an SQL statement that returns the correct data. Add the
insert into ...clause to the front of that SQL. Execute that SQL from Perl看起来这与您的Perl无关。您的SQL正在返回错误的数据(出于我不理解的原因)。这是要做什么。在源数据库上打开mysql会话。编写一个SQL语句，返回正确的数据。添加插入到…SQL前面的子句。从Perl中执行SQL
I'll try to unpick the misunderstandings here. But, given how long we've been answering questions on this seemingly simple task, I do wonder if it time for you to give up and employ a programmer to do this for you.
我将尽量消除这里的误解。但是，考虑到我们在这个看似简单的任务上已经回答了多长时间的问题，我确实想知道现在是否应该让您放弃并雇佣一个程序员来为您做这件事。
This task has two phases. First you need to identify an SQL statement which selects the data that you interested in. Secondly, you need to convert that select statement into an insert into ... statement and execute that statement from your Perl program.
这个任务有两个阶段。首先，您需要确定一个SQL语句来选择您感兴趣的数据。其次，您需要将select语句转换为insert into…语句并从Perl程序中执行该语句。
For the first stage, you don't need Perl at all. You just need a mysql command line program which is connected to your source database. You then try out a few SQL statements until you find one that gives you the data you need. It looks like you think the statement you need is:
对于第一阶段，您根本不需要Perl。您只需要一个mysql命令行程序，它连接到您的源数据库。然后尝试使用一些SQL语句，直到找到一个提供所需数据的语句。看起来你认为你需要的是:
select *
from data_1m.wms
where time = (select max(time) from wms)
That's great. I would probably make two tweaks to that SQL. I'd explicitly list the columns that I'm selecting and I'd specify the database that the wms table is in. So it would look like this:
太好了。我可能会对SQL做两次修改。我将显式列出正在选择的列，并指定wms表所在的数据库。看起来是这样的:
select time, available, closed, used, busy, reserved, down
from data_1m.wms
where time = (select max(time) from data_1m.wms)
Now we need to convert that to an insert ... statement. That probably looks like this:
现在我们需要将其转换为插入式…声明。大概是这样的:
insert into data_current.wms(time,available,closed,used,busy,reserved,down)
select time, available, closed, used, busy, reserved, down
from data_1m.wms
where time = (select max(time) from data_1m.wms)
(That's slightly different to what you had - you don't need the values keyword in an insert ... select ... statement.)
(这与您所拥有的稍有不同——您不需要在插入中使用值关键字……)选择……声明。)
So that's what you need to run from your Perl program. In fact you can test it from your mysql command line program first to ensure that it does the right thing.
这就是您需要从Perl程序中运行的。实际上，您可以先从mysql命令行程序中测试它，以确保它执行正确的操作。
Now we come to your current problem. And this is where I start to think that you might be better off just paying someone else to do this for you - because this problem is caused by you simply not reading (or not understanding) the basics of DBI.
现在我们来谈谈你目前的问题。这就是我开始认为你最好付钱给别人帮你做这件事的原因——因为这个问题仅仅是因为你没有阅读(或不理解)DBI的基本原理。
Running a query in DBI is a two-stage process. You prepare() the query and that gives you back a statement handle. You then call execute() on that statement handle to actually execute the query. Nowhere in the documentation is it even hinted that you can pass another (new) statement to the execute() method on an existing statement handle. If you want to run another statement then you need to prepare() that statement to get a new statement handle and then run execute() on that statement handle.
在DBI中运行查询是一个两阶段的过程。准备查询并返回一个语句句柄。然后调用语句句柄上的execute()来实际执行查询。在文档中没有任何地方暗示您可以将另一个(新的)语句传递给现有语句句柄上的execute()方法。如果您想运行另一个语句，那么您需要准备()该语句以获得一个新的语句句柄，然后在该语句句柄上运行execute()。
(Passing parameters to execute() is allowed in order to define values that are inserted into "bind points" in your SQL. That's why you got the error that you did. But you're not using bind points.)
(允许传递参数来执行()，以便定义插入到SQL“绑定点”中的值。这就是为什么你会犯这样的错误。但你不用绑定点)
So to do what you wanted to do in your code, you would write code like this:
要在代码中做你想做的事，你可以这样写:
my $stm1="select * from data_1m.wms where time =(select max(time) from wms)";
my $stmt2="insert into data_current.wms(time,available,closed,used,busy,reserved,down) values(select * from data_1m.wms where time =(select max(time) from wms)";
my $sth1 = $DBH1->prepare( $stmt1 );
$sth1->execute() or print "Could not insert data";
$sth1->finish;
my $sth2 = $DBH1->prepare( $stmt2 );
$sth2->execute;
$sth2->finish;
$DBH1->disconnect();
But (as I hope I've made clear above) you don't need to do this as $stmt1 is completely unnecessary. Also (as Chankey points out) it's far easier to run insert ... statements using the do() method than the prepare()/execute() cycle.
但是(我希望我在上面已经说过)您不需要这样做，因为$stmt1完全没有必要。而且(正如Chankey指出的)运行insert更容易…使用do()方法的语句，而不是使用prepare()/execute()循环。
I apologise if my comments yesterday confused you. I really tried to be a clear as possible. But I really think you need to step back and take a couple of deep breaths before continuing with this problem. It is starting to look a bit like you are programming by changing things at random - and that is never a good approach.
如果我昨天的评论让你感到困惑，我向你道歉。我真的尽力想要变得清楚。但我真的认为在继续这个问题之前，你需要退后一步，做几次深呼吸。它开始看起来有点像你通过随机改变事情来编程——这从来都不是一个好的方法。
#2
1
The typical method call sequence for a non-SELECT statement is:
非select语句的典型方法调用序列为:
prepare,
execute,
execute,
execute
Now in your case you are trying to insert data into table, which is non-SELECT statement. So you should follow the same approach.
现在，您正在尝试将数据插入到表中，这是一个非select语句。所以你应该遵循同样的方法。
1) Prepare the statement
1)准备语句
my $sth = $dbh->prepare("INSERT INTO...");
2) Execute the prepared statement
2)执行准备好的语句
$sth->execute(); #pass @bind_values if there are any
Alternatively you could also use do() method.
或者也可以使用do()方法。
$rows_affected = $dbh->do("INSERT INTO ...");
Please go through the documentation of DBI for more details.
请查看DBI的文档以了解更多细节。
DBD::mysql::st execute failed: called with 1 bind variables when 0 are needed
执行失败:在需要0时用1个绑定变量调用
You are getting the above error because you are passing another statement to execute method, whereas execute method accepts only bind values, which are none in your case.
您会得到上面的错误，因为您正在传递另一个语句来执行方法，而execute方法只接受绑定值，在您的例子中没有绑定值。