Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Date: Thu, 24 Apr 2008 10:49:32 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Developer's Guide : Example for table function creation has NO
 SQL
 clause but the Java method uses SQL
In-reply-to: <d9619e4a0804231412p5d2ff91cwf49a8e5cce5f57bf@mail.gmail.com>
Sender: Richard.Hillegas@Sun.COM
To: derby-dev@db.apache.org
Message-id: <4810C82C.7090601@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <d9619e4a0804231324i6b64773dy276d81e0131a95c8@mail.gmail.com>
 <480FA469.8030802@sun.com>
 <d9619e4a0804231410p79409333u9a449cec8d4d8e50@mail.gmail.com>
 <d9619e4a0804231412p5d2ff91cwf49a8e5cce5f57bf@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213)

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
>>>
>>>