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

Hi, Im trying to grab results from a database that returns multiple resultsets, with DBI.

Has anyone done this before?

essentially i want a array of results $sth->fetchrow_hashrefs()

the data im working with is:

Total results < -- need this in a variable
then the results < -- need this as a hashref

We need to do it this way to handle paging of results.
Database is SQL Server, freetds and DBI are the interfaces.

Replies are listed 'Best First'.
Re: Multiple Results Sets with DBI
by mpeppler (Vicar) on Aug 21, 2003 at 20:13 UTC
    First of all I'll assume that you are using DBD::Sybase along with FreeTDS. If that's the case, then multiple results are returned like this: 
    do {
    while($data = $sth->fetchrow_hashref()) {
        .... process $data ....
    }
} while($sth->{syb_more_results});

    [download]
    My guess is that you are issuing something like this: 
    select count(*) from table where ...
select ... from table where ...

    [download]
    In that case the first time through the do/while loop you'll get the output from the COUNT(), and the second time you'll get each of the rows from your SELECT.

    Michael

[reply]
[d/l]
[select]
      i am actually calling a stored procedure on the SQL server that returns the count first, then the recordset containing the data
[reply]
        OK - that actually makes no difference. The result sets that you're going to get are the same, with the addition of the return status from the stored proc, which should be easy to check.

        Michael

[reply]

Back to Seekers of Perl Wisdom