5 Replies Latest reply on Jan 22, 2013 10:02 AM by Steven Hawkins

    teiid 8.2 odbc interface linked tables fail from MSAccess 97-2010

    Marco Ardito Master

      Hi

       

      still struggling to make the most out of Teiid!

       

      i have a "Testmix" dynamic VDB, like

       

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <vdb name="Testmix" version="1">
          <description>Test Mix VDB</description>
          <property name="UseConnectorMetadata" value="true" />
           <model name="TestmixMS">
              <property name="importer.useFullSchemaName" value="true"/>
              <source name="sqlserver-connector" translator-name="sqlserver" connection-jndi-name="java:/magoDS"/>
          </model>
           <model name="TestmixMY">
              <property name="importer.useFullSchemaName" value="false"/>
              <source name="mysql5-connector" translator-name="mysql5" connection-jndi-name="java:/testmysql"/>
          </model>
      </vdb>
      
      

       

       

       

      which is working wonderfully from

      > Squirrel SQL with teiid client jdbc driver

      > Squirrel SQL with jdbc/odbc(postgresql) bridge

      > http://www.etl-tools.com/database-editors/database-browser/overview.html with ODBC (postgresql)

       

      so i think the Teiid odbc interface is fine.

       

      but i have found that MSaccess (tried both 97 and 2010 versions) has a problem linking or importing tables from my VDB ID they have an underscore "_" in their name... and 99.9% of my tables (in both models HAVE underscores i ntheir names, which is perfectly legal, i think.). I cannot try MSExcel (same versions) because it does not gives me the option to change the default port from 5432 to 35432, unfortunately

      for MSAccess part i also tried using VBA command docmd.Transferdatabase command, which gives complete control over the iport/link feature but i have the same result, always:

       

      i know this is not MSAccess support forum, but i wonder if anyone else has come into this issue and did find a workaround

       

      if i try to link the  'TestmixMY.conti_contratti' table from the VDB, Access complains that (google translated from italian) "The Microsoft Access database engine can not find the object 'TestmixMY.conti_contratti'. Make sure the object exists and that its name and location entered are correct. If 'TestmixMY.conti_contratti' is not a local object, check your network connection or contact the server administrator."


      if i try to link the  'TestmixMY.conticontratti' table from the VDB, Access wil not complain and result in an imported "TestmixMY_conticontratti" (ironically, note that it substiutes the "dot" with an "underscore" <grin>)


      the same goes if i link a table from my MS SQL model/source in the VDB, which is like "TestmixMS.ApiMn.dbo.MA_ChartOfAccounts" (this DB is from a commercial app, and has underscores in all its over 400 tables, plus i have to set  <property name="importer.useFullSchemaName" value="true"/> in the VBD so it prefixes with DBname and schema the original table name) which fails and i get:

      "The Microsoft Access database engine can not find the object 'TestmixMS.ApiMn.dbo.MA_ChartOfAccounts'. Make sure the object exists and that its name and location entered are correct. If 'TestmixMS.ApiMn.dbo.MA_ChartOfAccounts' is not a local object, check your network connection or contact the server administrator."

       

      if i try to link the "TestmixMS.ApiMn.sys.columns" table it works wonderfully and it links the table as "TestmixMS_ApiMn_sys_columns"

       

      i tried both with ANSI and UNICODE postgres ODBC drivers version 8.04 and the behaviour is identical...

       

      i thought i could

      - rename all tables in the source (of course not possible)

      - create an updatable view in teiid for all tables with underscored-names having corresponding non-underscored names. ithink now this is possible even with dynamic VDBs, or at least with Teiid Designer but frankly i feel it is a pain...

       

      is there any "substitution" machanism in the teiid engine, of which i am not aware, that could help in this situation, or any other trick/workaround i could use?

       

      Thanks, community...

       

      Marco

        • 1. Re: teiid 8.2 odbc interface linked tables fail from MSAccess 97-2010
          Steven Hawkins Master

          > if i try to link the  'TestmixMY.conticontratti' table from the VDB, Access wil not complain and result in an imported "TestmixMY_conticontratti" (ironically, note that it substiutes the "dot" with an "underscore" <grin>)

           

          Access may have some logic that converts the schema.table into a single name without dots and then on the way back out it could be replacing the _ with dots.  Of course this strategy does not work if the source table name contains _ which matches the erorr with 
          TestmixMY.conti_contratti and TestmixMS.ApiMn.dbo.MA_ChartOfAccounts, but not with the other tables.  So check if there is any configuration where that could be turned off or if there is a way to use a different substitution character.

           

          Another possiblity is that Access is using pg specific metadata queries - see https://docs.jboss.org/author/display/TEIID/System+Properties for the org.teiid.backslashDefaultMatchEscape property.  Without that property you may not get tables with underscores in them could get the wrong results - see also https://issues.jboss.org/browse/TEIID-2138

           

          > is there any "substitution" machanism in the teiid engine, of which i am not aware, that could help in this situation, or any other trick/workaround i could use?

           

          You'd want to validate the above first, but yes it is fairly straight-forward do configure a custom metadata repository or customize the JDBC metadata import to remove _ from names the runtime names (which is just the name that Teiid reports) if that is the root issue.

           

          Steve

          • 2. Re: teiid 8.2 odbc interface linked tables fail from MSAccess 97-2010
            Marco Ardito Master

            >Access may have some logic that converts the schema.table into a single name without dots and then on the way back out it could be replacing the _ with dots. 

            >Of course this strategy does not work if the source table name contains _ which matches the erorr with  TestmixMY.conti_contratti and TestmixMS.ApiMn.dbo.MA_ChartOfAccounts,

            > but not with the other tables.  So check if there is any configuration where that could be turned off or if there is a way to use a different substitution character.

             

            i follow your logic, but i think it is something more strange

            i can link the "dbo.MA_ChartOfAccounts" from a direct ODBC to MSSQLserver, to both access versions without problems, and it gets linked as "dbo_MA_ChartOfAccounts" in both cases.

            i can link "conti_contratti" table from its database (as well as any "db_name.dtable_name", notice the double underscores) in the same way and it gets linked as "conti_contratti" (its real database is "hidden" in TestmixMY.conti_contratti because i have <property name="importer.useFullSchemaName" value="false"/> in that model)

             

            i don't know why here the PostgreSQL ODBC through Teiid cannot make Access "see" the table, i suspect either some issue with the multiple "dots" in "TestmixMS.ApiMn.dbo.MA_ChartOfAccounts" but i am really puzzled by the mysql case but all i can guess is that, going throug postgresql odbc -> driver -> model-ds an underscored table is not found in Access..

             

            I attached a mashup of screenshots to better document what happens and not happens maybe it suggest something more to anyone seeing this and is a better reference, anyway.

             

            i will dig through the links you kindly provided, and then try to figure out what really "configure a custom metadata repository or customize the JDBC metadata import to remove _ from names the runtime names" in teiid-noob terms

             

            but for now, i thank you Steven,

            Marco

            • 3. Re: teiid 8.2 odbc interface linked tables fail from MSAccess 97-2010
              Marco Ardito Master

              > Another possiblity is that Access is using pg specific metadata queries - see https://docs.jboss.org/author/display/TEIID/System+Properties for the org.teiid.backslashDefaultMatchEscape property.  Without that property you may not get tables with underscores in them could get the wrong results - see also https://issues.jboss.org/browse/TEIID-2138

               

              i specifically tested this, putting <jboss>/bin/standalone-conf-bat file

              set "JAVA_OPTS=%$JAVA_OPTS% -Dorg.teiid.backslashDefaultMatchEscape=true"

               

              which gives in boot.log

              12:14:53,607 DEBUG [org.jboss.as.config] Configured system properties:

              ...


              org.jboss.boot.log.file = D:\sviluppo\jboss-as-7.1.1.Final\standalone\log\boot.log

              org.teiid.backslashDefaultMatchEscape = true

              ...

               

              and in this way, i CAN link the "TestmixMS.ApiMn.dbo.MA_ChartOfAccounts" table, in access 2010 which becomes the linked table "TestmixMS_ApiMn_dbo_MA_ChartOfAccounts"

              and also the Squirrel Sql client is still working through jfbc/odbc bridge...

               

              YES! Thank you SO much Steven! for pointing me in the right direction...

               

              i'll try in January (my company closes today, until january 7) to go further with more tests, but it seems really the solution...

               

              i just turned your previous answer from "useful" to "correct"

               

              Thanks, i wish i could help you in the same way, somehow!

              Marco

              • 4. Re: teiid 8.2 odbc interface linked tables fail from MSAccess 97-2010
                Steven Hawkins Master

                > Thanks, i wish i could help you in the same way, somehow!

                 

                Issues and questions definitely help.  I'll have to think some more on if there is an approach that would help avoid this issue in the first place.  We will probaby need some general switch for the odbc logic that turns on pg emulation options for when the transport is used as a pg source rather than just to issue teiid queries.

                 

                Steve

                • 5. Re: teiid 8.2 odbc interface linked tables fail from MSAccess 97-2010
                  Steven Hawkins Master

                  In 8.3 we'll now look for the use of ... LIKE E'...' without an escape and assume the escape character is \ to match the default postgresql behavior.  So having _ in a table name should work through odbc metadata even without setting the system emulation property.  However setting the property may still be necessary depending upon the full extent of the assumptions being made by the client.