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

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

    Marco Ardito Master

      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?
          Ramesh Reddy Master

          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?
            Marco Ardito Master

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

            • 3. Re: odbc, where are the quickstart's portfolio data?
              Ramesh Reddy Master

              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?
                Marco Ardito Master

                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>

                 

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

                 

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

                • 5. Re: odbc, where are the quickstart's portfolio data?
                  Marco Ardito Master

                  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

                  • 6. Re: odbc/jdbc, where are the quickstart's portfolio data?
                    Marco Ardito Master

                    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?
                      Ramesh Reddy Master

                      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?
                        Marco Ardito Master

                        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?
                          Ramesh Reddy Master

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