Skip navigation

This project is read only now. Read more.

1 2 Previous Next 15 Replies Latest reply on Nov 28, 2010 1:59 AM by donald.ng

[Teiid+Oracle10g] SQL seems cannot translate

donald.ng Nov 10, 2010 10:16 PM

ojdbc.jar is used for oracle10g driver.

<EOF> and as between from in intersect into is like limit not or order <ID>

Log shows:

2010-11-10 10:54:11,562 WARN [org.jboss.managed.plugins.factory.AbstractManagedObjectPopulator] (New I/O server worker #1-1) Cannot create String name from non-Simple property: ManagedProperty{JNDIName,JNDIName,metaType=SimpleMetaType:java.lang.String}, value=null

2010-11-10 10:54:11,562 WARN [org.jboss.managed.plugins.factory.AbstractManagedObjectPopulator] (New I/O server worker #1-1) Cannot create String name from non-Simple property: ManagedProperty{JNDIName,JNDIName,metaType=SimpleMetaType:java.lang.String}, value=null

2010-11-10 10:55:40,234 INFO [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue25) OracleExecutionFactory Commit=true;DatabaseProductName=Oracle;DatabaseProductVersion=Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options;DriverMajorVersion=10;DriverMajorVersion=2;DriverName=Oracle JDBC driver;DriverVersion=10.2.0.1.0;IsolationLevel=2

2010-11-10 10:58:26,281 WARN [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue35) Processing exception 'Parsing error: Encountered "?" at line 1, column 12.

Was expecting one of:

<EOF>

"and" ...

"as" ...

"between" ...

"from" ...

"in" ...

"intersect" ...

"into" ...

"is" ...

"like" ...

"limit" ...

"not" ...

"or" ...

"order" ...

<ID> ...

"," ...

"=" ...

"<>" ...

"!=" ...

"<" ...

"<=" ...

">" ...

">=" ...

"*" ...

"/" ...

"+" ...

"-" ...

";" ...

"||" ...

"union" ...

"except" ...

"option" ...

"(" ...

' for request eVBJTZzO5iVP.1. Exception type org.teiid.api.exception.query.QueryParserException thrown from org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:164). Enable more detailed logging to see the entire stacktrace.

2010-11-11 10:54:11,562 WARN [org.jboss.managed.plugins.factory.AbstractManagedObjectPopulator] (New I/O server worker #1-1) Cannot create String name from non-Simple property: ManagedProperty{JNDIName,JNDIName,metaType=SimpleMetaType:java.lang.String}, value=null

2010-11-11 10:54:11,562 WARN [org.jboss.managed.plugins.factory.AbstractManagedObjectPopulator] (New I/O server worker #1-1) Cannot create String name from non-Simple property: ManagedProperty{JNDIName,JNDIName,metaType=SimpleMetaType:java.lang.String}, value=null

2010-11-11 10:55:40,234 INFO [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue25) OracleExecutionFactory Commit=true;DatabaseProductName=Oracle;DatabaseProductVersion=Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options;DriverMajorVersion=10;DriverMajorVersion=2;DriverName=Oracle JDBC driver;DriverVersion=10.2.0.1.0;IsolationLevel=2

2010-11-11 10:58:26,281 WARN [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue35) Processing exception 'Parsing error: Encountered "?" at line 1, column 12.

Was expecting one of:

<EOF>

"and" ...

"as" ...

"between" ...

"from" ...

"in" ...

"intersect" ...

"into" ...

"is" ...

"like" ...

"limit" ...

"not" ...

"or" ...

"order" ...

<ID> ...

"," ...

"=" ...

"<>" ...

"!=" ...

"<" ...

"<=" ...

">" ...

">=" ...

"*" ...

"/" ...

"+" ...

"-" ...

";" ...

"||" ...

"union" ...

"except" ...

"option" ...

"(" ...

' for request eVBJTZzO5iVP.1. Exception type org.teiid.api.exception.query.QueryParserException thrown from org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:164). Enable more detailed logging to see the entire stacktrace.

1. Re: [Teiid+Oracle10g] SQL seems cannot translate

donald.ng Nov 11, 2010 12:54 AM (in response to donald.ng)

Another try is connecting to DB2 , similar log show:

2010-11-11 13:41:02,531 INFO [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue7) DB2ExecutionFactory Commit=true;DatabaseProductName=DB2 UDB for AS/400;DatabaseProductVersion=05.04.0000 V5R4m0;DriverMajorVersion=9;DriverMajorVersion=1;DriverName=AS/400 Toolbox for Java JDBC Driver;DriverVersion=9.1;IsolationLevel=1
2010-11-11 13:50:41,562 WARN [org.teiid.PROCESSOR] (Worker5_QueryProcessorQueue41) Processing exception 'Parsing error: Encountered "?" at line 1, column 12.
Was expecting one of:
    <EOF>
    "and" ...
    "as" ...
    "between" ...
    "from" ...
    "in" ...
    "intersect" ...
    "into" ...
    "is" ...
    "like" ...
    "limit" ...
    "not" ...
    "or" ...
    "order" ...
    <ID> ...
    "," ...
    "=" ...
    "<>" ...
    "!=" ...
    "<" ...
    "<=" ...
    ">" ...
    ">=" ...
    "*" ...
    "/" ...
    "+" ...
    "-" ...
    ";" ...
    "||" ...
    "union" ...
    "except" ...
    "option" ...
    "(" ...
    ' for request e9TSQl5Ai9+M.1. Exception type org.teiid.api.exception.query.QueryParserException thrown from org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:164). Enable more detailed logging to see the entire stacktrace.

2010-11-11 13:41:02,531 INFO [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue7) DB2ExecutionFactory Commit=true;DatabaseProductName=DB2 UDB for AS/400;DatabaseProductVersion=05.04.0000 V5R4m0;DriverMajorVersion=9;DriverMajorVersion=1;DriverName=AS/400 Toolbox for Java JDBC Driver;DriverVersion=9.1;IsolationLevel=1
2010-11-11 13:50:41,562 WARN [org.teiid.PROCESSOR] (Worker5_QueryProcessorQueue41) Processing exception 'Parsing error: Encountered "?" at line 1, column 12.
Was expecting one of:
    <EOF>
    "and" ...
    "as" ...
    "between" ...
    "from" ...
    "in" ...
    "intersect" ...
    "into" ...
    "is" ...
    "like" ...
    "limit" ...
    "not" ...
    "or" ...
    "order" ...
    <ID> ...
    "," ...
    "=" ...
    "<>" ...
    "!=" ...
    "<" ...
    "<=" ...
    ">" ...
    ">=" ...
    "*" ...
    "/" ...
    "+" ...
    "-" ...
    ";" ...
    "||" ...
    "union" ...
    "except" ...
    "option" ...
    "(" ...
    ' for request e9TSQl5Ai9+M.1. Exception type org.teiid.api.exception.query.QueryParserException thrown from org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:164). Enable more detailed logging to see the entire stacktrace.
Actions
2. Re: [Teiid+Oracle10g] SQL seems cannot translate

rareddy Nov 11, 2010 9:45 AM (in response to donald.ng)

What is your user query you submitted from client?
Actions
3. Re: [Teiid+Oracle10g] SQL seems cannot translate

donald.ng Nov 11, 2010 11:26 AM (in response to rareddy)

Thanks for your reply!
Actually the SQL query generated by hibernate 3.2 , not support ??
Actions
4. Re: [Teiid+Oracle10g] SQL seems cannot translate

rareddy Nov 11, 2010 11:55 AM (in response to donald.ng)

Turn on the DEBUG log in the JBoss AS, you will see Hibernate generated commands in the log. We support the generated SQL from hibernate, however there seems be some mismatch somewhere.
Actions
5. Re: [Teiid+Oracle10g] SQL seems cannot translate

donald.ng Nov 11, 2010 12:14 PM (in response to rareddy)

Provide 1 more simple example,
Original generated SQL: select mybasiccc0_.CASE_ID as col_0_0_ from MY_BASIC_CC mybasiccc0_
which is valid SQL , and the error from Teiid is:

[JDBCExecutionException] 933: Error Code:933 Message:'ORA-00933: SQL command not properly ended
' error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.CASE_ID FROM CM_BASIC_CC AS g_0]
1 [SQLException]ORA-00933: SQL command not properly ended
[JDBCExecutionException] 933: Error Code:933 Message:'ORA-00933: SQL command not properly ended
' error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.CASE_ID FROM MY_BASIC_CC AS g_0]
1 [SQLException]ORA-00933: SQL command not properly ended

Hope this can help.
Actions
6. Re: [Teiid+Oracle10g] SQL seems cannot translate

rareddy Nov 11, 2010 12:40 PM (in response to donald.ng)

The error code ORA-00933 and SQL shown here not match up. Does not reflect the error the driver was saying. This is simple query should have no issues IMO.

Are you using hibernate 3.3, if not can you use 3.3 and see if you see the issue happens there or not.
Actions
7. Re: [Teiid+Oracle10g] SQL seems cannot translate

shawkins Nov 12, 2010 9:56 AM (in response to rareddy)

As Ramesh is getting at there are two issues here. The first is you send a user query (presumably from Hibernate) that fails our parsing. You can see the exact user query at the detail log level or by specifically configuring command logging.

The other error is from a source query failing against Oracle. It looks like in this case that the Oracle translator is not being used. Whatever translator is there is using "tbl AS x" (our ANSI default), whereas Oracle only expects "tbl x".
Actions
8. Re: [Teiid+Oracle10g] SQL seems cannot translate

donald.ng Nov 12, 2010 1:35 PM (in response to shawkins)

The simple presentation of setting is :
Hibernate3.3 <----(JDBC connection) ---> Teiid7.2 <-----(Oracle Translator / JDBC) -------> Oracle10g

Are there 2 different issues mixed up ? or the above setting is not okay ?
Actions
9. Re: [Teiid+Oracle10g] SQL seems cannot translate

rareddy Nov 12, 2010 2:04 PM (in response to donald.ng)

This should be fine. Are you using the Dynamic VDB? Unless you provide the COMMAND logs as we have been asking there isn't much help we can provide to diagnose the issue.

Ramesh..
Actions
10. Re: [Teiid+Oracle10g] SQL seems cannot translate

donald.ng Nov 24, 2010 12:57 PM (in response to rareddy)

Thanks.
I'm not using the dynamic VDB. How can I gather the information for solving the problem?
Actions
11. Re: [Teiid+Oracle10g] SQL seems cannot translate

rareddy Nov 24, 2010 1:02 PM (in response to donald.ng)

Please see this to turn on command logging http://docs.jboss.org/teiid/7.2.0.Final/admin-guide/en-US/html_single/#command_logging
Actions
12. Re: [Teiid+Oracle10g] SQL seems cannot translate

donald.ng Nov 27, 2010 1:46 PM (in response to rareddy)

Found that counting is automatically called before the query execute, for example:
1: select count(*) from ( QUERY_TO_BE_CALL);
2: QUERY_TO_BE_CALL

error captured during running 1:
[SELECT - 0 row(s), 0.000 secs] [Error Code: 0, SQL State: 50000] Remote org.teiid.api.exception.query.QueryParserException: Parsing error: Encountered "select cmbasiccc0_.CASE_ID as col_0_0_ from VDB.CM_BASIC_CC cmbasiccc0_ ) <EOF>" at line 1, column 23.
Was expecting one of:
    "xmltable" ...
    <ID> ...
    "(" ...
    "with" ...
    "select" "all" ...
    "select" "distinct" ...
    "select" "*" ...
    "select" "not" ...
    "select" "translate" ...
    "select" "?" ...
    "select" <STRINGVAL> ...
    "select" <INTEGERVAL> ...
    "select" <FLOATVAL> ...
    "select" "false" ...
    "select" "true" ...
    "select" "unknown" ...
    "select" "null" ...
    "select" <BOOLEANTYPE> ...
    "select" <TIMESTAMPTYPE> ...
    "select" <DATETYPE> ...
    "select" <TIMETYPE> ...
    "select" "{" ...
    "select" <ID> "(" ...
    "select" "xmlagg" ...
    "select" "convert" ...
    "select" "cast" ...
    "select" "left" ...
    "select" "right" ...
    "select" "char" ...
    "select" "user" ...
    "select" "year" ...
    "select" "month" ...
    "select" "hour" ...
    "select" "minute" ...
    "select" "second" ...
    "select" "xmlconcat" ...
    "select" "xmlcomment" ...
    "select" "insert" ...
    "select" "xmlparse" ...
    "select" "xmlelement" ...
    "select" "xmlpi" ...
    "select" "xmlforest" ...
    "select" "xmlserialize" ...
    "select" "xmlquery" ...
    "select" <ID> "*" ...
    "select" <ID> "/" ...
    "select" <ID> "+" ...
    "select" <ID> "-" ...
    "select" <ID> "||" ...
    "select" <ID> "not" ...
    "select" <ID> "between" ...
    "select" <ID> "like" ...
    "select" <ID> "in" ...
    "select" <ID> "is" ...
    "select" <ID> "=" ...
    "select" <ID> "<>" ...
    "select" <ID> "!=" ...
    "select" <ID> "<" ...
    "select" <ID> "<=" ...
    "select" <ID> ">" ...
    "select" <ID> ">=" ...
    "select" <ID> "and" ...
    "select" <ID> "or" ...
    "select" <ID> "as" <ID> "," ...
    "select" <ID> "as" <ID> "into" ...
    "select" <ID> "as" <ID> "from" "{" ...
    "select" <ID> "as" <ID> "from" "xmltable" ...
    "select" <ID> "as" <ID> "from" <ID> "as" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "makedep" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "makenotdep" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "cross" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "union" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "right" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "left" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "full" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "inner" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "join" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "," ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "where" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "group" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "having" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "intersect" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "except" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "order" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "limit" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "option" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> ")" "as" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> ")" <ID> ...
    "table" ...
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

02:37:15 [SELECT - 0 row(s), 0.000 secs] [Error Code: 0, SQL State: 50000] Remote org.teiid.api.exception.query.QueryParserException: Parsing error: Encountered "select cmbasiccc0_.CASE_ID as col_0_0_ from VDB.Oracle58.WCSS_OWNER.CM_BASIC_CC cmbasiccc0_ ) <EOF>" at line 1, column 23.
Was expecting one of:
    "xmltable" ...
    <ID> ...
    "(" ...
    "with" ...
    "select" "all" ...
    "select" "distinct" ...
    "select" "*" ...
    "select" "not" ...
    "select" "translate" ...
    "select" "?" ...
    "select" <STRINGVAL> ...
    "select" <INTEGERVAL> ...
    "select" <FLOATVAL> ...
    "select" "false" ...
    "select" "true" ...
    "select" "unknown" ...
    "select" "null" ...
    "select" <BOOLEANTYPE> ...
    "select" <TIMESTAMPTYPE> ...
    "select" <DATETYPE> ...
    "select" <TIMETYPE> ...
    "select" "{" ...
    "select" <ID> "(" ...
    "select" "xmlagg" ...
    "select" "convert" ...
    "select" "cast" ...
    "select" "left" ...
    "select" "right" ...
    "select" "char" ...
    "select" "user" ...
    "select" "year" ...
    "select" "month" ...
    "select" "hour" ...
    "select" "minute" ...
    "select" "second" ...
    "select" "xmlconcat" ...
    "select" "xmlcomment" ...
    "select" "insert" ...
    "select" "xmlparse" ...
    "select" "xmlelement" ...
    "select" "xmlpi" ...
    "select" "xmlforest" ...
    "select" "xmlserialize" ...
    "select" "xmlquery" ...
    "select" <ID> "*" ...
    "select" <ID> "/" ...
    "select" <ID> "+" ...
    "select" <ID> "-" ...
    "select" <ID> "||" ...
    "select" <ID> "not" ...
    "select" <ID> "between" ...
    "select" <ID> "like" ...
    "select" <ID> "in" ...
    "select" <ID> "is" ...
    "select" <ID> "=" ...
    "select" <ID> "<>" ...
    "select" <ID> "!=" ...
    "select" <ID> "<" ...
    "select" <ID> "<=" ...
    "select" <ID> ">" ...
    "select" <ID> ">=" ...
    "select" <ID> "and" ...
    "select" <ID> "or" ...
    "select" <ID> "as" <ID> "," ...
    "select" <ID> "as" <ID> "into" ...
    "select" <ID> "as" <ID> "from" "{" ...
    "select" <ID> "as" <ID> "from" "xmltable" ...
    "select" <ID> "as" <ID> "from" <ID> "as" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "makedep" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "makenotdep" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "cross" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "union" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "right" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "left" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "full" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "inner" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "join" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "," ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "where" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "group" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "having" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "intersect" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "except" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "order" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "limit" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> "option" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> ")" "as" ...
    "select" <ID> "as" <ID> "from" <ID> <ID> ")" <ID> ...
    "table" ...
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
Actions
13. Re: [Teiid+Oracle10g] SQL seems cannot translate

donald.ng Nov 27, 2010 1:47 PM (in response to donald.ng)

Any special handling needed for Teiid in subquery?
Actions
14. Re: [Teiid+Oracle10g] SQL seems cannot translate

rareddy Nov 27, 2010 7:15 PM (in response to donald.ng)

Please use separate threads for different issus so that they can be searchable in future.

try

SELECT count(*) FROM ( QUERY_TO_BE_CALL) AS X

Ramesh..
Actions

1 2 Previous Next

Go to original post