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