http://qs321.pair.com?node_id=276228

chuleto1 has asked for the wisdom of the Perl Monks concerning the following question:

Fellow Monks,
I would like to know how to have the same functionality using DBI. Any suggestions?
ASP Code
Do while not rst.EOF rst1.MoveFirst Do while not rst1.EOF #blah blah Loop rst.MoveNext Loop
Perl Code
while ( @row = $sth3->fetchrow ) { #GO TO FIRST RECORD OF $sth2 while ( @row = $sth2->fetchrow ) { #blah blah } }
Many Thanks

Replies are listed 'Best First'.
Re: Going to first record on a record set in DBI
by gmax (Abbot) on Jul 21, 2003 at 12:14 UTC

    Using DBI, you access the recordset in two ways:

    • Either you use fetchrow_* methods with a loop, and then you get one record at the time. In this case, the first one in your loop is the first in the data set.
    • ... or you access all the records at once, with fetchall_*, selectcol_*, or selectall_* methods. In this case the result is a bi-dimensional array reference, and the first record is the element of the array with index 0.

    A word of caution, though. If you are referring to the first physical record in a table, then be aware that there is no such a thing in a relational database. You can have a record where a specific column (e.g. the primary key) is "1", but it does not mean that this is the "first" record, whatever you mean by that.

    The data set that you are "navigating" with the DBI is the result of a query, which can be influenced by WHERE and ORDER BY clauses. This data set is not guaranteed to have any relation whatsoever with the physical order of the records in your tables. There are some DBMS that implement a "record number" or similar attribute, but it is not standard, not portable, not relational.

    See also the discussion following this node for some practical examples.

    And, I almost forgot. You are using a "fetchrow" record. It does not exist. It is not a documented method. The methods at your disposal are "fetchrow_array," "fetchrow_arrayref," or "fetchrow_hashref."

    Update
    Thanks to cfreak, who points out that "fetchrow" exists and it is an alias for "fetchrow_array", even though it is not documented.
    I acknowledge it, but I still think it should be better to use one of the named methods I listed above, for two reasons:

    • there is also a "fetch" method that is alias for "fetchrow"arrayref." Even though I know that, I use the explicit method, to know for sure what the return value is. I can't tell at first glance what "fetch" and "fetchrow" are returning. Therefore, in the principle of defensive programming, I stick to the docs.
    • The DBI is continuously evolving. Even documented features of DBI can sometimes change. One more reason for being conservative.

    See Reading from a database in our Tutorials for more information.

    _ _ _ _ (_|| | |(_|>< _|
      Indeed. In a relational way of thinking, it only make sense to talk about the "first N rows", if you have a query with an ORDER BT clause. If you want the "first row" given some ordering, you can often do that with a WHERE clause. Say:
      SELECT name, gender FROM people ORDER BY age LIMIT 1

      can also be written as

      SELECT name, gender FROM people WHERE age = MIN (age)

      That is, you ask for the record with minimum age.

      Abigail

      And, I almost forgot. You are using a "fetchrow" record. It does not exist. The methods at your disposal are "fetchrow_array," "fetchrow_arrayref," or "fetchrow_hashref."

      Not true. The fetchrow() method does indeed exist and works quite well. It returns an array of the current row like fetchrow_array()

      Granted, the DBI documentation should be updated to reflect that.

      Lobster Aliens Are attacking the world!
Re: Going to first record on a record set in DBI
by edan (Curate) on Jul 21, 2003 at 12:58 UTC

    Not to denigrate the previous replies to this question, and I might be just plain wrong, but it seems to me that the question being asked hasn't been answered. I think the OP wants to loop over each row in one record set for each row in another record set.

    I would do the following to accomplish this, as illustrated by the following UNTESTED code snippet:

    # first, get all the results for the record set used in inner loop my $record_set_1 = $sth1->fetchall_arrayref; # loop over second record set while (my @row = $sth2->fetchrow_array) { foreach my $row_from_record_set_1 (@$record_set_1) { # blah blah } }

    Of course, if the first record set is very large, you'll run into memory problems. If that's the case, then you might be able to use cursors, depending on whether your DB supports it...

    HTH

    --
    3dan
      The other option, if the first records set is very large, is to just repeat the query.