在PHP中执行存储过程后调用odbc_fetch_array会给出错误[Microsoft] [ODBC SQL Server驱动程序]无效的描述符索引 [英] Calling odbc_fetch_array after executing a stored procedure in PHP gives error [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

问题描述

因此，我正在尝试使用ODBC在SQL数据库中执行存储过程，但它会返回错误

So, I'm trying to use ODBC to execute a stored procedure in an SQL database, but it returns the error

odbc_fetch_array() [function.odbc-fetch-array]: 
    SQL error: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index, 
    SQL state S1002 in SQLGetData

这是PHP部分，非常标准

Here's the PHP part, pretty standard

...
$id = 240

$user = "user";
$password = "password";
$server = "server";
$database = "database";

$con = odbc_connect("SERVER=$server; 
                     DRIVER=SQL Server;
                     DATABASE=$database", 
                     $user, 
                     $password);    

$res = odbc_exec($con, "exec usp_GetRelatedToID '$id'");

while($row = odbc_fetch_array($res)){
    print_r($row);
}

这是存储过程，非常小而简单

Here's the Stored Procedure, really small and easy

CREATE PROCEDURE [dbo].[usp_GetRelatedToID]
    @id int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT AMENDMENT_ID, WDATE, ALTERATION, VER, REASON
    FROM AMENDMENTS
    WHERE AMENDMENT_ID = $id
END

这是AMENDMENTS的表格模式

Here's the Table schema for AMENDMENTS

(Column_name)      (Type)        (Nullable)
AMENDMENT_ID        int             no
RAD_MAIN_ID         int             yes
WDATE               datetime        yes
USR_ID              int             yes
ALTERATION          varchar         yes
REASON              varchar         yes
VER                 int             yes

Identity        Seed   Increment   Not For Replication
AMENDMENT_ID      1        1               0

constraint_type               constraint_name      constraint_keys
PRIMARY KEY (non-clustered) aaaaaAMENDMENTS1_PK      AMENDMENT_ID

有趣的是，如果我从过程中删除列VER，它不会返回错误

What's interesting is that it doesn't return the error if I remove the column VER from the procedure

赞:

CREATE PROCEDURE [dbo].[usp_GetRelatedToID]
    @id int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT AMENDMENT_ID, WDATE, ALTERATION, REASON
    FROM AMENDMENTS
    WHERE AMENDMENT_ID = $id
END

谁能解释我在哪里做错了，为什么这种情况持续发生?我有其他的存储过程也给出了相同的错误(有些还共享VER列)，而我的存储过程却没有.

Can anyone explain where I'm doing wrong and why this keep happening? I've got other stored procedures that give the same errors (some also share the column VER), and I've got stored procedures that don't.

我尝试了使用odbc_prepare和odbc_execute以及两种查询结构{CALL usp_GetRelatedToID(?)}在PHP中获取数据的不同方法，但这给了我更多的错误.

I've tried different methods of fetching the data in PHP using odbc_prepare and odbc_execute with both query structures, {CALL usp_GetRelatedToID(?)}, but that just gave me more errors.

出于我不愿讨论的原因，我不能在PHP中使用mssql函数，ODBC是允许我进行连接和查询的唯一方法.

And for reasons I won't go into, I can't use mssql functions in PHP, ODBC is the only way I'm allowed to connect and query.

哦，执行普通(原始)查询而不是在存储过程中执行不会出错.

编辑

$stmt = odbc_prepare($con, "{CALL usp_GetRelatedToID($id)}");
$res = odbc_execute($stmt, array());

//or

$stmt = odbc_prepare($con, "{CALL usp_GetRelatedToID(?)}");
$res = odbc_execute($stmt, array($id));

均返回此错误消息:

Warning: odbc_execute() [function.odbc-execute]: 
    SQL error: [Microsoft][ODBC SQL Server Driver]Cursor type changed, 
    SQL state 01S02 in SQLExecute

推荐答案

我也遇到了这个问题.我解决该问题的方法是使用

I ran into this problem as well. The way I got around it was to use

odbc_exec($connection, $sql)

代替

odbc_execute($connection, $sql)

每个用户的评论此处

评论为:

顺便说一句.如果有人对光标类型已更改"警告不休 在使用带有ORDER BY子句的execute时，只需将exec用于 现在(记住自己加上斜线).

BTW. If anyone is banging his head about "cursor type changed" warning while using execute with ORDER BY clause, then just use exec for now (remember to addslashes for yourself).

在PHP 5.3中，错误#43668 将被修复，它将允许您将游标类型更改为 SQL_CUR_USE_ODBC

In PHP 5.3 a Bug #43668 will be fixed and it will allow you to change a cursor type to SQL_CUR_USE_ODBC

请注意，您也可以尝试在odbc_connect中选择一个游标类型，但这对我不起作用(出现了更多的问题，然后又解决了).

Note that you could also try to select a cursor type in odbc_connect, but that didn't work for me (much more problems appeared then it solved).

显然，这是PHP中的错误，将根据此进行修补.

Apparently this is a bug in PHP, and will be patched per this

因此，如果遇到此问题，请尝试使用由odbc_execute()插入的odbc_exec().

So try using odbc_exec() insted of odbc_execute() if you are having this problem.

这篇关于在PHP中执行存储过程后调用odbc_fetch_array会给出错误[Microsoft] [ODBC SQL Server驱动程序]无效的描述符索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！