Teiid for Excel Data Source
ripank_tcs Jan 4, 2012 1:26 AMWe have created one client program to fetch data from text and csv data files taking Teiid support. This is working fine. We are using Teiid 7.6.0 and the JBoss 5.1.0 GA. But facing problem for excel (.xls) and xml files through we have followed the same approach.
The config are as below:
1.excel-file-ds.xml:
<?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=${jboss.server.home.dir}/teiid-examples/dynamicvdb-portfolio/data/abcd.xls</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>
2. Added entry in portfolio-vdb.xml:
<model name="ExcelDS">
<source name="text-connector" translator-name="file" connection-jndi-name="java:excel-file"/>
</model>
3. abcd.xls
Name Address Country ZipCode
John 1st Street USA 12222
Mac 2nd Street USA 34421
Peter 3rd Street USA 54321
The connection Code:
url = "jdbc:teiid:" + vdb + "@mm://" + host + ":" + port + ";showplan=on";
Class.forName("org.teiid.jdbc.TeiidDriver");
DriverManager.getConnection(url, user, password);
The connection is getting created successfully.
But while executing the query we are not getting any result output. code snipet looks like this
sql = "SELECT NAME, ADDRESS, COUNTRY, ZIPCODE FROM (call ExcelDS.getTextFiles('abcd.xls')) f, TEXTTABLE(f.file COLUMNS NAME string,ADDRESS string,COUNTRY string,ZIPCODE bigdecimal HEADER) as stock ";
statement = connection.createStatement();
results = statementForCSV.executeQuery(sql);
results.next();
Running the program in debugging mode in eclipse we can see that the connection and statement all are created successfully but not getting any data from the ResultSet results.
Observations: We have also tried to change the query but not succeed.
a. sql = "SELECT * from [Sheet1$]"; // not working
b. sql = "SELECT * from (call ExcelData.getTextFiles('Sheet1$'))"; // not working
c. sql = "select * from (call ExcelDS.getTextFiles('abcd.xls'[Sheet1$])) "; // not working
Same Exception : org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException
d. sql = "select stock.* from (call ExcelDS.getTextFiles('abcd.xls')) f, TEXTTABLE(f.file COLUMNS NAME string,ADDRESS string,COUNTRY string,ZIPCODE bigdecimal HEADER) stock"; //not working
Exception: org.teiid.jdbc.TeiidSQLException: Input length = 1
Pls help us to resolve the issues soon.
Regards,
Ripan K
-
abcd.xls 7.0 KB