1 Reply Latest reply on Oct 4, 2012 7:48 AM by Ramesh Reddy

    Unable to configure two db in TEIID embedded mode.

    nishikant sahu Newbie

      Hi,

       

      I am trying to configure two database in teid server but unable to performe the operation on both the database at a time.

      I am getting the result from LAST added jdbc provider.

       

      I get the result if my query is

      select last_name from MDB_DB_SEC.mdb.dbo.ca_contact

      but if i change the query

       

      select last_name from MDB_DB.mdb_126.dbo.ca_contact";

      Then it is saying that "Group does not exist: MDB_DBC.mdb_126.dbo.ca_contact...."

       

      Am i doing some thing wrong in the code ?

       

      Here is the code:

       

      public class TeiidLayer {

       

          private static void buildDeployVDB(EmbeddedServer teiidServer)    throws Exception {

              // model for the SQL Server DB database

              ModelMetaData jdbcModel = new ModelMetaData();

              jdbcModel.setName("MDB_DB");

              jdbcModel.setSchemaSourceType("native");

              jdbcModel.addSourceMapping("sql-connector", "sqlserver", "source-jdbc");

              // model for the SQL Server DBSEC database

              ModelMetaData jdbcModelForSec = new ModelMetaData();

              jdbcModelForSec.setName("MDB_DB_SEC");

              jdbcModelForSec.setSchemaSourceType("native");

              jdbcModelForSec.addSourceMapping("sql-connector", "sqlserver","source-jdbc");

       

              teiidServer.deployVDB("DynamicVDB", jdbcModel, jdbcModelForSec);

       

          }

          public static void main(String[] args) throws Exception {

              final SQLServerDataSource SQLSERVER = new SQLServerDataSource();

              SQLSERVER.setURL("jdbc:sqlserver://sahni02-slcm:1433;databaseName=MDB_126");

              SQLSERVER.setUser("sa");

              SQLSERVER.setPassword("ca");

              EmbeddedServer.ConnectionFactoryProvider<DataSource> jdbcProvider = new EmbeddedServer.ConnectionFactoryProvider<DataSource>() {

                  @Override

                  public DataSource getConnectionFactory() throws TranslatorException {

                      return SQLSERVER;

                  }

              };

       

              final SQLServerDataSource SecSQL = new SQLServerDataSource();

              SecSQL.setURL("jdbc:sqlserver://slcm-priyanka:1433;databaseName=mdb");

              SecSQL.setUser("sa");

              SecSQL.setPassword("ca");

              EmbeddedServer.ConnectionFactoryProvider<DataSource> jdbcProviderForSec = new EmbeddedServer.ConnectionFactoryProvider<DataSource>() {

                  @Override

                  public DataSource getConnectionFactory() throws TranslatorException {

                      return SecSQL;

                  }

              };

              // now start Teiid in embedded mode

              EmbeddedConfiguration ec = new EmbeddedConfiguration();

              ec.setUseDisk(true);

              EmbeddedServer teiidServer = new EmbeddedServer();

              teiidServer.start(ec);

              teiidServer.addConnectionFactoryProvider("source-jdbc", jdbcProvider);

              teiidServer.addTranslator(new org.teiid.translator.jdbc.sqlserver.SQLServerExecutionFactory());

              teiidServer.addConnectionFactoryProvider("source-jdbc",    jdbcProviderForSec);

              teiidServer.addTranslator(new org.teiid.translator.jdbc.sqlserver.SQLServerExecutionFactory());

              buildDeployVDB(teiidServer);

              String sql = "select last_name from MDB_DB_SEC.mdb.dbo.ca_contact";

              try {

                  TeiidDriver td = teiidServer.getDriver();

                  Connection conn = td.connect("jdbc:teiid:DynamicVDB", null);

                  Statement statement = conn.createStatement();

                  ResultSet results = statement.executeQuery(sql);

                  while (results.next()) {

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

                  }

                  results.close();

                  statement.close();

              } catch (Exception e) {

                  e.printStackTrace();

              }

       

          }

      }

        • 1. Re: Unable to configure two db in TEIID embedded mode.
          Ramesh Reddy Master

          Nishikant,

           

          There are two models, not two VDBS. Each VDB can contain multiple models indide it.

           

          The code needs to be corrected as below (see two separate fragments)

           

                  ModelMetaData jdbcModelForSec = new ModelMetaData();

                  jdbcModelForSec.setName("MDB_DB_SEC");

                  jdbcModelForSec.setSchemaSourceType("native");

                  jdbcModelForSec.addSourceMapping("sql-connector", "sqlserver","source-jdbc-sec");

           

           

                  teiidServer.addTranslator(new org.teiid.translator.jdbc.sqlserver.SQLServerExecutionFactory());

                  teiidServer.addConnectionFactoryProvider("source-jdbc-sec",    jdbcProviderForSec);

           

          Right now both models are pointing to same datasource.

           

          Thanks

           

          Ramesh..