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

[Warren Gibson (JIRA)]

    [ https://jira.jboss.org/browse/TEIID-999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563511#comment-12563511 ] 

Warren Gibson commented on TEIID-999:
-------------------------------------

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>



> Create valid capabilities support for MS Excel and MS Access JDBC-ODBC Connectors
> ---------------------------------------------------------------------------------
>
>                 Key: TEIID-999
>                 URL: https://jira.jboss.org/browse/TEIID-999
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Misc. Connectors
>         Environment: MS Excel 2003
>            Reporter: Larry O'Leary
>             Fix For: 8.0
>
>   Original Estimate: 4 hours
>  Remaining Estimate: 4 hours
>
> Create a capabilities set that can be used by the MS Access and MS Excel connectors.  We currently offer the MS Excel connector which is using the generic JDBC capabilities and translators and the MS Access connector which is using its own capabilities and translators but they do not reflect MS Access.  
> The replacement connector types should work very much like the current ones but reflect the base capabilities offered by MS JET 4 and specific capabilities as defined by the MS Excel and MS Access limitations to the MS JET 4 implementation.  At bare minimum we should support push-down of all currently defined capabilities and remove any current ones that are not valid for MS JET/MS Access/MS Excel.

-- 
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira