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

    [Teiid+Oracle10g] SQL seems cannot translate

    donald donald Newbie

      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 donald Newbie

          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.

          • 2. Re: [Teiid+Oracle10g] SQL seems cannot translate
            Ramesh Reddy Master

            What is your user query you submitted from client?

            • 3. Re: [Teiid+Oracle10g] SQL seems cannot translate
              donald donald Newbie

              Thanks for your reply!

              Actually the SQL query generated by hibernate 3.2 , not support ??

              • 4. Re: [Teiid+Oracle10g] SQL seems cannot translate
                Ramesh Reddy Master

                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.

                • 5. Re: [Teiid+Oracle10g] SQL seems cannot translate
                  donald donald Newbie

                  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.

                  • 6. Re: [Teiid+Oracle10g] SQL seems cannot translate
                    Ramesh Reddy Master

                    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.

                    • 7. Re: [Teiid+Oracle10g] SQL seems cannot translate
                      Steven Hawkins Master

                      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".

                      • 8. Re: [Teiid+Oracle10g] SQL seems cannot translate
                        donald donald Newbie

                        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 ?

                        • 9. Re: [Teiid+Oracle10g] SQL seems cannot translate
                          Ramesh Reddy Master

                          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..

                          • 10. Re: [Teiid+Oracle10g] SQL seems cannot translate
                            donald donald Newbie

                            Thanks.

                            I'm not using the dynamic VDB. How can I gather the information for solving the problem?

                            • 12. Re: [Teiid+Oracle10g] SQL seems cannot translate
                              donald donald Newbie

                              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]
                              • 13. Re: [Teiid+Oracle10g] SQL seems cannot translate
                                donald donald Newbie

                                Any special handling needed for Teiid in subquery?

                                • 14. Re: [Teiid+Oracle10g] SQL seems cannot translate
                                  Ramesh Reddy Master

                                  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..

                                  1 2 Previous Next