List:MySQL and Perl« Previous MessageNext Message »
From:Jon and Jenny Steltenpohl Date:April 3 2000 3:04am
Subject:Newbie question... Fetch performance...
View as plain text  
Hi All,

It's Jon the newbie here.  I'm trying to teach myself all of the 
stuff I need to set up a basic weblog with Perl and MySQL, and in my 
self education, I'm starting to question my concept of how DBI::MySQL 
works.

Basically, I'm not sure how to use the DBI methods to retrieve 
records out of a selected query dataset.  I'm sitting here with Perl 
in a Nutshell's database chapter, section 20.5 of the MySQL manual 
("MySQL Perl API"), and Jochen's summary at the Perl/DBI site in 
front of me.  And, I've got fetch, fetchall_arrayref, and the other 
fetch functions taunting me with chants of "newbie, newbie, newbie".

I understand how to prepare an SQL query and I  understand that once 
you've prepared the statement, you can go ahead and execute it. 
(Jochen, your list of supported functions answers a lot of my 
questions on that end.  Very well written, thank you.)

But, I think I'm projecting Perl like hash functionality onto the 
fetch functions (because I read the chapters about DBM hashes before 
I read the DBI sections).  I just want to make sure I'm understanding 
properly.

Okay, the way I read it with DBI, you're supposed to do all of your 
selection, sorting, and "kitchen sink" criteria in your prepared SQL 
statement.  Then, "fetch" merely gives you a row by row method of 
returning that data?

But, what if you want to move and maneuver in a returned record set, 
is there any way to do this without a new query or dumping it all 
into a hash?

For instance, say I have a basic two table relational structure where 
StoryID is the key...

Story (StoryID, StoryBlob, StoryAuthorID)
Comment (CommentID, StoryID, CommentBlob, CommentAuthorID)

And, say I want to pull up all of the Comments matching a certain 
StoryID.  Simple enough SQL statement.  Okay, now, if I want to 
display the first 10 results (or 21 to 30), I see that MySQL supports 
a "limit" statement.

But, say ,theoretically, I want to pull 3 random rows from that 
query?  Is there any way for me to use the rows in any order I want? 
I mean, I can grok that I could put all 10 rows into a hash and then 
use perl to manipulate it, but is there any way to tell perl to pick 
row 5, 2 and 8 (in that order) from an executed query of 10 rows?  Or 
does fetch simply offer me a row by row method of going through the 
dataset where each time I get a new row, fetch is primed for row+1?

In other words, are there ways to execute new queries criteria on top 
of your current query or to use Perl like hash manipulations?  Or do 
you simply need to prepare a new statement and execute the new 
statement that includes the current one?  If I want to pull a 
specific Comment out by CommentID, would I just go ahead and do a new 
query, or is there some way to isolate just that one.  Or, say I 
wanted to select only certain Comment Author ID's from my current 
query... again, can I use Perl hash functions, or is the proper 
method to just do a new query?  I assume I can dump all of a query 
into a hash, but that this would have serious memory/performance 
implications if I get into the hundreds of records.

Also, if I am just using a fetch method and I get to the end of the 
query, does the next fetch command cause an error or does it restart 
back to the first row returned?  Do you just execute a new query at 
that point?

Sorry if some of these questions are non-sensical or obvious.  I am a 
newbie, so please be gentle.  I have a sneaking suspicion that they 
are, but like I said, the DBM docs show hash's, but the DBI docs just 
show "fetchs", and I want to make sure I'm not missing something.

Thanks much,

Jon
Thread
$dbh->{'Name'} undefinedDavid Jacobs24 Mar
  • Re: $dbh->{'Name'} undefinedJochen Wiedmann3 Apr
    • Newbie question... Fetch performance...Jon and Jenny Steltenpohl3 Apr
      • RE: Newbie question... Fetch performance...indrek siitan3 Apr
        • Re: Newbie question... Fetch performance...Mark Lybrand3 Apr
        • RE: Newbie question... Fetch performance...Jon and Jenny Steltenpohl3 Apr
          • RE: Newbie question... Fetch performance...indrek siitan3 Apr