DBI - retrieving a specific row

[S_Shrum]

I am going through the O'Reilly "Perl DBI" book and cannot find a section that addresses calling a specific row.

Example: I have a recordset of 10 records but I only want record 4.

What is the direct call to the DBI to return row 4 only? I don't want to set up a loop that monitors for a specific number. I want to call the record directly. This way I can place the call in a loop and increment the number for doing data slices.

Any ideas would be greatly apprechiated. TIA

Sean Shrum
[e-mail]
[e-mail]

[Biker]

This is not really a Perl problem. It's more a DB question.

You need to have a unique identifier (often: a primary key) and then formulate your query to select only the row with this unique identifier.

Never select everything from your DB to search for the data in your application.
Select only what you need from the DB and use it in your application.

"Livet är hårt" sa bonden.
"Grymt" sa grisen...

[hakkr]

You can fetch all the records as a 2D array ref and then directly access record 4. Each field of record 4 can then be accessed using the elements of the 2nd array.

#get array ref
my $results=$sth->fetchall_arrayref;
#rec 4 is array element 3 due to the 0 element
#my $field=$result->[row][fieldno]
my $field1=$results->[3][0];
my $field2=$results->[3][1]; 
my $field3=$results->[3][2];

Alternatively you fetch an array or array ref row by row with

my @results=$sth->fetchrow_array; 
my $arrayref=$sth->fetchrow_arrayref;

You can't retrieve record 4 only without a loop unless your sql statement returns only one record. You can usually specify the rows to return with a LIMIT clause

[beebware]

If it's a large database you are handling (ie lots of columns), that's (the 2D array method) is not going to be the most memory friendly method - but the loop and LIMIT is worth considering.

[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

[Trimbach]

If all you want is a specific row from the database you can do something like this:

my $sth=$dbh->prepare("SELECT * FROM my_table WHERE primary_key = ?");

$sth->execute(4);
    
my @record = $sth->fetchrow();

It's really no big deal, and doesn't require any freaky LIMIT or anything like that. Of course, this assumes that your SELECT will only return one row (which it will, if you're SELECTing from a Primary Key).

Update:Whoops. Misunderstood what the poster wanted. Ah well... hakkr's post has what you want.

Gary Blackburn
Trained Killer

[S_Shrum]

Let's take it a step further.

Let's say I have a group of records and I want to do a data slice. What I mean by this is say I have 50 records but I only want to display 10 records at a time. Say then I indicate I want slice 3; I would be viewing records 21-30 (1=1-10, 2=11-20, ...).

Is it possible to make a query call to the DBI that would allow for this sort of functionality? LIMIT seemed like the way to go but it appears that LIMIT is not supported in SQL::Statement.

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