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?
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>>
>
>
--
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.-tp25638168p25722084.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
