Using table results dynamically generated by Java classes

[Mark Rose <mr...@email.arc.nasa.gov>]

I'm interested in importing data into Derby databases from other  
sources, and the ability in Derby to have Java classes return  
ResultSets that can be used as dynamic tables seemed promising:

INSERT INTO sometable
SELECT *
FROM new somejavaclass(constructorParameters) t;

The class "somejavaclass" must implement java.sql.ResultSet.

I've gotten it to work, but only by having my class be in some  
subpackage of org.apache.derby.diag. There is code in the parser  
source that implements a special check if the java class is not in  
one of a few special packages (sqlgrammer.jj line 1644):

if (!javaClassName.startsWith("org.apache.derby.diag.") && ! 
javaClassName.startsWith("org.apache.derby.catalog.") && ! 
javaClassName.startsWith("com.ibm.db2j."))
{
     checkInternalFeature(javaClassName);
}

I don't like the fact that I must have my class in some special  
package, but I haven't been able to find out how to turn off the  
check for "foreign" Java classes. (It seems to be hard-coded in  
CompilerContext, checking for the bit INTERNAL_SQL_ILLEGAL in the  
flag set by setReliability().)

Does anyone know how to turn off this test so my class doesn't have  
to live in the Derby tree?

BTW, I realize you can do something similar with "CREATE PROCEDURE",  
but it seems you lose the ability to choose which columns you want-- 
or have to implement the selection yourself in Java--, and you can't  
join with other tables, etc., so I'd prefer to use the "FROM new  
javaclass()" form, if possible.

Thanks,
Mark
--
Mark Rose
QSS Group, Inc. / NASA Ames Research Center
650.604.3627
mrose@email.arc.nasa.gov

Re: Using table results dynamically generated by Java classes

[Daniel John Debrunner <dj...@apache.org>]

Daniel John Debrunner wrote:

> Mark Rose wrote:
> 
> 
>>I'm interested in importing data into Derby databases from other 
>>sources, and the ability in Derby to have Java classes return 
>>ResultSets that can be used as dynamic tables seemed promising:
> 
> 
> There have been some discussion to externalize the current internal
> virtual table support into something more standard.
> 
> A first step was is described in
> http://issues.apache.org/jira/browse/DERBY-571

And the issue of re-enabling VTIs is in

http://issues.apache.org/jira/browse/DERBY-716

I thought there had been some more discussion, but I can't find it in
the archives.

Dan.

Re: Using table results dynamically generated by Java classes

[Daniel John Debrunner <dj...@apache.org>]

Mark Rose wrote:

> I'm interested in importing data into Derby databases from other 
> sources, and the ability in Derby to have Java classes return 
> ResultSets that can be used as dynamic tables seemed promising:

There have been some discussion to externalize the current internal
virtual table support into something more standard.

A first step was is described in
http://issues.apache.org/jira/browse/DERBY-571

This took some of the internal diagnostic tables from the vti form

SELECT * FROM NEW org.apache.derby.diag.LockTable() as LOCK_TABLE

to a regular table syntax

SELECT * FROM SYSCS_DIAG.LOCK_TABLE

There are some discussions in the derby-dev archives about the next steps.

If you want to move this forward then join in the derby developer
community, I'd be willing to help out.

Thanks,
Dan.