ID: 35839
User updated by: muratyaman at gmail dot com
Reported By: muratyaman at gmail dot com
Status: Assigned
Bug Type: MSSQL related
Operating System: Win XP Pro SP2
PHP Version: 5CVS-2005-12-29 (snap)
Assigned To: fmk
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2005-12-29 22:37:33] [EMAIL PROTECTED]
Frank, can you make any sense to this? :)
------------------------------------------------------------------------
[2005-12-29 22:35:42] 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:15:50] [EMAIL PROTECTED]
Does using php_dblib.dll work any better or not?
------------------------------------------------------------------------
[2005-12-29 17:36:44] 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 16:53:51] [EMAIL PROTECTED]
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.
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/35839
--
Edit this bug report at http://bugs.php.net/?id=35839&edit=1
