db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: Developer's Guide : Example for table function creation has NO SQL clause but the Java method uses SQL
Date Wed, 23 Apr 2008 21:10:30 GMT
Hi Rick,

Thanks for your response. I did write the test case where the table
function is defined to have NO SQL and then created a Java class for
the table function with SQL access in the method and that resulted in
following exception during table function usage
ERROR 38001: The external routine is not allowed to execute SQL statements.
java.sql.SQLException: The external routine is not allowed to execute
SQL statements.

So, you are right, Derby is able to enforce the clause in the NO SQL case.

Mamta
ps Please let me know if you think we should have a Jira entry to
track the doc change. If yes, then I will go ahead and enter a Jira
for READS SQL DATA rather than NO SQL in the example.

On 4/23/08, Rick Hillegas <Richard.Hillegas@sun.com> wrote:
> Mamta Satoor wrote:
> > Hi,
> >
> > I could be understanding this wrong but section "Overview of
> > Derby-style table functions" in Developer's Guide has following
> > example
> > CREATE FUNCTION externalEmployees
> > ()
> > RETURNS TABLE
> > (
> >  employeeId    INT,
> >  lastName      VARCHAR( 50 ),
> >  firstName     VARCHAR( 50 ),
> >  birthday      DATE
> > )
> > LANGUAGE JAVA
> > PARAMETER STYLE DERBY_JDBC_RESULT_SET
> > NO SQL
> > EXTERNAL NAME 'com.acme.hrSchema.EmployeeTable.read'
> >
> > Notice that the example above says NO SQL. Later in the same section,
> > when we have an example of the implementation of
> > 'com.acme.hrSchema.EmployeeTable.read, it shows the usage
> of
> > PreparedStatement to execute a SQL query. I think we need to fix the
> > CREATE FUNCTION example to say "READS SQL DATA".
> >
> > thanks,
> > Mamta
> >
> >
> Hi Mamta,
>
> Thanks for digging into the user documentation on table functions. I agree
> that it would be less confusing if the example said "READS SQL DATA" rather
> than "NO SQL". I think that would be a better model for people writing this
> kind of table function. The meaning of those terms is ambiguous to me and I
> am not finding much guidance in the SQL standard--at least not in part 2,
> section 11.50 <SQL-invoked routine> or in part 13, section 9.8 <SQL-invoked
> routine>. It is unclear to me whether that clause is supposed to refer just
> to SQL issued against the current connection or whether it also refers to
> SQL issued against connections opened to foreign databases. Derby is able to
> enforce the clause in the first case but in the second case there is not
> much that Derby can do.
>
> Thanks,
> -Rick
>

Mime
View raw message