db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Developer's Guide : Example for table function creation has NO SQL clause but the Java method uses SQL
Date Thu, 24 Apr 2008 17:49:32 GMT
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 <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