4 Replies Latest reply on Mar 17, 2014 9:20 PM by 文芳 张

    how to use vdb to query data from integrated datasource

    文芳 张 Newbie

      hi,

          i'm a newer for jboss.now i am using jboss AS 7.1 with Teiid 8.2. the vdb has been activied. and follow is vdb configuration file:

      cat deployments/mysqltest-vdb.xml

       

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

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

      <description>A Mysql VDB</description>

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

      <model name="mysql89m">

              <property name="importer.useFullSchemaName" value="true"/>

              <property name="importer.schemaPattern" value="zhangwf"/>

              <source name="mysql89" translator-name="mysql5" connection-jndi-name="java:mysql89DS"/>

      </model>

      <model name="mysql90m">

              <property name="importer.useFullSchemaName" value="true"/>

              <property name="importer.schemaPattern" value="zhangwf"/>

              <source name="mysql90" translator-name="mysql5" connection-jndi-name="java:mysql90DS"/>

      </model>

      </vdb>

      and follow is vdb configuration file standalone-teiid.xml :

                      <datasource jndi-name="java:/mysql89DS" pool-name="mysql89DS" enabled="true" use-java-context="true">

                          <connection-url>jdbc:mysql://192.168.7.89:3306/zhangwf</connection-url>

                          <driver>mysql</driver>

                          <security>

                              <user-name>root</user-name>

                              <password>123</password>

                          </security>

                      </datasource>

                      <datasource jndi-name="java:/mysql90DS" pool-name="mysql90DS" enabled="true" use-java-context="true">

                          <connection-url>jdbc:mysql://192.168.7.90:3306/zhangwf</connection-url>

                          <driver>mysql</driver>

                          <security>

                              <user-name>root</user-name>

                              <password>123</password>

                          </security>

                      </datasource>

                      <datasource jndi-name="java:/teiidDS" pool-name="teiidDS">

                          <connection-url>jdbc:teiid:myvdb@mm://localhost:31000</connection-url>

                          <driver>teiid</driver>

                          <pool>

                              <prefill>false</prefill>

                              <use-strict-min>false</use-strict-min>

                              <flush-strategy>FailingConnectionOnly</flush-strategy>

                          </pool>

                          <security>

                              <user-name>admin</user-name>

                              <password>admin</password>

                          </security>

                      </datasource>

      i want to know how to acess database with vdb ? and is it right that "select e.id,e.name from ((select a.id,a.name from mysql89m.zhangwf.test) union all (select b.id,b.name from mysql90m.zhangwf.test)) as e"?

      or can someone offer some java API examples for me? by the way,if a server of vdb is down ,then is the whole vdb avilable?

      thanks a lot.

        • 1. Re: how to use vdb to query data from integrated datasource
          Ramesh Reddy Master

          You can access the VDB using JDBC or ODBC. See the documentation Client Developer's Guide - Teiid 8.7 (draft) - Project Documentation Editor

           

          Here is a simple JDBC client example Connecting to Teiid - SimpleClient Example - Teiid Examples - Project Documentation Editor

           

          Yes, you can execute query like how you defined above. You can also define views based on the above query too. If the VDB server is down then VDB is not available.

           

          Ramesh..

          • 2. Re: how to use vdb to query data from integrated datasource
            文芳 张 Newbie

            thanks Ramesh Reddy .i have read the simple JDBC client example Connecting to Teiid - SimpleClient Example - Teiid Examples - Project Documentation Editor

            and i try it .but it occured error,

             

            Exception thrown org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryResolverException: Group specified is ambiguous, resubmit the query by fully qualifying the group name: test

             

            follow is my example:

            public void execute() throws SQLException {

                String url = "jdbc:teiid:myvdb@mm://localhost:31000";

                String sql = "select id,name from test";

                Class.forName("org.teiid.jdbc.TeiidDriver");

                Connection connection;

                try{

                    connection = DriverManager.getConnection(url, "user", "user");

                    Statement statement = connection.createStatement();

                    ResultSet results = statement.executeQuery(sql);

                    while(results.next()) {

                      System.out.println(results.getString(1));

                      System.out.println(results.getString(2));

                    }

                    results.close();

                    statement.close();

                } catch (SQLException e){

                    e.printStackTrace();

                    throw e;

                } finally {

                    try{

                      connection.close();

                    }catch(SQLException e1){

                      // ignore

                    }

                }

            }

             

            and vdb myvdb has two models:mysql89m,mysql90m. each model has one datasource .and each datasource have the same table:test. i want to know that  if we can wrtite sql: select column a from  table_name  or select column a from vdb_name.table_name? though i have tried that the sql: select column a from model_name.database_name.table_name can excute succesfully. but the  type of sql is so complex. i think it may have simple writing by vdb_name instead of model_name and database_name.because database_name have writed into configuration file.

            1 of 1 people found this helpful
            • 3. Re: how to use vdb to query data from integrated datasource
              Steven Hawkins Master

              You have to qualify in some form if the table names are the same.  Rather than "select column a from model_name.database_name.table_name" you should just be able to use  "select column a from database_name.table_name".  If the schema/database_names are the same in each instance, then you can import with useFullSchemaName=false and use " select column a from model_name.table_name" instead.

               

              Do each of the sources represent the same structures such that this could be a multi-source scenario?

               

              Steve

              • 4. Re: how to use vdb to query data from integrated datasource
                文芳 张 Newbie

                yes,i use multi-source scenario now。but before i mistake that if i use single source  i can access it by "select column a from model_name.table_name". Thanks for your help. i have found the configuration of multi-source .  and i have sucessed . follow is vdb (i use teiid 8.2):

                <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

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

                <description>A Mysql VDB</description>

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

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

                        <property name="supports-multi-source-bindings" value="true"/>

                        <property name="importer.useFullSchemaName" value="true"/>

                        <source name="mysql89" translator-name="mysql5" connection-jndi-name="java:mysql89DS"/>

                        <source name="mysql90" translator-name="mysql5" connection-jndi-name="java:mysql90DS"/>

                </model>

                </vdb>

                and follow is my test jsp:

                <%@page contentType="text/html"

                  import="java.util.*,javax.naming.*,javax.sql.DataSource,java.sql.*,java.io.*"

                   %>

                   <%       

                      long start = System.currentTimeMillis();

                      String url = "jdbc:teiid:myvdb@mm://localhost:31000";

                      String sql = "select id,name from test";

                      Class.forName("org.teiid.jdbc.TeiidDriver");

                      Statement statement=null;

                      Connection con=null;

                      ResultSet rs=null;

                try {

                       con = DriverManager.getConnection(url, "user", "user");

                       statement = con.createStatement();

                       rs = statement.executeQuery(sql);

                       while (rs.next())

                            {

                              out.println("<br> " + rs.getString("id"));

                              out.println("<br> " + rs.getString("name"));

                             }

                      rs.close(); 

                     }catch(Exception e)

                          {

                              out.println("Exception thrown " +e);  

                          }finally

                          {

                              if(con != null){  con.close(); }  

                          }

                %>

                 

                we can read : https://docs.jboss.org/author/display/teiid82final/VDBs