%SSC_MOVE

Fetch rows of data

value = %SSC_MOVE(dbchannel, dbcursor, [ncount], [row_count], [more_results][, warn])

Return value

value

This function returns SSQL_NORMAL (success), SSQL_FAILURE (failure), or SSQL_NOMORE (no more data found for current result set). (i)

Arguments

dbchannel

An internal database channel previously initialized using %SSC_INIT and connected by %SSC_CONNECT. (n)

dbcursor

The ID number of an open database cursor (opened with %SSC_OPEN). This must be in the range 1 through the number specified by maxcur when the database channel was initialized (with %SSC_INIT). (n)

ncount

(optional) The number of rows to fetch. This argument defaults to 1 for databases that do not support multirow fetch. (n)

row_count

(optional) Returned number of rows actually fetched by the SQL statement associated with dbcursor. This count is valid only when value is returned as SSQL_NORMAL. (n)

more_results

(optional) When using VTX12_SQLNATIVE, if %SSC_MOVE returns SSQL_NOMORE and more_results is passed, more_results will be set if one or more result sets for the cursor have yet to be returned. See Returning multiple result sets when using VTX12_SQLNATIVE below for more information. This argument is ignored for all other drivers. (n)

warn

(optional) A variable that is set to 1 if one or more rows return a warning status (such as “data columns truncated”). (n)

Discussion

%SSC_MOVE fetches one or more rows of data into host variables defined by %SSC_DEFINE or %SSC_STRDEF. For multirow fetches, %SSC_MOVE returns SSQL_NOMORE if one or more requested rows are not fetched. (If you request a four-row fetch, for example, but %SSC_MOVE is able to fetch only three rows, %SSC_MOVE returns SSQL_NOMORE.) You can use row_count to find out how many rows were actually fetched.

Note the following:

%SSC_MOVE works only with SELECT statements and SQL Server stored procedures, so the %SSC_OPEN call that precedes %SSC_MOVE must set SSQL_SELECT. (%SSC_MOVE fetches rows for a SELECT cursor, even if there’s an intervening call to %SSC_SQLLINK.)
For large binary columns and large character columns, if you pass SSQL_LARGECOL in the %SSC_OPEN call, %SSC_MOVE returns the field length (rather than the data) into the host variables defined for the columns. You then use %SSC_LARGECOL calls to fetch the data. (If you don’t pass SSQL_LARGECOL in the %SSC_OPEN call, %SSC_MOVE fetches the column as a 65,535-byte binary or char column. If the data is longer than 65,535 bytes, it will be truncated.)
%SSC_MOVE can be used to fetch data from a SQL Server stored procedure result set. See Invoking a stored procedure, and see stp_sqlsrv2.dbl for an example.
To return data from database varchar*, varbinary*,and nvarchar* columns of the exact database size, you must use string variables or %SSC_LARGECOL. (Does not apply to Synergy .NET.)

Returning multiple result sets when using VTX12_SQLNATIVE

When using VTX12_SQLNATIVE, if %SSC_MOVE returns SSQL_NOMORE and more_results is set, this indicates that there are more result sets that can be returned for the cursor. Do one of the following to retrieve the remaining results:

If the next result set has the same number of columns as the current result set and the columns are identical (the same type and size), call %SSC_MOVE again.
If the next result set has different column types or a different number of columns, use %SSC_DEFINE with a negative number (for the numvars argument) to redefine the column list for the next call to %SSC_MOVE. Then call %SSC_MOVE again. For example:

if (%ssc_move(dbchn, cur1,,, more_results))		; If SSQL_NOMORE
  begin
    if (!more_results)					 ; More_results is set if another result set is pending
      exitloop		 				 ; Else exit
    if (%ssc_define(dbchn, cur1, -2, deptnum, salary))	 ; Redefine for next result set
      goto err_exit
    nextloop
  end

Note that this feature is not compatible with RO_CURSOR. You must set SSQL_RO_CURSOR to “no” for subsequent result sets to be returned. For example:

  ; After RO has been set, we have to unset SSQL_RO_CURSOR for multiple result sets:
if (%ssc_cmd(dbchn, cur1, SSQL_RO_CURSOR, "no"))
  goto err_exit

if (%ssc_cmd(dbchn, cur1, SSQL_CURSOR_TYPE,SSQL_CURSOR_FORWARD_ONLY))		; Same as default
  goto err_exit

if (%ssc_cmd(dbchn, cur1, SSQL_CMD_ODBC_CONNATTR, SSQL_COPT_SS_CURSOR_OPTIONS+" "+SSQL_COPT_CO_FIREHOSE))
  goto err_exit

Examples

The following example shows how to move column data to a Synergy DBL data area.

sqlp = "SELECT deptnum, deptname"
  &     " FROM org WHERE deptnum = :1"
sts=%ssc_open(dbchn, cur2, sqlp, SSQL_SELECT, SSQL_STANDARD, 1, deptnum)
sts=%ssc_define(dbchn, cur2, 2, deptnum, deptname)
;  Get dnum to SELECT rows
display(g_terminal, "Enter Department Number: ")
reads(g_terminal, %a(dnum))
;  Do fetch and display rows to screen one row at a time
do forever
  begin
    sts = %ssc_move(dbchn, cur2, 1)
    if (sts.eq.SSQL_FAILURE) then             ;ERROR
      goto err_exit
    else if (sts.eq.SSQL_NOMORE)              ;EOF
      exitloop
    writes(g_terminal, %string(deptnum) + ", " + deptname)
  end

For an example of a single row fetch, see exam_fetch.dbl. For an example of a multirow fetch, see exam_multirow_fetch.dbl. These example files are in the connect\synsqlx subdirectory of the main Synergy/DE installation directory.