Page 1 of 1

seriously quirky SELECT and DELETE on same table

Posted: Fri Feb 21, 2003 5:19 am
by Popcorn
Hi all !,

Man, I need some help coz I seriously don't get this ...

OK, the following statements make it easy for you to try the problem for yourself:

Use these statements to build and populate the table:

Code: Select all

CREATE TABLE test (nom nvarchar(20), val nvarchar(20));
DELETE test;
INSERT test VALUES ('bob', 'delete_me_1');
INSERT test VALUES ('bob', 'delete_me_2');
INSERT test VALUES ('bob', 'delete_me_3');
INSERT test VALUES ('bob', 'delete_me_4');
INSERT test VALUES ('bob', 'delete_me_5');
INSERT test VALUES ('bob', 'delete_me_6');
INSERT test VALUES ('bob', 'delete_me_7');
INSERT test VALUES ('bob', 'delete_me_8');
INSERT test VALUES ('bob', 'delete_me_9');
Then get a page to run this (with $my_resource_ODBCconnectionID as a connection to your db):

Code: Select all

//$sqlresultid = odbc_exec($my_resource_ODBCconnectionID, "SELECT * FROM test");
$sqlresultid = odbc_exec($my_resource_ODBCconnectionID, "SELECT * FROM test ORDER BY val");
$i = 1;
while (odbc_fetch_into($sqlresultid, $i++, $row)) {
	if (odbc_exec($my_resource_ODBCconnectionID, "DELETE test WHERE nom = '$rowї0]' AND val = '$rowї1]'")) {
		print "deleted row nom:$row&#1111;0] val:$row&#1111;1].<br />";
	&#125;
&#125;
I got this:

deleted row nom:bob val:delete_me_1
deleted row nom:bob val:delete_me_2
deleted row nom:bob val:delete_me_3
deleted row nom:bob val:delete_me_4
deleted row nom:bob val:delete_me_5
deleted row nom:bob val:delete_me_6
deleted row nom:bob val:delete_me_7
deleted row nom:bob val:delete_me_8
deleted row nom:bob val:delete_me_9

which seems fine .. woohoo ! ;)




Then run the same code but comment out the second line and use the first instead (WITHOUT the ORDER BY clause). I dunno about you but I got this !!

deleted row nom:bob val:delete_me_1
deleted row nom:bob val:delete_me_3
deleted row nom:bob val:delete_me_5
deleted row nom:bob val:delete_me_7
deleted row nom:bob val:delete_me_9



What the #%^@#%^@# !?!?!

Is the ODBC result identifier returned by odbc_exec() not fully separated from the table it runs on ??

I am running 4.0.6 on a windows machine with MSSQLServer 2000.

Dying to find out why this is ... bit worried it is part of a larger problem.

Thanks.

Posted: Fri Feb 21, 2003 7:22 am
by Stoker
You are using a stone-age PHP versiom update it to at least 4.2
then try something like this for an easier debug:

Code: Select all

while (odbc_fetch_into($sqlresultid, $i++, $row)) &#123;
   echo 'Debug: i is '.$i.'<br>';
   if (odbc_exec($my_resource_ODBCconnectionID, "DELETE test WHERE nom = '".$row&#1111;0]."' AND val = '".$row&#1111;1]."'")) &#123;
      echo 'deleted row nom:'.$row&#1111;0].' val:'.$row&#1111;1].'.<br />';
   &#125; 
  else echo 'Query failed: '.odbc_errormsg().'<br/>';
&#125;

happens in new version ?

Posted: Fri Feb 21, 2003 8:02 am
by Popcorn
Yeh, sorry Stoker, I should've said that I pared down the code to it's minimum, I had shed loads of debug statements while testing it. All to no avail.

So, I assume you have a more recent version, do you get this occuring ?