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