php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #14354 sybase_query returns 1 regardless of delete success
Submitted: 2001-12-05 18:53 UTC Modified: 2013-10-15 11:54 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:0 of 0 (0.0%)
From: mheumann at sei dot cl Assigned:
Status: No Feedback Package: Sybase-ct (ctlib) related
PHP Version: 4.3.2RC4-dev OS: Linux Suse 8.1
Private report: No CVE-ID: None
 [2001-12-05 18:53 UTC] mheumann at sei dot cl
When trying to delete a record that Sybase will not allow to be deleted because of referential integrity constraints, sybase_query returns 1. When the record can be deleted successfully, the return code is also 1.

Example:
// Delete record with referential integrity constraint
$strSQL = "delete from MyTable where MyId=3";
$iResult = sybase_query($strSQL, $iLink );
echo "Query Result: $iResult";  // Displays 1

// Delete record without referential integrity constraint
$strSQL = "delete from MyTable where MyId=4";
$iResult = sybase_query($strSQL, $iLink );
echo "Query Result: $iResult";  // Displays 1, too

We use Sybase ASE 11.9.2 on a Linux Mandrake 8 Box.
We have tried several times to run sybase-ct instead of the dblib, but could never make it work.

Our current configure line:

./configure \
--with-apxs=/usr/sbin/apxs \
--with-pear \
--with-gd=yes \
--with-tiff-dir=/usr/local \
--with-png-dir=/usr/local \
--with-jpeg-dir=/usr/local \
--with-ttf=/usr/src/freetype-1.3.1 \
--with-zlib \
--with-xml \
--with-ftp \
--with-mcal=../libmcal \
--with-imap=/usr/local \
--with-sybase=/opt/sybase-11.9.2 \
--with-mysql=/usr \
--with-mcrypt=/usr/local \
--with-mhash \
--with-pdflib \
--with-zlib-dir=/usr/local \
--with-fdftk=../fdftk \
--with-config-file-path=/etc/httpd/conf \
--enable-versioning \
--enable-track-vars

We use both PHP3 and PHP4 as apache modules; it's a test server, we need it that way.

Please contact me asap if you need more information.
Michael.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2001-12-11 07:36 UTC] mheumann at sei dot cl
Hello? Anybody home? I kinda need this problem to be solved, please.
 [2001-12-11 13:58 UTC] derick@php.net
Actually, this is not a bug. The query still executes ok, but the result is different then you want. You can check with the sybase_affected_rows function if it is deleted or not:
www.php.net/sybase_affected_rows

Derick
 [2001-12-12 06:24 UTC] mheumann at sei dot cl
I'm not sure if I can agree with you on that. If I don't use the @ sign, sybase_query displays a warning of the type

Warning: Sybase message: Children still exist in 'table1'. Cannot delete parent 'table2'. (severity 16) in /path/to/my/script.php on line 1432

Doesn't that mean that sybase_query didn't return ok? 

Besides, according to the documentation, the function sybase_affected_rows is only implemented for the ct library, not dblib. Unfortunately, we haven't been able to get sybase-ct to work for us, even though we tried all the hints we could get.

 [2001-12-12 06:41 UTC] derick@php.net
oh, ok, then it is not bogus.
 [2002-01-07 02:34 UTC] lobbin@php.net
Does this problem still exist on 4.1.1?
 [2002-01-08 17:02 UTC] mheumann at sei dot cl
I just tried it on php 4.1.1 and the problem still exists.

 [2002-01-08 17:08 UTC] mheumann at sei dot cl
Let me try to give you some more information to reproduce the problem.
1. create two tables in Sybase where one of them should include a field that represents a reference to the ID field of the other.
2. create a delete trigger on the parent table of that reference, so that deletion is prevented if a reference to the deleted record exists in the other table.
3. insert data into the tables making sure a record references another in the second table
4. use sybase_query to delete a record in the parent table. PHP will display a warning message of the type "Warning: Sybase message: Children still exist in "table1". Cannot delete parent "table2". (severity 16) in /path/to/file.php on line x".  sybase_query will return 1.
5. use sybase_query to delete a record that has no reference to the parent table. It will be deleted and sybase_query also returns 1.


 [2003-05-18 13:00 UTC] sniper@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php4-STABLE-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php4-win32-STABLE-latest.zip

And use --with-sybase-ct configure option instead.


 [2003-05-20 11:55 UTC] mheumann at sei dot cl
Hi,
the problem persists even in the current snapshot 4.3.2RC4-dev.  Since I've got the feeling, this issue might not be fully understood, I've written a complete test script that will demonstrate this.  Meanwhile, we have used --with-sybase-ct and are running Sybase 12.5.

Here's the script (please copy and paste it and edit the database info at the beginning. The required tables and triggers are created at the start and deleted at the end.):

<html>
<head>
<title>PHP Bug Test script</title>
</head>
<body>
<?php
  $iConnId = sybase_connect("sybaseserver", "sa", "sa_pwd" );
  if($iConnId) {
    if(!sybase_select_db("dbname",$iConnId)) {
      echo ("ERROR 2: Sybase select DB failed.");
      exit;
    }
  }
  else {
    echo ("ERROR 1: Sybase connect failed.");
    exit;
  }

  sybase_query( "if exists (select 1".
                "         from  sysobjects".
                "         where  id = object_id('Test_Users')".
                "         and    type = 'U')".
                "drop table Test_Users", $iConnId ) or die( "Error in drop table Test_Users." );

  sybase_query( "if exists (select 1".
                "         from  sysobjects".
                "         where  id = object_id('Test_Reference')".
                "         and    type = 'U')".
                "drop table Test_Reference", $iConnId ) or die( "Error in drop table Test_Reference." );

  sybase_query( "create table Test_Users ".
                "(".
                "    UserId             numeric                identity,".
                "    Username           varchar(50)            null    ,".
                "    Password           varchar(255)           null    ,".
                "    constraint PK_TESTUSERS primary key (UserId)".
                ")", $iConnId ) or die( "Error in create table Test_Users." );
  echo "Table Test_Users created.<br>";

  sybase_query( "create table Test_Reference ".
                "(".
                "    RefId              numeric                not null,".
                "    UserId             numeric                not null,".
                "    DummyData          varchar(255)           null,".
                "    constraint PK_TESTREF primary key (RefId, UserId)".
                ")", $iConnId ) or die( "Error in create table Test_Reference." );
  echo "Table Test_Reference created.<br>";

  sybase_query( "if exists (select 1".
                "    from  sysobjects".
                "            where id = object_id('td_test_users')".
                "            and   type = 'TR')".
                "   drop trigger td_test_users", $iConnId ) or die( "Error in drop trigger td_test_users." );

  sybase_query( "create trigger td_test_users on Test_Users for delete as \n".
                "  begin\n".
                "    declare\n".
                "       @numrows  int,\n".
                "       @errno    int,\n".
                "       @errmsg   varchar(255)\n".
                "\n".
                "    select  @numrows = @@rowcount\n".
                "    if @numrows = 0\n".
                "       return\n".
                "    /*  Cannot delete parent Test_Users if children still exist in Test_Reference  */\n".
                "    if exists (select 1\n".
                "               from   Test_Reference t2, deleted t1\n".
                "               where  t2.UserId = t1.UserId)\n".
                "       begin\n".
                "          select @errno  = 30006,\n".
                "                 @errmsg = 'Children still exist in Test_Reference. Cannot delete parent Test_Users.'\n".
                "          goto error\n".
                "       end\n".
                "\n".
                "    return\n".
                "\n".
                "/*  Errors handling  */\n".
                "error:\n".
                "    raiserror @errno @errmsg\n".
                "    rollback  transaction\n".
                "end\n", $iConnId ) or die( "Error in create trigger td_test_users." );
  echo "Trigger td_test_users created.<br>";
  
  echo "Insert new user test_noref:<br>";
  $Result = sybase_query( "insert into Test_Users (Username,Password)".
                          " values ('test_noref','test_noref')", $iConnId ) or die( "Error inserting test_noref." );
  if( $Result )
    echo "...Success - ";
    

  $Result = sybase_query( "SELECT LastId=@@identity", $iConnId );
  $Row = sybase_fetch_array( $Result );
  $iLastId = $Row["LastId"];
  sybase_free_result( $Result );
  echo "ID is $iLastId<br>";
  
  echo "Insert new user test_with_ref:<br>";
  $Result = sybase_query( "insert into Test_Users (Username,Password)".
                          " values ('test_with_ref','test_with_ref')", $iConnId ) or die( "Error inserting test_with_ref." );
  if( $Result )
    echo "...Success - ";
    

  $Result = sybase_query( "SELECT LastId=@@identity", $iConnId );
  $Row = sybase_fetch_array( $Result );
  $iLastId2 = $Row["LastId"];
  sybase_free_result( $Result );
  echo "ID is $iLastId2<br>";

  echo "Insert reference for user test_with_ref:<br>";
  $Result = sybase_query( "insert into Test_Reference (RefId, UserId, DummyData)".
                          " values (1,$iLastId2,'This is the reference')", $iConnId ) or die( "Error inserting reference." );
  if( $Result )
    echo "...Success.<br><br>";
  
  $Result = sybase_query( "select UserId, Username, Password from Test_Users", $iConnId ) or die( "Error selecting from Test_Users." );
  echo "<i>Test_Users:</i><br>";
  echo "<table border=1><tr><th>UserId</th><th>Username</th><th>Password</th></tr>";
  while( $Row = sybase_fetch_array( $Result ) ) {
    echo "<tr><td>".$Row["UserId"]."</td>";
    echo "<td>".$Row["Username"]."</td>";
    echo "<td>".$Row["Password"]."</td></tr>";
  }
  echo "</table>";
  sybase_free_result( $Result );

  $Result = sybase_query( "select RefId, UserId, DummyData from Test_Reference", $iConnId ) or die( "Error selecting from Test_Reference." );
  echo "<br><i>Test_Reference:</i><br>";
  echo "<table border=1><tr><th>RefId</th><th>UserId</th><th>DummyData</th></tr>";
  while( $Row = sybase_fetch_array( $Result ) ) {
    echo "<tr><td>".$Row["RefId"]."</td>";
    echo "<td>".$Row["UserId"]."</td>";
    echo "<td>".$Row["DummyData"]."</td></tr>";
  }
  echo "</table>";
  sybase_free_result( $Result );
  
  echo "<br><br>Delete User test_noref:<br>";
  $Result1 = sybase_query( "delete from Test_Users where UserId=$iLastId", $iConnId ) or die( "Error deleting test_noref." );
  $Result = sybase_query( "select UserId, Username, Password from Test_Users", $iConnId ) or die( "Error selecting from Test_Users." );
  echo "<i>Test_Users:</i><br>";
  echo "<table border=1><tr><th>UserId</th><th>Username</th><th>Password</th></tr>";
  while( $Row = sybase_fetch_array( $Result ) ) {
    echo "<tr><td>".$Row["UserId"]."</td>";
    echo "<td>".$Row["Username"]."</td>";
    echo "<td>".$Row["Password"]."</td></tr>";
  }
  echo "</table>";
  sybase_free_result( $Result );
  echo "<b><i><u>Result: $Result1</u></i></b><br><br>";
  
  echo "Delete User test_with_ref:<br>";
  $Result2 = sybase_query( "delete from Test_Users where UserId=$iLastId2", $iConnId ) or die( "Error deleting test_with_ref." );
  $Result = sybase_query( "select UserId, Username, Password from Test_Users", $iConnId ) or die( "Error selecting from Test_Users." );
  echo "<i>Test_Users:</i><br>";
  echo "<table border=1><tr><th>UserId</th><th>Username</th><th>Password</th></tr>";
  while( $Row = sybase_fetch_array( $Result ) ) {
    echo "<tr><td>".$Row["UserId"]."</td>";
    echo "<td>".$Row["Username"]."</td>";
    echo "<td>".$Row["Password"]."</td></tr>";
  }
  echo "</table>";
  sybase_free_result( $Result );
  echo "<b><i><u>Result: $Result2</u></i></b><br><br>";
  
  if( $Result1 == $Result2 )
    echo "<b>Both results are the same, Test NOT PASSED.</b><br>Query failed, so sybase_query should return other than 1.";
  else
    echo "<b>Results are different, Test PASSED.</b><br>";
  
  sybase_query( "drop trigger td_test_users", $iConnId ) or die( "Error dropping trigger." );
  sybase_query( "drop table Test_Users", $iConnId ) or die( "Error dropping table Test_Users." );
  sybase_query( "drop table Test_Reference", $iConnId ) or die( "Error dropping table Test_Reference." );
  
  sybase_close( $iConnId );
?>
</body>
</html>


Hope this helps.
Greetings,
Michael.
 [2010-12-29 10:46 UTC] jani@php.net
-Package: Feature/Change Request +Package: Sybase-ct (ctlib) related
 [2013-08-06 07:59 UTC] yohgaki@php.net
-Status: Open +Status: Feedback
 [2013-08-06 07:59 UTC] yohgaki@php.net
Please try using this snapshot:

  http://snaps.php.net/php5.4-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/


 [2013-10-15 11:54 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 14 13:01:26 2024 UTC