Thread: problem with multiple result sets

ODBC driver version: 7.2.5
PostgreSQL database version: 7.3.2

I'm using the database and odbc driver which ship with SuSE 8.2
Professional, but the problem is the same with the PostgreSQL database
and driver on RedHat 9.

The problem occurs when accessing multiple result sets from a batch
query such as `select * from table1; select * from table2'. To read back
the result sets, I use a loop involving a call to SQLMoreResults() as
described in the ODBC docs. The basic outline of the loop is as follows
(with error checking on return values omitted for clarity):

do {
   /* check for the availability of a result set with SQLNumResultCols()
      and get the column titles from the current result set using
      SQLDescribeCol() */
   while (SQLFetch(hstmt) != SQL_NO_DATA_FOUND) {
     /* get the data from the current row using SQLGetData() */
   }
} while (SQLMoreResults(hstmt) == SQL_SUCCESS);

I guess that's the right way to do it; I mostly pilfered this code from
the odbctest program included with iODBC.

Using this code, I can access single result sets, or multiple results if
the results are just row counts all right, but when I try to read
multiple result sets from a query like the one above, it seems that
after SQLMoreResults() the cursor is not properly reset to the first row
in the next result set. Specifically, if the 1st query yields 8 rows and
the 2nd one 10 rows, then I get all rows from the first result set, but
only the last one (the 10th row) from the 2nd.

This happens with both the iODBC and unixODBC driver managers. I also
found the same behaviour when executing the batched query from the
odbctest program, so it is not specific to my application.

Is there something I'm doing wrong here? Any way to fix this?

Many thanks in advance,
Albert Graef

P.S.: As I'm not subscribed to the list it would be nice if you cc to my
mail address. Thanks.

--
Dr. Albert Gr"af
Email:  Dr.Graef@t-online.de, ag@muwiinfa.geschichte.uni-mainz.de
WWW:    http://www.musikwissenschaft.uni-mainz.de/~ag

> -----Original Message-----
> From: Albert Graef
>
> ODBC driver version: 7.2.5
> PostgreSQL database version: 7.3.2
>
> I'm using the database and odbc driver which ship with SuSE 8.2
> Professional, but the problem is the same with the PostgreSQL
> database
> and driver on RedHat 9.
>
> The problem occurs when accessing multiple result sets from a batch
> query such as `select * from table1; select * from table2'.
> To read back
> the result sets, I use a loop involving a call to SQLMoreResults() as
> described in the ODBC docs. The basic outline of the loop is
> as follows
> (with error checking on return values omitted for clarity):
>
> do {
>    /* check for the availability of a result set with
> SQLNumResultCols()
>       and get the column titles from the current result set using
>       SQLDescribeCol() */
>    while (SQLFetch(hstmt) != SQL_NO_DATA_FOUND) {
>      /* get the data from the current row using SQLGetData() */
>    }
> } while (SQLMoreResults(hstmt) == SQL_SUCCESS);
>
> I guess that's the right way to do it; I mostly pilfered this
> code from
> the odbctest program included with iODBC.
>
> Using this code, I can access single result sets, or multiple
> results if
> the results are just row counts all right, but when I try to read
> multiple result sets from a query like the one above, it seems that
> after SQLMoreResults() the cursor is not properly reset to
> the first row
> in the next result set. Specifically, if the 1st query yields
> 8 rows and
> the 2nd one 10 rows, then I get all rows from the first
> result set, but
> only the last one (the 10th row) from the 2nd.

Oh I see. I would try to fix it. However, I'm not sure how to
fix it in your *nix environment.

regards,
Hiroshi Inoue

> -----Original Message-----
> From: Albert Graef [mailto:Dr.Graef@t-online.de]
> Sent: Sunday, September 21, 2003 8:14 PM
> To: Hiroshi Inoue
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] problem with multiple result sets
>
>
> Hiroshi Inoue wrote:
> > Oh I see. I would try to fix it. However, I'm not sure how to
> > fix it in your *nix environment.
>
> Hi, thanks a lot for your reply. If the fix is in the driver,
> I'll just
> take the diffs and apply them to the driver sources (or download the
> updated driver sources) and recompile, that shouldn't be a
> problem. Or
> would I have to change the code of the application?

I can't make diff now, sorry.
In PGAPI_MoreResults() in results.c, please try to insert
the 2 lines between /* insert the following ... */ and /* insert end */.

RETCODE        SQL_API
PGAPI_MoreResults(
                  HSTMT hstmt)
{
    const char *func = "PGAPI_MoreResults";
    StatementClass    *stmt = (StatementClass *) hstmt;
    QResultClass    *res;

    mylog("%s: entering...\n", func);
    if (stmt && (res = SC_get_Curres(stmt)))
        SC_set_Curres(stmt, res->next);
    if (res = SC_get_Curres(stmt), res)
    {
        stmt->diag_row_count = res->recent_processed_row_count;
        /* insert the following 2 lines */
        stmt->rowset_start = -1;
        stmt->currTuple = -1;
        /* insert end */
         return SQL_SUCCESS;
    }
    return SQL_NO_DATA_FOUND;
}

Hiroshi Inoue wrote:
> Oh I see. I would try to fix it. However, I'm not sure how to
> fix it in your *nix environment.

Hi, thanks a lot for your reply. If the fix is in the driver, I'll just
take the diffs and apply them to the driver sources (or download the
updated driver sources) and recompile, that shouldn't be a problem. Or
would I have to change the code of the application?

Albert

--
Dr. Albert Gr"af
Email:  Dr.Graef@t-online.de, ag@muwiinfa.geschichte.uni-mainz.de
WWW:    http://www.musikwissenschaft.uni-mainz.de/~ag