Try something like this:
select house_number, street, city from table (legacy_realty_data('house_number,
street, city where price < 500000'))
then parse the string passed to your table function and implement a way to push
down those predicates to your backend to only retrieve what is needed.
if syntax is ugly, you can accept it in your original sql form, but have a
preprocessor that converts it into this form before execution.
maybe to find out what columns are used, you don't have to parse it, if you
prepare your original sql, you can get resultsetmetadata to find out what
columns are being returned. to find what predicates are used, *maybe* you can
execute and see which predicate values were attempted to be accessed (derby may
skip evaluation of some predicates in some cases so this is probably not a good
idea). and after you collect all this with a mock trial run, you can do the
real run that really connects to your backend.
Regards,
Ali
--- On Tue, 7/14/09, Chris Goodacre <[email protected]> wrote:
> From: Chris Goodacre <[email protected]>
> Subject: Question about TableFunctions in Derby
> To: [email protected]
> Date: Tuesday, July 14, 2009, 8:35 PM
>
> I've read the Derby developer's guide and Rick Hillegas's
> informative white paper
> (http://developers.sun.com/javadb/reference/whitepapers/sampleTableFunctions/doc/TableFunctionsWhitePaper.html)
> on Table Functions, but am still struggling with the
> following issue:
>
> I am trying to create an RDB abstraction for a large
> CICS/VSAM-based legacy system and blend it with our newer,
> RDB-based tier. This seems like a good application of
> TableFunctions. The VSAM data is made available to me via
> an IP-based proprietary messaging interface. There are
> lots of different files here, but due to some historical
> forces, most of the data I'm interested in resides in 4 VSAM
> files.
>
> Unfortunately, each of those VSAM files has over a 1000
> fields in it.
>
> Now eventually, it might be possible to fully model a
> single VSAM file into (for the sake of argument) 50 tables;
> each table/row representing a small slice of a single VSAM
> record.
>
> In the meantime, for both this proof-of-concept and as a
> migration path to our existing clients, I'd like to
> represent each VSAM file as a table (subject to the 1024
> column SQL limitation per table). This will be a
> highly-denormalized and decidedly non-relational view of the
> data, but it will be easy to demonstrate and immediately
> recognizable to our customers.
>
> However, I can't seem to get around the problem of data
> granularity.
>
> For example, if my customer executes:
>
> select house_number, street, city from table
> (legacy_realty_data()) where price < 500000
>
> I don't appear to have any visibility to the actual query
> inside my legacy_realty_data TableFunction, so I have to go
> get all 1000 fields for however many listings are present
> where price< 500000 even though only three columns will
> be requested. Am I missing something? Aside from having
> the user repeat the columns as parameters to the table
> function (which looks awkward to say the least), I can't see
> a way around this based on my limited knowledge of Derby.
>
> Is there a way to only retrieve the columns that the user
> is querying for?
>
> Looking forward to your help/advice.
>
> -chris
>
>
