Facing issues while executing queries through teiid
nish18 Jun 9, 2016 7:30 AMHi ,
There are two issues I am facing.
1) Created one table on SQLServer though JDBC with ID column set as IDENTITY.
CREATE TABLE Persons (ID int IDENTITY(1,1) PRIMARY KEY, LastName varchar(255) NOT NULL,FirstName varchar(255),
Address varchar(255), City varchar(255));
Then executed below insert query through JDBC.
insert into Persons (id,LastName,FirstName,Address,City)values(2,'AAA','BBB','SB','INDIA');
Got error : java.sql.SQLException: Cannot insert explicit value for identity column in table 'Persons' when IDENTITY_INSERT is set to OFF.
Then, executed following query using JDBC : SET IDENTITY_INSERT Persons ON;
It worked and was able to insert value for column ID manually.
Facing issue while executing query " SET IDENTITY_INSERT Persons ON; on VDB created for this SQLSERVER.
It seems like Teiid is not able to recognized SET IDENTITY_INSERT as key word.
Got below exception :
org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]SET[*] IDENTITY_INSERT PERSONS_NEW" at line 1, column 1.
Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:703)
at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)
at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542)
How we can insert values manually for column set with identity of SQLServer database through teiid ?
2) Getting same exception while executing query :PACK TABLE <tableName>;
org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]PACK[*] TABLE" at line 1, column 1.
Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
Does teiid support pack table query ?
Please guide me on this
Thanks.