1 Reply Latest reply on Jan 6, 2016 10:02 AM by Steven Hawkins

    How to alter or create a new table in a particular database using teiid

    Nishant Agrawal Novice
      • I am trying to create a table dynamically on a particular database using teiid but it is not reflecting physically on the database.
      • I am trying to alter the existing table of database using teiid,but unable to alter the table dynamically.

       

       

      1. Create Table Problem

       

      Vdb File:

       

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

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

          <description>A Dynamic VDB</description>

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

      <model name="join" type="VIRTUAL">

              <metadata type="DDL"><![CDATA[

              

                 

      CREATE VIEW SvcView (emp_fname string,emp_lname string,salary long,cert_name string,emp_id long) OPTIONS (UPDATABLE true)AS

      SELECT e.emp_fname, e.emp_lname, e.salary,c.cert_name, c.emp_id

      FROM "aakash_test.employee" e  INNER JOIN "aakash_test.certificate" c ON e.emp_id = c.emp_id;

       

       

      CREATE TRIGGER ON SvcView INSTEAD OF INSERT AS 

                    FOR EACH ROW 

                    BEGIN ATOMIC 

        INSERT INTO aakash_test.employee (emp_fname,emp_lname,salary) VALUES (NEW.emp_fname, NEW.emp_lname,NEW.salary);

        INSERT INTO aakash_test.certificate (cert_name,emp_id)select NEW.cert_name,max(emp_id) from aakash_test.employee;

      END

              ]]>

              </metadata>

          </model>

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

              <source name="sqlTest2" translator-name="mysql5" connection-jndi-name="java:/mysql-ds"/>

          </model>

       

         </vdb>       

       

      I am using below JavaClient prgram to connect to Teiid:

       

      import java.sql.Connection;

      import java.sql.DriverManager;

      import java.sql.ResultSet;

      import java.sql.ResultSetMetaData;

      import java.sql.SQLException;

      import java.sql.Statement;

       

       

      import org.teiid.jdbc.TeiidDataSource;

      import org.teiid.jdbc.TeiidStatement;

       

       

      @SuppressWarnings("nls")

      public class JDBCClientInsert {

       

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

        if (args.length < 4) {

        System.out.println("usage: JDBCClient <host> <port> <vdb> <sql-command>");

        System.exit(-1);

        }

       

       

        System.out.println("Executing using the TeiidDriver");

        execute(getDriverConnection(args[0], args[1], args[2]), args[3]);

       

       

        System.out.println("-----------------------------------");

        System.out.println("Executing using the TeiidDataSource");

       

        }

       

        static Connection getDriverConnection(String host, String port, String vdb) throws Exception {

        String url = "jdbc:teiid:"+vdb+"@mm://"+host+":"+port+";showplan=on"; //note showplan setting

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

       

        return DriverManager.getConnection(url,"user", "test@123");

        }

       

        static Connection getDataSourceConnection(String host, String port, String vdb) throws Exception {

        TeiidDataSource ds = new TeiidDataSource();

        ds.setDatabaseName(vdb);

        ds.setUser("user");

        ds.setPassword("user");

        ds.setServerName(host);

        ds.setPortNumber(Integer.valueOf(port));

       

        ds.setShowPlan("on"); //turn show plan on

       

        return ds.getConnection();

        }

       

        public static void execute(Connection connection, String sql) throws Exception {

        try {

        Statement statement = connection.createStatement();

       

          statement.executeUpdate(sql);

       

        System.out.println("table has been created successfully!!!");

       

        statement.close();

        } catch (SQLException e) {

        e.printStackTrace();

        } finally {

        if (connection != null) {

        connection.close();

        }

        }

        }

      }

       

       

      I am using below command to run the above JavaPrgram:

       

      java -cp "C:/Aakash/TDM Document/teiid-7.7.0.Final-client.jar";. JDBCClientInsert localhost 31000 EmployeeVDBSingle "CREATE FOREIGN TABLE Customer_Test(cust_id integer,custName varchar(25)) OPTIONS(UPDATABLE 'TRUE')"

       

      But i am getting an below error:

       

      org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "CREATE FOREIGN [*]TABLE[*] Customer_

      est(" at line 1, column 16.

      Was expecting: "temporary"

       

      Then I have used the below command with temporary keyword:

       

      java -cp "C:/Aakash/TDM Document/teiid-7.7.0.Final-client.jar";. JDBCClientInsert localhost 31000 EmployeeVDBSingle "CREATE FOREIGN temporary TABLE Customer_Test(cust_id integer,custName varchar(25)) OPTIONS(UPDATABLE 'TRUE') on mysqlModel";

       

       

      Message shows "table has been created successfully!!!"

       

      But it is not reflecting on mySql database physically.

       

      Which create query syntax should i use to reflect it on database physically?

       

       

      2. ALTER TABLE PROBLEM:

       

      I am using below command to alter the existing table:

       

      java -cp "C:/Aakash/TDM Document/teiid-7.7.0.Final-client.jar";. JDBCClientInsert localhost 31000 EmployeeVDBSingle "ALTER TestView ADD COLUMN `testColumn` VARCHAR(45) NOT NULL AFTER `address`"

       

      But i am getting below error message:

       

      org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "ALTER [*]TestView[*] ADD COLUMN

      ine 1, column 7.

      Was expecting: "procedure" | "trigger" | "view"

              at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)

              at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)

              at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:624)

              at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:72)

              at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:539)

              at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:130)

              at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:37)

              at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:75)

              at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:220)

              at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:255)

              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

              at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

              at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

              at java.lang.reflect.Method.invoke(Unknown Source)

              at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:110)

              at com.sun.proxy.$Proxy1.read(Unknown Source)

              at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:349)

              at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:548)

              at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:347)

              at JDBCClientInsert.execute(JDBCClientInsert.java:74)

              at JDBCClientInsert.main(JDBCClientInsert.java:42)

      Caused by: [TeiidProcessingException]Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "ALTER [*]TestView[*] ADD COL

      t line 1, column 7.

      Was expecting: "procedure" | "trigger" | "view"

              at org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:214)

              at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:164)

              at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:140)

              at org.teiid.dqp.internal.process.Request.parseCommand(Request.java:299)

              at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:389)

              at org.teiid.dqp.internal.process.Request.processRequest(Request.java:463)

              at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:640)

              at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:337)

              at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

              at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)

              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

              at java.lang.Thread.run(Thread.java:745)

      -----------------------------------

        • 1. Re: How to alter or create a new table in a particular database using teiid
          Steven Hawkins Master

          > I am trying to create a table dynamically on a particular database using teiid but it is not reflecting physically on the database.

           

          Create foreign temporary table does not actually create the source table.  It merely brings that table into the local scope.  See Temp Tables - Teiid 8.11 - Project Documentation Editor.  You would have to issue a corresponding direct query with the source ddl to create the table if it does not exist.  It is a planned feature at some point to provide that functionality, but I don't think there is a JIRA for that yet.

           

          > I am trying to alter the existing table of database using teiid,but unable to alter the table dynamically.

           

          Teiid has limited alter support for its metadata at runtime - see DDL Commands - Teiid 8.12 - Project Documentation Editor

           

          To add a column would require issuing a direct query to the source, then to have Teiid aware of the column it would depend on the vdb.  For a designer vdb, the vdb would need to be modified in designer again. For a vdb that pulls the metadata from the translator, the vdb would need to be reloaded to allow Teiid to discover the new column.

          1 of 1 people found this helpful