#35839 [Bgs]: mssql_query(): "Possible network error: Bad token from SQL Server.."
ID: 35839 Updated by: [EMAIL PROTECTED] Reported By: muratyaman at gmail dot com Status: Bogus Bug Type: MSSQL related Operating System: Win XP Pro SP2 PHP Version: 5CVS-2005-12-29 (snap) Assigned To: fmk New Comment: I already closed it, but I do not get any errors with your original code using mssql_query(). I think this is caused by something else. Perhaps your version of ntwdblib.dll is different than mine. Previous Comments: [2005-12-30 01:54:39] muratyaman at gmail dot com Thank you very much indeed, at least you directed me to a solution.. Previous version of my function was already using mssql_query and fetch functions to get the row returned from my procedure (since inside procedure I have: "select @id as ID" it returns a resultset), not the output or return code: DECLARE @MY_ID INT8, @r int8 EXECUTE @r= SPMY_GET_NEXT_ID \"$mygenid\", @MY_ID OUTPUT and it was doing it all right, but subsequent calls to the db was causing the error I mentioned, because the connection is somehow lost after running this sql. If you think this is not a bug you can close it. Your prompt replies are much appreciated. Kind regards [2005-12-30 00:49:20] [EMAIL PROTECTED] using mssql_qyery() to execute storred procedures is usefull, but it can only be used to retreive result sets. output parameters and return values are not handled with this method. In that case you must use the mssql_init(), mssql_bind() and mssql_execute() functions. [2005-12-30 00:46:25] [EMAIL PROTECTED] bigint is not known on my mssql server 7 but it works fine on mssql server 2000. I have tested your code on both servers using PHP 5.1.2-dev and I do not get any errors. I don't think there is any errors here. I have also tested with both local and remote server and with php_mssql.dll and php_dblib.dll. [2005-12-30 00:21:51] muratyaman at gmail dot com Hi again :) I changed my function to this: function db_get_next_id($mygenid=''){ $id = 0; $ret_val=0; $stmt = mssql_init("SPMY_GET_NEXT_ID"); mssql_bind($stmt, "@GEN_ID_NAME", &$mygenid, SQLVARCHAR, FALSE,FALSE);//input, not null mssql_bind($stmt, "@ID", &$id, SQLINT4, TRUE,TRUE);//output,null mssql_bind($stmt,"RETVAL",&$ret_val,SQLINT4); $result = mssql_execute($stmt); while($row=mssql_fetch_row($result)){ //read id $id=$row[0];//but it should get from the output variable!? } mssql_free_statement($stmt); unset($stmt); // <---VERY important return $id; } Anyway, this solved my problem. But will I not be able to use arbitrary SQL statements with mssql_query, including execution of procedures, etc. ?! Kind regards [2005-12-29 23:54:16] muratyaman at gmail dot com Basically, my procedure inserts a dummy record into a special table and gets inserted id. (int8 is a udt for 'bigint', i'm changing it for you below to bigint). I have dummy tables to simulate sequence generators for different tables, here is a pair of them: CREATE TABLE TBL_GEN_ABC_ID ( ID bigint IDENTITY(1, 1) NOT NULL, DUMMY bit, CONSTRAINT PK_TBL_GEN_ABC_ID PRIMARY KEY CLUSTERED (ID) ) GO CREATE TABLE TBL_ABC ( ABC_ID INT NOT NULL, ABC_NAME] VARCHAR(100) NOT NULL, CONSTRAINT PK_TBL_ABC PRIMARY KEY CLUSTERED (ABC_ID) ) GO Procedure is like this: CREATE PROCEDURE spmy_get_next_id( @GEN_ID_NAME VARCHAR(100)='#NO TABLE', @ID bigint OUTPUT ) AS BEGIN SET @ID=NULL; DECLARE @GEN_ID bigint; SET @GEN_ID=0; IF (UPPER(@GEN_ID_NAME)='ABC_ID') BEGIN WHILE (1=1) BEGIN --generate id INSERT INTO TBL_GEN_ABC_ID (DUMMY) VALUES(0); SET @GEN_ID=@@IDENTITY; --get generated id IF(NOT EXISTS( --make sure it was not used SELECT ABC_ID FROM TBL_ABC WHERE [EMAIL PROTECTED]) ) BREAK; END END SET @[EMAIL PROTECTED]; SELECT @ID AS ID;--for PHP to read resultset and value RETURN @ID; --return value, not crucial END GO so in PHP you can use modifed version of my function: function db_get_next_id($mygenid=''){ $i=0; $sql=" DECLARE @MY_ID bigint, @r bigint EXECUTE @r=SPMY_GET_NEXT_ID \"$mygenid\", @MY_ID OUTPUT " ; if($qry=db_query($sql)){ while($row=db_fetch_row($qry)){ $i=$row[0]; break; } db_free_result($qry); } return $i; }//end fun get next id $new_id=db_get_next_id('ABC_ID'); (As I mentioned earlier, e.g. db_fetch_row is just using mssql_fetch_row.. all of my db_xyz functions are like this.) This works fine but subsequent mssql_query functions fail. This may not be the
#35839 [Bgs]: mssql_query(): "Possible network error: Bad token from SQL Server.."
ID: 35839 User updated by: muratyaman at gmail dot com Reported By: muratyaman at gmail dot com Status: Bogus Bug Type: MSSQL related Operating System: Win XP Pro SP2 PHP Version: 5CVS-2005-12-29 (snap) Assigned To: fmk New Comment: Thank you very much indeed, at least you directed me to a solution.. Previous version of my function was already using mssql_query and fetch functions to get the row returned from my procedure (since inside procedure I have: "select @id as ID" it returns a resultset), not the output or return code: DECLARE @MY_ID INT8, @r int8 EXECUTE @r= SPMY_GET_NEXT_ID \"$mygenid\", @MY_ID OUTPUT and it was doing it all right, but subsequent calls to the db was causing the error I mentioned, because the connection is somehow lost after running this sql. If you think this is not a bug you can close it. Your prompt replies are much appreciated. Kind regards Previous Comments: [2005-12-30 00:49:20] [EMAIL PROTECTED] using mssql_qyery() to execute storred procedures is usefull, but it can only be used to retreive result sets. output parameters and return values are not handled with this method. In that case you must use the mssql_init(), mssql_bind() and mssql_execute() functions. [2005-12-30 00:46:25] [EMAIL PROTECTED] bigint is not known on my mssql server 7 but it works fine on mssql server 2000. I have tested your code on both servers using PHP 5.1.2-dev and I do not get any errors. I don't think there is any errors here. I have also tested with both local and remote server and with php_mssql.dll and php_dblib.dll. [2005-12-30 00:21:51] muratyaman at gmail dot com Hi again :) I changed my function to this: function db_get_next_id($mygenid=''){ $id = 0; $ret_val=0; $stmt = mssql_init("SPMY_GET_NEXT_ID"); mssql_bind($stmt, "@GEN_ID_NAME", &$mygenid, SQLVARCHAR, FALSE,FALSE);//input, not null mssql_bind($stmt, "@ID", &$id, SQLINT4, TRUE,TRUE);//output,null mssql_bind($stmt,"RETVAL",&$ret_val,SQLINT4); $result = mssql_execute($stmt); while($row=mssql_fetch_row($result)){ //read id $id=$row[0];//but it should get from the output variable!? } mssql_free_statement($stmt); unset($stmt); // <---VERY important return $id; } Anyway, this solved my problem. But will I not be able to use arbitrary SQL statements with mssql_query, including execution of procedures, etc. ?! Kind regards [2005-12-29 23:54:16] muratyaman at gmail dot com Basically, my procedure inserts a dummy record into a special table and gets inserted id. (int8 is a udt for 'bigint', i'm changing it for you below to bigint). I have dummy tables to simulate sequence generators for different tables, here is a pair of them: CREATE TABLE TBL_GEN_ABC_ID ( ID bigint IDENTITY(1, 1) NOT NULL, DUMMY bit, CONSTRAINT PK_TBL_GEN_ABC_ID PRIMARY KEY CLUSTERED (ID) ) GO CREATE TABLE TBL_ABC ( ABC_ID INT NOT NULL, ABC_NAME] VARCHAR(100) NOT NULL, CONSTRAINT PK_TBL_ABC PRIMARY KEY CLUSTERED (ABC_ID) ) GO Procedure is like this: CREATE PROCEDURE spmy_get_next_id( @GEN_ID_NAME VARCHAR(100)='#NO TABLE', @ID bigint OUTPUT ) AS BEGIN SET @ID=NULL; DECLARE @GEN_ID bigint; SET @GEN_ID=0; IF (UPPER(@GEN_ID_NAME)='ABC_ID') BEGIN WHILE (1=1) BEGIN --generate id INSERT INTO TBL_GEN_ABC_ID (DUMMY) VALUES(0); SET @GEN_ID=@@IDENTITY; --get generated id IF(NOT EXISTS( --make sure it was not used SELECT ABC_ID FROM TBL_ABC WHERE [EMAIL PROTECTED]) ) BREAK; END END SET @[EMAIL PROTECTED]; SELECT @ID AS ID;--for PHP to read resultset and value RETURN @ID; --return value, not crucial END GO so in PHP you can use modifed version of my function: function db_get_next_id($mygenid=''){ $i=0; $sql=" DECLARE @MY_ID bigint, @r bigint EXECUTE @r=SPMY_GET_NEXT_ID \"$mygenid\", @MY_ID OUTPUT " ; if($qry=db_query($sql)){ while($row=db_fetch_row($qry)){ $i=$row[0]; break; } db_free_result($qry); } return $i; }//end fun get next id $new_id=db_get_next_id('ABC_ID'); (As I mentioned earlier, e.g. db_fetch_row is just using mssql_fetch_row.. all of my db_xyz functions are like this.) This works fine but subsequent mssql_query functions fail. This may not be the ideal way of doing it, but it should not cause any harm. Everything else is working fine. Maybe I should just change the way I use my procedures.. Thank you. [2005-12-29 22:37:33] [EMAIL PROTECTED] Frank, can you make any sense to this?