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