163 lines
11 KiB
HTML
163 lines
11 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="executable statement, nonexecutable statement,
|
||
|
|
static SQL, defined, dynamic SQL, preparation and execution, static select,
|
||
|
|
dynamic select, interactive entry of SQL statements" />
|
||
|
|
<title>How SQL statements are invoked</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="howsql"></a>
|
||
|
|
<h2 id="howsql"><a href="rbafzmst02.htm#ToC_722">How SQL statements are invoked</a></h2>
|
||
|
|
<p><a id="idx1261" name="idx1261"></a><a id="idx1262" name="idx1262"></a></p>
|
||
|
|
<p>The SQL statements described in this chapter are classified as <span class="italic">executable</span> or <span class="italic">nonexecutable</span>. The <span class="italic">Invocation</span> section in the description of each statement indicates whether
|
||
|
|
or not the statement is executable.</p>
|
||
|
|
<p>An <span class="italic">executable statement</span> can be invoked in any of
|
||
|
|
the following ways: </p>
|
||
|
|
<ul>
|
||
|
|
<li>Embedded in an application program</li>
|
||
|
|
<li>Dynamically prepared and executed</li>
|
||
|
|
<li>Issued interactively</li></ul><p class="indatacontent"> </p>
|
||
|
|
<a name="wq1020"></a>
|
||
|
|
<div class="notetitle" id="wq1020">Note:</div>
|
||
|
|
<div class="notebody">Statements embedded in REXX or processed using RUNSQLSTM
|
||
|
|
are prepared and executed dynamically.</div>
|
||
|
|
<p>Depending on the statement, you can use some or all of these methods. The <span class="italic">Invocation</span> section in the description of each statement
|
||
|
|
tells you which methods can be used.</p>
|
||
|
|
<p>A <span class="italic">nonexecutable statement</span> can only be embedded
|
||
|
|
in an application program.</p><a id="idx1263" name="idx1263"></a><a id="idx1264" name="idx1264"></a>
|
||
|
|
<a name="wq1021"></a>
|
||
|
|
<h3 id="wq1021"><a href="rbafzmst02.htm#ToC_723">Embedding a statement in an application program</a></h3>
|
||
|
|
<p>SQL statements can be included in a source program that will be submitted
|
||
|
|
to the precompiler by using the CRTSQLCBL, CRTSQLCBLI, CRTSQLCI, CRTSQLFTN,
|
||
|
|
CRTSQLCPPI, CRTSQLPLI, CRTSQLRPG, or CRTSQLRPGI commands. Such statements
|
||
|
|
are said to be <span class="italic">embedded</span> in the program. An embedded
|
||
|
|
statement can be placed anywhere in the program where a host language statement
|
||
|
|
is allowed. Each embedded statement must be preceded by a keyword (or keywords)
|
||
|
|
to indicate that the statement is an SQL statement:</p>
|
||
|
|
<ul>
|
||
|
|
<li>In C, COBOL, FORTRAN, PL/I, and RPG, each embedded statement must be preceded
|
||
|
|
by the keywords EXEC and SQL.</li>
|
||
|
|
<li>In Java™, each embedded statement must be preceded by the keywords #sql.</li>
|
||
|
|
<li>In REXX, each embedded statement must be preceded by the keyword EXECSQL.</li></ul>
|
||
|
|
<a name="wq1022"></a>
|
||
|
|
<h4 id="wq1022">Executable statements</h4><a id="idx1265" name="idx1265"></a>
|
||
|
|
<p>An executable statement embedded in an application program is executed
|
||
|
|
every time a statement of the host language would be executed if specified
|
||
|
|
in the same place. This means that a statement within a loop is executed every
|
||
|
|
time the loop is executed, and a statement within a conditional construct
|
||
|
|
is executed only when the condition is satisfied.</p>
|
||
|
|
<p>An embedded statement can contain references to variables. A
|
||
|
|
variable referenced in this way can be used in two ways: </p>
|
||
|
|
<ul>
|
||
|
|
<li>As input (the current value of the variable is used in the execution of
|
||
|
|
the statement)</li>
|
||
|
|
<li>As output (the variable is assigned a new value as a result of executing
|
||
|
|
the statement)</li></ul><p class="indatacontent"> In particular, all references to variables in expressions and predicates
|
||
|
|
are effectively replaced by current values of the variables; that is, the
|
||
|
|
variables are used as input. The treatment of other references is described
|
||
|
|
individually for each statement.</p>
|
||
|
|
<p>All executable statements should be followed by a test of an SQL return
|
||
|
|
code. Alternatively, the WHENEVER statement (which is itself nonexecutable)
|
||
|
|
can be used to change the flow of control immediately after the execution
|
||
|
|
of an embedded statement.</p>
|
||
|
|
<p>Objects referenced in SQL statements need not exist when the statements
|
||
|
|
are prepared.</p>
|
||
|
|
<a name="wq1023"></a>
|
||
|
|
<h4 id="wq1023">Nonexecutable statements</h4><a id="idx1266" name="idx1266"></a>
|
||
|
|
<p>An embedded nonexecutable statement is processed only by the precompiler.
|
||
|
|
The precompiler reports any errors encountered in such a statement. The statement
|
||
|
|
is <span class="italic">never</span> executed, and acts as a no-operation if placed
|
||
|
|
among executable statements of the application program. Therefore, do not
|
||
|
|
follow such statements by a test of an SQL return code.</p>
|
||
|
|
<a name="wq1024"></a>
|
||
|
|
<h3 id="wq1024"><a href="rbafzmst02.htm#ToC_726">Dynamic preparation and execution</a></h3><a id="idx1267" name="idx1267"></a>
|
||
|
|
<p>An application program can dynamically build an SQL statement in the form
|
||
|
|
of a character string placed in a variable. In general, the statement is built
|
||
|
|
from some data available to the program (for example, input from a workstation).
|
||
|
|
The statement can be prepared for execution using the (embedded) statement
|
||
|
|
PREPARE and executed by the (embedded) statement EXECUTE. Alternatively, the
|
||
|
|
(embedded) statement EXECUTE IMMEDIATE can be used to prepare and execute
|
||
|
|
a statement in one step. In Java, the statement can be prepared for
|
||
|
|
execution by means of the Statement, PreparedStatement, and CallableStatement
|
||
|
|
classes, and executed by means of their respective execute() methods.</p>
|
||
|
|
<p>A statement that is dynamically prepared must not contain references to
|
||
|
|
variables. It can contain parameter markers instead. See <a href="rbafzmstpreph2.htm#preph2">PREPARE</a> for
|
||
|
|
rules concerning the parameter markers. When the prepared statement is executed,
|
||
|
|
the parameter markers are effectively replaced by the current values of the
|
||
|
|
variables specified in the EXECUTE statement. See <a href="rbafzmsthexeqt.htm#hexeqt">EXECUTE</a> for
|
||
|
|
rules concerning this replacement. After a statement is prepared, it can be
|
||
|
|
executed several times with different values of variables. Parameter markers
|
||
|
|
are not allowed in EXECUTE IMMEDIATE.</p>
|
||
|
|
<p>In C, COBOL, FORTRAN, PL/I, REXX, and RPG, the successful or unsuccessful
|
||
|
|
execution of the statement is indicated by the values returned in the stand-alone
|
||
|
|
SQLCODE or SQLSTATE after the EXECUTE (or EXECUTE IMMEDIATE) statement. The
|
||
|
|
SQL return code should be checked as described above for embedded statements.
|
||
|
|
See the topic <a href="rbafzmstcksql.htm#cksql">SQL return codes</a> for more information. In Java, the successful or unsuccessful execution of the statement is handled
|
||
|
|
by Java Exceptions.</p>
|
||
|
|
<a name="wq1025"></a>
|
||
|
|
<h3 id="wq1025"><a href="rbafzmst02.htm#ToC_727">Static invocation of a select-statement</a></h3><a id="idx1268" name="idx1268"></a>
|
||
|
|
<p>A <span class="italic">select-statement</span> can be included as a part of
|
||
|
|
the (nonexecutable) statement DECLARE CURSOR. Such a statement is executed
|
||
|
|
every time the cursor is opened by means of the (embedded) statement OPEN.
|
||
|
|
After the cursor is open, the result table can be retrieved one row at a time
|
||
|
|
by successive executions of the FETCH statement or multiple rows at a time
|
||
|
|
by using the multiple-row FETCH statement.</p>
|
||
|
|
<p>Used in this way, the <span class="italic">select-statement</span> can contain
|
||
|
|
references to variables. These references are effectively replaced by the
|
||
|
|
values that the variables have at the moment of executing OPEN.</p>
|
||
|
|
<a name="wq1026"></a>
|
||
|
|
<h3 id="wq1026"><a href="rbafzmst02.htm#ToC_728">Dynamic invocation of a select-statement</a></h3><a id="idx1269" name="idx1269"></a>
|
||
|
|
<p>An application program can dynamically build a <span class="italic">select-statement</span> in the form of a character string placed in a variable. In general,
|
||
|
|
the statement is built from some data available to the program (for example,
|
||
|
|
a query obtained from a workstation). The statement is then executed every
|
||
|
|
time the cursor is opened by means of the (embedded) statement OPEN. After
|
||
|
|
the cursor is open, the result table can be retrieved one row at a time by
|
||
|
|
successive executions of the FETCH statement or multiple rows at a time by
|
||
|
|
using the multiple-row FETCH statement.</p>
|
||
|
|
<p>Used in this way, the <span class="italic">select-statement</span> must not
|
||
|
|
contain references to variables. It can instead contain parameter markers.
|
||
|
|
See <a href="rbafzmstpreph2.htm#preph2">PREPARE</a> for rules concerning the parameter markers. The
|
||
|
|
parameter markers are effectively replaced by the values of the variables
|
||
|
|
specified in the OPEN statement. See <a href="rbafzmsthopen.htm#hopen">OPEN</a> for rules concerning
|
||
|
|
this replacement.</p>
|
||
|
|
<a name="wq1027"></a>
|
||
|
|
<h3 id="wq1027"><a href="rbafzmst02.htm#ToC_729">Interactive invocation</a></h3><a id="idx1270" name="idx1270"></a>
|
||
|
|
<p>A capability for entering SQL statements from a workstation is part of
|
||
|
|
the architecture of the database manager. The DB2 UDB for iSeries licensed program provides
|
||
|
|
the Start Structured Query Language (STRSQL) command, the Start Query Manager
|
||
|
|
(STRQM) command, and the Run SQL Script support of iSeries Navigator for this facility.
|
||
|
|
Other products are also available. A statement entered in this way is said
|
||
|
|
to be issued interactively. A statement that cannot be dynamically prepared
|
||
|
|
cannot be issued interactively, with the exception of connection management
|
||
|
|
statements (CONNECT, DISCONNECT, RELEASE, and SET CONNECTION).</p>
|
||
|
|
<p>A statement issued interactively must be an executable statement that does
|
||
|
|
not contain parameter markers or references to variables, because these make
|
||
|
|
sense only in the context of an application program.</p>
|
||
|
|
<hr /><br />
|
||
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmststates.htm">Previous Page</a> | <a href="rbafzmstcksql.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>
|