Hi David,
Some more responses inline...
On 3/13/13 4:30 PM, drv wrote:
Hi Rick - yes this is useful, thanks.
Just to follow up on remaining points:
- Regarding the new issue (0) I inserted in my last post: Would it be
reasonable to request some sort of extension to the Table Functions set of
interfaces, to provide the original SQL to Table Functions at runtime (e.g.
before the initScan())?
I think it's ok to give Derby routines extra information about their
execution context. This mechanism would not be portable to other
databases, and we would want to make that clear to application programmers.
Perhaps another initScan() signature could be
provided that would include the VTIEnvironment?
I would not support any further use of VTIEnvironment or VTICosting. I
don't think that they fit well with ANSI/ISO table functions.
- Also I would be interested
to know what purpose the "sharedState" element of the VTIEnvironment could
have? I have not found anything I could use it for in the past...
From the code, it appears to me that sharedState is a Hashtable for
passing information between a VTI's compilation and execution phases.
- A quick note about the "com.ibm.db2j." package requirement: We have always
had all our VTIs under this package for the reason you stated (certainly up
to Derby 10.5). I have discovered however that with Derby 10.8, the
restriction is no longer there. I believe Derby will now work with VTIs from
any package.
Interesting! My crude attempt to reproduce this behavior failed. Could
you provide an example of how this is done?
Rick> There might be a way for the Java method to dig up one of the Derby
contexts and find the SQL text. However, that would not be a supported api.
=> It would be very useful if there was such a "context" I could grab hold
of. Perhaps this context would also have a reference to the original sql as
well as the function name for that particular invocation (note the same SQL
can trigger multiple invocations in the case of a JOIN)... Do you have any
clues on how I might get to this? We have often hoped that the
VTIEnvironment would act as that kind of handle...
The following code shows how to get the SQL text. Note that this is not
a supported api. It comes with no guarantees:
import java.sql.*;
import org.apache.derby.iapi.services.context.ContextManager;
import org.apache.derby.impl.jdbc.EmbedConnection;
import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
import org.apache.derby.iapi.sql.conn.StatementContext;
public class StatementInspector
{
public static String getStatement() throws Exception
{
Connection conn = DriverManager.getConnection(
"jdbc:default:connection" );
ContextManager contextManager = ((EmbedConnection)
conn).getContextManager();
LanguageConnectionContext lcc = (LanguageConnectionContext)
contextManager.getContext
( "LanguageConnectionContext" );
StatementContext statementContext = lcc.getStatementContext();
return statementContext.getStatementText();
}
}
- Thanks for the tips on StringColumnVTI, for raising the SYNONYM request
and for the info on existence of VTIs in the longer term... For now I think
we will have to stick with VTIs for our constructs that absolutely require
dynamic table shapes - in particular our VTI class named 'GaianQuery', which
takes a sub-query as argument (which may be any SQL: SELECT, CRUD, Stored
Procedure...) and which is propagated through a GaianDB network of database
nodes and run against any set of targeted nodes.
I appreciate your help. If you are interested in finding out more about the
Gaian Database, our old research release 1.5 (from 2011) is here:
https://www.ibm.com/developerworks/community/groups/service/html/communityview?communityUuid=f6ce657b-f385-43b2-8350-458e6e4a344f
We also have an in-house Asset release 2.1 - available on request - which
has been significantly hardened.
Thanks!
-Rick
Regards
David
--
View this message in context:
http://apache-database.10148.n7.nabble.com/Limitations-of-Table-Functions-vs-old-VTIs-tp127988p128096.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.
