CODEWITHSUNDEEP
phpsqlstored-proceduresodbc
ShadowScripter
ShadowScripter

Reputation: 7369

Calling odbc_fetch_array after executing a stored procedure in PHP gives error [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

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

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

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

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

Like this:

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

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.

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.

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.

Oh, and executing the plain (original) query instead of in a stored procedure gives no errors.

EDIT

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

Both return this error message:

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

Upvotes: 3

Views: 8430

Answers (1)

cwill747
cwill747

Reputation: 536

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

odbc_exec($connection, $sql)

Instead of

odbc_execute($connection, $sql)

Per a user comment here

Comment was:

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

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

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

Upvotes: 7

Related Questions