There are a fair number of moving parts here. I hope that the following
makes sense:
1) A SQL procedure can return any number of cursors (aka result sets).
This is true regardless of the language used to code the procedure. That
is, this is true even if the procedure is implemented in C or Cobol or
SQL/PSM. The cursors are not declared in the SQL argument list of the
procedure. Instead, the number of returned cursors is declared via a
special DYNAMIC RESULT SETS clause on the CREATE PROCEDURE statement.
The gory details of this are described in the SQL Standard, part 2,
section 4.27.5 (Result-sets returned by SQL-invoked procedures) and part
2, section 11.51 (<SQL-invoked routine>). The Derby syntax for the
CREATE PROCEDURE statement can be found here:
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#crefsqlj95081
So the answer to your question is that the returned result sets are not
declared as parameters in the CREATE PROCEDURE statement. From the point
of view of the SQL language, these cursors are not parameters. That's
the fundamental reason that they are not retrieved as OUT parameters and
why they don't appear in the argument list of the CALL statement itself.
2) The binding of that SQL language to Java is described in the SQL
Standard part 13. The relevant portion is section 8.6 (Java routine
signature determination), a very long, complicated set of rules. In
matching a Java method to a SQL procedure, the database is supposed to
look for methods whose trailing N arguments are of type ResultSet[]
(where N is the number of cursors declared in the DYNAMIC RESULT SETS
clause). Those trailing N arguments are visible in the Java method
signature, but as noted above, not in the SQL signature of the procedure.
3) Note that JDBC won't even let you try to retrieve the cursors as OUT
parameters. There is no java.sql.Types type code for ResultSet so you
cannot use CallableStatement.registerOutParameter() to register an
output parameter as a ResultSet. Similarly, there is no getXXX() method
for retrieving a column or parameter of this type.
4) The following section in the Derby Reference Guide explains a little
more about how the signatures of SQL procedures are mapped to the
signatures of Java static methods:
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#rrefsqljargmatching
Hope this helps,
-Rick
jstrunk wrote:
I finally got the program to work, but I still dont understand something.
Here is the stored procedure:
public class SupplierProcs {
public static void showSuppliers(ResultSet[] rs) throws SQLException {
String query = "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID =
COFFEES.SUP_ID";
Connection con = DriverManager.getConnection("jdbc:default:connection");
Statement stmt = con.createStatement();
rs[0] = stmt.executeQuery(query);
}
}
Here is how it is called:
public class RunProcs {
public static void main(String args[]) {
String url =
"jdbc:derby://localhost:1527/COFFEEBREAK;user=root;password=rootpw";
Connection con;
try {
con = DriverManager.getConnection(url);
CallableStatement cstmt = con.prepareCall("{call show_suppliers()}");
ResultSet rs = cstmt.executeQuery();
while (rs.next()) {
String supName = rs.getString(1);
String cofName = rs.getString(2);
System.out.println(supName + " supplies " + cofName);
}
cstmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
}
}
This works but it doesn't seem like it should.
The stored procedure takes a parameter: public static void
showSuppliers(ResultSet[] rs)
It is called without a parameter: call show_suppliers()}");
Shouldnt it be called with an OUT parameter to return the ResultSet like?
call show_suppliers(?)}");
jstrunk wrote:
I have been putting the jar file in RunProc's class path with the Eclipse
Build Path option.
Your reference in the DevGuide gives the clearest explanation of what's
going on that I have seen.
I will try to work through all of that.
Rick Hillegas-2 wrote:
One thing I notice is that the program stores the jar file in the
database but does not wire the jar file into the database classpath. If
you put the jar file on the VM's classpath, then you don't need this
step. However, if the jar file is stored in the database, then you need
to use SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY to wire the jar file into
the database classpath property "derby.database.classpath". See
http://db.apache.org/derby/docs/10.5/devguide/devguide-single.html#cdevdeploy30736
Hope this helps,
-Rick
jstrunk wrote:
OK, thanks.
1. I put the parens back in ran this program to store the procedure
import java.sql.*;
public class InstallProcs {
public static void main(String args[]) {
String url =
"jdbc:derby://localhost:1527/COFFEEBREAK;user=root;password=rootpw";
Connection con;
String installJar;
String createProc;
installJar = "{call sqlj.install_jar(" +
"'file:/C:/Users/jim/DerbyWorkspace/SQLRoutines.jar',
'routines_jar', 0)}";
createProc = "create procedure show_suppliers()\n" +
"reads sql data\n" +
"dynamic result sets 1\n" +
"external name
'routines_jar:SuppliersProcs.showSuppliers'\n" +
"language java parameter style java";
Statement stmt;
try {
con = DriverManager.getConnection(url);
stmt = con.createStatement();
stmt.executeUpdate(installJar);
stmt.executeUpdate(createProc);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
}
}
2. Then I ran this program to try to execute the stored procedure
import java.sql.*;
public class RunProcs {
public static void main(String args[]) {
String url =
"jdbc:derby://localhost:1527/COFFEEBREAK;user=root;password=rootpw";
Connection con;
try {
con = DriverManager.getConnection(url);
CallableStatement cstmt =con.prepareCall("{call
root.show_suppliers()}");
ResultSet rs = cstmt.executeQuery();
String supName = rs.getString(1);
String cofName = rs.getString(2);
System.out.println(supName + " supplies " + cofName);
cstmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
}
}
3. RunProcs gets this error
SQLException: The class 'routines_jar:SuppliersProcs' does not exist or
is
inaccessible. This can happen if the class is not public.
java.sql.SQLSyntaxErrorException: The class
'routines_jar:SuppliersProcs'
does not exist or is inaccessible. This can happen if the class is not
public.
at
org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
at org.apache.derby.client.am.Connection.prepareCall(Unknown Source)
at RunProcs.main(RunProcs.java:17)
Caused by: org.apache.derby.client.am.SqlException: The class
'routines_jar:SuppliersProcs' does not exist or is inaccessible. This
can
happen if the class is not public.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at
org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown
Source)
at
org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown
Source)
at org.apache.derby.client.net.NetStatementReply.readPrepare(Unknown
Source)
at org.apache.derby.client.net.StatementReply.readPrepare(Unknown
Source)
at org.apache.derby.client.net.NetStatement.readPrepare_(Unknown
Source)
at org.apache.derby.client.am.Statement.readPrepare(Unknown Source)
at
org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInput(Unknown
Source)
at
org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown
Source)
at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
at org.apache.derby.client.am.Connection.prepareCallX(Unknown Source)
... 2 more
Caused by: org.apache.derby.client.am.SqlException: Java exception:
'routines_jar:SuppliersProcs: java.lang.ClassNotFoundException'.
... 13 more
Rick Hillegas-2 wrote:
Note that the parentheses are required by the ANSI/ISO SQL Standard.
See
part 2 of the standard, section 11.51 <SQL-invoked routine>. Derby
enforces this standard syntax:
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#crefsqlj95081
Hope this helps,
-Rick
jstrunk wrote:
At my level, its too hard to understand the demo programs. I did find
several
places online that said you DO NOT code the parens on the procedure
name
if
there are no parameters. So I took that out. Here is my code now and
the
error I get now.
import java.sql.*;
public class InstallProcs {
public static void main(String args[]) {
String url =
"jdbc:derby://localhost:1527/COFFEEBREAK;user=root;password=rootpw";
Connection con;
String installJar;
String createProc;
installJar = "{call sqlj.install_jar(" +
"'file:/C:/Users/jim/DerbyWorkspace/SQLRoutines.jar',
'routines_jar', 0)}";
createProc = "create procedure show_suppliers " +
"reads sql data " +
"dynamic result sets 1 " +
"external name 'routines_jar:SuppliersProcs.showSuppliers'
" +
"language java parameter style java";
Statement stmt;
try {
con = DriverManager.getConnection(url);
stmt = con.createStatement();
stmt.executeUpdate(installJar);
stmt.executeUpdate(createProc);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
}
}
SQLException: Syntax error: Encountered "reads" at line 1, column 33.
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "reads" at
line
1, column 33.
at
org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
at org.apache.derby.client.am.Statement.executeUpdate(Unknown Source)
at InstallProcs.main(InstallProcs.java:35)
Caused by: org.apache.derby.client.am.SqlException: Syntax error:
Encountered "reads" at line 1, column 33.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at
org.apache.derby.client.am.Statement.completeExecuteImmediate(Unknown
Source)
at
org.apache.derby.client.net.NetStatementReply.parseEXCSQLIMMreply(Unknown
Source)
at
org.apache.derby.client.net.NetStatementReply.readExecuteImmediate(Unknown
Source)
at
org.apache.derby.client.net.StatementReply.readExecuteImmediate(Unknown
Source)
at
org.apache.derby.client.net.NetStatement.readExecuteImmediate_(Unknown
Source)
at org.apache.derby.client.am.Statement.readExecuteImmediate(Unknown
Source)
at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
at org.apache.derby.client.am.Statement.executeUpdateX(Unknown
Source)
... 2 more
jstrunk wrote:
I am using Eclipse 3.5.0 with Derby 10.5.3.0 (not the Derby Plugin).
I
created an SQLJ stored procedure and stored it. When I try to execute
it
I
get SQLException: Syntax error: Encountered "<EOF>" at line 1, column
19.
That is the only error information I get. How can I get a listing of
what
SQL thinks the procedure looks like up to the point where the error
occurs?
