php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #20861 sybase_query fail to handle stored procedure result with "nocount" option
Submitted: 2002-12-06 09:53 UTC Modified: 2003-05-21 03:19 UTC
From: csollet at coleebris dot com Assigned: thekid (profile)
Status: Closed Package: Sybase-ct (ctlib) related
PHP Version: 4.3.0RC2 OS: Linux
Private report: No CVE-ID: None
 [2002-12-06 09:53 UTC] csollet at coleebris dot com
Test script : 

<?
$sy = sybase_pconnect('DBHOST', 'user', 'pass');
sybase_select_db('MyDB', $sy);
sybase_query("test_bug 1", $sy);
?> 

produce : 

Notice: sybase_query() [http://www.php.net/function.sybase-query]: Sybase:  Unexpected results, cancelling current in - on line 4

with test_bug defined as follow : 

create procedure test_bug
@input int
AS
BEGIN
 set nocount on

 select @input 'result'
 return 2
END

Removing the "set nocount on" lead to expected result but, of course, in real wold sp, the nocount option is rarely used for fun...

Build with Sybase ctclient v12.5
Works perfectly well with 4.2.3 and previous.


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-12-06 10:48 UTC] csollet at coleebris dot com
After some more test, i can get the notice (notice -> no result) without "set nocount on". Don't have pushed further since the procedure involved is about 400 lines with call to others procedures.
So, just to add that "set nocount on" is a case, not the only one, that trigger the error.
 [2002-12-07 08:38 UTC] thekid@php.net
Well, in earlier versions sybase_ct silently cancelled the unexpected results, now it will inform you it is doing so - I thought it was nice to be a bit more verbose about things.
 [2002-12-07 13:51 UTC] thekid@php.net
<?php
/* Script to reproduce bug #20861
 *
 * $Id$
 */
 
  ini_set('sybct.min_server_severity', 11);
  $db= sybase_connect('gurke', '******', '******');
  sybase_select_db('tempdb', $db);
  
  // var_dump(sybase_query('
  //  create procedure test_bug
  //  @input int
  //  AS
  //  BEGIN
  //   set nocount on
  // 
  //   select @input 'result'
  //   return 2
  //  END
  // ', $db));

  // This doesn't print out the error message
  $q= sybase_query('test_bug 1', $db);
  while ($data= sybase_fetch_object($q)) {
    var_dump($q, $data);
  }

  // This actually prints out the warning message as
  // we have two resultsets  
  $q= sybase_query('
	declare @return int
	exec @return= test_bug 1				
	select @return
  ', $db);
  while ($data= sybase_fetch_object($q)) {
    var_dump($q, $data);
  }
  
  // var_dump(sybase_query('drop procedure test_bug', $db));
  
  sybase_close($db);
?>
 [2002-12-10 05:11 UTC] thekid@php.net
------ Feedback from original reporter ------
Timm Friebe wrote:
[...]
> Thanks - I've updated the bug. What do you think? 
> Should I remove the notice?

Hum, after getting a closer look at the problem, i've realized that i've been fooled by another new features of PHP4.3 that breaks our apps a bit later :
Dispite new sybase notice, i got expected result as you wrote it in #20861 (not what i figured out at first look).

So #20861 is boggus. Sorry for  the false alarm.

About the notice itself, i haven't, personaly, any requirement since, except for debuging purpose, all our sybase_query are @ prefixed (don't want any sql print out to users in case of errors). Maybe PHP4.3 should be shipped with a "BC changes" file that stat this one?

Regards,
Christophe.
 [2003-05-06 06:52 UTC] steil at zweitwerk dot com
Bug #20861 seems to be closed since supposed to be bogus if I understand things correctly.

I found the same bug and don't believe it being bogus.

My example (Sybase 11.7, Linux, PHP4.3.1) arose when trying to call sybase' build-in procedure sp_pkeys, i.e.

    $db = sybase_connect("host", "user", "pass");
    $query = sybase_query('sp_pkeys some_table', $db);
    var_dump(sybase_fetch_array($query));

This results in "bool(false)".

A closer look at the procedure's body shows that it 'set nocount on'.
Recompiling the procedure without this line gives the expected result, e.g.

array(10) { 
  [0]=> string(11) "jadisnetHUM"   ["table_qualifier"]=> string(11) "jadisnetHUM"            
  [1]=> string(3) "dbo" ["table_owner"]=> string(3) "dbo" 
  [2]=> string(5) "asset" ["table_name"]=> string(5) "asset" 
  [3]=> string(7) "assetid" ["column_name"]=> string(7) "assetid" 
  [4]=> string(1) "1" ["key_seq"]=> string(1) "1" 
} 

I strongly suspect PHP is looking for the rowcount before trying to retrieve data and therefore is mislead by 'nocount on'.
I also called both, original and without 'nocount on', versions of the procedure with a different programming language (Omnis) and encountered no problems in either case.
Therefore it should be possible to correct PHP's fetching routine to not relying on not setting 'nocount on'.
I would expect at least to be able to excute build-in procedures without problems, i.e. 'set nocount on' seems not to be that special as a former submitter suggested.
 [2003-05-20 15:21 UTC] thekid@php.net
Have not been able to reproduce (FreeBSD, FreeTDS, SQL Server/11.0.3.3, PHP Versions 4.3.1 and 5.0.0-dev):

sa@gurke:CAFFEINE.493> sp_pkeys news;-m vert
table_qualifier: CAFFEINE
table_owner:     dbo
table_name:      news
column_name:     news_id
key_seq:         1

thekid@friebes:~/devel/php/php5/ext/sybase_ct/tests > cat bug20861.php 
<?php
  $db= @sybase_connect('gurke', 'sa', '');
  @sybase_select_db('CAFFEINE', $db);
  $query= sybase_query('sp_pkeys news', $db);
  var_dump(sybase_fetch_assoc($query));
?>
thekid@friebes:~/devel/php/php5/ext/sybase_ct/tests > php bug20861.php 
array(5) {
  ["table_qualifier"]=>
  string(8) "CAFFEINE"
  ["table_owner"]=>
  string(3) "dbo"
  ["table_name"]=>
  string(4) "news"
  ["column_name"]=>
  string(7) "news_id"
  ["key_seq"]=>
  int(1)
}
thekid@friebes:~/devel/php/php5/ext/sybase_ct/tests > php -v
PHP 4.3.1 (cli) (built: Feb 23 2003 20:37:24)
Copyright (c) 1997-2002 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2002 Zend Technologies

thekid@friebes:~/devel/php/php5/ext/sybase_ct/tests > php5 bug20861.php 
array(5) {
  ["table_qualifier"]=>
  string(8) "CAFFEINE"
  ["table_owner"]=>
  string(3) "dbo"
  ["table_name"]=>
  string(4) "news"
  ["column_name"]=>
  string(7) "news_id"
  ["key_seq"]=>
  int(1)
}
thekid@friebes:~/devel/php/php5/ext/sybase_ct/tests > php5 -v           
PHP 5.0.0-dev (cli) (built: Apr 29 2003 11:25:44)
Copyright (c) 1997-2003 The PHP Group
Zend Engine v2.0.0-dev, Copyright (c) 1998-2003 Zend Technologies

 [2003-05-21 03:19 UTC] thekid@php.net
Cannot reproduce on Debian / Sybase-Libs / ASE 12.5.0.3

cgi@hlt1:~$ cat bug20861.php 
<?php
  $db= @sybase_connect('sybase', '*****', '*****');
  @sybase_select_db('MIGRAENE', $db);
  $query= sybase_query('sp_pkeys contract', $db);
  var_dump(sybase_fetch_assoc($query));

  var_dump(sybase_fetch_assoc(sybase_query('select @@version', $db)));
  var_dump(phpversion());
?>

cgi@hlt1:~$ php4 bug20861.php 
array(5) {
  ["table_qualifier"]=>
  string(8) "MIGRAENE"
  ["table_owner"]=>
  string(3) "dbo"
  ["table_name"]=>
  string(11) "contract"
  ["column_name"]=>
  string(14) "contract_id"
  ["key_seq"]=>
  int(1)
}
array(1) {
  ["computed"]=>
  string(119) "Adaptive Server Enterprise/12.5.0.3/EBF 10973 ESD#1/P/Sun_svr4/OS             5.8/rel12503/1920/64-bit/FBO/Thu Mar 27 01:09:31 2003"
}
string(5) "4.3.1"
cgi@hlt1:~$ php4 -v
PHP 4.3.1 (cli) (built: Feb 24 2003 11:37:26)
Copyright (c) 1997-2002 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2002 Zend Technologies

 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Nov 14 12:01:32 2024 UTC