[JBoss JIRA] Issue Comment Edited: (TEIID-965) Need Excel file importer / connector
by Warren Gibson (JIRA)
[ https://jira.jboss.org/browse/TEIID-965?page=com.atlassian.jira.plugin.sy... ]
Warren Gibson edited comment on TEIID-965 at 11/15/10 8:41 PM:
---------------------------------------------------------------
11-15-2010 WG - I was able to connect a MS Excel source to a Teiid VDB and query it using the following steps.
ODBC Excel as a Teiid source Test
Environment: Windows with Excel 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 Source Model with a Base Table representing the columns in the Excel file. I used a source model name to match the Excel target source.
3. Create a virtual table with same columns as the base table.
4. Create a vdb for Excel with a unique jndi name. The unique jndi name 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 the DSNs pointing to the source file.
7. Create a ds file for Excel using the jndi-name in the vdb. See example below.
8. Copy ds file 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:
?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 of source ,xls 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>
was (Author: wgibson(a)jboss.org):
11-15-2010 WG - I was able to connect a MS Excel source to a Teiid VDB and query it using the following steps.
ODBC Excel as a Teiid source Test
Environment: Windows with Excel 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 Source Model with a Base Table representing the columns in the Excel files. I used a source model name to match the Excel target source.
3. Create a virtual table with same columns as the base table.
4. Create a vdb for Excel 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 using the jndi-name in the vdb. See example below.
8. Copy ds file 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 admin and password teiid)
10 Queried the source table and virtual table with a good sampling of queries.
DS FILE EXAMPLE:
?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 of source ,xls 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>
> Need Excel file importer / connector
> ------------------------------------
>
> Key: TEIID-965
> URL: https://jira.jboss.org/browse/TEIID-965
> Project: Teiid
> Issue Type: Feature Request
> Components: Misc. Connectors
> Affects Versions: 7.0
> Reporter: Steven Hawkins
>
> Really need to add an Excel connector in SP1. The easiest way to do this is probably with one of the open source JDBC drivers that work over Excel files.
> Something like:
> https://xlsql.dev.java.net/ - Excel JDBC driver (GPL unfortunately, so we probably can't distribute the driver)
> http://www.andykhan.com/jexcelapi/ - Java Excel API (GPL too)
> http://xlsjdbc.sourceforge.net/ - LGPL (uses Apache POI), really cheesy and maybe not being updated
> http://jakarta.apache.org/poi/ - Excel API, event-based and not particularly friendly
> http://www.vistaportal.com/products/vistajdbc-QA.htm - commercial
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
15 years, 5 months
[JBoss JIRA] Commented: (TEIID-965) Need Excel file importer / connector
by Warren Gibson (JIRA)
[ https://jira.jboss.org/browse/TEIID-965?page=com.atlassian.jira.plugin.sy... ]
Warren Gibson commented on TEIID-965:
-------------------------------------
11-15-2010 WG - I was able to connect a MS Excel source to a Teiid VDB and query it using the following steps.
ODBC Excel as a Teiid source Test
Environment: Windows with Excel 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 Source Model with a Base Table representing the columns in the Excel files. I used a source model name to match the Excel target source.
3. Create a virtual table with same columns as the base table.
4. Create a vdb for Excel 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 using the jndi-name in the vdb. See example below.
8. Copy ds file 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 admin and password teiid)
10 Queried the source table and virtual table with a good sampling of queries.
DS FILE EXAMPLE:
?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 of source ,xls 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>
> Need Excel file importer / connector
> ------------------------------------
>
> Key: TEIID-965
> URL: https://jira.jboss.org/browse/TEIID-965
> Project: Teiid
> Issue Type: Feature Request
> Components: Misc. Connectors
> Affects Versions: 7.0
> Reporter: Steven Hawkins
>
> Really need to add an Excel connector in SP1. The easiest way to do this is probably with one of the open source JDBC drivers that work over Excel files.
> Something like:
> https://xlsql.dev.java.net/ - Excel JDBC driver (GPL unfortunately, so we probably can't distribute the driver)
> http://www.andykhan.com/jexcelapi/ - Java Excel API (GPL too)
> http://xlsjdbc.sourceforge.net/ - LGPL (uses Apache POI), really cheesy and maybe not being updated
> http://jakarta.apache.org/poi/ - Excel API, event-based and not particularly friendly
> http://www.vistaportal.com/products/vistajdbc-QA.htm - commercial
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
15 years, 5 months
[JBoss JIRA] Created: (TEIID-1359) DatabaseMetaData.getTables() not returning values when supplied with types.
by John Doyle (JIRA)
DatabaseMetaData.getTables() not returning values when supplied with types.
---------------------------------------------------------------------------
Key: TEIID-1359
URL: https://jira.jboss.org/browse/TEIID-1359
Project: Teiid
Issue Type: Bug
Components: JDBC Driver
Affects Versions: 7.1.1
Environment: SOA 5.1 ER3 w/ EDS, JBDS Beta 1
Reporter: John Doyle
Assignee: Steven Hawkins
I have a VDB deployed into EDS. When I connect with a Hibernate config I don't get any tables back. If I connect with the DTP tooling I do. The difference seems to be that Hibernate is passing "TABLE" and "VIEW" as table types, otherwise the params to the call are the same.
Here is the SQL created in the call that returns no tables:
SELECT VDBName AS TABLE_CAT, SchemaName AS TABLE_SCHEM, Name AS TABLE_NAME, CASE WHEN IsSystem = 'true' and UCASE(Type) = 'TABLE' THEN 'SYSTEM TABLE' ELSE UCASE(Type) END AS TABLE_TYPE, Description AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION, IsPhysical AS ISPHYSICAL FROM SYS.Tables g WHERE UCASE(VDBName) LIKE ? ESCAPE '\' AND UCASE(SchemaName) LIKE ? ESCAPE '\' AND UCASE(Name) LIKE ? ESCAPE '\' AND (CASE WHEN IsSystem = 'true' and UCASE(Type) = 'TABLE' THEN 'SYSTEM TABLE' ELSE UCASE(Type) END LIKE ? ESCAPE '\' OR CASE WHEN IsSystem = 'true' and UCASE(Type) = 'TABLE' THEN 'SYSTEM TABLE' ELSE UCASE(Type) END LIKE ? ESCAPE '\' ) ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME
And the SQL from the call that returns the correct tables.
SELECT VDBName AS TABLE_CAT, SchemaName AS TABLE_SCHEM, Name AS TABLE_NAME, CASE WHEN IsSystem = 'true' and UCASE(Type) = 'TABLE' THEN 'SYSTEM TABLE' ELSE UCASE(Type) END AS TABLE_TYPE, Description AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION, IsPhysical AS ISPHYSICAL FROM SYS.Tables g WHERE UCASE(VDBName) LIKE ? ESCAPE '\' AND UCASE(SchemaName) LIKE ? ESCAPE '\' AND UCASE(Name) LIKE ? ESCAPE '\' ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
15 years, 5 months
[JBoss JIRA] Created: (TEIID-1358) Getting wrong results when using JOINs in a query
by Wanja Pernath (JIRA)
Getting wrong results when using JOINs in a query
-------------------------------------------------
Key: TEIID-1358
URL: https://jira.jboss.org/browse/TEIID-1358
Project: Teiid
Issue Type: Bug
Components: Salesforce Connector
Affects Versions: 7.1
Environment: Fedora 12 64bit, Java 1.6.0_21 64bit
Teiid-7.1 is running on a fresh EAP 5.1 instance with 1.5GB of RAM.
Reporter: Wanja Pernath
Assignee: Steven Hawkins
I am getting completely wrong results if I am using a JOIN in my SELECT statement:
select o.Id,
o.Name,
o.Amount,
o.StageName,
o.ForecastCategory
from general.salesforce.Opportunity o
where 1=1
and o.Fiscal = '2011 3'
and o.OwnerId = (select Id from general.salesforce.User where Name = '****') --> real name
;
This statement returns 36 lines. I would now like to also get OpportunityLineItem(s) to this Opportunities, so I am adding a join
select o.Id,
(select Name from general.salesforce.User where id = o.OwnerId) as OpportunityOwner,
(select Name from general.salesforce.Account where id = o.AccountId) as AccountName,
o.Name,
o.Amount,
o.StageName,
o.ForecastCategory
from general.salesforce.OpportunityLineItem oli
LEFT INNER general.salesforce.Opportunity o ON oli.OpportunityId = o.id
where 1=1
and o.Fiscal = '2011 3'
and o.OwnerId = (select Id from general.salesforce.User where Name = '****') --> same name as above
;
Now I do not get any results back.
However, one may think that there is no OpportunityLineItem for those Opportunities above. But if I am querying just OpportunityLineItems of one Opp above, I do get many of them:
select * from general.salesforce.OpportunityLineItem oli where OpportunityId = '0066000000DoIHaAAN';
Maybe I am missing something here?
Thanks for any help.
Cheers,
Wanja
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
15 years, 5 months
[JBoss JIRA] Resolved: (TEIID-1356) EDS adminshell - changeVDBConnectionType - always fails
by Ramesh Reddy (JIRA)
[ https://jira.jboss.org/browse/TEIID-1356?page=com.atlassian.jira.plugin.s... ]
Ramesh Reddy resolved TEIID-1356.
---------------------------------
Resolution: Done
The ConnectionType is a Enum, and its wrapping was not provided by the code for managed object, thus the issue. It will work fine from "admin-console" with out this fix, but not through Admin API.
> EDS adminshell - changeVDBConnectionType - always fails
> -------------------------------------------------------
>
> Key: TEIID-1356
> URL: https://jira.jboss.org/browse/TEIID-1356
> Project: Teiid
> Issue Type: Bug
> Components: AdminApi
> Affects Versions: 7.1.1
> Reporter: Len DiMaggio
> Assignee: Ramesh Reddy
> Fix For: 7.1.1
>
>
> To recreate:
> groovy:000> getVDB("Interoperability", 2)
> ===> Interoperability.2[Weather[Weather/MessageWeather/java:WeatherDS]]
> groovy:000> changeVDBConnectionType("Interoperability", 2, "ANY")
>
> ERROR org.teiid.adminapi.AdminComponentException:
> Error Code:0 Message:Error Code:0 Message:Failed to convert from String value to java.lang.String type
> at org.teiid.client.util.ExceptionUtil.convertException (ExceptionUtil.java:72)
> at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler.invoke (SocketServerInstanceImpl.java:365)
> at org.teiid.net.socket.SocketServerConnection$1.invoke (SocketServerConnection.java:216)
> at $Proxy7.changeVDBConnectionType (Unknown Source)
> at org.teiid.adminapi.AdminFactory$AdminProxy.invoke (AdminFactory.java:76)
> at $Proxy7.changeVDBConnectionType (Unknown Source)
> at org.teiid.adminshell.AdminShell.changeVDBConnectionType (AdminShell.java:163)
> at org.teiid.adminshell.AdminShell$changeVDBConnectionType.callStatic (Unknown Source)
> at groovysh_evaluate.run (groovysh_evaluate:3)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
15 years, 5 months
[JBoss JIRA] Updated: (TEIID-1356) EDS adminshell - changeVDBConnectionType - always fails
by Ramesh Reddy (JIRA)
[ https://jira.jboss.org/browse/TEIID-1356?page=com.atlassian.jira.plugin.s... ]
Ramesh Reddy updated TEIID-1356:
--------------------------------
Assignee: Ramesh Reddy
Fix Version/s: 7.1.1
> EDS adminshell - changeVDBConnectionType - always fails
> -------------------------------------------------------
>
> Key: TEIID-1356
> URL: https://jira.jboss.org/browse/TEIID-1356
> Project: Teiid
> Issue Type: Bug
> Components: AdminApi
> Affects Versions: 7.1.1
> Reporter: Len DiMaggio
> Assignee: Ramesh Reddy
> Fix For: 7.1.1
>
>
> To recreate:
> groovy:000> getVDB("Interoperability", 2)
> ===> Interoperability.2[Weather[Weather/MessageWeather/java:WeatherDS]]
> groovy:000> changeVDBConnectionType("Interoperability", 2, "ANY")
>
> ERROR org.teiid.adminapi.AdminComponentException:
> Error Code:0 Message:Error Code:0 Message:Failed to convert from String value to java.lang.String type
> at org.teiid.client.util.ExceptionUtil.convertException (ExceptionUtil.java:72)
> at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler.invoke (SocketServerInstanceImpl.java:365)
> at org.teiid.net.socket.SocketServerConnection$1.invoke (SocketServerConnection.java:216)
> at $Proxy7.changeVDBConnectionType (Unknown Source)
> at org.teiid.adminapi.AdminFactory$AdminProxy.invoke (AdminFactory.java:76)
> at $Proxy7.changeVDBConnectionType (Unknown Source)
> at org.teiid.adminshell.AdminShell.changeVDBConnectionType (AdminShell.java:163)
> at org.teiid.adminshell.AdminShell$changeVDBConnectionType.callStatic (Unknown Source)
> at groovysh_evaluate.run (groovysh_evaluate:3)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
15 years, 5 months
[JBoss JIRA] Moved: (TEIID-1356) EDS adminshell - changeVDBConnectionType - always fails
by Van Halbert (JIRA)
[ https://jira.jboss.org/browse/TEIID-1356?page=com.atlassian.jira.plugin.s... ]
Van Halbert moved SOA-2574 to TEIID-1356:
-----------------------------------------
Project: Teiid (was: JBoss Enterprise SOA Platform)
Key: TEIID-1356 (was: SOA-2574)
Affects Version/s: 7.1.1
(was: 5.1.0.ER3)
Security: (was: Public)
Component/s: AdminApi
(was: EDS)
> EDS adminshell - changeVDBConnectionType - always fails
> -------------------------------------------------------
>
> Key: TEIID-1356
> URL: https://jira.jboss.org/browse/TEIID-1356
> Project: Teiid
> Issue Type: Bug
> Components: AdminApi
> Affects Versions: 7.1.1
> Reporter: Len DiMaggio
>
> To recreate:
> groovy:000> getVDB("Interoperability", 2)
> ===> Interoperability.2[Weather[Weather/MessageWeather/java:WeatherDS]]
> groovy:000> changeVDBConnectionType("Interoperability", 2, "ANY")
>
> ERROR org.teiid.adminapi.AdminComponentException:
> Error Code:0 Message:Error Code:0 Message:Failed to convert from String value to java.lang.String type
> at org.teiid.client.util.ExceptionUtil.convertException (ExceptionUtil.java:72)
> at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler.invoke (SocketServerInstanceImpl.java:365)
> at org.teiid.net.socket.SocketServerConnection$1.invoke (SocketServerConnection.java:216)
> at $Proxy7.changeVDBConnectionType (Unknown Source)
> at org.teiid.adminapi.AdminFactory$AdminProxy.invoke (AdminFactory.java:76)
> at $Proxy7.changeVDBConnectionType (Unknown Source)
> at org.teiid.adminshell.AdminShell.changeVDBConnectionType (AdminShell.java:163)
> at org.teiid.adminshell.AdminShell$changeVDBConnectionType.callStatic (Unknown Source)
> at groovysh_evaluate.run (groovysh_evaluate:3)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
15 years, 5 months
[JBoss JIRA] Issue Comment Edited: (TEIID-1355) adminshell.bat cannot read script from stdin
by Ramesh Reddy (JIRA)
[ https://jira.jboss.org/browse/TEIID-1355?page=com.atlassian.jira.plugin.s... ]
Ramesh Reddy edited comment on TEIID-1355 at 11/11/10 12:22 PM:
----------------------------------------------------------------
I am not sure what you want us do here, escaping is OS dependent. Executing the single statement is the behaviour of the Groovy shell. If you want you can add your script to a file then execute
adminshell.bat load file.groovy
was (Author: rareddy):
I am not sure what you us do here, escaping is OS dependent. Executing the single statement is the behaviour of the Groovy shell. If you want you can add your script to a file then execute
adminshell.bat load file.groovy
> adminshell.bat cannot read script from stdin
> --------------------------------------------
>
> Key: TEIID-1355
> URL: https://jira.jboss.org/browse/TEIID-1355
> Project: Teiid
> Issue Type: Bug
> Components: Tools
> Affects Versions: 7.1
> Environment: windows
> Reporter: Aleksandar Kostadinov
>
> On windows adminshell doesn't read script if supplied on stdin. It looks like it is reading from terminal (unlike adminshell on linux). I see that one can supply expression as first argument to adminshell but it seems to accept only a single expression and additionally complex expressions are a hell to escape properly.
> Assuming adminshell is intended to be used for automating administrative tasks, it needs a straightforward way to execute scripts non-interactively so current behavior on windows needs to be fixed.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
15 years, 5 months