db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.
Date Tue, 29 Sep 2009 21:33:48 GMT
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?
>>>>
>>>>     
>>>>         
>>>   
>>>       
>>
>>     
>
>   


Mime
View raw message