db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-716) Re-enable VTIs
Date Thu, 03 May 2007 16:03:15 GMT

    [ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493405
] 

A B commented on DERBY-716:
---------------------------

Thank you for answering my previous questions, Rick, and for incorporating my feedback into
the second version of the spec.  I took a look at the latest spec and I have the following
questions...

1) Under "Additional SELECT Syntax"

  "Value - A Value is an expression which could appear as an argument in the invocation
   of one of the diagnostic VTI functions. This includes literals and '?' parameters."

I wonder if it wouldn't be better to just explicitly state what is allowed here, instead of
referencing the diagnostic VTIs?  I.e. "Value" can be any expression which evaluates to a
single value whose corresponding JDBC type equals the JDBC equivalent of the relevant function
parameter's declared SQL type.

That's a mouthful (you'll probably want to wordsmith it a bit), but as an example:

  CREATE FUNCTION externalEmployees (LAST_NAME VARCHAR(50))
    RETURNS TABLE ...

The function parameter "LAST_NAME" has a declared SQL type of VARCHAR.  The JDBC equivalent
to this type is String.  Call this PARAM_JDBC_TYPE.  Then when calling the function:

  SELECT * FROM TABLE (externalEmployees( <Value> )) as EMP

<Value> can be any expression that evaluates to a type whose JDBC equivalent is PARAM_JDBC_TYPE.
 One exception here may be LOBs; I don't think Derby allows passing of LOBs as function parameters?

In this case PARAM_JDBC_TYPE is "String", so <Value> can be any character expression.
 And yes, this includes literals and '?' parameters.

Note that something like:

  SELECT * FROM TABLE (externalEmployees(SELECT DISTINCT 'hi' FROM SYS.SYSTABLES)) as EMP

would not work because the subquery returns "a result set with a single row", which is not
the same as "a single value".

2) Under "Appendix E: Sample VTI"

It's great to have an example, so thank you for putting this together.  Some initial comments...

 A -- The javadoc for the class includes:

    * 3) When you are done siphoning out the rows you need, release the
    *      connection to the external database:
    *
    *    EmployeeTable.close();

 I don't quite understand who the "you" is in this sentence?  It sounds like it's referring
to the user, but it seems odd to me to expect that the user is responsible for explicitly
calling "close" on the VTI class.

 Is the assumption here that an application will typically execute code such as:

    ResultSet rs = conn.createStatement().executeQuery(
        "select * from TABLE (employeeTable()) emps");

    while (rs.next())
    {
       ...
    }

    rs.close();
    EmployeeTable.close();

 If this is not what you had in mind, can you perhaps include an example program that would
call the EmployeeTable VTI, process results, and then clean up?

 Intuitively I would expect that a call to "rs.close()" internally leads Derby to call "close()"
on the VTI class, sparing the user the need to do so.  Which brings me to my next question...

 B -- What is "rs" in the following:

    ResultSet rs = conn.createStatement().executeQuery(
        "select * from TABLE (employeeTable()) emps");

 Is it:

   a) The exact same ResultSet object that is returned from EmployeeTable.read()
   b) A Derby ResultSet that somehow wraps the the EmployeeTable VTI
   c) A Derby ResultSet that somehow wraps the ResultSet returned from EmployeeTable.read()
   d) Something else entirely?

 If it's "a" then the user/app would indeed be responsible for calling EmployeeTable.close()
explicitly, which seems odd.  If it's "b" then Derby can internally propagate "rs.close()"
to EmployeeTable.close(), but would not have direct access to the underlying result set (or
would it?).  If it's "c" then Derby has more control over the behavior of the result set and
can propagate calls on "rs" to the underlying (user-defined) ResultSet--but Derby would not
be able to call methods on the VTI itself (such as EmployeeTable.close()).  Can you say which
of these, if any, correlates to your plans for VTIs?

Thanks for your patience as I try to wrap my head around this...

> Re-enable VTIs
> --------------
>
>                 Key: DERBY-716
>                 URL: https://issues.apache.org/jira/browse/DERBY-716
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>            Reporter: Rick Hillegas
>         Attachments: functionTables.html, functionTables.html
>
>
> Cloudscape used to expose Virtual Table Interfaces, by which any class which implemented
ResultSet could be included in a query's FROM list. Derby still exposes a number of these
VTIs as diagnostic tools. However, Derby now prevents customers from declaring their own VTIs.
The parser raises an error if a VTI's package isn't one of the Derby diagnostic packages.
> This is a very powerful feature which customers can use to solve many problems. We should
discuss the reasons that it was disabled and come up with a plan for putting this power back
into our customers' hands.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message