On 10/27/07, Keresztfalvi Laszlo <lkereszt@gmail.com> wrote:

I successfully retrieved the result of a stored procedure/function returning
sys_refcursor or ref_cursor (see below) with ODBC. It is simple! Poco::Data
don't do it because checks (requires) that an sp/func should return value(s).
Handling an sp/func returning sys_refcursor in the same way like a query does
the job!

From ODBCStatementImpl.cpp line 124: hasData(), in ODBCStatementImpl.h line
180: calls _pPreparation->columns(!isStoredProcedure()), columns() is in
Preparation.cpp line 72. Here, when sp/func called resize is disabled and so
SQLNumResultCols() is not called. Later, no resultset build-up is done.

The whole picture is this: Output variables (sp) and return value (func) can
be bound and receive result (I think) as it already works. The refcursor
outputs come the same way queries return. More than one output recordset are
possible (sp), in which case SQLMoreResults() can be used to obtain the next
resultset. Calling sp with output refcursors needs to simply forget those
params from the query string (less ? required).


To support this, maybe always checking SQLNumResultCols(), or a
manipulator/some special out binding.. would be needed to know that
recordset(s) also coming. Some MoreResults() member in Recordset would also be
required to fully support more than one refcursor output variable.

Here's the explanation why this functionality did not work:

When you have a select statement, driver will figure out the number and types of returned columns at the time when you compile the statement. With stored procedures, this does not appear to be the case (it may be with some drivers, but not Oracle). To work around that, additional check for number and types of returned columns had to be introduced at a later point in time, after the statement was executed. On top of that, Prepare creates temporary placeholders for the data types that can not be directly bound to the driver (such as BLOB, std::string, DateTime ...), so if statement was not prepared during compilation (normal behavior for select queries), it must be prepared between execution and first row fetch. This appears to do the job for Oracle driver. As mentioned, some more Oracle and other DB tests will definitely make us smarter.