#14354 [Fbk->Opn]: sybase_query returns 1 regardless of delete success

ID: 14354 User updated by: mheumann at sei dot cl Reported By: mheumann at sei dot cl -Status: Feedback +Status: Open Bug Type: Sybase (dblib) related -Operating System: Linux Mandrake 8 +Operating System: Linux Suse 8.1 -PHP Version: 4.1.1 +PHP Version: 4.3.2RC4-dev New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [2003-05-18 13:00:26] 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. ------------------------------------------------------------------------ [2002-01-08 17:08:45] 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. ------------------------------------------------------------------------ [2002-01-08 17:02:43] mheumann at sei dot cl I just tried it on php 4.1.1 and the problem still exists. ------------------------------------------------------------------------ [2002-01-07 02:34:06] lobbin@php.net Does this problem still exist on 4.1.1? ------------------------------------------------------------------------ [2001-12-12 06:41:44] derick@php.net oh, ok, then it is not bogus. ------------------------------------------------------------------------ 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/14354 -- Edit this bug report at http://bugs.php.net/?id=14354&edit=1