db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jstrunk <jstr...@stny.rr.com>
Subject Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.
Date Wed, 30 Sep 2009 18:53:48 GMT

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.


Mime
View raw message