119 lines
6.1 KiB
HTML
119 lines
6.1 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="concept" />
|
||
|
|
<meta name="DC.Title" content="DB2GENERAL parameter style" />
|
||
|
|
<meta name="abstract" content="When coding a Java stored procedure that uses the DB2GENERAL parameter style, you must use these conventions." />
|
||
|
|
<meta name="description" content="When coding a Java stored procedure that uses the DB2GENERAL parameter style, you must use these conventions." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="javaproc.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="db2genpm" />
|
||
|
|
<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>DB2GENERAL parameter style</title>
|
||
|
|
</head>
|
||
|
|
<body id="db2genpm"><a name="db2genpm"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">DB2GENERAL parameter style</h1>
|
||
|
|
<div><p>When coding a Java™ stored procedure that uses the DB2GENERAL
|
||
|
|
parameter style, you must use these conventions.</p>
|
||
|
|
<ul><li>The class that defines a Java stored procedure must <em>extend</em>,
|
||
|
|
or be a subclass of, the Java com.ibm.db2.app.StoredProc class.</li>
|
||
|
|
<li>The Java method must be a public void instance method.</li>
|
||
|
|
<li>The parameters of the Java method must be SQL-compatible types.</li>
|
||
|
|
<li>A Java method
|
||
|
|
may test for a SQL NULL value using the isNull method.</li>
|
||
|
|
<li>The Java method must explicitly set the return parameters
|
||
|
|
using the set method.</li>
|
||
|
|
<li>The Java method may access the current database using the
|
||
|
|
getConnection method.</li>
|
||
|
|
</ul>
|
||
|
|
<p>A class that includes a Java stored procedure must extend the class,
|
||
|
|
com.ibm.db2.app.StoredProc. Java stored procedures are public instance
|
||
|
|
methods. Within the classes, the stored procedures are identified by their
|
||
|
|
method name and signature. When you call a stored procedure, its signature
|
||
|
|
is generated dynamically, based on the variable types defined by the CREATE
|
||
|
|
PROCEDURE statement.</p>
|
||
|
|
<p>The com.ibm.db2.app.StoredProc class provides the isNull method, which
|
||
|
|
permits a Java method to determine if an input parameter is an
|
||
|
|
SQL NULL. The com.ibm.db2.app.StoredProc class also provides set...( ) methods
|
||
|
|
that set output parameters. You must use these methods to set output parameters.
|
||
|
|
If you do not set an output parameter, then the output parameter returns the
|
||
|
|
SQL NULL value.</p>
|
||
|
|
<p>The com.ibm.db2.app.StoredProc class provides the following routine to
|
||
|
|
fetch a JDBC connection to the embedding application context. A connection
|
||
|
|
to the embedding application context is accessed using the following JDBC
|
||
|
|
call:</p>
|
||
|
|
<blockquote><pre>public Java.sql.Connection getConnection( )</pre>
|
||
|
|
</blockquote>
|
||
|
|
<p>This connection then runs SQL statements with JDBC APIs.</p>
|
||
|
|
<p>The following is a small stored procedure with one input and two outputs.
|
||
|
|
It processes the given SQL query, and returns both the number of rows in the
|
||
|
|
result and the SQLSTATE.</p>
|
||
|
|
<p><strong>Example:</strong> Stored procedure with one input and two outputs</p>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> By using the
|
||
|
|
code examples, you agree to the terms of the <a href="codedisclaimer.htm">Code license and disclaimer information</a>.</div>
|
||
|
|
<blockquote><pre>package mystuff;
|
||
|
|
|
||
|
|
import com.ibm.db2.app.*;
|
||
|
|
import java.sql.*;
|
||
|
|
public class sample2 extends StoredProc {
|
||
|
|
public void donut(String query, int rowCount,
|
||
|
|
String sqlstate) throws Exception {
|
||
|
|
try {
|
||
|
|
Statement s=getConnection().createStatement();
|
||
|
|
ResultSet r=s.executeQuery(query);
|
||
|
|
int counter=0;
|
||
|
|
while(r.next()){
|
||
|
|
counter++;
|
||
|
|
}
|
||
|
|
r.close(); s.close();
|
||
|
|
set(2, counter);
|
||
|
|
}catch(SQLException x){
|
||
|
|
set(3, x.getSQLState());
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}</pre>
|
||
|
|
</blockquote>
|
||
|
|
<p>To return a result set in procedures that use the DB2GENERAL parameter
|
||
|
|
style, the result set and the responding statement must be left open at the
|
||
|
|
end of the procedure. The result set that is returned must be closed by the
|
||
|
|
client application. If multiple results sets are returned, they are returned
|
||
|
|
in the order in which they were opened. For example, the following stored
|
||
|
|
procedure returns two results sets.</p>
|
||
|
|
<p><strong>Example:</strong> Stored procedure that returns two results sets</p>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> By using the
|
||
|
|
code examples, you agree to the terms of the <a href="codedisclaimer.htm">Code license and disclaimer information</a>.</div>
|
||
|
|
<blockquote><pre>public void returnTwoResultSets() throws Exception
|
||
|
|
{
|
||
|
|
// get caller's connection to the database; inherited from StoredProc
|
||
|
|
Connection con = getConnection ();
|
||
|
|
Statement stmt1 = con.createStatement ();
|
||
|
|
String sql1 = "select value from table01 where index=1";
|
||
|
|
ResultSet rs1 = stmt1.executeQuery(sql1);
|
||
|
|
Statement stmt2 = con.createStatement();
|
||
|
|
String sql2 = "select value from table01 where index=2";
|
||
|
|
ResultSet rs2 = stmt2.executeQuery(sql2);
|
||
|
|
}</pre>
|
||
|
|
</blockquote>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="javaproc.htm" title="When using Java to write stored procedures, you can use two possible parameter passing styles.">Java stored procedures</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|