|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[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. PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Thu Dec 26 14:01:30 2024 UTC |
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.