95 lines
6.8 KiB
HTML
95 lines
6.8 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 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="Varying-list SELECT statements" />
|
||
|
|
<meta name="abstract" content="In dynamic SQL, varying-list SELECT statements are ones for which the number and format of result columns to be returned are not predictable; that is, you do not know how many variables you need, or what the data types are." />
|
||
|
|
<meta name="description" content="In dynamic SQL, varying-list SELECT statements are ones for which the number and format of result columns to be returned are not predictable; that is, you do not know how many variables you need, or what the data types are." />
|
||
|
|
<meta name="DC.subject" content="dynamic SQL, varying-list SELECT statement, SELECT statement, using varying-list, INTO clause, PREPARE statements, in dynamic SQL, clause, INTO, PREPARE statement, use with in dynamic SQL, allocating storage" />
|
||
|
|
<meta name="keywords" content="dynamic SQL, varying-list SELECT statement, SELECT statement, using varying-list, INTO clause, PREPARE statements, in dynamic SQL, clause, INTO, PREPARE statement, use with in dynamic SQL, allocating storage" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafyprocesssqlda.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafydynexample.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rbafyvarlst" />
|
||
|
|
<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>Varying-list SELECT statements</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbafyvarlst"><a name="rbafyvarlst"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Varying-list SELECT statements</h1>
|
||
|
|
<div><p>In dynamic SQL, varying-list SELECT statements are ones for which
|
||
|
|
the number and format of result columns to be returned are not predictable;
|
||
|
|
that is, you do not know how many variables you need, or what the data types
|
||
|
|
are. </p>
|
||
|
|
<div class="section"><p>Therefore, you cannot define host variables in advance to accommodate
|
||
|
|
the result columns returned.</p>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> <img src="./delta.gif" alt="Start of change" />In REXX, steps 5.b,
|
||
|
|
6, and 7 are not applicable. REXX only supports SQL descriptors defined using
|
||
|
|
the SQLDA structure; it does not support allocated SQL descriptors.<img src="./deltaend.gif" alt="End of change" /></div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>If your application accepts varying-list SELECT statements, your
|
||
|
|
program has to: </p>
|
||
|
|
</div>
|
||
|
|
<div class="section"> <ol><li>Place the input SQL statement into a host variable.</li>
|
||
|
|
<li>Issue a PREPARE statement to validate the dynamic SQL statement and put
|
||
|
|
it into a form that can be run. If DLYPRP (*YES) is specified on the CRTSQLxxx
|
||
|
|
command, the preparation is delayed until the first time the statement is
|
||
|
|
used in an EXECUTE or DESCRIBE statement, unless the USING clause is specified
|
||
|
|
on the PREPARE statement.</li>
|
||
|
|
<li>Declare a cursor for the statement name.</li>
|
||
|
|
<li>Open the cursor (declared in step 3) that includes the name of the dynamic
|
||
|
|
SELECT statement.</li>
|
||
|
|
<li><img src="./delta.gif" alt="Start of change" />For an allocated SQL descriptor, run an ALLOCATE DESCRIPTOR
|
||
|
|
statement to define the descriptor you intend to use.<img src="./deltaend.gif" alt="End of change" /></li>
|
||
|
|
<li>Issue a DESCRIBE statement to request information from SQL about the type
|
||
|
|
and size of each column of the result table. <div class="note"><span class="notetitle">Notes:</span> <ol type="a"><li>You can also code the PREPARE statement with an INTO clause to perform
|
||
|
|
the functions of PREPARE and DESCRIBE with a single statement.</li>
|
||
|
|
<li id="rbafyvarlst__jan1"><a name="rbafyvarlst__jan1"><!-- --></a><p>If using an SQLDA and the SQLDA is not large enough to contain
|
||
|
|
column descriptions for each retrieved column, the program must determine
|
||
|
|
how much space is needed, get storage for that amount of space, build a new
|
||
|
|
SQLDA, and reissue the DESCRIBE statement.</p>
|
||
|
|
<p><img src="./delta.gif" alt="Start of change" /> If using
|
||
|
|
an allocated SQL descriptor and the descriptor is not large enough, deallocate
|
||
|
|
the descriptor, allocate it with a larger number of entries, and reissue the
|
||
|
|
DESCRIBE statement.<img src="./deltaend.gif" alt="End of change" /></p>
|
||
|
|
</li>
|
||
|
|
</ol>
|
||
|
|
</div>
|
||
|
|
</li>
|
||
|
|
<li id="rbafyvarlst__jan2"><a name="rbafyvarlst__jan2"><!-- --></a>For an SQLDA descriptor, allocate the amount of storage needed
|
||
|
|
to contain a row of retrieved data.</li>
|
||
|
|
<li id="rbafyvarlst__jan3"><a name="rbafyvarlst__jan3"><!-- --></a>For an SQLDA descriptor, put storage addresses into the SQLDA
|
||
|
|
to tell SQL where to put each item of retrieved data.</li>
|
||
|
|
<li><img src="./delta.gif" alt="Start of change" />FETCH a row.<img src="./deltaend.gif" alt="End of change" /></li>
|
||
|
|
<li>Process the data returned in the SQL descriptor.</li>
|
||
|
|
<li>Handle any SQL return codes that might result.</li>
|
||
|
|
<li>When end of data occurs, close the cursor.</li>
|
||
|
|
<li><img src="./delta.gif" alt="Start of change" />For an allocated SQL descriptor, run a DEALLOCATE
|
||
|
|
DESCRIPTOR statement to delete the descriptor.<img src="./deltaend.gif" alt="End of change" /></li>
|
||
|
|
</ol>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafyprocesssqlda.htm" title="There are two basic types of SELECT statements: fixed list and varying list.">Process SELECT statements and use a descriptor</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="rbafydynexample.htm" title="Suppose your application needs to be able to handle a dynamic SELECT statement, one that changes from one use to the next. This statement can be read from a display, passed in from another application, or built dynamically by your application.">Example: Select-statement for allocating storage for SQLDA</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|