LIMIT in virtual procedure

[gioppoluca]

I'm trying to apply a limit clause to a virtual procediure with no luck

CREATE VIRTUAL PROCEDURE

BEGIN

            SELECT XMLELEMENT(NAME alberate, XMLAGG(XMLELEMENT(NAME alberata, XMLFOREST(AlberataView.AlberataV.WKT_GEOM, AlberataView.AlberataV.ST_P_PIA, AlberataView.AlberataV.AMBITO, AlberataView.AlberataV.PAVIMENTAZIONE, AlberataView.AlberataV.POSIZIONE, AlberataView.AlberataV.SPECIE, AlberataView.AlberataV.ALTEZZA, AlberataView.AlberataV.TRONCO, AlberataView.AlberataV.ETA)))) AS result FROM AlberataView.AlberataV LIMIT AlberataView.AlberataRest."limit";

END

Where AlberataView.AlberataRest."limit" is an input parameter of the procdure defined as integer.

I get this error:

The query is not parsable.

TEIID31100 Parsing error: Encountered "FROM AlberataView.AlberataV LIMIT [*]AlberataView.AlberataRest.\"limit\"[*]; END" at line 5, column 432.

Was expecting: <UNSIGNEDINTEGER> | "?"

Any hint?

[gioppoluca]

TEIID Designer version 8.1 beta

[shawkins]

Logically that should be allowed as it's functionally the same as allowing a prepared parameter.  An enhancement would be needed though as the parser is not looking for a variable reference.  Can you log something?

The workaround would be to use a dynamic query.

Steve

[shawkins]

I went ahead and worked this in 8.5 as https://issues.jboss.org/browse/TEIID-2595

Thanks,

Steve

[gioppoluca]

Thanks Steven,

does this also affect designer and the validation at transformation editor level?

At which nightly build will it be merged?

Also can you shed an example of dynamic query with LIMIT?

Also since I want to implement a REWST api pagination using the LIMIT and the offset as REST parameters and I want to generate the rest war using Designer I could generate the WAR with the procedure without LIMIT (so I do not get errors and the generator works) and than creating the dyamic query with the same name and substitute the VDB at deployment time?

Thanks

Luca

[shawkins]

> does this also affect designer and the validation at transformation editor level?

Yes, Designer has to pick up the corresponding engine bits to support the language features at runtime.

> At which nightly build will it be merged?

I can't really say.  It will be a while before they pick up an 8.5 build and I don't think there will be a community release against Teiid 8.4.1.

> Also can you shed an example of dynamic query with LIMIT?

See https://docs.jboss.org/author/display/TEIID/Procedure+Language

EXECUTE IMMEDIATE 'SELECT XMLELEMENT(NAME alberate, ... LIMIT' || AlberataView.AlberataRest."limit" AS COL XML;

> Also since I want to implement a REWST api pagination using the LIMIT and the offset as REST parameters

The OData layer uses result caching for more efficent paging.  Such that at a user scope for a specified time limit the entire result is cached and subsets are returned as the pages.  Adding a cache hint to a query within a procedure however isn't a supported use of the cache hint.  But an enhancement would be warrented for situations like this.