8 Replies Latest reply on Apr 4, 2014 8:24 AM by rareddy

    Teiid VDBs for help

    wanglei

      I use Jboss as 7.1.1 and teiid 8.2 create a vdbs. The VDBs configure is:

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>  
      <vdb name="SybiqVDB" version="1">  
      <description>My sybiq VDB</description>  
      <property name="UseConnectorMetadata" value="cache" />  
      <model visible="true" type="PHYSICAL" name="sybiq">  
      <property name="importer.useFullSchemaName" value="true"/>  
      <property name="supports-multi-source-bindings" value="true"/>  
      <source name="sybiq21" translator-name="sybase" connection-jndi-name="java:sybiq21DS"/>  
      <source name="sybiq22" translator-name="sybase" connection-jndi-name="java:sybiq22DS"/>  
      </model>  
      </vdb> 
      
      

      DataSource sybiq21(sybiq22) configure is:

      <datasource jndi-name="java:/sybiq21DS" pool-name="sybiq21DS" enabled="true" use-java-context="true">    
          <connection-url>jdbc:sybase:Tds:172.1.0.21:2640/dmpiq1</connection-url>    
          <driver>sybase</driver>    
          <security>    
              <user-name>zcss7db</user-name>    
              <password>zcss7db</password>    
          </security>
          <validation>
              <check-valid-connection-sql>select name from sysobjects where type='U' order by name</check-valid-connection-sql>
          </validation>    
      </datasource>    
      <datasource jndi-name="java:/sybiq22DS" pool-name="sybiq22DS" enabled="true" use-java-context="true">    
          <connection-url>jdbc:sybase:Tds:172.1.0.22:2640/dmpiq1</connection-url>    
          <driver>sybase</driver>    
          <security>    
              <user-name>zcss7db</user-name>    
              <password>zcss7db</password>    
          </security>
          <validation>
              <check-valid-connection-sql>select name from sysobjects where type='U' order by name</check-valid-connection-sql>
          </validation>    
      </datasource>
      

      DataSource sybiq21DS hava one table vdb_gprs but DataSource sybiq22DS havn't.  The VDBs is running .Using Java code I can  get the correct query results.

      import java.util.*;
      import javax.naming.*;
      import javax.sql.DataSource;
      import java.sql.*;
      import java.io.*;
      public class connTest
      {
          public static void main(String[] args)
          {
          String url = "jdbc:teiid:SybiqVDB@mm://172.1.0.21:31000";
          String sql = "select count(1) from test";
          try
          {
      Class.forName("org.teiid.jdbc.TeiidDriver").newInstance();
          Connection connection = DriverManager.getConnection(url, "user", "user");
              Statement statement = connection.createStatement();
              statement.execute("set partialResultsMode true");
              ResultSet results = statement.executeQuery(sql);
              while(results.next()) {  System.out.println(results.getString(1));  }
              results.close();
              statement.close();
          } 
      catch(Exception e)
      { e.printStackTrace(); }
      }
      }
      

      Now I want to write "statement.execution (" set partialResultsMode true");" To VDBs configuration file or To file “standalone-teiid.xml”  ,rather than in Java code setting. How should I set

        • 1. Re: Teiid VDBs for help
          rareddy

          you can use connection URL as

           

          String url = "jdbc:teiid:SybiqVDB@mm://172.1.0.21:31000;partialResultsMode=true";

          • 2. Re: Teiid VDBs for help
            wanglei

            I mean, I don't want to use the "String url = "jdbc:teiid:SybiqVDB@mm://172.1.0.21:31000;partialResultsMode=true";"  or  "statement.execute("set partialResultsMode true");" in  java code

            • 3. Re: Teiid VDBs for help
              tom9729

              Lei,

               

              From your original post it sounds like you want to set this property either in the VDB or in the Teiid server config, but I do not think this is possible.

               

              It sounds like the two ways to do this are to either set it in the JDBC string (URL), or issue a SQL command to alter the session. Why don't these work for you?

               

              Usually you would want to externalize the JDBC string (URL) anyways, so I do not see why this is a problem. For example, if you are using an app server like JBoss you could setup a datasource "MyDatasource" in the server config and that is where you would set the JDBC string. In your code you would lookup "MyDatasource" with JNDI and get connections from there.

               

              Thanks,

              Tom

              • 4. Re: Teiid VDBs for help
                shawkins

                Tom is spot on.  If you are configuring access to Teiid through a pool you have a way through configuration and not java code to set options.  If however that doesn't make sense for your usage, then log an issue to have partialResultsMode if not all execution properties something that can have defaults set through the vdb.xml.

                 

                Thanks,

                Steve

                • 5. Re: Teiid VDBs for help
                  shirley_0201

                  Using the above  configuration, I deploy the .vdb file first, then I create a table tableA in database dmpiq1, after that I can't get this table. The same problem as below. 

                   

                  https://bugzilla.redhat.com/show_bug.cgi?id=780412#c0

                  • 6. Re: Teiid VDBs for help
                    rareddy

                    You need to create the table in the database before you deploy the VDB in JBoss AS. There is no workaround other than running "reload" of the VDB after deploying the VDB. You can use web-console to reload the VDB.

                    1 of 1 people found this helpful
                    • 7. Re: Teiid VDBs for help
                      shirley_0201

                      Thank you.

                      Another question. Can ‘partialResultsMode=true’ be used in odbc? Or some other attributes like partialResultsMode be used in odbc?

                      • 8. Re: Teiid VDBs for help
                        rareddy

                        Yes, use SET partialResultsMode with ODBC.

                         

                        If you want other ways to enable this at VDB level log enhancement request in Teiid JIRA as Steve indicated.

                         

                        Ramesh..

                        1 of 1 people found this helpful