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:12:50 GMT
I should have been more clear in my earlier mail. In my test case, the
method in Java class is issuing SQL against Derby when the table
funciton is defined to have NO SQL.

Mamta

On 4/23/08, Mamta Satoor <msatoor@gmail.com> wrote:
> 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