9 Replies Latest reply on Nov 29, 2010 11:24 AM by rareddy

odbc/jdbc, where are the quickstart's portfolio data?

m.ardito Nov 25, 2010 5:58 AM

Hi,

i'm moving first steps around jboss as and teiid.

i have set up all the quickstart resources (i.e: the portfolio derby database, and the csv file)

now i can successfully connect from msacces (on win7prox64) to the "DynamicPortfolio" vdb on the teiid server (7.2)

but, as i try to link tables there, i get only these tables (examining msaccess table name and "connect" property):

(all linked tables have the same connect property text:

ODBC;DSN=teiid-example_ansi;DATABASE=DynamicPortfolio.1;SERVER=192.168.3.70;PORT=35432;UID=admin;PWD=teiid;CA=d;A6=;A7=100;A8=4096;B0=255;B1=8190;BI=0;C2=dd_;;CX=1b502bb;A1=7.4)

SYS_Columns:
SYS_DataTypes
SYS_KeyColumns
SYS_Keys:
SYS_ProcedureParams:
SYS_Procedures:
SYS_Properties:
SYS_ReferenceKeyColumns:
SYS_Schemas:
SYS_Tables:
SYS_VirtualDatabases:
SYSADMIN_MatViews:
SYSADMIN_VDBResources:

looling at data, i see it's much interesting stuff, but.. ...i expected to see at least the derby "portfolio" tables..

? i am a bit confused, maybe i skipped some steps, anyone?

edit: do i need to create a "Logical Model" in the vdb xml to actually see the quickstart example data?

Thx, Marco

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?
Actions
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 VIEW
               TABLE
               VIEW
               PROCEDURE
               UDT
               SEQUENCE
          SYSADMIN
               SYSTEM TABLE
                SYSTEM VIEW
               TABLE
                    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
Actions
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..
Actions
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:46

here 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>

    
    <property name="UseConnectorMetadata" value="cached" />

    
    <model name="MarketData">
        
        <source name="text-connector" translator-name="file" connection-jndi-name="java:marketdata-file"/>
    </model>

    <model name="Accounts">
        
        <property name="importer.useFullSchemaName" value="false"/>

         
        <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>
      

      <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       |YES

ij> 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

JBoss AS Administration Console - Mozilla Firefox_2010-11-29_09-52-08.png 55.9 KB
Actions
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
Actions
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
Actions
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?
Actions
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
Actions
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..
Actions

Go to original post