-
15. Re: virtual procedure SQL transformation
jietao Oct 27, 2015 8:52 AM (in response to shawkins)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?
-
16. Re: virtual procedure SQL transformation
rareddy Oct 27, 2015 10:28 AM (in response to shawkins)IMO, the simplest ting to do is to add a procedure to source model in the Designer resembling the "native" procedure in the translator.
The signature of the procedure is like
naitve (request IN string, variable IN Object) returns Object[]
then use it in your view
-
17. Re: virtual procedure SQL transformation
jietao Oct 28, 2015 5:28 AM (in response to rareddy)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.
-
18. Re: virtual procedure SQL transformation
rareddy Oct 28, 2015 10:31 AM (in response to jietao)The "VARIABLE" in the above "Native" procedure is for the parameter values for your native query with ? place holders. So, you should be able to pass the table name. As far the error it is not clear to me what you may be seeing. Provide example, may be then we can help.
Ramesh..
-
19. Re: virtual procedure SQL transformation
jietao Oct 28, 2015 12:00 PM (in response to rareddy)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?
-
20. Re: virtual procedure SQL transformation
shawkins Oct 28, 2015 4:35 PM (in response to jietao)> You see that the table name is wrapped with ' '. This is not correct.
The substitution is roughly equivalent to prepared statements - we interpret the binding as a literal not as the sql string itself. So this is behaving as designed, but does not fit your need of changing the table name.
> Actually, teiid should issue only "select CustomName, Address from customers". What's wrong?
I don't think that the translator is issuing the direct query, but instead is just executing the native procedure as if it were a standard source stored procedure. The support needs to be turned on and the procedure name needs to match the configured direct query procedure name, which defaults to native. Can you post your vdb.xml to confirm this is setup correctly?
-
21. Re: virtual procedure SQL transformation
jietao Oct 29, 2015 4:06 AM (in response to shawkins)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:
-
22. Re: virtual procedure SQL transformation
shawkins Oct 29, 2015 8:59 AM (in response to jietao)The translator override is not being used by localmysql. You need to change the translator-name to mysql5-override.
-
23. Re: virtual procedure SQL transformation
jietao Oct 29, 2015 10:12 AM (in response to shawkins)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!
-
24. Re: virtual procedure SQL transformation
shawkins Oct 29, 2015 2:18 PM (in response to jietao)> This may be a problem of Teiid designer where I can specify Translator overrides but cannot tell Designer to take this as my translator.
If that's the case, then please log an issue.
-
25. Re: virtual procedure SQL transformation
jietao Nov 24, 2015 9:02 AM (in response to rareddy)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.
-
26. Re: virtual procedure SQL transformation
jietao Nov 24, 2015 9:14 AM (in response to rareddy)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?
-
27. Re: virtual procedure SQL transformation
shawkins Nov 24, 2015 3:12 PM (in response to jietao)> Can I specify the length of the SQL string for the native query?
Are you using the extension metadata property or the direct query procedure? In DDL there should not be a limit to the size of the extension metadata property. As a string parameter though to the direct query procedure, it can be subject to the 4000 character limit. Other than changing the system wide default for max string length there isn't a way to control that - System Properties - Teiid 9.0 (draft) - Project Documentation Editor It would take an enhancement to support a clob argument.
> 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?
There currently isn't an explicit fork/task framework for the Teiid procedure language and by default procedure processing is sequential. An enhancement would be needed to detect when statements could be executed in parallel (although that can get complicated) or to add some additional language / system constructs.
-
28. Re: virtual procedure SQL transformation
rareddy Nov 24, 2015 3:16 PM (in response to jietao)I believe this is bound by string length 4000. You can increase with performance penalty. See "org.teiid.maxStringLength"at https://docs.jboss.org/author/display/TEIID/System+Properties
as per paralleling your query if you do not need results from first one in the second one, then try to split them into two different virtual procedures and execute them separately. The procedures are executed sequentially.
-
29. Re: virtual procedure SQL transformation
jietao Nov 25, 2015 4:35 AM (in response to rareddy)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?