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.db2_5.4.0.1/rbafzmstpreph2.htm

806 lines
45 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 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="PREPARE statement, PREPARE, SQL statements,
dynamic SQL, dynamically prepared by PREPARE, prepared SQL statement,
referring to prepared statements, unit of work, in PREPARE statement,
statement-name, USING keyword, SQL-descriptor-name, INTO clause, descriptor-name,
USING clause, in USING clause, NAMES, SYSTEM NAMES, LABELS, ANY clause,
BOTH clause, ALL clause, FROM clause, string-expression, variable,
parameter marker, rules, typed, untyped, usage in expressions,
predicates and functions" />
<title>PREPARE</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="preph2"></a>
<h2 id="preph2"><a href="rbafzmst02.htm#ToC_1184">PREPARE</a></h2><a id="idx2946" name="idx2946"></a><a id="idx2947" name="idx2947"></a><a id="idx2948" name="idx2948"></a><a id="idx2949" name="idx2949"></a><a id="idx2950" name="idx2950"></a>
<a name="prep"></a>
<p id="prep">The PREPARE statement creates an executable form of an SQL statement
from a character-string form of the statement. The character-string form is
called a <span class="italic">statement string</span>, and the executable form
is called a <span class="italic">prepared statement</span>.</p>
<a name="wq1648"></a>
<h3 id="wq1648"><a href="rbafzmst02.htm#ToC_1185">Invocation</a></h3>
<p>This statement can only be embedded in an application program,
SQL function, SQL procedure, or trigger. It is an executable statement that
cannot be dynamically prepared. It must not be specified in Java&trade;.</p>
<a name="wq1649"></a>
<h3 id="wq1649"><a href="rbafzmst02.htm#ToC_1186">Authorization</a></h3>
<p>The authorization rules are the same as those defined for the SQL statement
specified by the PREPARE statement. For example, see <a href="rbafzmstintsel.htm#intsel">select-statement</a> for the authorization rules that apply when a SELECT statement
is prepared.</p>
<p>If DLYPRP(*NO) is specified on the CRTSQLxxx command, the authorization
checking is performed when the statement is prepared, except: </p>
<ul>
<li>If a DROP SCHEMA statement is prepared, the system authority *OBJEXIST
on all objects in the schema is not checked until the statement is executed.</li>
<li>If a DROP TABLE statement is prepared, the system authority *OBJEXIST
on all views, indexes, and logical files that reference the table is not checked
until the statement is executed.</li>
<li>If a DROP VIEW statement is prepared, the system authority of *OBJEXIST
on all views that reference the view is not checked until the statement is
executed.</li>
<li>If a CREATE TRIGGER statement is prepared, privileges on objects
referenced in the <var class="pv">triggered-action</var> are not checked until the statement
is executed.</li></ul>
<p>If DLYPRP(*YES) is specified on the CRTSQLxxx command, all authorization
checking is deferred until the statement is executed or used in an OPEN statement.</p>
<p>The authorization ID of the statement is the run-time authorization ID
unless DYNUSRPRF(*OWNER) was specified on the CRTSQLxxx command when the program
was created. For more information, see <a href="rbafzmstch2auth.htm#ch2auth">Authorization IDs and authorization names</a>.</p>
<a name="wq1650"></a>
<h3 id="wq1650"><a href="rbafzmst02.htm#ToC_1187">Syntax</a></h3>
<a href="rbafzmstpreph2.htm#synsprepare"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
<a name="wq1651"></a>
<div class="fignone" id="wq1651">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn361.htm"
border="0" /></span><a href="#skipsyn-360"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>>>-PREPARE--<span class="italic">statement-name</span>-------------------------------------->
>--+-----------------------------------------------------------------+-->
| .-SQL-. .-LOCAL--. |
'-+-USING--+-----+--DESCRIPTOR--+--------+--<span class="italic">SQL-descriptor-name</span>-+-'
| '-GLOBAL-' |
'-INTO----<span class="italic">descriptor-name</span>----+-------------------------+------'
'-USING--+-NAMES--------+-'
+-SYSTEM NAMES-+
+-LABELS-------+
+-ANY----------+
+-BOTH---------+
'-ALL----------'
>--+-FROM--<span class="italic">string-expression</span>-----------------------+----------->&lt;
'-+---------------------------+--FROM--<span><span class="italic">variable</span></span>-'
'-ATTRIBUTES--<span><span class="italic">attr-variable</span></span>-'
</pre>
<a name="skipsyn-360" id="skipsyn-360"></a></div>
<a name="wq1652"></a>
<div class="fignone" id="wq1652">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn362.htm"
border="0" /></span><a href="#skipsyn-361"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>attribute-string:
.------------------------------------.
V | (1)
|----+--------------------------------+-+-----------------------|
+-+-ASENSITIVE-------------+-----+
| +-INSENSITIVE------------+ |
| | .-DYNAMIC-. | |
| '-SENSITIVE--+---------+-' |
+-+-NO SCROLL-+------------------+
| '-SCROLL----' |
+-+-WITHOUT HOLD-+---------------+
| '-WITH HOLD----' |
+-+-WITHOUT RETURN-------------+-+
| | .-TO CALLER-. | |
| '-WITH RETURN--+-----------+-' |
| '-TO CLIENT-' |
+-<span class="italic">fetch-first-clause</span>-------------+
+-+-<span class="italic">read-only-clause</span>-+-----------+
| '-<span class="italic">update-clause</span>----' |
+-<span class="italic">optimize-clause</span>----------------+
'-<span class="italic">isolation-clause</span>---------------'
</pre>
<a name="skipsyn-361" id="skipsyn-361"></a>
<a name="wq1653"></a>
<div class="notelisttitle" id="wq1653">Notes:</div>
<ol type="1">
<li>The same clause must not be specified more than once. If the options
are not specified, their defaults are whatever was specified for the corresponding
options in an associated DECLARE CURSOR and the prepared SELECT statement.</li>
</ol></div>
<a name="synsprepare"></a>
<h3 id="synsprepare"><a href="rbafzmst02.htm#ToC_1188">Description</a></h3>
<dl class="parml">
<dt class="bold"><var class="pv">statement-name</var> </dt><a id="idx2951" name="idx2951"></a>
<dd>Names the prepared statement. If the name identifies an existing prepared
statement, that prepared statement is destroyed if:
<ul>
<li>it was prepared in the same instance of the same program, or</li>
<li>CLOSQLCSR(*ENDJOB), CLOSQLCSR(*ENDACTGRP), or CLOSQLCSR(*ENDSQL) are specified
on the CRTSQLxxx commands associated with both prepared statements.</li></ul>The name must not identify a prepared statement that is the SELECT statement
of an open cursor of this instance of the program.
</dd>
<dt class="bold">USING SQL DESCRIPTOR <var class="pv">SQL-descriptor-name</var><a id="idx2952" name="idx2952"></a></dt>
<dd>Identifies an SQL descriptor. If USING is specified, and
the PREPARE statement is successfully executed, information about the prepared
statement is placed in the SQL descriptor specified by the <span class="italic">SQL-descriptor-name</span>. Thus, the PREPARE statement:
<a name="nrx15"></a>
<pre id="nrx15" class="xmp"> EXEC SQL <span class="bold">PREPARE</span> S1 <span class="bold">USING SQL DESCRIPTOR</span> :sqldescriptor <span class="bold">FROM</span> :V1;</pre>
<p>is equivalent to: </p>
<a name="nrx16"></a>
<pre id="nrx16" class="xmp"> EXEC SQL <span class="bold">PREPARE</span> S1 <span class="bold">FROM</span> :V1;
EXEC SQL <span class="bold">DESCRIBE</span> S1 <span class="bold">USING SQL DESCRIPTOR</span> :sqldescriptor;</pre>
<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="idx2953" name="idx2953"></a>
</dd>
</dl>
<p>See <a href="rbafzgetdescr.htm#getdescr">GET DESCRIPTOR</a> for an explanation of the information
that is placed in the SQLDA.</p>
</dd>
<dt class="bold">INTO </dt><a id="idx2954" name="idx2954"></a><a id="idx2955" name="idx2955"></a>
<dd>If INTO is used, and the PREPARE statement is successfully executed,
information about the prepared statement is placed in the SQLDA specified
by the <span class="italic">descriptor-name</span>. Thus, the PREPARE statement:
<a name="nrx15a"></a>
<pre id="nrx15a" class="xmp"> EXEC SQL <span class="bold">PREPARE</span> S1 <span class="bold">INTO</span> :SQLDA <span class="bold">FROM</span> :V1;</pre>
<p>is equivalent to: </p>
<a name="nrx16a"></a>
<pre id="nrx16a" class="xmp"> EXEC SQL <span class="bold">PREPARE</span> S1 <span class="bold">FROM</span> :V1;
EXEC SQL <span class="bold">DESCRIBE</span> S1 <span class="bold">INTO</span> :SQLDA;</pre>
<dl class="parml">
<dt class="bold"><var class="pv">descriptor-name</var></dt>
<dd>Identifies an SQL descriptor area (SQLDA), which is described in <a href="rbafzmstsqldda.htm#sqldda">Appendix D. SQLDA (SQL descriptor area)</a>. Before the PREPARE statement is executed, the
following variable in the SQLDA must be set (The rules for REXX are different.
For more information, see the <a href="../rzajp/rzajpkickoff.htm">Embedded SQL Programming</a> book.) :
<dl class="parml">
<dt class="bold"><span class="bold">SQLN</span></dt>
<dd>Indicates the number of variables represented by SQLVAR. (SQLN provides
the dimension of the SQLVAR array.) SQLN must be set to a value greater than
or equal to zero before the PREPARE statement is executed. For information
on techniques to determine the number of occurrences required, see <a href="rbafzmstfielddescsqlda.htm#howmany">Determining how many SQLVAR occurrences are needed</a>.
</dd>
</dl>
</dd>
</dl>
<p>See <a href="rbafzmstdescr.htm#descr">DESCRIBE</a> for an explanation of the information
that is placed in the SQLDA.</p>
</dd>
<dt class="bold">USING </dt><a id="idx2956" name="idx2956"></a>
<dd>Specifies what value to assign to each SQLNAME variable in
the SQLDA. If the requested value does not exist or a name is longer than
30, SQLNAME is set to length 0.
<dl class="parml">
<dt class="bold"><span class="pk">NAMES</span> </dt><a id="idx2957" name="idx2957"></a>
<dd>Assigns the name of the column. This is the default. For a prepared
statement where the names are explicitly specified in the select-list, the
name specified is returned.
</dd>
<dt class="bold"><span class="pk">SYSTEM NAMES</span> </dt><a id="idx2958" name="idx2958"></a>
<dd>Assigns the system column name of the column.
</dd>
<dt class="bold">LABELS </dt><a id="idx2959" name="idx2959"></a>
<dd>Assigns the label of the column. (Column labels are defined by the LABEL
statement.) Only the first 20 bytes of the label are returned.
</dd>
<dt class="bold">ANY </dt><a id="idx2960" name="idx2960"></a>
<dd>Assigns the column label. If the column has no label, the label is the
column name.
</dd>
<dt class="bold">BOTH </dt><a id="idx2961" name="idx2961"></a>
<dd>Assigns both the label and name of the column. In this case, two or
three occurrences of SQLVAR per column, depending on whether the result set
contains distinct types, are needed to accommodate the additional information.
To specify this expansion of the SQLVAR array, set SQLN to 2*<span class="italic">n</span> or 3*<span class="italic">n</span>(where <span class="italic">n</span> is the
number of columns in the table or view). The first <span class="italic">n</span> occurrences
of SQLVAR contain the column names. Either the second or third <span class="italic">n</span> occurrences contain the column labels. If there are no distinct types,
the labels are returned in the second set of SQLVAR entries. Otherwise, the
labels are returned in the third set of SQLVAR entries.
<p>If the same SQLDA
is used on a subsequent FETCH statement, set SQLN to <span class="italic">n</span> after the PREPARE is complete.</p>
</dd>
<dt class="bold">ALL </dt><a id="idx2962" name="idx2962"></a>
<dd>Assigns the label, column name, and system column name. In
this case three or four occurrences of SQLVAR per column, depending on whether
the result set contains distinct types, are needed to accommodate the additional
information. To specify this expansion of the SQLVAR array, set SQLN to 3*<span class="italic">n</span> or 4*<span class="italic">n</span> (where <span class="italic">n</span> is the number of columns in the result table). The first <span class="italic">n</span> occurrences of SQLVAR contain the system column names. The second or
third <span class="italic">n</span> occurrences contain the column labels. The
third or fourth <span class="italic">n</span> occurrences contain the column names
if they are different from the system column name. If there are no distinct
types, the labels are returned in the second set of SQLVAR entries and the
column names are returned in the third set of SQLVAR entries. Otherwise, the
labels are returned in the third set of SQLVAR entries and the column names
are returned in the fourth set of SQLVAR entries.
<p>If the same SQLDA is used
on a subsequent FETCH statement, set SQLN to <span class="italic">n</span> after
the PREPARE is complete.</p>
</dd>
</dl>
</dd>
<dt class="bold">ATTRIBUTES <var class="pv">attr-variable</var></dt>
<dd>Specifies the attributes for this cursor that are in effect if a corresponding
attribute has not been specified as part of the associated SELECT statement.
If attributes are specified in the SELECT statement, they are used instead
of the corresponding attributes specified on the PREPARE statement. In turn,
if attributes are specified in the PREPARE statement, they are used instead
of the corresponding attributes specified on a DECLARE CURSOR statement.
<p><var class="pv">attr-variable</var> must identify a character-string, UTF-16 graphic,
or UCS-2 graphic variable that is declared in the program in accordance with
the rules for declaring string variables. <var class="pv">attr-variable</var> must
be a string variable (either fixed-length or varying-length) that has a length
attribute that does not exceed the maximum length of a VARCHAR. Leading and
trailing blanks are removed from the value of the variable. The variable must
contain a valid <var class="pv">attribute-string</var>.</p>
<p>An indicator variable can
be used to indicate whether or not attributes are actually provided on the
PREPARE statement. Thus, applications can use the same PREPARE statement regardless
of whether attributes need to be specified or not. The options that can be
specified as part of the <var class="pv">attribute-string</var> are as follows:</p>
<dl class="parml">
<dt class="bold">ASENSITIVE, SENSITIVE, <span class="base">or</span> INSENSITIVE</dt>
<dd>Specifies whether the cursor is asensitive, sensitive, or insensitive
to changes. For more information, see <a href="rbafzmsth2clcu.htm#h2dclcu">DECLARE CURSOR</a>.
<p>If SENSITIVE
is specified, then a <var class="pv">fetch-first-clause</var> must not be specified. If
INSENSITIVE is specified, then an <var class="pv">update-clause</var> must not be specified.</p>
</dd>
<dt class="bold">NO SCROLL <span class="base">or</span> SCROLL</dt>
<dd>Specifies whether the cursor is scrollable or not scrollable. For more
information, see <a href="rbafzmsth2clcu.htm#h2dclcu">DECLARE CURSOR</a>.
</dd>
<dt class="bold">WITHOUT HOLD <span class="base">or</span> WITH HOLD</dt>
<dd>Specifies whether the cursor should be prevented from being closed as
a consequence of a commit operation. For more information, see <a href="rbafzmsth2clcu.htm#h2dclcu">DECLARE CURSOR</a>.
</dd>
<dt class="bold">WITHOUT RETURN <span class="base">or</span> WITH RETURN</dt>
<dd>Specifies whether the result table of the cursor is intended to be used
as a result set that will be returned from a procedure. For more information,
see <a href="rbafzmsth2clcu.htm#h2dclcu">DECLARE CURSOR</a>.
</dd>
<dt class="bold"><var class="pv">fetch-first-clause</var></dt>
<dd>Specifies that a maximum number of rows should be retrieved. For more
information, see <a href="rbafzmstintsel.htm#fetchfirst">fetch-first-clause</a>.
<p>If a <var class="pv">fetch-first-clause</var> is specified, then an <var class="pv">update-clause</var> must not be specified.</p>
</dd>
<dt class="bold"><var class="pv">read-only-clause</var> <span class="base">or</span>
<span class="term"><var class="pv">update-clause</var></span></dt>
<dd>Specifies whether the result table is read-only or updatable. The <var class="pv">update-clause</var> clause must be specified without column names (FOR UPDATE).
For more information, see <a href="rbafzmstintsel.htm#readonly">read-only-clause</a> and <a href="rbafzmstintsel.htm#updateclause">update-clause</a>.
</dd>
<dt class="bold"><var class="pv">optimize-clause</var></dt>
<dd>Specifies that the database manager should assume that the program does
not intend to retrieve more than <var class="pv">integer</var> rows from the result table.
For more information, see <a href="rbafzmstintsel.htm#optimize">optimize-clause</a>.
</dd>
<dt class="bold"><var class="pv">isolation-clause</var></dt>
<dd>Specifies an isolation level at which the select statement is executed.
For more information, see <a href="rbafzmstintsel.htm#isocl">isolation-clause</a>.
</dd>
</dl>
</dd>
<dt class="bold">FROM </dt><a id="idx2963" name="idx2963"></a>
<dd>Introduces the statement string. The statement string is the value of
the specified <var class="pv">string-expression</var> or the identified <var class="pv">variable</var>.
<dl class="parml">
<dt class="bold"><var class="pv">string-expression</var> </dt><a id="idx2964" name="idx2964"></a>
<dd>A <var class="pv">string-expression</var> is any PL/I <var class="pv">string-expression</var> that
yields a character string. SQL expressions that yield a character string are
not allowed. A <var class="pv">string-expression</var> is only allowed in PL/I.
</dd>
<dt class="bold"><var class="pv">variable</var> </dt><a id="idx2965" name="idx2965"></a>
<dd>Identifies a <var class="pv">variable</var> that is declared
in the program in accordance with the rules for declaring character-string,
UTF-16 graphic, or UCS-2 graphic variables. An indicator variable must not
be specified.
</dd>
</dl>
<p>The statement string must be one of the following SQL statements:</p>
<a name="wq1655"></a>
<table id="wq1655" width="100%" summary="" border="0" frame="void" rules="none">
<tbody valign="top">
<tr>
<td width="33%" align="left" valign="top">ALTER</td>
<td width="33%">HOLD LOCATOR</td>
<td width="33%" align="left" valign="top"><var class="pv">select-statement</var></td>
</tr>
<tr>
<td align="left" valign="top">CALL</td>
<td>INSERT</td>
<td align="left" valign="top">SET CURRENT DEBUG MODE</td>
</tr>
<tr>
<td align="left" valign="top">COMMENT</td>
<td>LABEL</td>
<td align="left" valign="top">SET CURRENT DEGREE</td>
</tr>
<tr>
<td align="left" valign="top">COMMIT</td>
<td>LOCK TABLE</td>
<td>SET ENCRYPTION PASSWORD</td>
</tr>
<tr>
<td align="left" valign="top">CREATE</td>
<td>REFRESH TABLE</td>
<td>SET PATH</td>
</tr>
<tr>
<td align="left" valign="top">DECLARE GLOBAL TEMPORARY TABLE</td>
<td>RELEASE SAVEPOINT</td>
<td>SET SCHEMA</td>
</tr>
<tr>
<td align="left" valign="top">DELETE</td>
<td>RENAME</td>
<td align="left" valign="top">SET SESSION AUTHORIZATION</td>
</tr>
<tr>
<td align="left" valign="top">DROP</td>
<td>REVOKE</td>
<td align="left" valign="top">SET TRANSACTION</td>
</tr>
<tr>
<td align="left" valign="top">FREE LOCATOR</td>
<td>ROLLBACK</td>
<td align="left" valign="top">UPDATE</td>
</tr>
<tr>
<td>GRANT</td>
<td>SAVEPOINT</td>
<td>VALUES INTO</td>
</tr>
</tbody>
</table>
<p>The statement string must not: </p>
<ul>
<li>Begin with EXEC SQL and end with END-EXEC or a semicolon (;).</li>
<li>Include references to variables.</li></ul>
</dd>
</dl>
<a name="wq1656"></a>
<h3 id="wq1656"><a href="rbafzmst02.htm#ToC_1189">Notes</a></h3>
<a name="parmark"></a>
<p id="parmark"><a id="idx2966" name="idx2966"></a><a id="idx2967" name="idx2967"></a><span class="bold">Parameter markers:</span> Although a statement string cannot include references
to variables, it may include <span class="italic">parameter markers</span>. These
can be replaced by the values of variables when the prepared statement is
executed. A parameter marker is a question mark (?) that is used where a
variable could be used if the statement string were a static SQL statement.
For an explanation of how parameter markers are replaced by values, see <a href="rbafzmsthopen.htm#hopen">OPEN</a> and <a href="rbafzmsthexeqt.htm#hexeqt">EXECUTE</a>.</p><a id="idx2968" name="idx2968"></a><a id="idx2969" name="idx2969"></a><a id="idx2970" name="idx2970"></a>
<p>There are two types of parameter markers: </p>
<dl class="parml">
<dt class="bold"><span class="bold-italic">Typed parameter marker</span></dt>
<dd>A parameter marker that is specified along with its target data type.
It has the general form:
<pre class="xmp"> <span class="bold">CAST(? AS</span> data-type<span class="bold">)</span></pre> This notation is not a function call, but a &quot;promise&quot;
that the type of the parameter at run time will be of the data type specified
or some data type that can be converted to the specified data type. For example,
in:
<pre class="xmp"> <span class="bold">UPDATE EMPLOYEE</span>
<span class="bold">SET</span> LASTNAME <span class="bold">= TRANSLATE(CAST(</span>? <span class="bold">AS</span> VARCHAR(12)<span class="bold">))</span>
<span class="bold">WHERE</span> EMPNO <span class="bold">= ?</span></pre> the value
of the argument of the TRANSLATE function will be provided at run time. The
data type of that value will either be VARCHAR(12), or some type that can
be converted to VARCHAR(12). For more information, refer to <a href="rbafzmstdatetimearith.htm#cast">CAST specification</a>.
</dd>
<dt class="bold"><span class="bold-italic">Untyped parameter marker</span></dt>
<dd>A parameter marker that is specified without its target data type.
It has the form of a single question mark. The data type of an untyped parameter
marker is provided by context. For example, the untyped parameter marker
in the predicate of the above update statement is the same as the data type
of the EMPNO column.
</dd>
</dl>
<p>Typed parameter markers can be used in dynamic SQL statements wherever
a variable is supported and the data type is based on the promise made in
the CAST function.</p>
<p>Untyped parameters markers can be used in dynamic SQL statements in selected
locations where variables are supported. These locations and the resulting
data type are found in <a href="rbafzmstpreph2.htm#parmtbl">Table 74</a>. The locations are grouped in
this table into expressions, predicates and functions to assist in determining
applicability of an untyped parameter marker. </p>
<a name="parmtbl"></a>
<table id="parmtbl" width="100%" summary="" border="1" frame="hsides" rules="rows">
<caption>Table 74. Untyped Parameter Marker Usage</caption>
<thead valign="bottom">
<tr>
<th id="wq1657" width="50%" align="left" valign="bottom">Untyped Parameter Marker Location</th>
<th id="wq1658" width="49%" align="left" valign="bottom">Data Type</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td colspan="2" align="center" valign="top" headers="wq1657 wq1658"><span class="bold">Expressions (including select list, CASE, and VALUES)</span></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Alone in a select list that is not in a subquery</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Alone in a select list that is in an EXISTS
subquery</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Alone in a select list that is in a subquery</td>
<td align="left" valign="top" headers="wq1658">The data type of the other operand of the
subquery.<sup class="fn"><a href="rbafzmstpreph2.htm#dtparam">78</a></sup></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Alone in a select list that is in a <var class="pv">select-statement</var> of an INSERT statement</td>
<td align="left" valign="top" headers="wq1658">The data type of the associated column of
the target table. <sup class="fn"><a href="rbafzmstpreph2.htm#dtparam">78</a></sup></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Both operands of a single arithmetic operator,
after considering operator precedence and order of operation rules.
<p>Includes
cases such as: </p>
<pre class="xmp"> ? + ? + 10</pre></td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">One operand of a single operator in an arithmetic
expression (not a datetime expression)
<p>Includes cases such as: </p>
<pre class="xmp"> ? + ? * 10</pre></td>
<td align="left" valign="top" headers="wq1658">The data type of the other operand.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Labelled duration within a datetime expression.
(Note that the portion of a labelled duration that indicates the type of
units cannot be a parameter marker.)</td>
<td align="left" valign="top" headers="wq1658">DECIMAL(15,0)</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Any other operand of a datetime expression
(for instance 'timecol + ?' or '? - datecol').</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Any operands of a CONCAT operator</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">As a value on the right hand side of a SET
clause of an UPDATE statement.</td>
<td align="left" valign="top" headers="wq1658">The data type of the column. If the column
is defined as a user-defined distinct type, then it is the source data type
of the user-defined distinct type. <sup class="fn"><a href="rbafzmstpreph2.htm#dtparam">78</a></sup></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">The expression following the CASE keyword
in a simple CASE expression</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">At least one of the result-expressions in
a CASE expression (both Simple and Searched) with the rest of the result-expressions
either untyped parameter marker or NULL.</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Any or all expressions following WHEN in
a simple CASE expression.</td>
<td align="left" valign="top" headers="wq1658">Result of applying the <a href="rbafzmstopcomb.htm#opcomb">Rules for result data types</a> to
the expression following CASE and the expressions following WHEN that are
not untyped parameter markers.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">A result-expression in a CASE expression
(both Simple and Searched) where at least one result-expression is not NULL
and not an untyped parameter marker.</td>
<td align="left" valign="top" headers="wq1658">Result of applying the <a href="rbafzmstopcomb.htm#opcomb">Rules for result data types</a> to
all result-expressions that are other than NULL or untyped parameter markers.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Alone as a column-expression in a single-row
VALUES clause that is not within an INSERT statement.</td>
<td align="left" valign="top" headers="wq1658">Error.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Alone as a column-expression in a single-row
VALUES clause within an INSERT statement.</td>
<td align="left" valign="top" headers="wq1658">The data type of the column. If the column
is defined as a user-defined distinct type, then it is the source data type
of the user-defined distinct type. <sup class="fn"><a href="rbafzmstpreph2.htm#dtparam">78</a></sup></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">As a value on the right side of a SET special
register statement</td>
<td align="left" valign="top" headers="wq1658">The data type of the special register.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">As a value in the INTO clause of the VALUES
INTO statement</td>
<td align="left" valign="top" headers="wq1658">The data type of the associated expression. <sup class="fn"><a href="rbafzmstpreph2.htm#dtparam">78</a></sup></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">As a value in a FREE LOCATOR or HOLD LOCATOR
statement</td>
<td align="left" valign="top" headers="wq1658">Locator.</td>
</tr>
<tr>
<td headers="wq1657">As a value for the password in a SET ENCRYPTION
PASSWORD statement</td>
<td headers="wq1658">VARCHAR(128)</td>
</tr>
<tr>
<td headers="wq1657">As a value for the hint in a SET ENCRYPTION
PASSWORD statement</td>
<td headers="wq1658">VARCHAR(32)</td>
</tr>
<tr>
<td headers="wq1657">As a value in an <var class="pv">insert-multiple-rows</var> of an INSERT statement.</td>
<td headers="wq1658">INTEGER</td>
</tr>
<tr>
<td colspan="2" align="center" valign="top" headers="wq1657 wq1658"><span class="bold">Predicates</span></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Both operands of a comparison operator</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">One operand of a comparison operator where
the other operand is other than an untyped parameter marker or a distinct type.</td>
<td align="left" valign="top" headers="wq1658">The data type of the other operand.<sup class="fn"><a href="rbafzmstpreph2.htm#dtparam">78</a></sup></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">One operand of a comparison operator where
the other operand is a distinct type.</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">All operands of a BETWEEN predicate</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Two operands of a BETWEEN predicate (either
the first and second, or the first and third)</td>
<td align="left" valign="top" headers="wq1658">Same as that of the only non-parameter marker.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Only one operand of a BETWEEN predicate</td>
<td align="left" valign="top" headers="wq1658">Result of applying the <a href="rbafzmstopcomb.htm#opcomb">Rules for result data types</a> on
all operands that are other than untyped parameter markers, except the CCSID
attribute is the CCSID of the value specified at execution time.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">All operands of an IN predicate, for example,
? IN (?,?,?)</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">The first operand of an IN predicate
where the right hand side is a fullselect, for example, ? IN (fullselect).</td>
<td align="left" valign="top" headers="wq1658">Data type of the selected column</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">The first operand of an IN predicate
where the right hand side is not a fullselect, for example, ? IN (?,A,B) or
for example, ? IN (A,?,B,?).</td>
<td align="left" valign="top" headers="wq1658">Result of applying the <a href="rbafzmstopcomb.htm#opcomb">Rules for result data types</a> on
all operands of the IN list (operands to the right of IN keyword) that are
other than untyped parameter markers, except the CCSID attribute is the CCSID
of the value specified at execution time.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Any or all operands of the IN list of the
IN predicate, for example, for example, A IN (?,B,?).</td>
<td align="left" valign="top" headers="wq1658">Result of applying the <a href="rbafzmstopcomb.htm#opcomb">Rules for result data types</a> on
all operands of the IN predicate (operands to the left and right of the IN
predicate) that are other than untyped parameter markers, except the CCSID
attribute is the CCSID of the value specified at execution time.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Any operands in a <var class="pv">row-value-expression</var> of an IN predicate, for example, (c1,?) IN ...</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Any select list items in a subquery if a <var class="pv">row-value-expression</var> is specified in an IN predicate, for example, (c1,c2)
IN (SELECT ?, c1 FROM ...)</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">All three operands of the LIKE predicate.</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">The match expression of the LIKE predicate.</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">The pattern expression of the LIKE predicate.</td>
<td align="left" valign="top" headers="wq1658">Either VARCHAR(32740) or VARGRAPHIC(16370)
or VARBINARY(32740) depending on the data type of the match expression.
<p>For information about using fixed-length variables for the value of the pattern,
see <a href="rbafzmstch2pred.htm#ch2like">LIKE predicate</a>.</p></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">The escape expression of the LIKE predicate.</td>
<td align="left" valign="top" headers="wq1658">Either VARCHAR(1) or VARGRAPHIC(1) or VARBINARY(1)
depending on the data type of the match expression.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Operand of the NULL or DISTINCT
predicate</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td colspan="2" align="center" valign="top" headers="wq1657 wq1658"><span class="bold">Functions</span></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">All operands of COALESCE, IFNULL, LAND, LOR,
MIN, MAX, NULLIF, VALUE, or XOR</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">The first operand of NULLIF</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Any operand of COALESCE, IFNULL, LAND, LOR,
MIN, MAX, NULLIF, VALUE, or XOR where at least one operand is other than an
untyped parameter marker.</td>
<td align="left" valign="top" headers="wq1658">Result of applying the <a href="rbafzmstopcomb.htm#opcomb">Rules for result data types</a> on
all operands that are other than untyped parameter markers.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">The first operand of LOCATE, the
first operand of POSITION, or the second operand of POSSTR.</td>
<td align="left" valign="top" headers="wq1658">Either VARCHAR(32740) or VARGRAPHIC(16370)
or VARBINARY(32740) depending on the data type of the other operand.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">The first operand of VARCHAR_FORMAT</td>
<td align="left" valign="top" headers="wq1658">TIMESTAMP</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">All other operands of all other scalar functions
including user-defined functions.</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1657">Operand of an aggregate function</td>
<td align="left" valign="top" headers="wq1658">Error</td>
</tr>
</tbody>
</table>
<p><span class="bold">Error checking:</span> When a PREPARE statement is executed,
the statement string is parsed and checked for errors. If the statement string
is not valid, a prepared statement is not created and an error is returned.</p>
<p>In local and remote processing, the DLYPREP(*YES) option can cause some
SQL statements to receive "delayed" errors. For example, DESCRIBE, EXECUTE,
and OPEN might receive an SQLCODE that normally occurs during PREPARE processing.</p>
<p><span class="bold">Reference and execution rules:</span> Prepared statements
can be referred to in the following kinds of statements, with the following
restrictions shown: </p>
<pre class="xmp"><span class="bold">Statement</span> <span class="bold">The prepared statement restrictions</span>
DESCRIBE None
DECLARE CURSOR Must be SELECT when the cursor is opened
EXECUTE Must not be SELECT</pre>
<p>A prepared statement can be executed many times. If a prepared statement
is not executed more than once and does not contain parameter markers, it
is more efficient to use the EXECUTE IMMEDIATE statement rather than the PREPARE
and EXECUTE statements.</p>
<p><span class="bold">Prepared statement persistence:</span> All prepared statements
are destroyed when:<sup class="fn"><a id="wq1659" name="wq1659" href="rbafzmstpreph2.htm#wq1660">79</a></sup></p>
<ul>
<li>A CONNECT (Type 1) statement is executed.</li>
<li>A DISCONNECT statement disconnects the connection with which the prepared
statement is associated.</li>
<li>A prepared statement is associated with a release-pending connection and a successful
commit occurs.</li>
<li>The associated scope (job, activation group, or program) of the SQL statement
ends.</li></ul>
<p><span class="bold">Scope of a statement:</span> The scope of <var class="pv">statement-name</var> is the source program in which it is defined. You can only reference
a prepared statement by other SQL statements that are precompiled with the
PREPARE statement. For example, a program called from another separately compiled
program cannot use a prepared statement that was created by the calling program.</p>
<p>The scope of statement-name is also limited to the thread in which the
program that contains the statement is running. For example, if the same program
is running in two separate threads in the same job, the second thread cannot
use a statement that was prepared by the first thread.</p>
<p>Although the scope of a statement is the program in which it is defined,
each package created from the program includes a separate instance of the
prepared statement and more than one prepared statement can exist at run time.
For example, assume a program using CONNECT (Type 2) statements connects to
location X and location Y in the following sequence: </p>
<pre class="xmp"> EXEC SQL <span class="bold">CONNECT TO</span> X;
EXEC SQL <span class="bold">PREPARE</span> S <span class="bold">FROM</span> :hv1;
EXEC SQL <span class="bold">EXECUTE</span> S;
.
.
.
EXEC SQL <span class="bold">CONNECT TO</span> Y;
EXEC SQL <span class="bold">PREPARE</span> S <span class="bold">FROM</span> :hv1;
EXEC SQL <span class="bold">EXECUTE</span> S;</pre>
<p>The second prepare of S prepares another instance of S at Y.</p>
<p>A prepared statement can only be referenced in the same instance of the
program in the program stack, unless CLOSQLCSR(*ENDJOB), CLOSQLCSR(*ENDACTGRP),
or CLOSQLCSR(*ENDSQL) is specified on the CRTSQLxxx commands. </p>
<ul>
<li>If CLOSQLCSR(*ENDJOB) is specified, the prepared statement can be referred
to by any instance of the program (that prepared the statement) on the program
stack. In this case, the prepared statement is destroyed at the end of the
job.</li>
<li>If CLOSQLCSR(*ENDSQL) is specified, the prepared statement can be referred
to by any instance of the program (that prepared the statement) on the program
stack until the last SQL program on the program stack ends. In this case,
the prepared statement is destroyed when the last SQL program on the program
stack ends.</li>
<li>If CLOSQLCSR(*ENDACTGRP) is specified, the prepared statement can be referred
to by all instances of the module in the program that prepared the statement
until the activation group ends. In this case, the prepared statement is destroyed
when the activation group ends.</li></ul><a id="idx2971" name="idx2971"></a>
<p><span class="bold">Allocating the SQL descriptor:</span> If a USING
clause is specified, before the PREPARE statement is executed, the SQL descriptor
must be allocated using the ALLOCATE DESCRIPTOR statement. If the number of
descriptor items allocated is less than the number of result columns, a warning
(SQLSTATE 01005) is returned.</p>
<a name="wq1661"></a>
<h3 id="wq1661"><a href="rbafzmst02.htm#ToC_1190">Examples</a></h3>
<p><span class="italic">Example 1:</span> Prepare and execute a non-<var class="pv">select-statement</var> in a COBOL program. Assume the statement is contained in a variable
HOLDER and that the program will place a statement string into the variable
based on some instructions from the user. The statement to be prepared does
not have any parameter markers. </p>
<pre class="xmp"> EXEC SQL <span class="bold">PREPARE</span> STMT_NAME <span class="bold">FROM</span> :HOLDER END-EXEC.
EXEC SQL <span class="bold">EXECUTE</span> STMT_NAME END-EXEC.</pre>
<p><span class="italic">Example 2:</span> Prepare and execute a non-<var class="pv">select-statement</var> as in example 1, except assume the statement to be prepared can contain
any number of parameter markers. </p>
<pre class="xmp"> EXEC SQL <span class="bold">PREPARE</span> STMT_NAME <span class="bold">FROM</span> :HOLDER END-EXEC.
EXEC SQL <span class="bold">EXECUTE</span> STMT_NAME <span class="bold">USING DESCRIPTOR</span> :INSERT_DA END-EXEC.</pre>
<p> Assume that the following statement is to be prepared: </p>
<pre class="xmp"> <span class="bold">INSERT INTO</span> DEPARTMENT <span class="bold">VALUES(</span>?, ?, ?, ?<span class="bold">)</span></pre><p class="indatacontent"> To insert department number G01 named COMPLAINTS, which has no
manager and reports to department A00, the structure INSERT_DA should have
the following values before executing the EXECUTE statement.</p>
<p></p>
<div class="mmobj">
<img src="rbal3501.gif" alt="INSERT_DA structure" /></div><p class="indatacontent"> <a id="idx2972" name="idx2972"></a> <a id="idx2973" name="idx2973"></a></p>
<hr /><div class="fnnum"><a id="dtparam" name="dtparam">78</a>.</div>
<div class="fntext">If the data type is DATE, TIME, or TIMESTAMP, then
VARCHAR(32740) is used.</div><div class="fnnum"><a id="wq1660" name="wq1660" href="rbafzmstpreph2.htm#wq1659">79</a>.</div>
<div class="fntext">Prepared statements may be cached and not actually
destroyed. However, a cached statement can only be used if the same statement
is prepared again.</div>
<br />
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmsthopen.htm">Previous Page</a> | <a href="rbafzmsthrefresh.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>
Reference in New Issue View Git Blame Copy Permalink