Re: DBI - retrieving a specific row

[beebware]

I think this will be DB specific, but I know on MySQL you can use select * from table LIMIT 3,1 just to get the fourth result.

Personally, if you are going to be accessing the data sequentially (ie first row, second row etc), you may as well do it in a loop as it will be a lot more effecient on the database servers cache system (it should 'anticipate' that you will request the next record, but if you then start a new query it may dump the cached data).

[S_Shrum]

Ahhh...LIMIT sound like just what I need.

Makes sense that it wouldn't be in the DBI book...need to get me the SQL book.

Thanx for the suggestions all.

Sean

[beebware]

I would recommend you getting SQL In A Nutshell (O'Reilly) - I've only had it 3 months and it's alreeady quite thumbed and bent. Page 149 gives SELECT...LIMIT as a MySQL specific and PostgreSQL specific functions, the syntax is:
MySQL: SELECT * FROM table_name LIMIT (starting_row-1),number_of_rows
PostgreSQL: SELECT * FROM table_name LIMIT number_of_rows,(starting_row-1)
Don't get the two different parameter orders mixed up!

If you aren't using either of these databases (have a look at this comparision table between them if need be), use might be able to use stored procedures to do the job. For a database-independent way, you'll be best having a function within your script in which you loop round to get the data you require.

[S_Shrum]

Ack! It appears that SQL::Statement does not support a LIMIT method (or at least as far as I can tell; it fails in the script call and I can't find any references to LIMIT in the SQL::Statement code. Any other ideas?

======================
Sean Shrum
http://www.shrum.net