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?
