ID: 35884 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: 5.1.2RC1 Assigned To: fmk New Comment:
1) BIGINT is not a known data type for MS SQL Server 6.5. Microsoft has not updated the API (used for the mssql extension) since this version of the server. You can use the php_dblib.dll extension this is build using FreeTDS and is more up to date. 2) You should use msql_init(), mssql_bind() and mssql_execute() functions when working with storrd procedures. mssql_query() can handle multiple results but it can not return output parameters or return values. Please make sure to use mssql_next_result() to process additional result sets. Previous Comments: ------------------------------------------------------------------------ [2006-01-03 16:25:26] muratyaman at gmail dot com Sorry, my comments in PHP code before running the 1st query might be misleading (forgot to modify during submission to here). Discard them and please refer to the explanation. Thanks. ------------------------------------------------------------------------ [2006-01-03 16:19:55] [EMAIL PROTECTED] Assigned to the maintainer. ------------------------------------------------------------------------ [2006-01-03 16:17:39] muratyaman at gmail dot com Description: ------------ Hi, I am disappointed about the way PHP communicates with MS SQL Server and handles queries. I costed me more than a week to find out this. Basically, it is related to BIGINT data types, I guess. Here is the test case: Have a simple table including a field of type bigint. Have a procedure reading data from it, returning an output field of type bigint and a resultset on the fly. Have a simple SQL statement to declare a temp variable of type bigint, calling this procedure and getting the output. Use this through mssql_query() function. Have another simle select query. I expect 2 resultsets from the 1st query (2 values output) and another from the 2nd query. But I have 1 resultset from the 1st query (1 value) and a failure for a correct SQL statement. Because it is understood that connection is lost somehow during the 1st query without any error message. If you change every BIGINT to INT it works fine. However, I do NOT think this is the solution. Reproduce code: --------------- --sql statements to prepare database CREATE TABLE [T1] ( [id] bigint NOT NULL, [name] nvarchar(50) NOT NULL, PRIMARY KEY CLUSTERED ([id]) ) GO INSERT INTO [T1] ([id], [name]) VALUES (1, 'abc') GO INSERT INTO [T1] ([id], [name]) VALUES (2, 'def') GO CREATE PROCEDURE my_proc @output1 bigint output AS BEGIN --set the value select top 1 @output1 = id from t1 --return recordset for php select @output1 as MY_BIG_INT_output1 END GO --end of sql <?php //PHP file: if($dbh = mssql_connect('host\sqlserver', 'dba', 'pwd')){ echo 'Host connected<br>'; if(mssql_select_db('mydb')){ echo 'Database selected!<br>'; $input1=2; $sql =" DECLARE @o BIGINT EXECUTE my_proc @o output SELECT @o as output2 " ; echo 'Running SQL:<br><pre>'.$sql.'</pre>'; //if there is an emp record // there will be 3 result sets: emp, return_value, output1 //else // there will be 2 result sets: return_value, output1 if($rs = mssql_query($sql)){ do{ while($row = mssql_fetch_assoc($rs)){ echo '<pre>'.print_r($row,true).'</pre>'; } } while(mssql_next_result($rs)); mssql_free_result($rs); }else{ echo 'Error: '.mssql_get_last_message().'<br>'; } //try another query $sql2='SELECT TOP 1 * FROM t1'; echo 'Running SQL:<br><pre>'.$sql2.'</pre>'; if($rs2 = mssql_query($sql2)){ while($row2 = mssql_fetch_assoc($rs2)){ echo '<pre>'.print_r($row2,true).'</pre>'; } mssql_free_result($rs2); }else{ echo 'Error: '.mssql_get_last_message().'<br>'; } }else{ echo 'Database selection failed!'; }//end if select db mssql_close($dbh); }else{ echo 'Host connection failed!'; }//end if connection //end of PHP file ?> Expected result: ---------------- Host connected Database selected! Running SQL: DECLARE @o bigint EXECUTE my_proc @o output SELECT @o as output2 Array ( [MY_BIG_INT_output1] => 1 ) Array ( [output2] => 1 ) Running SQL: SELECT TOP 1 * FROM t1 Array ( [id] => 1 [name] => abc ) //// or an error message/warning if a particular datatype is not supported, etc. Actual result: -------------- Host connected Database selected! Running SQL: DECLARE @o bigint EXECUTE my_proc @o output SELECT @o as output2 Array ( [MY_BIG_INT_output1] => 1 ) Running SQL: SELECT TOP 1 * FROM t1 Warning: mssql_query() [function.mssql-query]: Unable to set query in ...test_proc.php on line 34 Error: ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=35884&edit=1