php 无效的游标状态，SQLExecDirect 中的 SQL 状态为 24000

Invalid cursor state, SQL state 24000 in SQLExecDirect

提问by weotch

I need to call two stored procedures in sequence via ODBC in PHP:

我需要在 PHP 中通过 ODBC 依次调用两个存储过程：

#run stored procedure 1
$query = "Shipped_Not_Shipped_Rep ".$_GET['rep_id'];
$result = odbc_exec($dbh, $query);
odbc_result_all($result);

#run stored procedure 2
$query = "Shipped_Not_Shipped_Account ".$_GET['account_id'];
$result = odbc_exec($dbh, $query);
odbc_result_all($result);

I'm getting this error in PHP after the second stored procedure call:

在第二次存储过程调用后，我在 PHP 中收到此错误：

Warning: odbc_exec() [function.odbc-exec]: SQL error: [unixODBC][FreeTDS][SQL Server]Invalid cursor state, SQL state 24000 in SQLExecDirect

警告：odbc_exec() [function.odbc-exec]：SQL 错误：[unixODBC][FreeTDS][SQL Server]游标状态无效，SQLExecDirect 中的 SQL 状态为 24000

If I re-arrange the order I call the stored procedures, it is always the second that errors. Is there a way to, idk, reset the cursor position between calls? A little out of my element here.

如果我重新安排我调用存储过程的顺序，那么错误总是第二个。有没有办法，idk，重置调用之间的光标位置？这里有点超出我的元素。

采纳答案by wallyk

Open two handles to the database. ODBC probably maintains the cursor in the handle.

打开数据库的两个句柄。ODBC 可能会在句柄中维护游标。

回答by Hyman Davidson

Something to try for people getting invalid cursor state with SQL server:

对于使用 SQL Server 获取无效游标状态的人，可以尝试一些方法：

SET NOCOUNT ON;

At the top of your stored procedure or SQL script. Found here: https://social.msdn.microsoft.com/Forums/en-US/f872382a-b226-4186-83c7-0d0fcadcd3eb/invalid-cursor-state?forum=sqldataaccessI had this problem just running some very average SQL in SQL Server 2017

在存储过程或 SQL 脚本的顶部。在这里找到：https: //social.msdn.microsoft.com/Forums/en-US/f872382a-b226-4186-83c7-0d0fcadcd3eb/invalid-cursor-state?forum=sqldataaccess我在运行一些非常普通的SQL时遇到了这个问题在 SQL Server 2017 中

回答by PBnJ

I found the exact problem as well. Apparently this is common with the free ODBC drivers. This has been my morning headache from trying to migrate a project from MySQL to ODBC SQL Server. I finally found what got rid of this for me.

我也发现了确切的问题。显然，这在免费的 ODBC 驱动程序中很常见。这是我早上尝试将项目从 MySQL 迁移到 ODBC SQL Server 时头疼的问题。我终于找到了让我摆脱这个的东西。

This error shows up because an active cursor still exists from the previous result set. I was able to get rid of this error without using the disconnect/reconnect method by ensuring I read through the entire first record set (even if only using a partial piece of it) before issuing a new one. Note: I'm using PHP.

出现此错误是因为上一个结果集中的活动游标仍然存在。通过确保在发布新记录之前阅读整个第一个记录集（即使只使用其中的一部分），我能够在不使用断开连接/重新连接方法的情况下消除此错误。注意：我正在使用 PHP。

Gives me an error:

给我一个错误：

$sql="SELECT COUNT(whatever) as whatever FROM whatever";<br />
$countResult = odbc_exec($db, $sql);<br />
$countMenuHeader = odbc_fetch_array($countResult);<br />
extract ($countMenuHeader);<br />
$countRecords = $NumMenuHeader;<br />

$sql="SELECT whatever as whatever FROM whatever";<br />
$result = odbc_exec($db, $sql);<br />
$MenuHeader = odbc_fetch_array($result);<br />

Cleared the error:

清除错误：

 $sql="SELECT COUNT(whatever) as whatever FROM whatever";<br />
 $countResult = odbc_exec($db, $sql);<br />

 while($countMenuHeader = odbc_fetch_array($countResult))<br />
 {<br />&nbsp;&nbsp;
 extract ($countMenuHeader);<br />&nbsp;&nbsp;
 $countRecords = $NumMenuHeader;<br />}

 $sql="SELECT whatever as whatever FROM whatever";<br />
 $result = odbc_exec($db, $sql);<br />
 $MenuHeader = odbc_fetch_array($result);<br />

In short, make sure you completely read or fetch the data set before moving to the next statement.

简而言之，请确保在移动到下一条语句之前完全读取或获取数据集。

回答by martinlue

I ran into the same problem, but odbc_free_result($result)between the 2 queries did the job for me.

我遇到了同样的问题，但odbc_free_result($result)在 2 个查询之间为我完成了这项工作。

Documentation:

文件：

bool odbc_free_result ( resource $result_id )

Free resources associated with a result.

odbc_free_result()only needs to be called if you are worried about using too much memory while your script is running. All result memory will automatically be freed when the script is finished.

Note: If auto-commit is disabled (see odbc_autocommit()) and you call odbc_free_result()before committing, all pending transactions are rolled back.

bool odbc_free_result ( resource $result_id )

与结果相关的免费资源。

odbc_free_result()仅当您担心在脚本运行时使用过多内存时才需要调用。脚本完成后，所有结果内存将自动释放。

注意：如果禁用了自动提交（请参阅参考资料odbc_autocommit()）并且您odbc_free_result()在提交之前调用，则所有挂起的事务都将回滚。

回答by Will

Just to clarify that calling finish() means you are merely finished with the current query and its results and you can now safely call execute() again. No need to call prepare() again first.

只是为了澄清调用 finish() 意味着您只是完成了当前查询及其结果，现在您可以再次安全地调用 execute()。无需先再次调用 prepare() 。

I got caught by this one on AIX using the ODBC driver to a DB2 SQL database. I think it was AIX that had an old ODBC driver perhaps because things were fine on Linux.

我在 AIX 上使用 ODBC 驱动程序连接到 DB2 SQL 数据库时遇到了这个问题。我认为是 AIX 有一个旧的 ODBC 驱动程序，也许是因为 Linux 上的情况很好。

Anyway, I did an SQL query that returns one row, over and over in a for loop as follows and got the 24000 error.

无论如何，我做了一个 SQL 查询，它在 for 循环中一遍又一遍地返回一行，如下所示，并得到 24000 错误。

my $sth = $dbh->prepare( $query ) or die "dying";

foreach my $i (@blah)
{
    $sth->execute($fred, $i) or die "dying";

    my $hash_ref = $sth->fetchrow_hashref("NAME_uc"); # only a single row exists

    ...

    $sth->finish(); # MUST do this
} 

My conclusion is that I had to call $sth->finish() so that I could safely call $sth->execute() again, otherwise I could get "Invalid cursor state. SQLSTATE=24000" error message.

我的结论是我必须调用 $sth->finish() 以便我可以安全地再次调用 $sth->execute() ，否则我会得到“无效的游标状态。SQLSTATE=24000”错误消息。

This is because we must make sure we completely read or fetch all the data set before moving to the next statement.$sth->finish() indicates to DBI that you are finished with the statement handle. It can then be reused for a call to execute()

这是因为我们必须确保在移动到下一个语句之前完全读取或获取所有数据集。$sth->finish() 向 DBI 指示您已完成语句句柄。然后可以重用它来调用 execute()

I also found that I could instead put the fetch in a while loop, even though only one row is ever returned but the query. It seems trying to fetch the next non-existent row also makes the sth resuable for an execute.

我还发现我可以改为将 fetch 放在 while 循环中，即使只返回了一行，但查询。似乎试图获取下一个不存在的行也会使 sth 可重新执行以执行。

my $sth = $dbh->prepare( $query ) or die "dying";

for
{ 
    $sth->execute($fred, $i) or die "dying";

    while (my $hash_ref = $sth->fetchrow_hashref("NAME_uc"))
    {
         ...
    }    
}

Both solutions worked for me. :)

两种解决方案都对我有用。:)

回答by Hunky Dory

A work around for opening nested queries using unixODBC and freeTDS in PHP

在 PHP 中使用 unixODBC 和 freeTDS 打开嵌套查询的解决方法

if you can modify to your odbc.ini create another configuration section using the same connection info but another section name:

如果您可以修改您的 odbc.ini 使用相同的连接信息但另一个部分名称创建另一个配置部分：

[DATASOURCE1]
Description = "Data Connection 1"
Driver = FreeTDS
Server = <your server>
Port = 1433
Database = <your db>

[DATASOURCE1A]
Description = "Data Connection 2"
Driver = FreeTDS 
Server = <your server>
Port = 1433
Database = <your db>

Then in your code create two handles, one for each data source description:

然后在您的代码中创建两个句柄，一个用于每个数据源描述：

$dbhandle = odbc_connect('DATASOURCE1', 'user', 'password');
$dbhandle1 = odbc_connect('DATASOURCE1A', 'user', 'password');

Then you can use the handles in nested queries:

然后您可以在嵌套查询中使用句柄：

$dbresult = odbc_exec($dbhandle, "SELECT <some sql>");
while($row = odbc_fetch_array($dbresult)) {

 $dbresult1 = odbc_exec($dbhandle1, "<some different sql>");

 while($row1 = odbc_fetch_array($dbresult1)) {
  
  #do stuff with nested query data like $row['name'] and $row1['time']
 }
}

You could nest deeper but you have to create a odbc.ini entry for each level. It ain't pretty but it works for me until multiple cursors are available.

您可以嵌套更深，但您必须为每个级别创建一个 odbc.ini 条目。它并不漂亮，但它对我有用，直到有多个游标可用。

回答by Dmitry

Try accessing the results using different cursors, $result1 and $result2.

尝试使用不同的游标 $result1 和 $result2 访问结果。