teiid 8.2 odbc interface linked tables fail from MSAccess 97-2010
m.ardito Dec 20, 2012 12:23 PMHi
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