Cursors and fetch 1

[neilph]

I can use cursors, fetching and loops fine in T-SQL but am failing hopelessly in MySQL 4.1. I want to fetch the email field from a table (stepping through the rows) and concatenate them with a comma after each one to build up a 'mail-to' string. I just get the ambiguous 'check your documentation' error message. I want to do it straight in SQL. Never thought I would be praising Books On Line but it does usually link everything together; whereas alot of MySQl references (inc. at mysql.com) are broken up into their component parts. I'd like a fullsome reference if one exists. Ta for any helpful pointers.

 

[sleipnir214]

To the best of my knowledge, cursors are not supported in MySQL until version 5.x.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[neilph]

Thanks for the answer to my consternation.

Do you have any ideas about how I would build this string of emails without cursors? I have some vague recollection of being able to use functions but I am may be confusing things with T-SQL again (apologies for swearing). I could always import the data to V5.x. on my local machine but if there is another way?

Cheers
Neil

 

[TonyGroves]

The normal way to process a set of records is to send your query to the server, wait for the server to return the complete result set, and then have your application process the records at its leisure. That is normally far more efficient than having the server store the result set, and return each one on demand.

However, MySQL does have a mechanism for processing tables a record at a time, by using HANDLER statements. See

http://dev.mysql.com/doc/mysql/en/handler.html

for details.

You might also be interested in the GROUP_CONCAT function, which returns a comma-separated string containing all the values in the specified column. For example:[tt]
SELECT groupno,GROUP_CONCAT(email)
FROM tbl
GROUP BY groupno
might return:[tt]
1 as@sdf.dsf,tyik@ert.gfh,yhsjy@ery.ty
2 dsryoi@r6w.rts
3 w46yb3@srth.w5,w4vq5wey@seth.rt
[/tt]