#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? :)
#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 ideal way of doing
