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:
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 Previous Comments: ------------------------------------------------------------------------ [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? ------------------------------------------------------------------------ [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.. ------------------------------------------------------------------------ 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