437 lines
26 KiB
HTML
437 lines
26 KiB
HTML
<?xml version="1.0" encoding="utf-8"?>
|
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
|
|
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
|
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
|
|
<head>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
|
<meta name="dc.language" scheme="rfc1766" content="en-us" />
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
|
<!-- US Government Users Restricted Rights -->
|
|
<!-- Use, duplication or disclosure restricted by -->
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
|
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
|
<meta name="security" content="public" />
|
|
<meta name="Robots" content="index,follow"/>
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
|
<meta name="keywords" content="FETCH statement, FETCH, SQL statements,
|
|
moving position, cursor, in FETCH statement, BEFORE clause, AFTER clause,
|
|
NEXT clause, PRIOR clause, FIRST clause, LAST clause, CURRENT clause,
|
|
RELATIVE clause, cursor-name, INTO clause, variable, INTO SQL DESCRIPTOR clause,
|
|
SQL-descriptor-name, INTO DESCRIPTOR clause, descriptor-name, FOR ROWS clause,
|
|
host-structure-array, row-storage-area, positions for open, current row,
|
|
closed by error, error, open state of cursor, *CNULRQD precompiler option,
|
|
*NOCNULRQD precompiler option" />
|
|
<title>FETCH</title>
|
|
<link rel="stylesheet" type="text/css" href="ibmidwb.css" />
|
|
<link rel="stylesheet" type="text/css" href="ic.css" />
|
|
</head>
|
|
<body>
|
|
<a id="Top_Of_Page" name="Top_Of_Page"></a><!-- Java sync-link -->
|
|
<script language = "Javascript" src = "../rzahg/synch.js" type="text/javascript"></script>
|
|
|
|
|
|
<a name="fets"></a>
|
|
<h2 id="fets"><a href="rbafzmst02.htm#ToC_1071">FETCH</a></h2><a id="idx2573" name="idx2573"></a><a id="idx2574" name="idx2574"></a><a id="idx2575" name="idx2575"></a>
|
|
<a name="fetch"></a>
|
|
<p id="fetch"> The FETCH statement positions a cursor on a row of the result
|
|
table. It can return zero, one, or multiple rows, and it assigns the values
|
|
of the rows returned to variables.</p>
|
|
<a name="wq1499"></a>
|
|
<h3 id="wq1499"><a href="rbafzmst02.htm#ToC_1072">Invocation</a></h3>
|
|
<p>This statement can only be embedded in an application program. It is an
|
|
executable statement that cannot be dynamically prepared. Multiple row fetch
|
|
is not allowed in a REXX procedure.</p>
|
|
<a name="wq1500"></a>
|
|
<h3 id="wq1500"><a href="rbafzmst02.htm#ToC_1073">Authorization</a></h3>
|
|
<p>See <a href="rbafzmsth2clcu.htm#h2dclcu">DECLARE CURSOR</a> for an explanation of the authorization
|
|
required to use a cursor.</p>
|
|
<a name="wq1501"></a>
|
|
<h3 id="wq1501"><a href="rbafzmst02.htm#ToC_1074">Syntax</a></h3>
|
|
<a href="rbafzmstfets.htm#refresh"><img src="c.gif" alt="Click to skip syntax diagram" /></a><a id="idx2576" name="idx2576"></a><a id="idx2577" name="idx2577"></a><a id="idx2578" name="idx2578"></a><a id="idx2579" name="idx2579"></a>
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn340.htm"
|
|
border="0" /></span><a href="#skipsyn-339"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a> .-FROM-.
|
|
>>-FETCH--+------------------------+--+------+--<span class="italic">cursor-name</span>----->
|
|
+-NEXT-------------------+
|
|
+-PRIOR------------------+
|
|
+-FIRST------------------+
|
|
+-LAST-------------------+
|
|
| (1) |
|
|
+-BEFORE-----------------+
|
|
| (2) |
|
|
+-AFTER------------------+
|
|
+-CURRENT----------------+
|
|
'-RELATIVE--+-<span><span class="italic">variable</span></span>-+-'
|
|
'-<span class="italic">integer</span>--'
|
|
|
|
>--+--------------------+--------------------------------------><
|
|
+-<span class="italic">single-fetch</span>-------+
|
|
'-<span class="italic">multiple-row-fetch</span>-'
|
|
|
|
single-fetch:
|
|
|
|
.-,--------.
|
|
V |
|
|
|--+-INTO----<span><span class="italic">variable</span></span>-+-------------------------------------+---|
|
|
| .-LOCAL--. |
|
|
+-<span>INTO</span>--<span>SQL</span>--DESCRIPTOR--+--------+--<span class="italic">SQL-descriptor-name</span>-+
|
|
| '-GLOBAL-' |
|
|
'-INTO DESCRIPTOR--<span class="italic">descriptor-name</span>-----------------------'
|
|
|
|
multiple-row-fetch:
|
|
|
|
|--FOR--+-<span><span class="italic">variable</span></span>-+--ROWS-------------------------------------->
|
|
'-<span class="italic">integer</span>--'
|
|
|
|
>--+-INTO--<span class="italic">host-structure-array</span>----------------------------------------------------------+--|
|
|
| .-LOCAL--. |
|
|
'-+-USING--<span>SQL</span>--DESCRIPTOR--+--------+--<span class="italic">SQL-descriptor-name</span>-+--INTO--<span class="italic">row-storage-area</span>-'
|
|
| '-GLOBAL-' |
|
|
'-USING DESCRIPTOR--<span class="italic">descriptor-name</span>-----------------------'
|
|
|
|
row-storage-area:
|
|
|
|
|--:--<span class="italic">host-identifier-1</span>--+-------------------------------------+--|
|
|
| .-INDICATOR-. |
|
|
'-+-----------+--:--<span class="italic">host-identifier-2</span>-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-339" id="skipsyn-339"></a>
|
|
<a name="wq1502"></a>
|
|
<div class="notelisttitle" id="wq1502">Notes:</div>
|
|
<ol type="1">
|
|
<li>If BEFORE is specified, a <span class="italic">single-fetch</span> or
|
|
<span class="italic">multiple-row-fetch</span> must not be specified.</li>
|
|
<li>If AFTER is specified, a <span class="italic">single-fetch</span> or
|
|
<span class="italic">multiple-row-fetch</span> must not be specified.</li>
|
|
</ol>
|
|
<a name="refresh"></a>
|
|
<h3 id="refresh"><a href="rbafzmst02.htm#ToC_1075">Description</a></h3>
|
|
<p>The following keywords specify a new position for the cursor: NEXT, PRIOR,
|
|
FIRST, LAST, BEFORE, AFTER, CURRENT, and RELATIVE. Of those keywords, only
|
|
NEXT may be used for cursors that have not been declared SCROLL. </p>
|
|
<dl class="parml">
|
|
<dt class="bold">NEXT </dt><a id="idx2580" name="idx2580"></a>
|
|
<dd>Positions the cursor on the next row of the result table relative to
|
|
the current cursor position. NEXT is the default if no other cursor orientation
|
|
is specified.
|
|
</dd>
|
|
<dt class="bold">PRIOR </dt><a id="idx2581" name="idx2581"></a>
|
|
<dd>Positions the cursor on the previous row of the result table relative
|
|
to the current cursor position.
|
|
</dd>
|
|
<dt class="bold">FIRST </dt><a id="idx2582" name="idx2582"></a>
|
|
<dd>Positions the cursor on the first row of the result table.
|
|
</dd>
|
|
<dt class="bold">LAST </dt><a id="idx2583" name="idx2583"></a>
|
|
<dd>Positions the cursor on the last row of the result table.
|
|
</dd>
|
|
<dt class="bold">BEFORE </dt><a id="idx2584" name="idx2584"></a>
|
|
<dd>Positions the cursor before the first row of the result table.
|
|
</dd>
|
|
<dt class="bold">AFTER </dt><a id="idx2585" name="idx2585"></a>
|
|
<dd>Positions the cursor after the last row of the result table.
|
|
</dd>
|
|
<dt class="bold">CURRENT </dt><a id="idx2586" name="idx2586"></a>
|
|
<dd>Does not reposition the cursor, but maintains the current cursor position.
|
|
If the cursor has been declared as DYNAMIC SCROLL and the current row has
|
|
been updated so its place within the sort order of the result table is changed,
|
|
an error is returned.
|
|
</dd>
|
|
<dt class="bold">RELATIVE </dt><a id="idx2587" name="idx2587"></a>
|
|
<dd><span class="italic">Variable</span> or <span class="italic">integer</span> is assigned to an integer value <span class="italic">k</span>. RELATIVE
|
|
positions the cursor to the row in the result table that is either <span class="italic">k</span> rows after the current row if <span class="italic">k</span>>0, or <span class="italic">k</span> rows before the current row if <span class="italic">k</span><0. If a <span class="italic">variable</span> is specified, it must be a numeric
|
|
variable with zero scale and it must not include an indicator variable.
|
|
<a name="synspec"></a>
|
|
<table id="synspec" width="100%" summary="" border="1" frame="border" rules="all">
|
|
<caption>Table 55. Synonymous Scroll Specifications</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq1504" width="50%" align="left" valign="bottom">Specification</th>
|
|
<th id="wq1505" width="50%" align="left" valign="bottom">Alternative</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1504">RELATIVE +1</td>
|
|
<td align="left" valign="top" headers="wq1505">NEXT</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1504">RELATIVE -1</td>
|
|
<td align="left" valign="top" headers="wq1505">PRIOR</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1504">RELATIVE 0</td>
|
|
<td align="left" valign="top" headers="wq1505">CURRENT</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</dd>
|
|
<dt class="bold">FROM</dt>
|
|
<dd>This keyword is provided for clarity only. If a scroll position option
|
|
is specified, then this keyword is required. If no scrolling option is specified,
|
|
then the FROM keyword is optional.
|
|
</dd>
|
|
<dt class="bold"><span class="italic">cursor-name</span> </dt><a id="idx2588" name="idx2588"></a>
|
|
<dd>Identifies the cursor to be used in the fetch operation. The <span class="italic">cursor-name</span> must identify a declared cursor as explained in <a href="rbafzmsth2clcu.htm#deccur">Description</a> for the DECLARE CURSOR statement. When the FETCH statement is
|
|
executed, the cursor must be in the open state.
|
|
<p>If a <span class="italic">single-fetch</span> or <span class="italic">multiple-row-fetch</span> clause is
|
|
not specified, no data is returned to the user. However, the cursor is positioned
|
|
and a row lock may be acquired. For more information about locking, see <a href="rbafzmstisol.htm#isol">Isolation level</a>.</p>
|
|
</dd>
|
|
</dl>
|
|
<a name="wq1506"></a>
|
|
<h3 id="wq1506"><a href="rbafzmst02.htm#ToC_1076">single-fetch</a></h3>
|
|
<dl class="parml">
|
|
<dt class="bold">INTO <span class="italic">variable</span>,... </dt><a id="idx2589" name="idx2589"></a><a id="idx2590" name="idx2590"></a>
|
|
<dd>Identifies one or more host structures or variables that must be declared
|
|
in accordance with the rules for declaring host structures and variables.
|
|
In the operational form of INTO, a host structure is replaced by a reference
|
|
to each of its variables. The first value in the result row is assigned to
|
|
the first variable in the list, the second value to the second variable, and
|
|
so on.
|
|
</dd>
|
|
<dt class="bold">INTO SQL DESCRIPTOR <span class="italic">SQL-descriptor-name</span><a id="idx2591" name="idx2591"></a></dt>
|
|
<dd>Identifies an SQL descriptor which contains valid descriptions
|
|
of the output variables to be used with the FETCH statement. Before the FETCH
|
|
statement is executed, a descriptor must be allocated using the ALLOCATE DESCRIPTOR
|
|
statement.
|
|
<dl class="parml">
|
|
<dt class="bold">LOCAL</dt>
|
|
<dd>Specifies the scope of the name of the descriptor to be local to program
|
|
invocation.
|
|
</dd>
|
|
<dt class="bold">GLOBAL</dt>
|
|
<dd>Specifies the scope of the name of the descriptor to be global to the
|
|
SQL session.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">SQL-descriptor-name</var></dt>
|
|
<dd>Names the SQL descriptor. The name must identify a descriptor that already
|
|
exists with the specified scope. <a id="idx2592" name="idx2592"></a>
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">INTO DESCRIPTOR <span class="italic">descriptor-name</span> </dt><a id="idx2593" name="idx2593"></a><a id="idx2594" name="idx2594"></a>
|
|
<dd>Identifies an SQLDA that must contain a valid description of zero or
|
|
more variables.
|
|
<p>Before the FETCH statement is processed, the user must set
|
|
the following fields in the SQLDA. (The rules for REXX are different. For
|
|
more information see the <a href="../rzajp/rzajpkickoff.htm">Embedded SQL Programming</a> book.) </p>
|
|
<ul>
|
|
<li>SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA</li>
|
|
<li>SQLDABC to indicate the number of bytes of storage allocated for the SQLDA</li>
|
|
<li>SQLD to indicate the number of variables used in the SQLDA when processing
|
|
the statement</li>
|
|
<li>SQLVAR occurrences to indicate the attributes of the variables</li></ul>
|
|
<p>The SQLDA must have enough storage to contain all SQLVAR occurrences.
|
|
Therefore, the value in SQLDABC must be greater than or equal to 16 + SQLN*(80),
|
|
where 80 is the length of an SQLVAR occurrence. If LOBs are specified, there
|
|
must be two SQLVAR entries for each parameter marker and SQLN must be set
|
|
to two times the number of parameter markers.</p>
|
|
<p>SQLD must be set to a
|
|
value greater than or equal to zero and less than or equal to SQLN. For more
|
|
information, see <a href="rbafzmstsqldda.htm#sqldda">Appendix D. SQLDA (SQL descriptor area)</a>.</p>
|
|
</dd>
|
|
</dl>
|
|
<a name="multiplerowfetch"></a>
|
|
<h3 id="multiplerowfetch"><a href="rbafzmst02.htm#ToC_1077">multiple-row-fetch</a></h3>
|
|
<dl class="parml">
|
|
<dt class="bold">FOR <span class="italic">variable</span> <span>or</span> <span class="italic">integer</span> ROWS </dt><a id="idx2595" name="idx2595"></a>
|
|
<dd>Evaluates <span class="italic">variable</span> or <span class="italic">integer</span> to an integral value that represents the number
|
|
of rows to fetch. If a <span class="italic">variable</span> is specified,
|
|
it must be a numeric variable with zero scale and it must not include an indicator
|
|
variable. The value must be in the range of 1 to 32767. The cursor is positioned
|
|
on the row specified by the orientation keyword (for example, NEXT), and that
|
|
row is fetched. Then the next rows are fetched (moving forward in the table),
|
|
until either the specified number of rows have been fetched or the end of
|
|
the cursor is reached. After the fetch operation, the cursor is positioned
|
|
on the last row fetched.
|
|
<p>For example, FETCH PRIOR FROM C1 FOR 3 ROWS causes
|
|
the previous row, the current row, and the next row to be returned, in that
|
|
order. The cursor is positioned on the next row. FETCH RELATIVE -1 FROM C1
|
|
FOR 3 ROWS returns the same result. FETCH FIRST FROM C1 FOR :x ROWS returns
|
|
the first <span class="italic">x</span> rows, and leaves the cursor positioned
|
|
on row number <span class="italic">x</span>.</p>
|
|
<p>When a <span class="italic">multiple-row-fetch</span> is successfully executed, three statement information
|
|
items are available in the SQL Diagnostics Area (or the SQLCA): </p>
|
|
<ul>
|
|
<li>ROW_COUNT (or SQLERRD(3) of the SQLCA) shows the number of rows retrieved.</li>
|
|
<li>DB2_ROW_LENGTH (or SQLERRD(4) of the SQLCA) contains the length of the
|
|
row retrieved.</li>
|
|
<li>DB2_LAST_ROW (or SQLERRD(5) of the SQLCA) contains +100 if the last row
|
|
was fetched. <sup class="fn"><a id="wq1507" name="wq1507" href="rbafzmstfets.htm#wq1508">71</a></sup></li></ul>
|
|
</dd>
|
|
<dt class="bold">INTO <span class="italic">host-structure-array</span> </dt><a id="idx2596" name="idx2596"></a><a id="idx2597" name="idx2597"></a>
|
|
<dd><span class="italic">host-structure-array</span> identifies an array of
|
|
host structures defined in accordance with the rules for declaring host structures.
|
|
<p>The first structure in the array corresponds to the first row, the second
|
|
structure in the array corresponds to the second row, and so on. In addition,
|
|
the first value in the row corresponds to the first item in the structure,
|
|
the second value in the row corresponds to the second item in the structure,
|
|
and so on. The number of rows to be fetched must be less than or equal to
|
|
the dimension of the host structure array.</p>
|
|
</dd>
|
|
<dt class="bold">USING SQL DESCRIPTOR <span class="italic">SQL-descriptor-name</span></dt>
|
|
<dd>Identifies an SQL descriptor.
|
|
<dl class="parml">
|
|
<dt class="bold">LOCAL</dt>
|
|
<dd>Specifies the scope of the name of the descriptor to be local to program
|
|
invocation.
|
|
</dd>
|
|
<dt class="bold">GLOBAL</dt>
|
|
<dd>Specifies the scope of the name of the descriptor to be global to the
|
|
SQL session.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">SQL-descriptor-name</var></dt>
|
|
<dd>Names the SQL descriptor. The name must identify a descriptor that already
|
|
exists with the specified scope.
|
|
<p>The COUNT field in the descriptor
|
|
header must be set to reflect the number of columns in the result set. The
|
|
TYPE and DATETIME_INTERVAL_CODE (if applicable) must be set for each column
|
|
in the result set.</p><a id="idx2598" name="idx2598"></a>
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">USING DESCRIPTOR <span class="italic">descriptor-name</span></dt>
|
|
<dd>Identifies an SQLDA that must contain a valid description of zero or
|
|
more variables that describe the format of a row in the <span class="italic">row-storage-area</span>.
|
|
<p>Before the FETCH statement is processed, the user
|
|
must set the following fields in the SQLDA: </p>
|
|
<ul>
|
|
<li>SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA.</li>
|
|
<li>SQLDABC to indicate the number of bytes of storage allocated for the SQLDA.</li>
|
|
<li>SQLD to indicate the number of variables used in the SQLDA when processing
|
|
the statement.</li>
|
|
<li>SQLVAR occurrences to indicate the attributes of the variables.</li></ul>
|
|
<p>The values of the other fields of the SQLDA (such as SQLNAME)
|
|
may not be defined after the FETCH statement is executed and should not be
|
|
used.</p>
|
|
<p>The SQLDA must have enough storage to contain all SQLVAR occurrences.
|
|
Therefore, the value in SQLDABC must be greater than or equal to 16 + SQLN*(80),
|
|
where 80 is the length of an SQLVAR occurrence. If LOBs or distinct types
|
|
are specified, there must be two SQLVAR entries for each parameter marker
|
|
and SQLN must be set to two times the number of parameter markers.</p>
|
|
<p>SQLD must be set to a value greater than or equal to zero and less than or
|
|
equal to SQLN. For more information, see <a href="rbafzmstsqldda.htm#sqldda">Appendix D. SQLDA (SQL descriptor area)</a>.</p>
|
|
<p>On completion
|
|
of the FETCH, the SQLDATA pointer in the first SQLVAR entry addresses the
|
|
returned value for the first column in the allocated storage in the first
|
|
row, the SQLDATA pointer in the second SQLVAR entry addresses the returned
|
|
value for the second column in the allocated storage in the first row, and
|
|
so on. The SQLIND pointer in the first nullable SQLVAR entry addresses the
|
|
first indicator value, the SQLIND pointer in the second nullable SQLVAR entry
|
|
addresses the second indicator value, and so on. The SQLDA must be allocated
|
|
on a 16-byte boundary.</p>
|
|
</dd>
|
|
<dt class="bold">INTO <span class="italic">row-storage-area</span> </dt><a id="idx2599" name="idx2599"></a><a id="idx2600" name="idx2600"></a>
|
|
<dd><span class="italic">host-identifier-1</span> specified with
|
|
a variable identifies an allocation of storage in which to return the rows.
|
|
The rows are returned into the storage area in the format described by the
|
|
SQLDA or SQL descriptor. <span class="italic">host-identifier-1</span> must be
|
|
large enough to hold all the rows requested.
|
|
<p><span class="italic">host-identifier-2</span> identifies the optional indicator area. It should be
|
|
specified if any of the data types returned are nullable. The indicators are
|
|
returned as small integers. <span class="italic">host-identifier-2</span> must
|
|
be large enough to contain an indicator for each nullable value for each row
|
|
to be returned.</p>
|
|
<p>The GET DIAGNOSTICS statement can be used
|
|
to return the DB2_ROW_LENGTH which indicates the length of each row returned
|
|
into the <var class="pv">row-storage-area</var>.</p>
|
|
</dd>
|
|
</dl>
|
|
<p>The <span class="italic">nth</span> variable identified by the INTO clause
|
|
or described in the SQLDA corresponds to the <span class="italic">nth</span> column
|
|
of the result table of the cursor. The data type of each variable must be
|
|
compatible with its corresponding column.</p>
|
|
<p>Each assignment to a variable is made according to the retrieval assignment
|
|
rules described in <a href="rbafzmstch2bas.htm#reta">Retrieval assignment</a>. If the number of variables is less
|
|
than the number of values in the row, the SQLSTATE is set to '01503' (or the
|
|
SQLWARN3 field of the SQLCA is set to 'W'). Note that there is no
|
|
warning if there are more variables than the number of result columns. If
|
|
the value is null, an indicator variable must be provided. If an assignment
|
|
error occurs, the value is not assigned to the variable, and no more values
|
|
are assigned to variables. Any values that have already been assigned to variables
|
|
remain assigned.</p>
|
|
<p>If an error occurs as the result of an arithmetic expression in the SELECT
|
|
list of an outer SELECT statement (division by zero, overflow, etc.) or a
|
|
character conversion error occurs, the result is the null value. As in any
|
|
other case of a null value, an indicator variable must be provided. The value
|
|
of the variable is undefined. In this case, however, the indicator variable
|
|
is set to -2. Processing of the statement continues as if the error had not
|
|
occurred. (However, a warning is returned.) If you do not provide an indicator
|
|
variable, an error is returned. It is possible that some values have already
|
|
been assigned to variables and will remain assigned when the error occurs.</p>
|
|
<p><span class="italic">multiple-row-fetch</span> is not allowed if any of the
|
|
result columns are LOBs or if the current connection is to a remote server.</p>
|
|
<a name="wq1509"></a>
|
|
<h3 id="wq1509"><a href="rbafzmst02.htm#ToC_1078">Notes</a></h3><a id="idx2601" name="idx2601"></a><a id="idx2602" name="idx2602"></a><a id="idx2603" name="idx2603"></a><a id="idx2604" name="idx2604"></a><a id="idx2605" name="idx2605"></a>
|
|
<p><span class="bold">Cursor position:</span> An open cursor has three possible
|
|
positions: </p>
|
|
<ul>
|
|
<li>Before a row</li>
|
|
<li>On a row</li>
|
|
<li>After the last row</li></ul>
|
|
<p>If a cursor is positioned on a row, that row is called the current row
|
|
of the cursor. A cursor referenced in an UPDATE or DELETE statement must be
|
|
positioned on a row. A cursor can only be positioned on a row as a result
|
|
of a FETCH statement.</p>
|
|
<p>It is possible for an error to occur that makes the state of the cursor
|
|
unpredictable.</p>
|
|
<a name="incif"></a>
|
|
<p id="incif"><span class="bold">Variable assignment:</span> If the
|
|
specified variable is character and is not large enough to contain the result,
|
|
a warning (SQLSTATE 01004) is returned (and 'W' is assigned to SQLWARN1
|
|
in the SQLCA). The actual length of the result is returned in the indicator
|
|
variable associated with the <span class="italic">variable</span>,
|
|
if an indicator variable is provided.</p>
|
|
<p>If the specified variable is a C NUL-terminated variable and is not large
|
|
enough to contain the result and the NUL-terminator:<a id="idx2606" name="idx2606"></a><a id="idx2607" name="idx2607"></a></p>
|
|
<ul>
|
|
<li>If the *CNULRQD option is specified on the CRTSQLCI or CRTSQLCPPI command
|
|
(or CNULRQD(*YES) on the SET OPTION statement), the following occurs:
|
|
<ul>
|
|
<li>The result is truncated.</li>
|
|
<li>The last character is the NUL-terminator.</li>
|
|
<li>A warning (SQLSTATE 01004) is returned (and 'W' is assigned to
|
|
SQLWARN1 in the SQLCA).</li></ul></li>
|
|
<li>If the *NOCNULRQD option on the CRTSQLCI or CRTSQLCPPI command (or CNULRQD(*NO)
|
|
on the SET OPTION statement) is specified, the following occurs:
|
|
<ul>
|
|
<li>The NUL-terminator is not returned.</li>
|
|
<li>A warning (SQLSTATE 01004) is returned (and 'N' is assigned to
|
|
SQLWARN1 in the SQLCA).</li></ul></li></ul>
|
|
<p><span class="bold">Syntax alternatives:</span> The following keywords are synonyms
|
|
supported for compatibility to prior releases. These keywords are non-standard
|
|
and should not be used:</p>
|
|
<ul>
|
|
<li>USING DESCRIPTOR may be used as a synonym for INTO DESCRIPTOR in the single-fetch-clause.</li></ul>
|
|
<a name="wq1510"></a>
|
|
<h3 id="wq1510"><a href="rbafzmst02.htm#ToC_1079">Example</a></h3>
|
|
<p><span class="italic">Example 1:</span> In this C example, the FETCH statement
|
|
fetches the results of the SELECT statement into the program variables <tt class="xph">dnum</tt>, <tt class="xph">dname</tt>, and <tt class="xph">mnum</tt>. When no more rows remain
|
|
to be fetched, the not found condition is returned. </p>
|
|
<pre class="xmp"> EXEC SQL <span class="bold">DECLARE</span> C1 <span class="bold">CURSOR FOR</span>
|
|
<span class="bold">SELECT</span> DEPTNO, DEPTNAME, MGRNO <span class="bold">FROM</span> TDEPT
|
|
<span class="bold">WHERE</span> ADMRDEPT = 'A00';
|
|
EXEC SQL <span class="bold">OPEN</span> C1;
|
|
while (<span class="bold">SQLCODE</span>==0) {
|
|
EXEC SQL <span class="bold">FETCH</span> C1 <span class="bold">INTO</span> :dnum, :dname, :mnum;
|
|
}
|
|
EXEC SQL <span class="bold">CLOSE</span> C1;</pre>
|
|
<p><span class="italic">Example 2:</span> This FETCH statement uses an SQLDA. </p>
|
|
<pre class="xmp"> <span class="bold">FETCH</span> CURS <span class="bold">USING DESCRIPTOR</span> :sqlda3</pre>
|
|
<p><a id="idx2608" name="idx2608"></a><a id="idx2609" name="idx2609"></a></p>
|
|
<hr /><div class="fnnum"><a id="wq1508" name="wq1508" href="rbafzmstfets.htm#wq1507">71</a>.</div>
|
|
<div class="fntext">If the number of rows returned is equal to the number of
|
|
rows requested, then an end of data warning may not occur and DB2_LAST_ROW
|
|
(or SQLERRD(5) of the SQLCA) may not contain +100.</div>
|
|
<br />
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstexim.htm">Previous Page</a> | <a href="rbafzmstfreeloc.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> |
|
|
<a href="rbafzmstindex.htm#index">Index</a> ]
|
|
|
|
<a id="Bot_Of_Page" name="Bot_Of_Page"></a>
|
|
</body>
|
|
</html>
|