I am using Teiid 8.6, JBoss EAP 6.1 final, and Oracle 11g.
Does anyone have a working example of using Oracle Spatial functions (SDO_*) through Teiid? It seems like there is some limited support, but I haven't been able to find much information on this.
From this it looks like there are several Oracle spatial function defined in Teiid, but queries similar to the ones in the tester are not working for me.
I cannot get the spatial functions to work in the WHERE clause. They appear to work fine as SELECTs.
select sdo_relate(footprint,footprint,'mask=ANYINTERACT') from foo.stuff;
This does not work:
select * from foo.stuff where sdo_relate(footprint,footprint,'mask=ANYINTERACT') = 'TRUE';
TEIID30019 Unexpected exception for request 10BpOpvKhXev.15: java.lang.NullPointerException
at org.teiid.query.metadata.TempMetadataAdapter.isVirtualModel(TempMetadataAdapter.java:403) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.optimizer.relational.rules.CapabilitiesUtil.supports(CapabilitiesUtil.java:451) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluatePushdown(SubqueryAwareEvaluator.java:359) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
I also tried adding the functions I need that I need in my VDB as FOREIGN:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="Foo" version="1">
<property name="UseConnectorMetadata" value="true"/>
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.schemaPattern" value="FOO"/>
<source name="foo" translator-name="oracle" connection-jndi-name="java:/foo-ds"/>
CREATE FOREIGN FUNCTION sdo_elem_info_array(variadic x integer) RETURNS object;
CREATE FOREIGN FUNCTION sdo_ordinate_array(variadic x integer) RETURNS string;
CREATE FOREIGN FUNCTION sdo_geometry(a integer, b integer, c object, d object, e object) RETURNS object;
CREATE FOREIGN FUNCTION sdo_anyinteract(a object, b object) RETURNS string;
Again, these work fine in SELECTs but do not work in WHERE. I'm pretty new to Teiid, but it appears as though it is trying to evaluate the expressions early. In the log I see things like "select sdo_anyinteract(?,?) from dual" which I don't think will ever work.
Let me know if you need any more info. Any help would be appreciated. Thanks!