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? >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > > -- View this message in context: http://www.nabble.com/SQLException%3A-Syntax-error%3A-Encountered-%22%3CEOF%3E%22-at-line-1%2C-column-19.-tp25638168p25686813.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
