Download HyperSQL User Guide

Transcript
SQL-Invoked Routines
There are two ways to use the JDBC Connection object.
1. Define the Java method with a Connection parameter as the first parameter. This parameter is "hidden" and only
visible to the engine. The rest of the parameters, if any, are used to choose the method according to the required
types of parameters.
2. Use the SQL/JRT Standard "jdbc:default:connection" method. With this approach, the Java method
does not include a Connection parameter. In the method body, the connection is established with a method call to
DriverManager, as in the example below:
Connection con = DriverManager.getConnection("jdbc:default:connection");
Both methods return a connection that is based on the current session. This connection has some extra properties, for
example, the Close() method does not actually close it.
An example of an SQL PROCEDURE with its Java method definition is given below. The CREATE PROCEDURE
statement is the same with or without the Connection parameter:
CREATE PROCEDURE proc1(IN P1 INT, IN P2 INT, OUT P3 INT)
SPECIFIC P2 LANGUAGE JAVA DETERMINISTIC MODIFIES SQL DATA EXTERNAL NAME
'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procTest2'");
In the first example, the "jdbc:default:connection" method is used. In the second example, a connection
parameter is used
public static void procTest2(int p1, int p2,
Integer[] p3) throws java.sql.SQLException {
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
java.sql.Statement stmt = conn.createStatement();
stmt.execute("INSERT INTO MYTABLE VALUES(" + p1 + ",'test1')");
stmt.execute("INSERT INTO MYTABLE VALUES(" + p2 + ",'test2')");
java.sql.ResultSet rs = stmt.executeQuery("select * from MYTABLE");
java.sql.ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
p3[0] = Integer.valueOf(cols);
rs.close();
stmt.close();
}
//
//
//
alternative declaration with Connection parameter
public static void procTest2(Connection conn, int p1, int p2,
Integer[] p3) throws java.sql.SQLException {
When the stored procedure is called by the user's program, the value of the OUT parameter can be read after the call.
// a CallableStatement is used to prepare the call
// the OUT parameter contains the return value
CallableStatement c = conn.prepareCall("call proc1(1,2,?)");
c.execute();
int value = c.getInt(1);
Legacy Support
The legacy HyperSQL statement, CREATE ALIAS <name> FOR <fully qualified Java method
name> is no longer supported directly. It is supported when importing databases and translates to a special CREATE
FUNCTION <name> statement that creates the function in the PUBLIC schema.
151