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 Fri, 02 Oct 2009 21:55:42 GMT

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.


Mime
View raw message