friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.cli_5.4.0.1/rzadpfnfetch.htm

393 lines
17 KiB
HTML
Raw Permalink Blame History

<?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 lang="en-us" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<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="DC.Type" content="reference" />
<meta name="DC.Title" content="SQLFetch - Fetch next row" />
<meta name="DC.Relation" scheme="URI" content="rzadphdapi.htm" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1999, 2006" />
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1999, 2006" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="rzadpfnfetch" />
<meta name="DC.Language" 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. -->
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
<link rel="stylesheet" type="text/css" href="./ic.css" />
<title>SQLFetch</title>
</head>
<body id="rzadpfnfetch"><a name="rzadpfnfetch"><!-- --></a>
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
<h1 class="topictitle1">SQLFetch - Fetch next row</h1>
<div><div class="section"></div>
<div class="section"><h4 class="sectiontitle">Purpose</h4> <p><samp class="codeph">SQLFetch()</samp> advances the cursor to
the next row of the result set, and retrieves any bound columns.</p>
<p><samp class="codeph">SQLFetch()</samp> can
be used to receive the data directly into variables you specify with <samp class="codeph">SQLBindCol()</samp>,
or the columns can be received individually after the fetch, by calling <samp class="codeph">SQLGetData()</samp>.
Data conversion is also performed when <samp class="codeph">SQLFetch()</samp> is called,
if conversion is indicated when the column is bound.</p>
</div>
<div class="section"><h4 class="sectiontitle">Syntax</h4><pre>SQLRETURN SQLFetch (SQLHSTMT hstmt);</pre>
</div>
<div class="section"><h4 class="sectiontitle">Function arguments</h4>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" width="100%" frame="hsides" border="1" rules="all"><caption>Table 1. SQLFetch arguments</caption><thead align="left"><tr><th align="left" valign="bottom" width="20%" id="d0e62">Data type</th>
<th align="left" valign="bottom" width="20%" id="d0e64">argument</th>
<th align="left" valign="bottom" width="20%" id="d0e66">Use</th>
<th align="left" valign="bottom" width="40%" id="d0e68">Description</th>
</tr>
</thead>
<tbody><tr><td align="left" valign="top" width="20%" headers="d0e62 ">SQLHSTMT</td>
<td align="left" valign="top" width="20%" headers="d0e64 "><em>hstmt</em></td>
<td align="left" valign="top" width="20%" headers="d0e66 ">Input</td>
<td align="left" valign="top" width="40%" headers="d0e68 ">Statement handle</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section"><h4 class="sectiontitle">Usage</h4><p><samp class="codeph">SQLFetch()</samp> can only be called
if the most recently processed statement on <em>hstmt</em> is a SELECT.</p>
<p>The
number of application variables bound with <samp class="codeph">SQLBindCol()</samp> must
not exceed the number of columns in the result set; otherwise <samp class="codeph">SQLFetch()</samp> fails.</p>
<p>If <samp class="codeph">SQLBindCol()</samp> has
not been called to bind any columns, then <samp class="codeph">SQLFetch()</samp> does
not return data to the application, but just advances the cursor. In this
case <samp class="codeph">SQLGetData()</samp> can then be called to obtain all of the
columns individually. Data in unbound columns is discarded when <samp class="codeph">SQLFetch()</samp> advances
the cursor to the next row.</p>
<p>If any bound variables are
not large enough to hold the data returned by <samp class="codeph">SQLFetch()</samp>,
the data is truncated. If character data is truncated, and the <em>SQLSetEnvAttr()</em> attribute
SQL_ATTR_TRUNCATION_RTNC is set to SQL_TRUE, then the CLI return code SQL_SUCCESS_WITH_INFO
is returned, along with an SQLSTATE that indicates truncation. Note that the
default is SQL_FALSE for SQL_ATTR_TRUNCATION_RTNC. Also, in the case of character
data truncation, the <samp class="codeph">SQLBindCol()</samp> deferred output argument <em>pcbValue</em> contains
the actual length of the column data retrieved from the server. The application
should compare the output length to the input length (<em>pcbValue</em> and <em>cbValueMax</em> arguments
from <samp class="codeph">SQLBindCol()</samp>) to determine which character columns have
been truncated.</p>
<p>Truncation of numeric data types is not reported if
the truncation involves digits to the right of the decimal point. If truncation
occurs to the left of the decimal point, an error is returned (refer to the
diagnostics section).</p>
<p>Truncation of graphic data types is treated the
same as character data types. Except the <em>rgbValue</em> buffer is filled
to the nearest multiple of two bytes that is still less than or equal to the <em>cbValueMax</em> specified
in <samp class="codeph">SQLBindCol()</samp>. Graphic data transferred between DB2<sup>®</sup> UDB CLI and
the application is never null-terminated.</p>
<p>When all the rows have been
retrieved from the result set, or the remaining rows are not needed, <samp class="codeph">SQLFreeStmt()</samp> should
be called to close the cursor and discard the remaining data and associated
resources.</p>
</div>
<div class="section"><h4 class="sectiontitle">Return codes</h4><ul><li>SQL_SUCCESS</li>
<li>SQL_SUCCESS_WITH_INFO</li>
<li>SQL_ERROR</li>
<li>SQL_INVALID_HANDLE</li>
<li>SQL_NO_DATA_FOUND</li>
</ul>
<p>SQL_NO_DATA_FOUND is returned if there are no rows in the result set,
or previous <samp class="codeph">SQLFetch()</samp> calls have fetched all the rows from
the result set.</p>
</div>
<div class="section"><h4 class="sectiontitle">Diagnostics</h4>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" width="100%" frame="hsides" border="1" rules="rows"><caption>Table 2. SQLFetch SQLSTATEs</caption><thead align="left"><tr><th align="left" valign="top" width="25%" id="d0e191">SQLSTATE</th>
<th align="left" valign="top" width="25%" id="d0e193">Description</th>
<th align="left" valign="top" width="50%" id="d0e195">Explanation</th>
</tr>
</thead>
<tbody><tr><td align="left" valign="top" width="25%" headers="d0e191 "><strong>01</strong>004</td>
<td align="left" valign="top" width="25%" headers="d0e193 ">Data truncated</td>
<td align="left" valign="top" width="50%" headers="d0e195 ">The data returned for one or more columns
is truncated. String values are right truncated. (SQL_SUCCESS_WITH_INFO is
returned if no error occurred.)</td>
</tr>
<tr><td align="left" valign="top" width="25%" headers="d0e191 "><strong>HY</strong>001</td>
<td align="left" valign="top" width="25%" headers="d0e193 ">Memory allocation failure</td>
<td align="left" valign="top" width="50%" headers="d0e195 ">The driver is unable to allocate memory required
to support the processing or completion of the function.</td>
</tr>
<tr><td align="left" valign="top" width="25%" headers="d0e191 "><strong>HY</strong>010</td>
<td align="left" valign="top" width="25%" headers="d0e193 ">Function sequence error</td>
<td align="left" valign="top" width="50%" headers="d0e195 ">The specified <em>hstmt</em> is not in an processed
state. The function is called without first calling SQLExecute or SQLExecDirect.</td>
</tr>
<tr><td align="left" valign="top" width="25%" headers="d0e191 "><strong>HY</strong>013 <strong>*</strong></td>
<td align="left" valign="top" width="25%" headers="d0e193 ">Memory management problem</td>
<td align="left" valign="top" width="50%" headers="d0e195 ">The driver is unable to access memory required
to support the processing or completion of the function.</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section"><h4 class="sectiontitle">Example</h4></div>
<div class="example" id="rzadpfnfetch__xmfetch"><a name="rzadpfnfetch__xmfetch"><!-- --></a><div class="note"><span class="notetitle">Note:</span> By using the code
examples, you agree to the terms of the <a href="codedisclaimer.htm">Code license and disclaimer information</a>.</div>
<pre>/*************************************************************************
** file = fetch.c
**
** Example of executing an SQL statement.
** SQLBindCol &amp; SQLFetch is used to retrieve data from the result set
** directly into application storage.
**
** Functions used:
**
** SQLAllocConnect SQLFreeConnect
** SQLAllocEnv SQLFreeEnv
** SQLAllocStmt SQLFreeStmt
** SQLConnect SQLDisconnect
**
** SQLBindCol SQLFetch
** SQLTransact SQLExecDirect
** SQLError
**
**************************************************************************/
#include &lt;stdio.h&gt;
#include &lt;string.h&gt;
#include "sqlcli.h"
#define MAX_STMT_LEN 255
int initialize(SQLHENV *henv,
SQLHDBC *hdbc);
int terminate(SQLHENV henv,
SQLHDBC hdbc);
int print_error (SQLHENV henv,
SQLHDBC hdbc,
SQLHSTMT hstmt);
int check_error (SQLHENV henv,
SQLHDBC hdbc,
SQLHSTMT hstmt,
SQLRETURN frc);
/*******************************************************************
** main
** - initialize
** - terminate
*******************************************************************/
int main()
{
SQLHENV henv;
SQLHDBC hdbc;
SQLCHAR sqlstmt[MAX_STMT_LEN + 1]="";
SQLRETURN rc;
rc = initialize(&amp;henv, &amp;hdbc);
if (rc == SQL_ERROR) return(terminate(henv, hdbc));
{SQLHSTMT hstmt;
SQLCHAR sqlstmt[]="SELECT deptname, location from org where division = 'Eastern'";
SQLCHAR deptname[15],
location[14];
SQLINTEGER rlength;
rc = SQLAllocStmt(hdbc, &amp;hstmt);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, SQL_NULL_HSTMT, rc);
rc = SQLExecDirect(hstmt, sqlstmt, SQL_NTS);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, hstmt, rc);
rc = SQLBindCol(hstmt, 1, SQL_CHAR, (SQLPOINTER) deptname, 15,
&amp;rlength);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, hstmt, rc);
rc = SQLBindCol(hstmt, 2, SQL_CHAR, (SQLPOINTER) location, 14,
&amp;rlength);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, hstmt, rc);
printf("Departments in Eastern division:\n");
printf("DEPTNAME Location\n");
printf("-------------- -------------\n");
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS)
{
printf("%-14.14s %-13.13s \n", deptname, location);
}
if (rc != SQL_NO_DATA_FOUND )
check_error (henv, hdbc, hstmt, rc);
rc = SQLFreeStmt(hstmt, SQL_DROP);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, SQL_NULL_HSTMT, rc);
}
rc = SQLTransact(henv, hdbc, SQL_COMMIT);
if (rc != SQL_SUCCESS )
check_error (henv, hdbc, SQL_NULL_HSTMT, rc);
terminate(henv, hdbc);
return (0);
}/* end main */
/*******************************************************************
** initialize
** - allocate environment handle
** - allocate connection handle
** - prompt for server, user id, &amp; password
** - connect to server
*******************************************************************/
int initialize(SQLHENV *henv,
SQLHDBC *hdbc)
{
SQLCHAR server[SQL_MAX_DSN_LENGTH],
uid[30],
pwd[30];
SQLRETURN rc;
rc = SQLAllocEnv (henv); /* allocate an environment handle */
if (rc != SQL_SUCCESS )
check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc);
rc = SQLAllocConnect (*henv, hdbc); /* allocate a connection handle */
if (rc != SQL_SUCCESS )
check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc);
printf("Enter Server Name:\n");
gets(server);
printf("Enter User Name:\n");
gets(uid);
printf("Enter Password Name:\n");
gets(pwd);
if (uid[0] == '\0')
{ rc = SQLConnect (*hdbc, server, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
if (rc != SQL_SUCCESS )
check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc);
}
else
{ rc = SQLConnect (*hdbc, server, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);
if (rc != SQL_SUCCESS )
check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc);
}
return(SQL_SUCCESS);
}/* end initialize */
/*******************************************************************
** terminate
** - disconnect
** - free connection handle
** - free environment handle
*******************************************************************/
int terminate(SQLHENV henv,
SQLHDBC hdbc)
{
SQLRETURN rc;
rc = SQLDisconnect (hdbc); /* disconnect from database */
if (rc != SQL_SUCCESS )
print_error (henv, hdbc, SQL_NULL_HSTMT);
rc = SQLFreeConnect (hdbc); /* free connection handle */
if (rc != SQL_SUCCESS )
print_error (henv, hdbc, SQL_NULL_HSTMT);
rc = SQLFreeEnv (henv); /* free environment handle */
if (rc != SQL_SUCCESS )
print_error (henv, hdbc, SQL_NULL_HSTMT);
return(rc);
}/* end terminate */
/*******************************************************************
** - print_error - call SQLError(), display SQLSTATE and message
*******************************************************************/
int print_error (SQLHENV henv,
SQLHDBC hdbc,
SQLHSTMT hstmt)
{
SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER sqlcode;
SQLSMALLINT length;
while ( SQLError(henv, hdbc, hstmt, sqlstate, &amp;sqlcode, buffer,
SQL_MAX_MESSAGE_LENGTH + 1, &amp;length) == SQL_SUCCESS )
{
printf("\n **** ERROR *****\n");
printf(" SQLSTATE: %s\n", sqlstate);
printf("Native Error Code: %ld\n", sqlcode);
printf("%s \n", buffer);
};
return ( SQL_ERROR);
} /* end print_error */
/*******************************************************************
** - check_error - call print_error(), checks severity of return code
*******************************************************************/
int check_error (SQLHENV henv,
SQLHDBC hdbc,
SQLHSTMT hstmt,
SQLRETURN frc)
{
SQLRETURN rc;
print_error(henv, hdbc, hstmt);
switch (frc){
case SQL_SUCCESS : break;
case SQL_ERROR :
case SQL_INVALID_HANDLE:
printf("\n ** FATAL ERROR, Attempting to rollback transaction **\n");
rc = SQLTransact(henv, hdbc, SQL_ROLLBACK);
if (rc != SQL_SUCCESS)
printf("Rollback Failed, Exiting application\n");
else
printf("Rollback Successful, Exiting application\n");
terminate(henv, hdbc);
exit(frc);
break;
case SQL_SUCCESS_WITH_INFO :
printf("\n ** Warning Message, application continuing\n");
break;
case SQL_NO_DATA_FOUND :
printf("\n ** No Data Found ** \n");
break;
default :
printf("\n ** Invalid Return Code ** \n");
printf(" ** Attempting to rollback transaction **\n");
SQLTransact(henv, hdbc, SQL_ROLLBACK);
terminate(henv, hdbc);
exit(frc);
break;
}
return(SQL_SUCCESS);
} /* end check_error */</pre>
</div>
<div class="section"><h4 class="sectiontitle">References</h4><ul><li><a href="rzadpfnbindc.htm#rzadpfnbindc">SQLBindCol - Bind a column to an application variable</a></li>
<li><a href="rzadpfnexec.htm#rzadpfnexec">SQLExecute - Execute a statement</a></li>
<li><a href="rzadpfnexecd.htm#rzadpfnexecd">SQLExecDirect - Execute a statement directly</a></li>
<li><a href="rzadpfngcol.htm#rzadpfngcol">SQLGetCol - Retrieve one column of a row of the result set</a></li>
<li><a href="rzadpfetchsc.htm#rzadpfetchsc">SQLFetchScroll - Fetch from a scrollable cursor</a></li>
</ul>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzadphdapi.htm" title="This topic provides a description of each CLI function.">DB2 UDB CLI functions</a></div>
</div>
</div>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink