144 lines
11 KiB
HTML
144 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="bind, SQL (Structured Query Language), prepared,
|
||
|
|
SQL statements, static SQL, SQL (structured query language), description,
|
||
|
|
dynamic SQL, extended dynamic SQL, interactive SQL facility,
|
||
|
|
SQL (Structured Query language), interactive SQL, call level interface (CLI),
|
||
|
|
Open Database Connectivity (ODBC), Java Database Connectivity (JDBC),
|
||
|
|
Embedded SQL for Java (SQLJ), OLE DB, ADO, .NET" />
|
||
|
|
<title>Structured Query Language</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="sqlcon"></a>
|
||
|
|
<h2 id="sqlcon"><a href="rbafzmst02.htm#ToC_17">Structured Query Language</a></h2><a id="idx2" name="idx2"></a><a id="idx3" name="idx3"></a>
|
||
|
|
<p>Structured Query Language (SQL) is a standardized language for defining
|
||
|
|
and manipulating data in a relational database. In accordance with the relational
|
||
|
|
model of data, the database is perceived as a set of tables, relationships
|
||
|
|
are represented by values in tables, and data is retrieved by specifying a
|
||
|
|
result table that can be derived from one or more base tables.</p>
|
||
|
|
<p>SQL statements are executed by a database manager. One of the functions
|
||
|
|
of the database manager is to transform the specification of a result table
|
||
|
|
into a sequence of internal operations that optimize data retrieval. This
|
||
|
|
transformation occurs when the SQL statement is <span class="italic">prepared</span>. This transformation is also known as <span class="italic">binding</span>.</p><a id="idx4" name="idx4"></a>
|
||
|
|
<p>All executable SQL statements must be prepared before they can be executed.
|
||
|
|
The result of preparation is the executable or <span class="italic">operational
|
||
|
|
form</span> of the statement. The method of preparing an SQL statement and the
|
||
|
|
persistence of its operational form distinguish <span class="italic">static</span> SQL from <span class="italic">dynamic</span> SQL.</p>
|
||
|
|
<a name="staticsql"></a>
|
||
|
|
<h3 id="staticsql"><a href="rbafzmst02.htm#ToC_18">Static SQL</a></h3><a id="idx5" name="idx5"></a><a id="idx6" name="idx6"></a>
|
||
|
|
<p>The source form of a <span class="italic">static</span> SQL statement is embedded
|
||
|
|
within an application program written in a host language such as COBOL, C,
|
||
|
|
or Java™. The statement is prepared before the program is executed and the
|
||
|
|
operational form of the statement persists beyond the execution of the program.</p>
|
||
|
|
<p>A source program containing static SQL statements must be processed by
|
||
|
|
an SQL precompiler before it is compiled. The precompiler checks the syntax
|
||
|
|
of the SQL statements, turns them into host language comments, and generates
|
||
|
|
host language statements to call the database manager.</p>
|
||
|
|
<p>The preparation of an SQL application program includes precompilation,
|
||
|
|
the preparation of its static SQL statements, and compilation of the modified
|
||
|
|
source program.</p>
|
||
|
|
<a name="dynamicsql"></a>
|
||
|
|
<h3 id="dynamicsql"><a href="rbafzmst02.htm#ToC_19">Dynamic SQL</a></h3><a id="idx7" name="idx7"></a><a id="idx8" name="idx8"></a>
|
||
|
|
<p>Programs containing embedded <span class="italic">dynamic</span> SQL statements
|
||
|
|
must be precompiled like those containing static SQL, but unlike static SQL,
|
||
|
|
the dynamic SQL statements are constructed and prepared at run time. The source
|
||
|
|
form of the statement is a character or graphic string that is passed to the
|
||
|
|
database manager by the program using the static SQL PREPARE or EXECUTE IMMEDIATE
|
||
|
|
statement. The operational form of the statement persists for the duration
|
||
|
|
of the connection or until the last SQL program leaves the call stack.</p>
|
||
|
|
<p>SQL statements embedded in a REXX application are dynamic SQL statements.
|
||
|
|
SQL statements submitted to the interactive SQL facility and to the Call Level
|
||
|
|
Interface (CLI) are also dynamic SQL statements.</p>
|
||
|
|
<a name="exdynamicsql"></a>
|
||
|
|
<h3 id="exdynamicsql"><a href="rbafzmst02.htm#ToC_20">Extended Dynamic SQL</a></h3><a id="idx9" name="idx9"></a><a id="idx10" name="idx10"></a>
|
||
|
|
<p>An extended dynamic SQL statement is neither fully static nor fully dynamic.
|
||
|
|
The QSQPRCED API provides users with extended dynamic SQL capability. Like
|
||
|
|
dynamic SQL, statements can be prepared, described, and executed using this
|
||
|
|
API. Unlike dynamic SQL, SQL statements prepared into a package by this API
|
||
|
|
persist until the package or statement is explicitly dropped. For more information,
|
||
|
|
see the <a href="../apis/file1.htm">i5/OS™ APIs</a> information in the <span class="bold">Programming</span> category of the iSeries Information Center.</p>
|
||
|
|
<a name="intsql"></a>
|
||
|
|
<h3 id="intsql"><a href="rbafzmst02.htm#ToC_21">Interactive SQL</a></h3><a id="idx11" name="idx11"></a><a id="idx12" name="idx12"></a>
|
||
|
|
<p>An interactive SQL facility is associated with every database manager.
|
||
|
|
Essentially, every interactive SQL facility is an SQL application program
|
||
|
|
that reads statements from a workstation, prepares and executes them dynamically,
|
||
|
|
and displays the results to the user. Such SQL statements are said to be issued <span class="italic">interactively</span>.</p>
|
||
|
|
<p>The interactive facilities for DB2 UDB for iSeries are invoked by the STRSQL command,
|
||
|
|
the STRQM command, or the Run SQL Script support of iSeries Navigator. For more information
|
||
|
|
about the interactive facilities for SQL, see the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> and <a href="../books/sc415212.pdf">Query Manager Use</a>
|
||
|
|
<img src="wbpdf.gif" alt="Link to PDF" />books.</p>
|
||
|
|
<a name="cliodbc"></a>
|
||
|
|
<h3 id="cliodbc"><a href="rbafzmst02.htm#ToC_22">SQL Call Level Interface (CLI) and Open Database Connectivity (ODBC)</a></h3><a id="idx13" name="idx13"></a><a id="idx14" name="idx14"></a><a id="idx15" name="idx15"></a><a id="idx16" name="idx16"></a>
|
||
|
|
<p>The DB2® Call Level Interface is an application programming interface in which
|
||
|
|
functions are provided to application programs to process dynamic SQL statements. DB2 CLI
|
||
|
|
allows users of any of the ILE languages to access SQL functions directly
|
||
|
|
through procedure calls to a service program provided by DB2 UDB for iSeries. CLI programs
|
||
|
|
can also be compiled using an Open Database Connectivity (ODBC) Software Developer's
|
||
|
|
Kit, available from Microsoft® or other vendors, enabling access
|
||
|
|
to ODBC data sources. Unlike using embedded SQL, no precompilation is required.
|
||
|
|
Applications developed using this interface may be executed on a variety of
|
||
|
|
databases without being compiled against each of the databases. Through the
|
||
|
|
interface, applications use procedure calls at execution time to connect to
|
||
|
|
databases, to issue SQL statements, and to get returned data and status information.</p>
|
||
|
|
<p>The DB2 CLI interface provides many features not available in embedded SQL.
|
||
|
|
For example:</p>
|
||
|
|
<ul>
|
||
|
|
<li>CLI provides function calls which support a consistent way to query and
|
||
|
|
retrieve database system catalog information across the DB2 family of database
|
||
|
|
management systems. This reduces the need to write application server specific
|
||
|
|
catalog queries.</li>
|
||
|
|
<li>Stored procedures called from application programs written using CLI can
|
||
|
|
return result sets to those programs.</li></ul>
|
||
|
|
<p>For a complete description of all the available functions, and their syntax,
|
||
|
|
see <a href="../cli/rzadpkickoff.htm">SQL Call Level Interfaces (ODBC)</a> book.</p>
|
||
|
|
<a name="jdbcsqlj"></a>
|
||
|
|
<h3 id="jdbcsqlj"><a href="rbafzmst02.htm#ToC_23">Java DataBase Connectivity (JDBC) and embedded SQL for Java (SQLJ)
|
||
|
|
programs</a></h3><a id="idx17" name="idx17"></a><a id="idx18" name="idx18"></a><a id="idx19" name="idx19"></a><a id="idx20" name="idx20"></a>
|
||
|
|
<p>DB2 UDB for iSeries implements two standards-based Java programming APIs: Java Database
|
||
|
|
Connectivity (JDBC) and embedded SQL for Java (SQLJ). Both can be used to create Java applications and applets that access DB2.</p>
|
||
|
|
<p>JDBC calls are translated to calls to DB2 CLI through Java native methods. You can access iSeries databases
|
||
|
|
through two JDBC drivers: IBM Developer Kit for Java driver or IBM Toolbox for Java JDBC driver. For specific
|
||
|
|
information about the IBM Toolbox for Java JDBC driver, see <a href="../rzahh/page1.htm">IBM® Toolbox
|
||
|
|
for Java</a>.</p>
|
||
|
|
<p>Static SQL cannot be used by JDBC. SQLJ applications use JDBC as a foundation
|
||
|
|
for such tasks as connecting to databases and handling SQL errors, but can
|
||
|
|
also contain embedded static SQL statements in the SQLJ source files. An SQLJ
|
||
|
|
source file has to be translated with the SQLJ translator before the resulting Java source code can be compiled.</p>
|
||
|
|
<p>For more information about JDBC and SQLJ applications, refer to the <a href="../rzaha/whatitis.htm">Developer Kit for Java</a> book.</p>
|
||
|
|
<a name="oledb"></a>
|
||
|
|
<h3 id="oledb"><a href="rbafzmst02.htm#ToC_24">OLE DB and ADO (ActiveX Data Object)</a></h3><a id="idx21" name="idx21"></a><a id="idx22" name="idx22"></a><a id="idx23" name="idx23"></a>
|
||
|
|
<p>iSeries Access for Windows includes OLE DB Providers, along with the Programmer's Toolkit to
|
||
|
|
allow iSeries™ client/server application development quick and easy from the Windows® client PC. For more information, refer to the iSeries Access for Windows OLE DB provider in the iSeries Information Center.</p>
|
||
|
|
<a name="dotnet"></a>
|
||
|
|
<h3 id="dotnet"><a href="rbafzmst02.htm#ToC_25">.NET</a></h3><a id="idx24" name="idx24"></a><a id="idx25" name="idx25"></a>
|
||
|
|
<p>iSeries Access for Windows include a .NET Provider to allow iSeries client/server application development
|
||
|
|
quick and easy from the Windows client PC. For more information,
|
||
|
|
refer to the iSeries Access for Windows .NET provider in the iSeries Information Center.</p>
|
||
|
|
<hr /><br />
|
||
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstrelationaldb.htm">Previous Page</a> | <a href="rbafzmstcollex.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>
|