7 Replies Latest reply on Feb 14, 2011 10:40 PM by meltedmetal

    How to handle specific table name "ELEMENT"

    meltedmetal

      Hi,

       

      I have a table in VDB named "Element".

      I found that in the SQL statement the table name Element should be wrapped by double quotation character like below: 

      SELECT ELE_ID FROM "ELEMENT";
      

       

      But the "Element" table in this VDB (VDB1) is based on underlying another "Element" table in another VDB (VDB2).

      In this case, the table name Element in the SQL against underlying "Element" table of VDB2 is not wrapped by double quotation characters.

      BTW: I use 'multi-sources' feature on VDB1. And VDB2 is one of the data source of VDB1.

      For example, the below SQL on VDB1:

      SELECT ELE_ID FROM "ELEMENT";
      

      Then the SQL on VDB2 become below, no double quotation here. The SQL is created by Teiid.

      SELECT g_0.ELE_ID, 'dfs' FROM ELEMENT AS g_0
      

      So the error log below:

      2011-01-27 11:38:01,898 WARN  [org.teiid.PROCESSOR] (Worker16_QueryProcessorQueue165) 
      Processing exception 'Error Code:0 Message:Error Code:0 Message:'Parsing error: 
      Encountered "ELEMENT" at line 1, column 31.
      Was expecting one of:
          "{" ...
          "xmltable" ...
          <ID> ...
          "table" ...
          "(" ...
          ' error executing statement(s): 
      [Prepared Values: [] SQL: SELECT g_0.ELE_ID, 'dfs' FROM ELEMENT AS g_0]' 
      for request XtzEhfxxDArG.0.  
      Exception type org.teiid.core.TeiidProcessingException thrown 
      from org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:164). 
      Enable more detailed logging to see the entire stacktrace.
      

       

      How can I handle this case?

      It is a little urgent. Any tips are welcome.

      Thank you!

        • 1. How to handle specific table name "ELEMENT"
          rareddy

          S.Q.R,

           

          The quickest thing to do is edit your VDB1 using the Designer, and on "Element" table properties find the "Name in Source" property and enter the name as "Element" in there with double quotation marks. I believe that should resolve it.

           

          I am not entirely sure that Teiid does not do this already based on some configuration. If in case it does not support it, this can be added as an issue to be fixed. I will investigate.

           

          On side note, can I persuade you in writing a small blog about your use case in showing how Teiid is helping to solve your data integration needs?

           

          Thanks.

           

          Ramesh..

          • 2. How to handle specific table name "ELEMENT"
            shawkins

            Designer 7.2 should resolve this issue automatically - if you reimport.  All imported JDBC metadata should be quoted using the quoting character from the source.  Dynamic VDBs already import metadata in this way.

             

            There is however no automatic mechanism to correct this on the Teiid side, so the workaround that Ramesh suggests is the best approach until you use Designer 7.2.

             

            Steve

            • 3. Re: How to handle specific table name "ELEMENT"
              meltedmetal

              Hi, Ramesh & Steve

               

              Thanks for your help.

               

              I have tried the approach. But it is same with before.  I will try it on Designer 7.2.

               

              However, I think that even though the approach can work. It has another question.

              Because in my case, there are two data source, one is Teiid VDB, another is oracle database. They have same schema.

              Below SQL (table name is wrapped by double quotation marks) is ok for Teiid VDB, but it is not ok for Oracle.

              select ELE_ID from "ELEMENT";
              

               

              Ramesh: I am pleasure to write a blog to share my usage of Teiid.

               

              Thank Teiid Community for the great efforts!

               

               

              Best Regards,

              S.Q.

              • 4. Re: How to handle specific table name "ELEMENT"
                shawkins

                If you changed the name in source value for the ELEMENT table in VDB1 that is querying Teiid to be "ELEMENT", then the generated query against VDB2 should work against Teiid.  This is essentially what Designer 7.2 will do for you automatically.

                 

                What do mean by the quoted query will not work against Oracle?  Oracle supports the SQL standard double quoting of identifiers.

                 

                Steve

                 

                select ELE_ID from "ELEMEN
                • 5. How to handle specific table name "ELEMENT"
                  meltedmetal

                  Hi, Steve

                   

                  Below is the screen snapshot of SQL execution result against Oracle. Is there any wrong of my SQL?

                  sql.PNG

                  Best Regards,

                  S.Q.R

                  • 6. How to handle specific table name "ELEMENT"
                    shawkins

                    S.Q.R,

                     

                    Here's the oracle naming rules http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm

                     

                    The most likely issue is that the quoted name is being treated as case sensitive.

                     

                    Steve

                    1 of 1 people found this helpful
                    • 7. How to handle specific table name "ELEMENT"
                      meltedmetal

                      Hi, Steve

                       

                      You are right.

                      Thank you.

                       

                      BRs,

                      S.Q.R