[teiid-issues] [JBoss JIRA] Issue Comment Edited: (TEIID-999) Create valid capabilities support for MS Excel and MS Access JDBC-ODBC Connectors

[ https://jira.jboss.org/browse/TEIID-999?page=com.atlassian.jira.plugin.sy... ] Warren Gibson edited comment on TEIID-999 at 11/15/10 8:56 PM: --------------------------------------------------------------- 11-15-2010 WG - I was able to connect a Excel/Access source to a Teiid VDB and query it using the following steps. ODBC Excel/Access as a Teiid source Test Environment: Windows with Excel/Access 2003 or later, designer installed, Teiid installed, and a jdbc client such as Squirrel installed. 1. Open Designer and create a new project. 2. Create a Excel/Access Source Model with a Base Table representing the columns in the Excel/Access files. I used a source model name to match the Excel/Access target source. 3. Create a virtual table with same columns as the base table. 4. Create a vdb for Excel/Access with a unique jndi name. The unique jndi names will also be used in the ds file and as the Windows DSN name. Used jdbc-simple as the Translator. 5. Deployed the vdb to a running Teiid instance. 6. Use the Windows ODBC Data Source Administration tool to create two DSNs pointing to the source files. 7. Create a ds file for Excel/Access using the jndi-name in the vdb. See examples below. 8. Copy ds files to server deploy directory. 9. Use a JDBC client such as Squirrel to query the vdbs. The Teiid client jar (teiid-<x.xx>-client.jar) is used to set up a driver in Squirrel. Connect to vdbs. (example url:jdbc:teiid:<vdb>@mm://localhost:31000, user name <xxx> and password <xxx>) 10 Queried the source table and virtual table with a good sampling of queries. DS FILE EXAMPLE Excel <?xml version="1.0" encoding="UTF-8"?> <datasources> <no-tx-datasource> <jndi-name>ExcelDS</jndi-name> <connection-url>jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};Dbq=<url for source file location></connection-url> <driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class> <transaction-isolation>TRANSACTION_NONE</transaction-isolation> <min-pool-size>1</min-pool-size> <max-pool-size>1</max-pool-size> <idle-timeout-minutes>5</idle-timeout-minutes> </no-tx-datasource> </datasources> DS FILE EXAMPLE Access <?xml version="1.0" encoding="UTF-8"?> <datasources> <local-tx-datasource> <jndi-name>AccessDS</jndi-name> <connection-url>jdbc:odbc:AccessDS</connection-url> <driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class> <min-pool-size>1</min-pool-size> <max-pool-size>1</max-pool-size> <idle-timeout-minutes>5</idle-timeout-minutes> </local-tx-datasource> </datasources> was (Author: wgibson(a)jboss.org): 11-15-2010 WG - I was able to connect a Excel/Access source to a Teiid VDB and query it using the following steps. ODBC Excel/Access as a Teiid source Test Environment: Windows with Excel/Access 2003 or later, designer installed, Teiid installed, and a jdbc client such as Squirrel installed. 1. Open Designer and create a new project. 2. Create a Excel/Access Source Model with a Base Table representing the columns in the Excel/Access files. I used a source model name to match the Excel/Access target source. 3. Create a virtual table with same columns as the base table. 4. Create a vdb for Excel/Access with a unique jndi name. The unique jndi names will also be used in the ds file and as the Windows DSN name. Used jdbc-simple as the Translator. 5. Deployed the vdb to a running Teiid instance. 6. Use the Windows ODBC Data Source Administration tool to create two DSNs pointing to the source files. 7. Create a ds file for Excel/Access using the jndi-name in the vdb. See examples below. 8. Copy ds files to server deploy directory. 9. Use a JDBC client such as Squirrel to query the vdbs. The Teiid client jar (teiid-<x.xx>-client.jar) is used to set up a driver in Squirrel. Connect to vdbs. (example url:jdbc:teiid:<vdb>@mm://localhost:31000, user name <xxx> and password <xxx>) 10 Queried the source table and virtual table with a good sampling of queries. DS FILE EXAMPLE Excel <?xml version="1.0" encoding="UTF-8"?> <datasources> <no-tx-datasource> <jndi-name>ExcelDS</jndi-name> <connection-url>jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};Dbq=<url for source file location></connection-url> <driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class> <transaction-isolation>TRANSACTION_NONE</transaction-isolation> <min-pool-size>1</min-pool-size> <max-pool-size>1</max-pool-size> <idle-timeout-minutes>5</idle-timeout-minutes> </no-tx-datasource> </datasources> DS FILE EXAMPLE Access <?xml version="1.0" encoding="UTF-8"?> <datasources> <local-tx-datasource> <jndi-name>AccessDS</jndi-name> <connection-url>jdbc:odbc:AccessDS</connection-url> <driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class> <min-pool-size>1</min-pool-size> <max-pool-size>1</max-pool-size> <idle-timeout-minutes>5</idle-timeout-minutes> </local-tx-datasource> </datasources> -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira