php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #35839 mssql_query(): "Possible network error: Bad token from SQL Server.."
Submitted: 2005-12-29 16:33 UTC Modified: 2005-12-30 01:58 UTC
From: muratyaman at gmail dot com Assigned: fmk (profile)
Status: Not a bug Package: MSSQL related
PHP Version: 5CVS-2005-12-29 (snap) OS: Win XP Pro SP2
Private report: No CVE-ID: None
 [2005-12-29 16:33 UTC] muratyaman at gmail dot com
Description:
------------
Error message is:
"mssql_query() [function.mssql-query]: Possible network error: Bad token from SQL Server: Datastream processing out of sync. (severity 9)
   in file ... line ..."

I have a WinXP, Apache2, PHP5, MSDE2000 app and many pages inserting, updating, deleting records..
Pages for inserts include code to return on error and not lose entered data.


Reproduce code:
---------------
(Every page opens connection, queries database multiple times, closes the connection.)

Create a form with a few textboxes and a combobox.
I create a combobox with:
<?
$selected_option = 0;
echo db_combo('field1', 'select field1 from tbl1', $selected_option);
?>

db_combo simply uses mssql_query to select records from a table, fill with options, create HTML code of a combobox with
<select>
  <option></option>
</select>

I use a global $dbh to handle database connection, also use  mssql_connect, mssql_select_db functions.

Expected result:
----------------
1. load page: tbl2_new.php 
   combobox FULL with records read from the table
2. post to  : tbl2_insert.php
   may not insert record because of normal reasons
3. return to: tbl2_new.php
   any entered data is post back
   combobox FULL with records read from the table
4. post to  : tbl2_insert.php
   insert record


Actual result:
--------------
1. load page: tbl2_new.php 
   with EMPTY combobox and ERROR
   connection is lost
   every call afterwards to mssql_query fails
2. post to  : tbl2_insert.php
   may or may not insert record
3. return to: tbl2_new.php
   any entered data is post back
   combobox FULL with records read from the table
   no problem at all

Briefly, "new" and "edit" pages are very much similar with comboboxes etc. But on "new" pages, combobox cannot get any records on 1st run.

I could not figure out the problem.
But I think there is a communication problem when SQL server tries to send the records from a table.

Thanks in advance.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-12-29 16:53 UTC] fmk@php.net
Can you reproduce the error with out the DB abstraction layer you are using?
The data send to the server contains garbage, and this could be an indication that you are getting this error in a multithreaded environment. The standard MSSQL extension is not thread safe du to the msdblib library used to build the extension.
You could replace php_mssql.dll with php_dblib.dll. It provides the same functions but uses FreeTDS to create conenctions.
 [2005-12-29 17:36 UTC] muratyaman at gmail dot com
Hi!
I installed latest version of PHP (5.1.2rc2) but same :(
I found out that even the simplest of my pages have same problem regardless of combobox.
My db abstraction layer is really simple:
I just replaced "mssql_abc" functions with "db_abc" functions.
I will try the DLL you recommended.
I think I need to find out when/how/why the connection is broken if that's the case.. Unfortunately, there are not many traces, I looked at Apache log, SQL server log.. nothing. I do not have anything apart from PHP errors, because mssql_get_last_message() does not contain anything.

Regards..
 [2005-12-29 22:15 UTC] sniper@php.net
Does using php_dblib.dll work any better or not?
 [2005-12-29 22:35 UTC] muratyaman at gmail dot com
Thank you.
I could not integrate php_dblib.dll :(
I tried to compile FreeTDS using Dev-C++ but could not get it working with PHP.. I am stuck.

I was investigating the problem.
I have a function as follows:

function db_get_next_id($mygenid=''){
  $i=0;
  $sql="
      DECLARE @MY_ID INT8, @r int8
      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('keyfield');

This just works fine, I found that after this, calls to the db fails and produces the error. Because when I comment out the line calling this function, I don't get any error message.
Any ideas?
Regards
 [2005-12-29 22:37 UTC] sniper@php.net
Frank, can you make any sense to this? :)
 [2005-12-29 23:54 UTC] 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 ABC_ID=@GEN_ID)
         )
           BREAK;
     END

  END
  
  SET @ID=@GEN_ID; 
  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-30 00:21 UTC] 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-30 00:46 UTC] fmk@php.net
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:49 UTC] fmk@php.net
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 01:54 UTC] 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 01:58 UTC] fmk@php.net
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.
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Sun May 29 03:03:49 2022 UTC