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