SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

jstrunk
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?
Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

Rick Hillegas-2
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?
>  
A good place to start would be to look in derby.log. That's the console
log which Derby creates (by default, in the directory where the vm was
booted). You should see the following information in derby.log:

1) The SQLException you're looking for, including the message text above
as well as a stack trace.

2) Just before the SQLException, you should see the statement text for
the query which Derby didn't understand. The statement text will be
prepended with the words "Failed Statement is:"

You can also enable the logging of all statements. To do this, set the
system property -Dderby.language.logStatementText=true when you boot the
vm. For more information on Derby diagnostic properties, see
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#rrefproper32213

Hope this helps,
-Rick
Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

Knut Anders Hatlen
Rick Hillegas <[hidden email]> writes:

> 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?
>>  
> A good place to start would be to look in derby.log. That's the
> console log which Derby creates (by default, in the directory where
> the vm was booted). You should see the following information in
> derby.log:
>
> 1) The SQLException you're looking for, including the message text
> above as well as a stack trace.
>
> 2) Just before the SQLException, you should see the statement text for
> the query which Derby didn't understand. The statement text will be
> prepended with the words "Failed Statement is:"

Note that syntax errors won't be logged by default (except if you use a
debug build). Here's more information about how to set the log level:

http://db.apache.org/derby/docs/10.5/ref/rrefproper26985.html


> You can also enable the logging of all statements. To do this, set the
> system property -Dderby.language.logStatementText=true when you boot
> the vm. For more information on Derby diagnostic properties, see
> http://db.apache.org/derby/docs/10.5/ref/ref-single.html#rrefproper32213
>
> Hope this helps,
> -Rick

--
Knut Anders
Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

jstrunk
In reply to this post by jstrunk
I was coding "create procedure show_suppliers ...." and the error went away when I changed it to
"create procedure show_suppliers() ....". Does that make sense? There are no parms on the method but I saw the parens in a syntax diagram and thought they might be required anyway.

The sample code I am using seems to be full of errors which makes it very hard to learn from.
Does anyone have working sample programs that use JBDC and to create, store, and run a SQLJ stored procedure?


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?
Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

Rick Hillegas-2
jstrunk wrote:
> I was coding "create procedure show_suppliers ...." and the error went away
> when I changed it to
> "create procedure show_suppliers() ....". Does that make sense?
It is like declaring a method with no arguments in Java: you still need
an empty argument list.
> There are no
> parms on the method but I saw the parens in a syntax diagram and thought
> they might be required anyway.
>
> The sample code I am using seems to be full of errors which makes it very
> hard to learn from.
> Does anyone have working sample programs that use JBDC and to create, store,
> and run a SQLJ stored procedure?
>  
Try looking at the "scores" demo which is in the Derby source code under
java/demo/scores. In particular,
java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Database.java
will give you examples of how to declare Derby functions and procedures.
The main class of that application shows you how to use functions and
procedures at runtime:
java/demo/scores/java/client/org/apache/derbyDemo/scores/app/Scores.java.

Hope this helps,
-Rick

>
>
> 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?
>>
>>    
>
>  

Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

jstrunk
In reply to this post by jstrunk
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?
Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

Rick Hillegas-2
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?
>>
>>    
>
>  

Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

jstrunk
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?
>>
>>    
>
>  
Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

Rick Hillegas-2
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?
>>>>
>>>>    
>>>>        
>>>  
>>>      
>>
>>    
>
>  

Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

jstrunk
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?
>>>>
>>>>    
>>>>        
>>>  
>>>      
>>
>>    
>
>  
Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

jstrunk
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?
>>>>
>>>>    
>>>>        
>>>  
>>>      
>>
>>    
>
>  
Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

Rick Hillegas-2
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?
>>>>>>>
>>>>>>>    
>>>>>>>        
>>>>>>>              
>>>>>>  
>>>>>>      
>>>>>>            
>>>>>    
>>>>>          
>>>>  
>>>>        
>>>
>>>      
>>    
>
>  

Reply | Threaded
Open this post in threaded view
|

Re: SQLException: Syntax error: Encountered "<EOF>" at line 1, column 19.

jstrunk
In reply to this post by jstrunk
Yes, it helps! Awesome explanation. You should write a book.

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?