- Mar 18, 2003
- 5,513
- 0
- 0
<rant>
So I was reading an article in a linux magazine about the new mysql functionality and I was appalled by the ridiculous syntax they expect you to use for cursors. I wrote a short demo, just to point out my gripes.
So a cursor is a really basic thing. You create it with a SELECT statement, and it starts as a pointer to the first result. You can advance it to the next result with a FETCH statement and so on, till you've iterated over all the rows. You can only advance one row at a time and you can't move backwards. So basically there is only one way to use a cursor and the syntax should be very simple.
The attached code operates on a table called foo with one column, named bar, containing an integer. It iterates through the table to find the biggest integer (yeah, I know that's what indexes are for, this is just an example...).
First off, the loop syntax is clumsy (why do I have to name it?). It's essentially an infinite loop that must be broken manually with a LEAVE statement. That means that the FETCH statement takes an un-needed line. It also means that I have to manually OPEN and CLOSE the cursor when it's obvious that's where those statements need to go.
The dumbest part is that the loop has no knowledge of when the cursor runs off the end of the results. To detect this, you have to declare a new variable (finished), tell mysql to change it when we run off the end (line 18, but you don't even specify which cursor; that'll get really messy when you have multiple cursors open at the same time) and then put a whole IF block inside the loop to exit manually.
Below the real example I've written what I think the syntax should be. All the necessary info is there for mysql to perform the needed operations like opening/closing the cursor, fetching the next value and detecting loop termination. Comparing the two, I see 10 lines of needless boilerplate code that you're forced to write.
I'm aware that mysql must have a certain internal data and function execution model that requires them to have things like this infinite loop and explicit OPEN and CLOSE statements and I'm not suggesting that they change that. But it would be trivial to add a layer into the parser to automatically expand the FOR IN statement to the more verbose version.
It's for reasons like this that I detest mysql. I can't figure out why they would think it's ok to leave things so unwieldy and messy for the developers that use their platform. I also can't figure out why everyone seems to love using it so much.
</rant> Now please, somebody point out to me that the article I read simply neglected to demonstrate the sane version of this code
So I was reading an article in a linux magazine about the new mysql functionality and I was appalled by the ridiculous syntax they expect you to use for cursors. I wrote a short demo, just to point out my gripes.
So a cursor is a really basic thing. You create it with a SELECT statement, and it starts as a pointer to the first result. You can advance it to the next result with a FETCH statement and so on, till you've iterated over all the rows. You can only advance one row at a time and you can't move backwards. So basically there is only one way to use a cursor and the syntax should be very simple.
The attached code operates on a table called foo with one column, named bar, containing an integer. It iterates through the table to find the biggest integer (yeah, I know that's what indexes are for, this is just an example...).
First off, the loop syntax is clumsy (why do I have to name it?). It's essentially an infinite loop that must be broken manually with a LEAVE statement. That means that the FETCH statement takes an un-needed line. It also means that I have to manually OPEN and CLOSE the cursor when it's obvious that's where those statements need to go.
The dumbest part is that the loop has no knowledge of when the cursor runs off the end of the results. To detect this, you have to declare a new variable (finished), tell mysql to change it when we run off the end (line 18, but you don't even specify which cursor; that'll get really messy when you have multiple cursors open at the same time) and then put a whole IF block inside the loop to exit manually.
Below the real example I've written what I think the syntax should be. All the necessary info is there for mysql to perform the needed operations like opening/closing the cursor, fetching the next value and detecting loop termination. Comparing the two, I see 10 lines of needless boilerplate code that you're forced to write.
I'm aware that mysql must have a certain internal data and function execution model that requires them to have things like this infinite loop and explicit OPEN and CLOSE statements and I'm not suggesting that they change that. But it would be trivial to add a layer into the parser to automatically expand the FOR IN statement to the more verbose version.
It's for reasons like this that I detest mysql. I can't figure out why they would think it's ok to leave things so unwieldy and messy for the developers that use their platform. I also can't figure out why everyone seems to love using it so much.
</rant> Now please, somebody point out to me that the article I read simply neglected to demonstrate the sane version of this code
Facebook
Twitter