101 lines
5.6 KiB
HTML
101 lines
5.6 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="task" />
|
|
<meta name="DC.Title" content="Call stored procedures" />
|
|
<meta name="abstract" content="Use stored procedures to improve the performance and function of an ODBC application." />
|
|
<meta name="description" content="Use stored procedures to improve the performance and function of an ODBC application." />
|
|
<meta name="DC.Relation" scheme="URI" content="rzaikdirectcodeodbcapis.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="callingsp" />
|
|
<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>Call stored procedures</title>
|
|
</head>
|
|
<body id="callingsp"><a name="callingsp"><!-- --></a>
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|
<h1 class="topictitle1">Call stored procedures</h1>
|
|
<div><p>Use stored procedures to improve the performance and function of
|
|
an ODBC application.</p>
|
|
<div class="section"> <p>Any iSeries™ program
|
|
can act as a stored procedure. iSeries stored procedures support input,
|
|
input/output and output parameters. They also support returning result sets,
|
|
both single and multiple. The stored procedure program can return a result
|
|
set by specifying a cursor to return (from an embedded SQL statement) or by
|
|
specifying an array of values. See <a href="rzaikodbcstoredproc.htm#odbcstoredproc">Stored procedures</a> for
|
|
more information.</p>
|
|
<p>To call a stored procedure, complete the following
|
|
steps:</p>
|
|
</div>
|
|
<ol><li class="stepexpand"><span>Verify that the stored procedure has been declared by using the <span class="keyword">SQL</span> statement CREATE PROCEDURE. </span> <div class="note"><span class="notetitle">Detail:</span> CREATE PROCEDURE should be executed
|
|
only once for the life of the stored procedure. DROP PROCEDURE can be used
|
|
to delete the procedure without deleting the procedure's program. DECLARE
|
|
PROCEDURE also can be used, but this method has several disadvantages. The <em>Database
|
|
Programming</em> book contains additional information about DECLARE PROCEDURE.
|
|
View an HTML online version of the book, or print a PDF version, from the <a href="../db2/rbafzmst02.htm">DB2
|
|
Universal Database™ for iSeries SQL Reference</a> topic in the <strong>iSeries Information
|
|
Center</strong>.</div>
|
|
</li>
|
|
<li class="stepexpand"><span>Prepare the call of the stored procedure by using <strong><span class="keyword">SQL</span> Prepare</strong>.</span></li>
|
|
<li class="stepexpand"><span>Bind the parameters for input and output parameters.</span></li>
|
|
<li class="stepexpand"><span>Execute the call to the stored procedure.</span></li>
|
|
<li class="stepexpand"><span>Retrieve the result set (if one is returned)</span></li>
|
|
</ol>
|
|
<div class="example"><p>In this C example, a COBOL program named NEWORD which resided
|
|
in the default iSeries library,
|
|
is called. A value in a field named <strong>szCustId</strong> is passed, and it returns
|
|
a value to a field named <strong>szName</strong>. </p>
|
|
<pre>SQLRETURN rc;
|
|
HSTMT hstmt;
|
|
SQLCHAR Query[320];
|
|
SQLCHAR szCustId[10];
|
|
SQLCHAR szName[30];
|
|
SQLINTEGER strlen_or_indPtr = SQL_NTS, strlen_or_indPtr2 = SQL_NTS;
|
|
|
|
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
|
|
|
|
// Create the stored procedure definition.
|
|
// The create procedure could be moved to the application's
|
|
// install program so that it is only executed once.
|
|
strcpy(Query,"CREATE PROCEDURE NEWORD (:CID IN CHAR(10), :NAME OUT CHAR(30) )");
|
|
strcat(Query," (EXTERNAL NAME NEWORD LANGUAGE COBOL GENERAL WITH NULLS)");
|
|
|
|
// Create the stored procedure
|
|
rc = SQLExecDirect(hstmt, (unsigned char *)Query, SQL_NTS);
|
|
|
|
strcpy(Query, "CALL NEWORD(?,?)");
|
|
|
|
// Prepare the stored procedure call
|
|
rc = SQLPrepare(hstmt, (unsigned char *)Query, SQL_NTS);
|
|
|
|
// Bind the parameters
|
|
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
|
|
10, 0, szCustId, 11, &strlen_or_intPtr);
|
|
|
|
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR,
|
|
30, 0, szName, 31, &strlen_or_indPtr2);
|
|
strcpy (szCustId,"0000012345");
|
|
// Execute the stored procedure
|
|
rc = SQLExecute(hstmt);</pre>
|
|
</div>
|
|
</div>
|
|
<div>
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzaikdirectcodeodbcapis.htm" title="Many PC applications make ODBC calls that allow the user to seamlessly access data on different platforms. Before you begin developing your own application with ODBC APIs, you should understand how an ODBC application connects to and exchanges information with a database server.">Code directly to ODBC APIs</a></div>
|
|
</div>
|
|
</div>
|
|
</body>
|
|
</html> |