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 Mon, 05 Oct 2009 13:51:17 GMT
There are a fair number of moving parts here. I hope that the following 
makes sense:

1) A SQL procedure can return any number of cursors (aka result sets). 
This is true regardless of the language used to code the procedure. That 
is, this is true even if the procedure is implemented in C or Cobol or  
SQL/PSM. The cursors are not declared in the SQL argument list of the 
procedure. Instead, the number of returned cursors is declared via a 
special DYNAMIC RESULT SETS clause on the CREATE PROCEDURE statement. 
The gory details of this are described in the SQL Standard, part 2, 
section 4.27.5 (Result-sets returned by SQL-invoked procedures) and part 
2, section 11.51 (<SQL-invoked routine>). The Derby syntax for the 
CREATE PROCEDURE statement can be found here: 
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#crefsqlj95081

So the answer to your question is that the returned result sets are not 
declared as parameters in the CREATE PROCEDURE statement. From the point 
of view of the SQL language, these cursors are not parameters. That's 
the fundamental reason that they are not retrieved as OUT parameters and 
why they don't appear in the argument list of the CALL statement itself.

2) The binding of that SQL language to Java is described in the SQL 
Standard part 13. The relevant portion is section 8.6 (Java routine 
signature determination), a very long, complicated set of rules. In 
matching a Java method to a SQL procedure, the database is supposed to 
look for methods whose trailing N arguments are of type ResultSet[] 
(where N is the number of cursors declared in the DYNAMIC RESULT SETS 
clause). Those trailing N arguments are visible in the Java method 
signature, but as noted above, not in the SQL signature of the procedure.

3) Note that JDBC won't even let you try to retrieve the cursors as OUT 
parameters. There is no java.sql.Types type code for ResultSet so you 
cannot use CallableStatement.registerOutParameter() to register an 
output parameter as a ResultSet. Similarly, there is no getXXX() method 
for retrieving a column or parameter of this type.

4) The following section in the Derby Reference Guide explains a little 
more about how the signatures of SQL procedures are mapped to the 
signatures of Java static methods: 
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#rrefsqljargmatching

Hope this helps,
-Rick

jstrunk wrote:
> 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?
>>>>>>>
>>>>>>>     
>>>>>>>         
>>>>>>>               
>>>>>>   
>>>>>>       
>>>>>>             
>>>>>     
>>>>>           
>>>>   
>>>>         
>>>
>>>       
>>     
>
>   


Mime
View raw message