jdbccallablestatement.java

hsqldb是100％java实现的数据库,是一个开放源代码的JAVA数据库 l 具有标准的SQL语法和JAVA接口 l HSQLDB可以自由使用和分发 l 非常简洁和快速的

/* Copyright (c) 2001-2005, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


package org.hsqldb.jdbc;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.SQLException;
import java.util.Calendar;

//#ifdef JAVA2
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Ref;
import java.util.Map;

//#endif JAVA2
import org.hsqldb.HsqlException;
import org.hsqldb.Trace;
import org.hsqldb.lib.IntValueHashMap;

// boucherb@users patch 1.7.2 - CallableStatement impl removed
// from jdbcPreparedStatement and moved here; sundry changes elsewhere to
// comply
// TODO: 1.7.2 Alpha N :: DONE
//       maybe implement set-by-parameter-name.  We have an informal spec,
//       being "@p1" => 1, "@p2" => 2, etc.  Problems: return value is "@p0"
//       and there is no support for registering the return value as an out
//       parameter.
// TODO: 1.8.x
//       engine and client-side mechanisms for adding, retrieving,
//       navigating (and perhaps controlling holdability of) multiple
//       results generated from a single execution.
// boucherb@users 2004-03/04-xx - patch 1.7.2 - some minor code cleanup
//                                            - parameter map NPE correction
//                                            - embedded SQL/SQLCLI client usability
//                                              (parameter naming changed from @n to @pn)
// boucherb@users 2004-04-xx - doc 1.7.2 - javadocs added/updated

/**
 * <!-- start generic documentation -->
 *
 * The interface used to execute SQL stored procedures.  The JDBC API
 * provides a stored procedure SQL escape syntax that allows stored
 * procedures to be called in a standard way for all RDBMSs. This escape
 * syntax has one form that includes a result parameter and one that does
 * not. If used, the result parameter must be registered as an OUT parameter.
 * The other parameters can be used for input, output or both. Parameters
 * are referred to sequentially, by number, with the first parameter being 1.
 * <PRE>
 *   {?= call &lt;procedure-name&gt;[&lt;arg1&gt;,&lt;arg2&gt;, ...]}
 *   {call &lt;procedure-name&gt;[&lt;arg1&gt;,&lt;arg2&gt;, ...]}
 * </PRE>
 * <P>
 * IN parameter values are set using the <code>set</code> methods inherited from
 * {@link PreparedStatement}.  The type of all OUT parameters must be
 * registered prior to executing the stored procedure; their values
 * are retrieved after execution via the <code>get</code> methods provided here.
 * <P>
 * A <code>CallableStatement</code> can return one {@link ResultSet} object or
 * multiple <code>ResultSet</code> objects.  Multiple
 * <code>ResultSet</code> objects are handled using operations
 * inherited from {@link Statement}.
 * <P>
 * For maximum portability, a call's <code>ResultSet</code> objects and
 * update counts should be processed prior to getting the values of output
 * parameters.
 * <P>
 * <!-- end generic documentation -->
 * <!-- start Release-specific documentation -->
 * <div class="ReleaseSpecificDocumentation">
 * <h3>HSQLDB-Specific Information:</h3> <p>
 *
 * Since 1.7.2, the JDBC CallableStatement interface implementation has been
 * broken out of the jdbcPreparedStatement class into this one. <p>
 *
 * With 1.7.2, some of the previously unsupported features of this interface
 * are now supported, such as the parameterName-based setter methods. <p>
 *
 * More importantly, jdbcCallableStatement objects are now backed by a true
 * compiled parameteric representation. Hence, there are now significant
 * performance gains to be had by using a CallableStatement object instead of
 * a Statement object, if a short-running CALL statement is to be executed more
 * than a small number of times.  Moreover, the recent work lays the foundation
 * for work in a subsequenct release to support CallableStatement OUT and
 * IN OUT style parameters, as well as the generation and retrieval of multiple
 * results in response to the execution of a CallableStatement object. <p>
 *
 * For a more in-depth discussion of performance issues regarding 1.7.2
 * prepared and callable statement objects, please see overview section of
 * {@link jdbcPreparedStatement jdbcPreparedStatment}.
 *
 * <hr>
 *
 * As with many DBMS, HSQLDB support for stored procedures is not provided in
 * a completely standard fashion. <p>
 *
 * Beyond the XOpen/ODBC extended scalar functions, stored procedures are
 * typically supported in ways that vary greatly from one DBMS implementation
 * to the next.  So, it is almost guaranteed that the code for a stored
 * procedure written under a specific DBMS product will not work without
 * at least some modification in the context of another vendor's product
 * or even across a single vendor's product lines.  Moving stored procedures
 * from one DBMS product line to another almost invariably involves complex
 * porting issues and often may not be possible at all. <em>Be warned</em>. <p>
 *
 * At present, HSQLDB stored procedures map directly onto the methods of
 * compiled Java classes found on the classpath of the engine at runtime. This
 * is done in a non-standard but fairly efficient way by issuing a class
 * grant (and possibly method aliases) of the form: <p>
 *
 * <PRE class="SqlCodeExample">
 * GRANT ALL ON CLASS &quot;package.class&quot; TO [&lt;user-name&gt; | PUBLIC]
 * CREATE ALIAS &ltcall-alias&gt; FOR &quot;package.class.method&quot; -- optional
 * </PRE>
 *
 * This has the effect of allowing the specified user(s) to access the
 * set of uniquely named public static methods of the specified class,
 * in either the role of SQL functions or stored procedures.

 * For example: <p>
 *
 * <PRE class="SqlCodeExample">
 * CONNECT &lt;admin-user&gt; PASSWORD &lt;admin-user-password&gt;;
 * GRANT ALL ON CLASS &quot;org.myorg.MyClass&quot; TO PUBLIC;
 * CREATE ALIAS sp_my_method FOR &quot;org.myorg.MyClass.myMethod&quot;
 * CONNECT &lt;any-user&gt; PASSWORD &lt;any-user-password&gt;;
 * SELECT &quot;org.myorg.MyClass.myMethod&quot;(column_1) FROM table_1;
 * SELECT sp_my_method(column_1) FROM table_1;
 * CALL 2 + &quot;org.myorg.MyClass.myMethod&quot;(-5);
 * CALL 2 + sp_my_method(-5);
 * </PRE>
 *
 * Please note the use of the term &quot;uniquely named&quot; above. Including
 * 1.7.2, no support is provided to deterministically resolve overloaded
 * method names, and there can be issues with inherited methods as well;
 * currently, it is strongly recommended that developers creating stored
 * procedure library classes for HSQLDB simply avoid designs such that SQL
 * stored procedure calls attempt to resolve to: <p>
 *
 * <ol>
 * <li>inherited public static methods
 * <li>overloaded public static methods
 * </ol>
 *
 * Also, please note that <code>OUT</code> and <code>IN OUT</code> parameters
 * are not yet supported due to some unresolved low level support issues. <p>
 *
 * Including 1.7.2, the HSQLDB stored procedure call mechanism is essentially a
 * thin wrap of the HSQLDB SQL function call mechanism, extended to include the
 * more general HSQLDB SQL expression evaluation mechanism.  In addition to
 * stored procedure calls that resolve directly to Java method invocations, the
 * extention provides the ability to evaluate simple SQL expressions, possibly
 * containing Java method invocations, outside any <code>INSERT</code>,
 * <code>UPDATE</code>, <code>DELETE</code> or <code>SELECT</code> statement
 * context. <p>
 *
 * With HSQLDB, executing a <code>CALL</code> statement that produces an opaque
 * (OTHER) or known scalar object reference has virtually the same effect as:
 *
 * <PRE class="SqlCodeExample">
 * CREATE TABLE DUAL (dummy VARCHAR);
 * INSERT INTO DUAL VALUES(NULL);
 * SELECT &lt;simple-expression&gt; FROM DUAL;
 * </PRE>
 *
 * As a transitional measure, HSQLDB provides the ability to materialize a
 * general result set in response to stored procedure execution.  In this case,
 * the stored procedure's Java method descriptor must specify a return type of
 * java.lang.Object for external use (although at any point in the devlopment
 * cycle, other, proprietary return types may accepted internally for engine
 * development purposes).

 * When HSQLDB detects that the runtime class of the resulting Object is
 * elligible, an automatic internal unwrapping is performed to correctly
 * expose the underlying result set to the client, whether local or remote. <p>
 *
 * Additionally, HSQLDB automatically detects if java.sql.Connection is
 * the class of the first argument of any underlying Java method(s).  If so,
 * then the engine transparently supplies the internal Connection object
 * corresponding to the Session executing the call, adjusting the positions
 * of other arguments to suite the SQL context. <p>
 *
 * The features above are not intended to be permanent.  Rather, the intention
 * is to offer more general and powerful mechanisms in a future release;
 * it is recommend to use them only as a temporary convenience. <p>
 *
 * For instance, one might be well advised to future-proof by writing
 * HSQLDB-specific adapter methods that in turn call the real logic of an
 * underlying generalized JDBC stored procedure library. <p>
 *
 * Here is a very simple example of an HSQLDB stored procedure generating a
 * user-defined result set:
 *
 * <pre class="JavaCodeExample">
 * <span class="JavaKeyWord">package</span> mypackage;
 *
 * <span class="JavaKeyWord">class</span> MyClass {
 *
 *      <span class="JavaKeyWord">public static</span> Object <b>mySp</b>(Connection conn) <span class="JavaKeyWord">throws</span> SQLException {
 *          <span class="JavaKeyWord">return</span> conn.<b>createStatement</b>().<b>executeQuery</b>(<span class="JavaStringLiteral">"select * from my_table"</span>);
 *      }
 * }
 * </pre>
 *
 * Here is a refinement demonstrating no more than the bare essence of the idea
 * behind a more portable style:
 *
 * <pre class="JavaCodeExample">
 * <span class="JavaKeyWord">package</span> mypackage;
 *
 * <span class="JavaKeyWord">import</span> java.sql.ResultSet;
 * <span class="JavaKeyWord">import</span> java.sql.SQLException;
 *
 * <span class="JavaKeyWord">class</span> MyLibraryClass {
 *
 *      <span class="JavaKeyWord">public static</span> ResultSet <b>mySp()</b> <span class="JavaKeyWord">throws</span> SQLException {
 *          <span class="JavaKeyWord">return</span> ctx.<b>getConnection</b>().<b>createStatement</b>().<b>executeQuery</b>(<span class="JavaStringLiteral">"select * from my_table"</span>);
 *      }
 * }
 *
 * //--
 *
 * <span class="JavaKeyWord">package</span> myadaptorpackage;
 *
 * <span class="JavaKeyWord">import</span> java.sql.Connection;
 * <span class="JavaKeyWord">import</span> java.sql.SQLException;
 *
 * <span class="JavaKeyWord">class</span> MyAdaptorClass {
 *
 *      <span class="JavaKeyWord">public static</span> Object <b>mySp</b>(Connection conn) <span class="JavaKeyWord">throws</span> SQLException {
 *          MyLibraryClass.<b>getCtx()</b>.<b>setConnection</b>(conn);
 *          <span class="JavaKeyWord">return</span> MyLibraryClass.<b>mySp</b>();
 *      }
 * }
 * </pre>
 *
 * In a future release, it is intended to provided some new features
 * that will support writing fairly portable JDBC-based stored procedure
 * code: <P>
 *
 * <ul>
 *  <li> Support for the <span class="JavaStringLiteral">"jdbc:default:connection"</span>
 *       standard database connection url. <p>
 *
 *  <li> A well-defined specification of the behaviour of the HSQLDB execution
 *       stack under stored procedure calls. <p>
 *
 *  <li> A well-defined, pure JDBC specification for generating multiple
 *       results from HSQLDB stored procedures for client retrieval.
 * </ul>
 *
 * (boucherb@users)
 * </div>
 * <!-- end Release-specific documentation -->
 *
 * @author boucherb@users
 * @version 1.7.2
 * @since 1.7.2
 * @see jdbcConnection#prepareCall
 * @see jdbcResultSet
 */
public class jdbcCallableStatement extends jdbcPreparedStatement
implements CallableStatement {

    /** parameter name => parameter index */
    private IntValueHashMap parameterNameMap;

    /** parameter index => registered OUT type */

    //    private IntKeyIntValueHashMap outRegistrationMap;

    /**
     * Constructs a new jdbcCallableStatement with the specified connection and
     * result type.
     *
     * @param  c the connection on which this statement will execute
     * @param sql the SQL statement this object represents
     * @param type the type of result this statement will produce
     * @throws HsqlException if the statement is not accepted by the database
     * @throws SQLException if preprocessing by driver fails
     */
    public jdbcCallableStatement(jdbcConnection c, String sql,
                                 int type)
                                 throws HsqlException, SQLException {

        super(c, sql, type);

        String[] names;
        String   name;

        // outRegistrationMap = new IntKeyIntValueHashMap();
        parameterNameMap = new IntValueHashMap();

        if (pmdDescriptor != null && pmdDescriptor.metaData != null) {
            names = pmdDescriptor.metaData.colNames;