JBossDeveloper

I'm on:

TEIID 8.0

Designer nightly build

AS 7.1.1

For the XML error that is the problem, thanks for the hint on how to create the limit.

Tomorrow I'll try.

Luca

Ok now I manage to deploy the VDB and run the query, but get null from the invokehttp/parseXML side.

The parse should be correct since if I call

I'm wondering if there is a way to see what teiid is doing is there a way to get a log of all the query execution to understand what are the intermediate result (the XML received by the invokehttp, thedata from the parsing etc.)?

Thanks

Luca

No I do not think that that could be the problem.

I have the same invokehttp ... the folowing code that generate a virtual table

SELECT

        A.lat AS lat, A.lng AS lng

    FROM

        (EXEC elgoog.invokeHttp('GET', null, 'http://maps.googleapis.com/maps/api/geocode/xml?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&sensor=true')) AS f, XMLTABLE('/GeocodeResponse' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS lat string PATH 'result/geometry/location/lat/text()', lng string PATH 'result/geometry/location/lng/text()') AS A

That queryed by squirrel return the two columns with the correct data inside.

But when I execute this code:

SELECT

        i.ID_INCIDENTE, i.PROVINCIA, i.COMUNE, l.TIPO_VIA, l.NOME_VIA, l.CIVICO, l.PROGRESSIVA_KM, (SELECT A.lat AS lat FROM (EXEC elgoog.invokeHttp('GET', null, QUERYSTRING('http://maps.googleapis.com/maps/api/geocode/xml', ((((((((((l.CIVICO || ',') || l.TIPO_VIA) || ' ') || l.NOME_VIA) || ',') || i.COMUNE) || ',') || i.PROVINCIA) || ',') || 'Italy') AS address, 'true' AS sensor))) AS f, XMLTABLE('/GeocodeResponse' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS lat string PATH 'result/geometry[0]/location/lat/text()', lng string PATH 'result/geometry[0]/location/lng/text()') AS A LIMIT 1) AS lat

    FROM

        SISS.SISS.SISS_T_INCIDENTI AS i, SISS.SISS.SISS_T_LOCALIZZ_INCIDENTE AS l

    WHERE

        i.FK_LOCALITA = l.ID_LOCALIZZAZIONE

The lat column that should represent the result of the invockehttp and xml parsing (it return the value of the node and not an XML chunk) is null.

I'm afreid that this is because data from external tables "i" and "l" do not get passed in the internal select.

Any hint?

Luca

yes you where right ...

the correct indexing in xpath is [1] to get the first element and not [0]

Now I get the expected result and with good performances also, really nice.

Will try to produce some documentation.

By the way is it possible to have a virtual table that accept parameters so that I can make a join and not a subquery? This way I could return more than one column.

In this case the problem is that the web service behind is not a table and get values based on the current row of the relational table so creating a join is a bit quirky on me.

Thanks

Luca

I've tryed to set the table as materialized in the table editor (setting it to true).

But I get this error do I make some errors?

11:56:58,421 ERROR [org.teiid.PROCESSOR] (Worker20_QueryProcessorQueue1136) TEIID30019 Unexpected exception for request rRZ074VTn20N.0: java.lang.NullPointerException

    at org.teiid.query.optimizer.relational.RelationalPlanner.resolveVirtualGroup(RelationalPlanner.java:1233) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.query.optimizer.relational.RelationalPlanner.buildTree(RelationalPlanner.java:836) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.query.optimizer.relational.RelationalPlanner.createQueryPlan(RelationalPlanner.java:737) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.query.optimizer.relational.RelationalPlanner.createQueryPlan(RelationalPlanner.java:696) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.query.optimizer.relational.RelationalPlanner.generatePlan(RelationalPlanner.java:490) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:180) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:188) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:429) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.dqp.internal.process.Request.processRequest(Request.java:449) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:534) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:278) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:217) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:241) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288) [teiid-engine-8.0.0.Final.jar:8.0.0.Final]

    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885) [rt.jar:1.6.0_04]

    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907) [rt.jar:1.6.0_04]

    at java.lang.Thread.run(Thread.java:619) [rt.jar:1.6.0_04]