In Advpl there are three ways to run a Statement on an SGDB through Dbaccess:
- TCSPEXEC() -- To perform stored procedures with one or more
Input and/or output parameters (Return is a list of
parameters defined in Procedure as IN/OUT or OUT only)
- TCSQLEXEC() -- To execute DDL or DML statements (No return from
data, only Success or Failure)
- TCGENQRY() -- To run a SELECT in SGDB (A Query, whose
return is or can be treated as a result-set.
The only function that would make Advpl visible to a result-set would be the TCGenQry()
, used in conjunction with the function DbUseArea()
or even the command USE
... But Dbaccess only allows you to open a cursor using Tcgenqry() s the SQL statement is started with a SELECT. An SQL statement started with "EXECUTE","CALL" or the like, if executed by TCGenQry()
, results in the execution error "Invalid Select / Query Statement"
.
Already the function TCSqlExec()
allows the execution of SQL statements such as INSERT, UPDATE, CALL, EXECUTE and the like, but if the execution generates a result set, it is not recovered in Advpl.
If, instead of creating a Stored Procedure, you create a Table Function, it will be possible to recover the return by Advpl using TCGenQry()
. For this, see the following topic: Procedure Oracle return query lines
Once using the above alternative, the cursor opened in Advpl has no difference compared to a result-set obtained with a "Select". For more information on how to open a result-set in Advpl, see the function documentation Tcgenqry.
A cursor in Advpl through Dbaccess using the Tcgenqry() function is always opened as a cursor Read-Only and Forward-Only (read-only and read-sequentially) addressed via the ALIAS used in the opening as a parameter for the function DbUseaArea()
. The columns of the current return row are accessed using the syntax ALIAS->COLUNA
. To check if the cursor has reached the end, use the function EOF()
, and to fetch the next cursor result, use the function DBSKIP()
-- amounts to one Fetch on the cursor.
References:
And is the result set manipulation equivalent to the return of the cursor? At no time of your answer have I seen you dealing with this in specific.
– Jefferson Quesado
Thanks, I’ll test it.
– Motta