-
1. Re: odbc, where are the quickstart's portfolio data?
rareddy Nov 24, 2010 12:38 PM (in response to m.ardito)You are right you should see the "portfolio" tables. You should be able to query the tables too. Can issue a query? Can see if same issue is there if you some other ODBC tool like Excel?
-
2. Re: odbc, where are the quickstart's portfolio data?
m.ardito Nov 25, 2010 5:57 AM (in response to rareddy)..well... i can issue any query from there, only against those tables, anyway (i guess those are teiid system tables, or something like that)
I tried from excel query but... i don't know why my excel has the port option locked to 5432 and i can't change that (txtbox disabled)
i tried from openoffice base 3.2.1, through odbc connector, and i succeed (same dsn as msaccess), but... i can only see a tree like:
==================
DynamicPortfolio
SYSADMIN
Matviews
VBDResources
==================
but can't open any of the two "tables" shown there,
"opening" the tables (i.e executing:
==================
SELECT * FROM "DynamicPortfolio"."SYSADMIN"."MatViews"
==================
)
i get an error like
==================
SQL state: 50000
Error code: 7...plus what seems a bunch of binary simbols
==================
i tried from openoffice base through JDBC
i configured the connection as
==================
data source URL:
jdbc:teiid:DynamicPortfolio@mm://192.168.3.70:35432
i also have to specify a JDBC class driver, so i provided (copied the jar from the teiid server)
C:\Users\ardito\Desktop\teiid-7.2.0.Final-client.jar
[edit: now after playing with dquirrel i got it : the path it needs is really
"org.teiid.jdbc.TeiidDriver"
but i don't know where to put the jar file for ooo base to see it...]
but openoffice base says "i can't load the class"
==================
now, i'm downloading SQirrel to test with that... will report later.
edit: tried SQuirrel
i installed and update the stable SQuirrel 3.2.0
tried the jdbc:odbc bridge with the same dsn as before with msaccess and ooobase, and the connection succeeds, but here i see in the objects tree:
==================
alias
DynamicPortfolio
MarketData
SYSTEM TABLE
SYSTEM VIEWTABLE
VIEW
PROCEDURE
UDT
SEQUENCE
SYSADMIN
SYSTEM TABLE
SYSTEM VIEWTABLE
Matviews
TRIGGER
INDEX
VBDResources
TRIGGER
INDEX
VIEW
PROCEDURE
UDT
SEQUENCE
==================
...but i don't know how to test...
in the SQL view i tried
==================
select * from "DynamicPortfolio"."MarketData"
==================
and got no data but only this error in the log
==================
Error: ERROR: Group does not exist: DynamicPortfolio.MarketData
org.teiid.jdbc.TeiidSQLException: Group does not exist: DynamicPortfolio.MarketData;
Error while executing the query
SQLState: 50000
ErrorCode: 7==================
edit (again):
now i can successfully connect SQuirrel through teiid driver
using
==================
jdbc:teiid:DynamicPortfolio@mm://192.168.3.70:31000
==================
i still cannot see any portfolio data...i see many tables (SYS, etc)
i can also connect with SQuirrel to a lan mysql server with no problems...
really lost here...
Marco
-
3. Re: odbc, where are the quickstart's portfolio data?
rareddy Nov 27, 2010 6:52 PM (in response to m.ardito)Marco,
Using SquirreL can issue query to your Derby instance? Like can you execute query like
SELECT * FROM Customer
This will prove that you can see your Derby database correctly. I suspect you have it configured but not able access yet. Also there is a model called MarketData not a table. That model is of type "text", see the README file in the example how to query the data from the file. Also, see this for complete details about how to convert text to table.
Ramesh..
-
4. Re: odbc, where are the quickstart's portfolio data?
m.ardito Nov 29, 2010 4:28 AM (in response to rareddy)well, i don't know what happened... today i get always:
<<teiid_jdbc: Remote org.teiid.client.security.LogonException: VDB "DynamicPortfolio" version "latest" does not exist.>>
but as you can see from jboss screenshot below, the vdb appears up and running.
the server has never restarted...
jbadmin@jbossas01:~$ who -b
avvio di sistema 2010-11-18 13:46here is the "quickstart" xml i'm using
===========================
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="DynamicPortfolio" version="1"><description>A Dynamic VDB</description>
<!--
Setting to use connector supplied metadata. Can be "true" or "cached".
"true" will obtain metadata once for every launch of Teiid.
"cached" will save a file containing the metadata into
the deploy/<vdb name>/<vdb version/META-INF directory
-->
<property name="UseConnectorMetadata" value="cached" />
<!--
Each model represents a access to one or more sources.
The name of the model will be used as a top level schema name
for all of the metadata imported from the connector.NOTE: Multiple model, with different import settings, can be bound to
the same connector binding and will be treated as the same source at
runtime.
-->
<model name="MarketData">
<!--
Each source represents a translator and data source. There are
pre-defined translators, or you can create one. ConnectionFactories
or DataSources in JBoss AS they are typically defined using "xxx-ds.xml" files.
-->
<source name="text-connector" translator-name="file" connection-jndi-name="java:marketdata-file"/>
</model><model name="Accounts">
<!--
JDBC Import settingsimporter.useFullSchemaName directs the importer to drop the source
schema from the Teiid object name, so that the Teiid fully qualified name
will be in the form of <model name>.<table name>
-->
<property name="importer.useFullSchemaName" value="false"/><!--
This connector is defined in the "derby-connector-ds.xml"
-->
<source name="derby-connector" translator-name="derby" connection-jndi-name="java:PortfolioDS"/>
</model></vdb>
===========================
here is the portfolio-ds
===========================
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<xa-datasource>
<jndi-name>PortfolioDS</jndi-name>
<isSameRM-override-value>false</isSameRM-override-value>
<xa-datasource-class>org.apache.derby.jdbc.ClientXADataSource</xa-datasource-cl$
<xa-datasource-property name="DatabaseName">teiid/accounts</xa-datasource-prope$
<xa-datasource-property name="PortNumber">1527</xa-datasource-property>
<xa-datasource-property name="ServerName">192.168.3.70</xa-datasource-property><track-connection-by-tx>true</track-connection-by-tx>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation><max-pool-size>5</max-pool-size>
<min-pool-size>1</min-pool-size>
<metadata>
<type-mapping>Derby</type-mapping>
</metadata>
</xa-datasource>
</datasources>===========================
and here is the marketdata file xml
===========================
<?xml version="1.0" encoding="UTF-8"?>
<connection-factories>
<no-tx-connection-factory>
<jndi-name>marketdata-file</jndi-name>
<rar-name>teiid-connector-file.rar</rar-name>
<connection-definition>javax.resource.cci.ConnectionFactory</connection-definition>
<!--
All the available properties for this connector are defined inside the "ra.xml" defined inside the rar
file mentioned above.
--><config-property name="ParentDirectory" type="java.lang.String">${jboss.server.home.dir}/teiid-examples/dynamicvdb-portfolio/data</config-property>
<max-pool-size>20</max-pool-size>
</no-tx-connection-factory>
</connection-factories>
===========================
derby is running on the same host
===========================
jbadmin@jbossas01:~/derby/db-derby$ ./bin/startNetworkServer
2010-11-29 08:45:21.878 GMT : Gestore della sicurezza installato con i criteri d i sicurezza di base del server.
2010-11-29 08:45:22.648 GMT : Apache Derby Server di rete: 10.6.2.1 - (999685) a vviato e pronto ad accettare connessioni sulla porta 1527===========================
and here is what i can see there...
===========================
ij> CONNECT 'jdbc:derby://localhost:1527/teiid/accounts;';
ij> show tables;
TABLE_SCHEM |TABLE_NAME |REMARKS
------------------------------------------------------------------------
SYS |SYSALIASES |
SYS |SYSCHECKS |
SYS |SYSCOLPERMS |
SYS |SYSCOLUMNS |
SYS |SYSCONGLOMERATES |
SYS |SYSCONSTRAINTS |
SYS |SYSDEPENDS |
SYS |SYSFILES |
SYS |SYSFOREIGNKEYS |
SYS |SYSKEYS |
SYS |SYSPERMS |
SYS |SYSROLES |
SYS |SYSROUTINEPERMS |
SYS |SYSSCHEMAS |
SYS |SYSSEQUENCES |
SYS |SYSSTATEMENTS |
SYS |SYSSTATISTICS |
SYS |SYSTABLEPERMS |
SYS |SYSTABLES |
SYS |SYSTRIGGERS |
SYS |SYSVIEWS |
SYSIBM |SYSDUMMY1 |
APP |ACCOUNT |
APP |CUSTOMER |
APP |HOLDINGS |
APP |PRODUCT |26 righe selezionate
ij> describe APP.PRODUCT;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID |INTEGER |0 |10 |10 |NULL |NULL |NO
SYMBOL |VARCHAR |NULL|NULL|16 |NULL |32 |YES
COMPANY_NAME |VARCHAR |NULL|NULL|256 |NULL |512 |YESij> select * from product;
ID |SYMBOL |COMPANY_NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------
1002 |BA |The Boeing Company
1003 |MON |Monsanto Company
1004 |PNRA |Panera Bread Company
1005 |SY |Sybase Incorporated
1006 |BTU |Peabody Energy
1007 |IBM |International Business Machines Corporation
1008 |DELL |Dell Computer Corporation
1010 |HPQ |Hewlett-Packard Company
1011 |GTW |Gateway, Incorporated
1012 |GE |General Electric Company
1013 |MRK |Merck and Company Incorporated
1014 |DIS |Walt Disney Company
1015 |MCD |McDonalds Corporation
1016 |DOW |Dow Chemical Company
1018 |GM |General Motors Corporation
1024 |SBGI |Sinclair Broadcast Group Incorporated
1025 |COLM |Columbia Sportsware Company
1026 |COLB |Columbia Banking System Incorporated
1028 |BSY |British Sky Broadcasting Group PLC
1029 |CSVFX |Columbia Strategic Value Fund
1030 |CMTFX |Columbia Technology Fund
1031 |F |Ford Motor Company
1033 |FCZ |Ford Motor Credit Company
1034 |SAP |SAP AG
1036 |TM |Toyota Motor Corporation===========================
any ideas of what could be wrong..?
M
-
5. Re: odbc, where are the quickstart's portfolio data?
m.ardito Nov 29, 2010 7:42 AM (in response to m.ardito)i restarted the server, and... DynamicPortfolio VDB was DOWN! (before was UP).
had to change the
<xa-datasource-property name="ServerName">192.168.3.70</xa-datasource-property>
back to
<xa-datasource-property name="ServerName">localhost</xa-datasource-property>
and it went UP... ?!?!? (in the first tries i had to change from localhost to the IP exactly for the same reason...
(see http://community.jboss.org/message/572810#572810)
now i can connect from SQuirrel to the DynamicPortfolio VDB, but i get no Table data (see attachment 2)
instead, i created another TestVBD that connects to a mysql through a my-ds which is reported DOWN but WORKS.
(see http://community.jboss.org/thread/159316 and attachment 1)
simply, dynamicportfolio (teiid connected to derby) show nothing under marketdata >> table
while testvdb (teiid connected to mysql) show allt he mysql tables under testmodel>> table
so i assume teiid is connected and working well with mysql
but is connected and NOT wirking well with derby, since i can see no data (i can see that data accessing derby db with ij from the shell)
if i execute "select * from customer", in dynamicportfolio vdb, as you suggested, i get no result, and an error
"Error: Remote org.teiid.api.exception.query.QueryResolverException: Group does not exist: Customer
SQLState: 50000
ErrorCode: 0"marco
-
testvdb.c.png 63.9 KB
-
dynamicportfolio.png 45.6 KB
-
-
6. Re: odbc/jdbc, where are the quickstart's portfolio data?
m.ardito Nov 29, 2010 8:57 AM (in response to m.ardito)I think i found the trouble with the "quickstart" examples... apparently i had to "touch" the portfolio-vdb.xml file,
and now i am able to run this query in SQuirrel...
select stock.* from product,(call MarketData.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS symbol string,price bigdecimal HEADER) stock where product.symbol=stock.symbol
and get the results as expected...
Thanks, Ramesh for your patience
Marco
-
7. Re: odbc, where are the quickstart's portfolio data?
rareddy Nov 29, 2010 10:55 AM (in response to m.ardito)Marco,
During the deployment of the VDB, Teiid query engine tries to load the metadata required from the source systems. If for any reason the data source is DOWN, it fails the metadata load, then it marks the VDB as inactive. At this stage VDB is unusable. Once you correct the data source configuration, VDB requires another deployment, in this case a "touch" would do the same.
However in your case, VDB is has been flagged as active by a bug, thus you were able to access it. This has been logged and fixed here. What version of Teiid are you using?
Now that you have fixed your issue, is ODBC still an issue?
-
8. Re: odbc, where are the quickstart's portfolio data?
m.ardito Nov 29, 2010 11:10 AM (in response to rareddy)i'm using teiid 7.2 (teiid-7.2.0.Final-jboss-dist.zip)
now odbc looks quite fine from SQuirrel.
quite nice also from msaccess, nut some mysql tables are not linkable through odbc, although they show in the odbc source tables in msaccess.
i also tried from ooo base , 3.2.1, and through odbc can link all the tables, but got errors opening data - not that important, though, i will later try also ooo base jdbc
and i will report later, probably
yes, i must get used to "touch" vdb xml more than it's intuitive
now i'm going to learn anbout the desinger too
many many thanks, really nice community, Marco
-
9. Re: odbc, where are the quickstart's portfolio data?
rareddy Nov 29, 2010 11:24 AM (in response to m.ardito)Thanks. The trick is to make sure the data sources are correctly configured before you deploy the VDB, then no need to re-deploy the VDBs again and again.
Yes, I have seen the issue with Open Office too, was not clear what was the issue.
Ramesh..