9 Replies Latest reply on May 17, 2016 9:05 AM by Marco Ardito

    Not able to connect to db as well as not able to deploy vdb.

    Nishant Agrawal Novice

      Hi Ramesh ,

       

      I created a db named mysql_db having only tables and created a datasource corresponding to it.

      Also , I created a VDB dynamically corresponding to the datasource.

      But while creating VDB , we are adding below properties for every VDB created dynamically :

       

      <property name="importer.tableTypes" value="TABLE">

      <property name="importer.tableTypes" value="VIEW">

       

      But as mysql_db doesn't have any views it was creating problem saying models have errors.

       

      So , while deploying VDB dynamically , how we can know whether database has any views present or not in it through teiid.

       

      So , that we can add only those properties in VDB .

        • 1. Re: Not able to connect to db as well as not able to deploy vdb.
          Steven Hawkins Master

          > So , that we can add only those properties in VDB .

           

          The setting of import properties is supposed to be based upon your knowledge of the source, there isn't an interactive model for determining the import properties.  You also need a single property name value pair with comma separated values:

           

          <property name="importer.tableTypes" value="TABLE,VIEW">

          • 2. Re: Not able to connect to db as well as not able to deploy vdb.
            Marco Ardito Master

            But, I am not specifying that property and Teiid dynamic vdb imports mysql databases just fine, with or without views...

            Or am I missing something? Do you have a strict requirement to include that option?

             

            Marco

            • 3. Re: Not able to connect to db as well as not able to deploy vdb.
              Ramesh Reddy Master

              When not specified it is treated as "*" for all. See DatabaseMetaData (Java Platform SE 7 ) types parameter in this call. If you want to restrict the types for importing, then you want to use this property.

              • 4. Re: Not able to connect to db as well as not able to deploy vdb.
                Marco Ardito Master

                Yes but he said

                "But as mysql_db doesn't have any views it was creating problem saying models have errors."

                 

                btw, when/where "saying" is referred to? web gui, jboss-cli, manual deploy...?

                 

                I never had this error., and I have a VDB loading over 12 different mysql db, and some of them have views, others don't, but all are loading fine.

                 

                Could it be that specifying even TABLE,VIEW instead of omitting the properry somehow "forces" the vdb to look for views (that sometime aren't there)?

                 

                Just wondering

                • 5. Re: Not able to connect to db as well as not able to deploy vdb.
                  Steven Hawkins Master

                  > btw, when/where "saying" is referred to? web gui, jboss-cli, manual deploy...?

                   

                  We can infer that it's the empty model/schema validation error upon deployment.

                   

                  > Could it be that specifying even TABLE,VIEW instead of omitting the properry somehow "forces" the vdb to look for views (that sometime aren't there)?

                   

                  In a way yes.  Specifying the property forces the import logic to look only for those specific types, rather than all table types.  In cases where you want SYSTEM TABLES or SYSTEM VIEWS for example, they will be excluded if you just look for TABLE,VIEW. 

                   

                  This looks like it's mostly an issue with trying to split the property into multiple elements and/or not knowing exactly what is needed from the source.  Ideally that is what the designer tooling is for - as it can assist in determining the appropriate import settings.

                  • 6. Re: Not able to connect to db as well as not able to deploy vdb.
                    Ramesh Reddy Master

                    When you omit the property, it is tries to import indexes, temp tables etc, it possible some times based on the database is setup we have seen others complain about  index importing, he may be running into that.

                    • 7. Re: Not able to connect to db as well as not able to deploy vdb.
                      Nishant Agrawal Novice

                      Ramesh,

                       

                      If I do not add property for importer.tableTypes in dynamic VDB, then it return a schema info with the all tables and views and for both(table and view) in result it gives a statement as

                       

                      CREATE FOREIGN TABLE "xxxxxxx.xxxxxxxx" (..

                       

                      That means for table and view it say as TABLE. Due to this it is very difficult to identify the actual table and view. because of this I added two model for same connection. one having property importer.tableTypes set to TABLE and other set to view. so when i get schema from TABLE model, it return schema info for only tables and when get schema from VIEW it return only view info(even though it says foreign table but they are actually view in database)

                       

                      My sample VDb as below -

                       

                      <?xml version="1.0" ?>

                      <vdb name="DSMYSQL" version="1">

                      <description>VDB for: DSMySQL, Version:1</description>

                      <connection-type>BY_VERSION</connection-type>

                      <model name="DSMYSQL_tbl" type="PHYSICAL" visible="true">

                      <property name="importer.tableTypes" value="TABLE"></property>

                      <source name="DSMYSQL_tbl" translator-name="mysql5" connection-jndi-name="java:/DSMYSQL"> </source>

                      </model>

                       

                       

                      <model name="DSMYSQL_vu" type="PHYSICAL" visible="true">

                      <property name="importer.tableTypes" value="VIEW"> </property>

                      <source name="DSMYSQL_vu" translator-name="mysql5" connection-jndi-name="java:/DSMYSQL"> </source>

                      </model>

                      </model>

                      </vdb>

                       

                      It Work if database has both tables and views; but when views are not present then dynamic VDB deployment is fail. with above mentioned error. for deployment, I am uploading vdb xml file from admin console ui. And getting above error in server log.

                       

                      My concern is to get tables schema info and views schema info separately. 

                      • 8. Re: Not able to connect to db as well as not able to deploy vdb.
                        Ramesh Reddy Master

                        Nishant,

                         

                        The term "FOREIGN TABLE" is in the context of Teiid, not in the context of your database. For Teiid, the way it interacts with TABLE or VIEW is exactly same, thus Teiid does not differentiate. I am not sure how you are intending to use the TABLE/VIEW difference in Teiid, but it is certainly possible to add metadata property to the TABLE definition indicating if it is VIEW or TABLE. That would take a enhancement. Since this information will NOT be used by Teiid, you can layout your usecase why you need it, then we can take upon this issue, or you can try extending the "jdbc" translator for it.

                         

                        Now as per the error, Teiid has a requirement that all models MUST have at least one TABLE, PROCEDURE to be valid, I think you are running into that. I think, it may be valid request to allow a "empty" model in VDB based on some flag. Essentially deployment of VDB will just ignore the model defined. This also would require a enhancement request.

                         

                        Ramesh..

                        • 9. Re: Not able to connect to db as well as not able to deploy vdb.
                          Marco Ardito Master

                          As a workaround, If your views have a recognizable pattern naming, like "view_A", "view_B",

                          you could also import everything (*) but instead of specify tableTypes , filter out (or in) tables and  views using combinations of

                           

                          schemaPattern, tableNamePattern, excludeTables,

                           

                          just another idea, maybe you already discarded this

                           

                          Marco