JBossDeveloper

thx. In this case I have to create native query procedures. I tried this, but:

1. I passed a table name as variable (type string) to the relational:native-query and the query looks SELECT * from 'sometable', where the table name is wrapped with ''. Of course, this is an invalis query. Can I work around with this problem?

2. I have the following virtual procedure:

BEGIN

    SELECT XMLELEMENT(NAME customers, XMLAGG(XMLELEMENT(NAME customer, XMLFOREST(y.CustomName AS CustomName, y.Address AS Address)))) AS result FROM (EXEC New_MySQL.testnative('Steff')) AS w, ARRAYTABLE(w."output" COLUMNS CustomName string, Address string) AS y;

END

where testnative is the native query procedure I created that do "select * from customers":

I created a rest war and tested the virtual procedure getsteff and got this error:

SEVERE [org.teiid.rest] (http-/127.0.0.1:8080-1) null Exception in execute: {0}: java.lang.Throwable: org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate XMLELEMENT(NAME customer, XMLFOREST(convert(array_get(w."output", 1), string) AS CustomID, convert(array_get(w."output", 2), string) AS Address)): TEIID30384 Error while evaluating function array_get

    at org.teiid.rest.services.TeiidRSProviderPost.execute(TeiidRSProviderPost.java:116) [classes:]

    at org.teiid.rest.services.relation.getSteff(relation.java:115) [classes:]

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_60]

    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_60]

Something wrong with my virtual procedure?

I got the problem. I not used ARRAYTABLE correctly and I had to create two columns for the Resultset of the native query procedures. But one question: is it possible to give a table name as a parameter to the native query? When I use "string", the name is inserted in the query in the form of a string. This resulted in an invalid query.

example: I give the table name as string in EXEC New_MySQL.testnative('customers')) to my native query procedure with a query: select CustomName, Address from $1 as x. Teiid send the following query to my database:

select CustomName, Address from 'customers' as x

You see that the table name is wrapped with ' '. This is not correct.

Another problem:

I tried to use Direct query procedure: created a native query procedure to my source with one input (string) and one Resultset (object); created VDB and translator mysql5-override with Support directory query procedure=true. I can call in my virtual procedure with "EXEC native('select CustomName, Address from customers')) but got: Error executing statement(s): {call native(?)}. From my database log I see that teiid issued two statements:

Query      SELECT name, type, comment FROM mysql.proc WHERE name like 'native' and db <=> 'customer' ORDER BY name, type

Query    CALL native('select CustomName, Address from customers')

Actually, teiid should issue only "select CustomName, Address from customers". What's wrong?

This is it:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="clienttestvdb" version="1">

    <property name="preview" value="false"/>

    <property name="validationDateTime" value="Thu Oct 29 08:34:39 CET 2015"/>

    <property name="validationVersion" value="8.11.4"/>

    <model name="localmysql" type="PHYSICAL" visible="true" path="/JDBCtest/sources/localmysql.xmi">

        <property name="checksum" value="4127734425"/>

        <property name="indexName" value="3209395221.INDEX"/>

        <property name="modelUuid" value="mmuuid:ec1cdd67-c5fb-4133-a69f-996dc8dd3e74"/>

        <property name="modelClass" value="Relational"/>

        <property name="builtIn" value="false"/>

        <source name="localmysql" connection-jndi-name="java:/LocalMySQL" translator-name="mysql"/>

    </model>

    <translator name="mysql5-override" type="mysql5" description="">

        <property name="supportsDirectQueryProcedure" value="true"/>

    </translator>

</vdb>

The picture shows my native query procedure defined to my source, which is not combined with a query but has only a string as input:

yes, you are right. I changed the vdb.xml and now it works. This may be a problem of Teiid designer where I can specify Translator overrides but cannot tell Designer to take this as my translator.

Thanks a lot!

Hi Ramesh,

I successfully used direct query procedure but encountered a problem: my SQL statement is very long and I see that the statement is cut somewhere by Teiid such that an error appears due to the incomplete statement. Can I specify the length of the SQL string for the native query? The default length in Teiid does not fulfil my requirement.

another question: I have several statements in the virtual procedure in this way:

SELECT x.* INTO TEMP_clicks FROM (EXEC DBMStrack.native(array_get(VARIABLES.querystrings, 1))) AS w, ARRAYTABLE(w."output" COLUMNS groupitem string, clicks long, clicks_unique long) AS x;

SELECT x.* INTO TEMP_visits FROM (EXEC DBMStrack.native(array_get(VARIABLES.querystrings, 2))) AS w, ARRAYTABLE(w."output" COLUMNS groupitem string, visits long) AS x;

......

My feeling is that Teiid issues these SELECT statements sequentially to the data base, i.e., after one is finished. Correct? When yes, how to make this in parallel?

parallel select: my select statements are independent. Each of them issues a native query to a different table of my database. The query strings are created using a Java function. But: I have to merge the results of all my queries together. The current structure of my procedure is: call the Java function to get an array of all query strings; issue a SELECT with each string and store the results in a temporary table; merge all temporary tables to deliver the final results. Even there is a separate procedure for each query, I have to use a SELECT w.* into #TEMP from (EXEC procedureXX(sqlstring)) as w

for each query in my procedure for merging. In this case, the procedures are sequential. Right? How can I work around?