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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 > > >
