%SSC_MOVE

Fetch rows of data

value = %SSC_MOVE(dbchannel, dbcursor, [ncount], [row_count], [compute_flg][, 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)

compute_flg

This argument is deprecated and ignored.

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 not all requested rows are 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.)

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.