ID: 35839
Updated by: [EMAIL PROTECTED]
Reported By: muratyaman at gmail dot com
-Status: Assigned
+Status: Feedback
Bug Type: MSSQL related
Operating System: Win XP Pro SP2
PHP Version: 5CVS-2005-12-29 (snap)
Assigned To: fmk
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[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? :)
------------------------------------------------------------------------
[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?
------------------------------------------------------------------------
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
