所以,我正在尝试使用ODBC在SQL数据库中执行存储过程,但它返回错误

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

这是PHP部分,非常标准

...
$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);
}

这是存储过程,非常小而且容易

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的表模式

(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,它不会返回错误

像这样：

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),而且我的存储过程没有.

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

由于我不会进入的原因,我不能在PHP中使用mssql函数,ODBC是我允许连接和查询的唯一方法.

哦,执行普通(原始)查询而不是存储过程不会产生任何错误.

编辑

$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

解决方法:

我也遇到了这个问题.我绕过它的方式是使用

odbc_exec($connection, $sql)

代替

odbc_execute($connection, $sql)

根据用户评论here

评论是：

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).

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

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中的一个错误,并将按照this进行修补

因此,如果您遇到此问题,请尝试使用odbc_exec()输入odbc_execute().