You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Mamta Satoor <ms...@gmail.com> on 2008/04/23 22:24:31 UTC
Developer's Guide : Example for table function creation has NO SQL clause but the Java method uses SQL
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
Re: Developer's Guide : Example for table function creation has NO SQL
clause but the Java method uses SQL
Posted by Rick Hillegas <Ri...@Sun.COM>.
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 <ms...@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 <Ri...@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
>>>
>>>
Re: Developer's Guide : Example for table function creation has NO SQL clause but the Java method uses SQL
Posted by Mamta Satoor <ms...@gmail.com>.
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 <ms...@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 <Ri...@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
> >
>
Re: Developer's Guide : Example for table function creation has NO SQL clause but the Java method uses SQL
Posted by Mamta Satoor <ms...@gmail.com>.
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 <Ri...@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
>
Re: Developer's Guide : Example for table function creation has NO SQL
clause but the Java method uses SQL
Posted by Rick Hillegas <Ri...@Sun.COM>.
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