Thanks for raising this issue, Mamta. I have changed the "NO SQL" to
"READS SQL DATA" in the trunk (651322) and the 10.4 branch (651324).
Regards,
-Rick
Mamta Satoor wrote:
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
