How to alter or create a new table in a particular database using teiid
- 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.
- 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)
-----------------------------------
