472,374 Members | 1,156 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

resultsets - deleting from while processing safe?

Is it safe to do what is below? deleting from a resultset while you are
processing it? I don't know how dynamic the SQL database is. I assume you
get a cursor to live dataset. Even if it is a cursor as opposed to a table,
will the cursor's integrity be ruined if the current record is deleted?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
mysql_query("DELETE FROM table1 WHERE id1=5 AND id2=$row2[id2]", $link);
}
mysql_free_result($q2);


Mar 18 '06 #1
4 1567
Jim Michaels wrote:
Is it safe to do what is below? deleting from a resultset while you are
processing it? I don't know how dynamic the SQL database is. I assume you
get a cursor to live dataset. Even if it is a cursor as opposed to a table,
will the cursor's integrity be ruined if the current record is deleted?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
mysql_query("DELETE FROM table1 WHERE id1=5 AND id2=$row2[id2]", $link);
}
mysql_free_result($q2);


AFAIK, it'll be totally fine. But isn't your code equivalent to this?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
}
mysql_free_result($q2);
mysql_query("DELETE FROM table1 WHERE id1=5", $link);

Cheers,
Nicholas Sherlock

--
http://www.sherlocksoftware.org
Mar 18 '06 #2
Nicholas Sherlock wrote:
Jim Michaels wrote:
Is it safe to do what is below? deleting from a resultset while you
are processing it? I don't know how dynamic the SQL database is. I
assume you get a cursor to live dataset. Even if it is a cursor as
opposed to a table, will the cursor's integrity be ruined if the
current record is deleted?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
mysql_query("DELETE FROM table1 WHERE id1=5 AND id2=$row2[id2]",
$link);
}
mysql_free_result($q2);

AFAIK, it'll be totally fine. But isn't your code equivalent to this?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
}
mysql_free_result($q2);
mysql_query("DELETE FROM table1 WHERE id1=5", $link);

Cheers,
Nicholas Sherlock


Jim,

I'm not sure if it's safe or not - typically I've tried to stay away
from this construct. And I haven't been able to find any doc talking
about it one way or the other.

If I were doing it, I'd save the id's in an array and delete them after
I'm done with the result set. But that's just me.

And Nicholas, no, it's not the same. Jim is deleting from the result
set (potentially multiple rows based on other selection criteria).
You're deleting all rows with id1=5, which may or may not be what he
wants (he may only want to delete some of the rows, for instance).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Mar 18 '06 #3

Jim Michaels wrote:
Is it safe to do what is below? deleting from a resultset while you are
processing it? I don't know how dynamic the SQL database is. I assume you
get a cursor to live dataset. Even if it is a cursor as opposed to a table,
will the cursor's integrity be ruined if the current record is deleted?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
mysql_query("DELETE FROM table1 WHERE id1=5 AND id2=$row2[id2]", $link);
}
mysql_free_result($q2);


With MySQL I believe it's safe, as the client buffers the whole result
set. It's more efficient though to save the ids into an array, then
delete all the applicable rows in a single operation with a "id2 IN (
.... )" condition.

Mar 18 '06 #4

"Chung Leong" <ch***********@hotmail.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...

Jim Michaels wrote:
Is it safe to do what is below? deleting from a resultset while you are
processing it? I don't know how dynamic the SQL database is. I assume
you
get a cursor to live dataset. Even if it is a cursor as opposed to a
table,
will the cursor's integrity be ruined if the current record is deleted?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
mysql_query("DELETE FROM table1 WHERE id1=5 AND id2=$row2[id2]",
$link);
}
mysql_free_result($q2);


With MySQL I believe it's safe, as the client buffers the whole result
set. It's more efficient though to save the ids into an array, then
delete all the applicable rows in a single operation with a "id2 IN (
... )" condition.


thx for the tip. Hadn't thought of that. Makes sense.
Mar 19 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: fabio de francesco | last post by:
Hi what do you think of the following? Why are we permitted to do that? And why the C++ Library doesn't stop someone willing to perfom that assignement (*a = 20)? #include <iostream> ...
6
by: Tor Heigre | last post by:
Hello While testing our code on DB2 we have encountered a difference in the behaviour of DB2Driver (com.ibm.db2.jcc.DB2Driver with driverType= 4) compared to the drivers offered by Oracle 9i and...
1
by: Kevin | last post by:
Hi, How can I enumerate a linked list while being hable to delete any number of elements while enumerating ? I was using: struct st { ... struct st *prev;
6
by: Dan | last post by:
I've created a pocketpc app which has a startup form containing a listview. The form creates an object which in turn creates a System.Threading.Timer. It keeps track of the Timer state using a...
8
by: shandra | last post by:
I have a file I need to delete or truncate. I tried using the KILL command in VB6. I tried using the file.delete command in VB.net. I tried manually deleting, renaming, and copying over the...
0
by: Crazy Cat | last post by:
Hi, Using Visual Basic 2005 -- I have a stored procedure that returns multiple resultsets. I fill a datareader (SQLDataReader to be exact) with the results from a command object's ExecuteReader...
5
by: praveen | last post by:
Hi, I am using nested resultsets to execute queries on two different tables. The code structure is: stmt1 = conn.createStatement(); rs1 = stmt1.executeQuery(query1); while (rs1.next()) {...
10
by: =?Utf-8?B?UGV0ZXI=?= | last post by:
I'm using this coding to get 2 resultsets thru datareader and then load them into 2 datatables and bind the datatables to datagridviews. But sdrGrid.NextResult() is returning false for some...
0
by: Camfa | last post by:
Hi all, Iíve been messing around with the following question for 2 days now, and i still havenít find a fix. So i hope you can help me. Iíll quickly show you guys the situation: There are 2...
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
0
by: F22F35 | last post by:
I am a newbie to Access (most programming for that matter). I need help in creating an Access database that keeps the history of each user in a database. For example, a user might have lesson 1 sent...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.