SQL92[EXPERTS]

Hey /prog/

Is it legal to modify a table while there's a read cursor open on it? Is it defined behavior?

For example, in some pseudo-code:

result = sql("SELECT * FROM faggot_table WHERE faggot_type=5;");
for each (result) {
  if (whatever) sql("DELETE FROM faggot_table WHERE faggot_id=3;");
}

The reason I ask is because I tested with several database engines from the ENTERPRISE to the _toy, and it works on some but errors out in others. So I can't use it in the general case.

However I'd like to know if it should work and some databases suck, if it shouldn't but is supported on some, or if it mustn't and it works by chance on some.

(It could work in some because they prefetch results, and maybe if they prefetch everything the cursor is already closed the moment you read the first row)

Is it legal to modify a table
No, and you can go to jail for that. So you won't pass Go and won't collect $200

　　　　　　　　　　∧＿∧　　　／￣￣￣￣￣￣￣￣
　　　　　　　　　 （　´∀｀）　＜ If you modify the table, what will I sit on?
　　　　　　　　／　　　　|　　　 ＼＿＿＿＿＿＿＿＿
　　　　　　　/ 　　　 　 .|　　　　　
　　　　　　 / "⌒ヽ |.ｲ |
　　　＿＿ |　　　.ノ | || |＿＿
　 .　　　 ノく＿＿つ∪∪　　 ＼
　　　＿（（＿＿＿＿＿＿＿＿_＼
　　 ￣￣ヽつ￣￣￣￣￣￣ | |￣
　　　＿＿＿＿＿＿＿＿＿__| |
　　 ￣￣￣￣￣￣￣￣￣￣| |

>>3

An [aa] tag.

>>1
You know about cursors, so why do you want to do something stupid like that?

Legal or not you should almost never use a cursor like that. Just write the delete query differently.

>>5,6
Usually I'd agree with you, but what if the condition for deletion can't be evaluated in SQL? (remember: SQL is not touring-complete)

Random example, you suddenly want to delete all the rows whose faggot_prime is not, in fact, a prime number. You can't to that directly in SQL (or maybe you can, don't make me pick another example).

You can:

* select to a temp table, read the temp table row by row issuing deletes on the main table if the condition is met

* read row by row generating a list, then delete using that list (a bit better if you don't expect to delete too much)

* do it cowboy style such as in the example above, reading row by row and deleting immediately when the condition is met

I can confirm that some of the SQL servers where the last option works internally implement cursors by generating a temporary table, so they're doing the first option for you whether you like it or not.

>>7
So do it the first way then. Jesus.

>>7
You create a new table storing all prime numbers.

I was once (circa 2000) told to modify PostgreSQL to add a certain kind of functionality to triggers (this was before PL/pgSQL etc were supported.) It was not exactly the best solution, but I'll say this: the PostgreSQ

L code was very, very clean, especially for something of its size and complexity.

_{I managed to typo into the reply button.}

A single logical post may span

several physical posts

why are you even trying to do use your database to do this anyway? why not generate your data using tools that were designed for such purposes and then stick the data in the database at the end?

/prog/ at its finest: nobody is able or willing to answer the actual question.

The question is whether the behavior is valid in general, not how to overcome it, neither how to design the system such as to make the problem irrelevant.

"Can you cast a float as an integer in C? I have this weird compiler where it doesn't work"

"Well you shouldn't be doing that in the first place but you can always sprintf it and then atoi it back"

The default cursor behavior is to lock the row currently fetched so you can update it. Due to this, if you try to delete that specific row without utilizing the cursor it should fail. To avoid this you can set your cursor to READ_ONLY. If you would like to discuss different engines' behavior please mention which ones.

>>15
you can always sprintf it and then atoi it back
Leah Culver quality!

>>15
I'm sure that many of us are able, but trollan is much more interesting.