6 Replies Latest reply on Jul 7, 2016 11:06 AM by Nishant Agrawal

    Facing issues while executing queries through teiid

    Nishant Agrawal Novice

      Hi ,

       

      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.

        • 1. Re: Facing issues while executing queries through teiid
          Van Halbert Master

          Hi Nishant,

           

          Teiid only supports ANSI sql to be submitted thru its JDBC driver.   To issue a data source specific command, you can use the Native Query features [1].

           

          [1]  JDBC Translators · Teiid Documentation

           

          HTH

           

          Van

          • 2. Re: Facing issues while executing queries through teiid
            Nishant Agrawal Novice

            Hi Van/Ramesh ,

             

             

            Thanks.

             

            <?xml version="1.0" ?><vdb name="SQLSERVER20160707" version="1">

            <description>VDB for: SQLSERVER20160707, Version: 1</description>

            <connection-type>BY_VERSION</connection-type>

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

            <source name="ModelSQLSERVER20160707" translator-name="sqlserver-native" connection-jndi-name="java:/ModelSQLSERVER20160707"></source>

            </model>

            <translator name="sqlserver-native" type="sqlserver"><property name="SupportsDirectQueryProcedure" value="true"/>

            </translator>

            </vdb>

             

            i created vdb like above by setting property for translator and executed the below query

             

            execute("SET IDENTITY_INSERT Persons ON");

            insertTableQuery = "insert into Persons (id,LastName,FirstName,Address,City)values(9,'testLname','TestFname','testAddress','testCity')";

            executeUpdate(insertTableQuery);

            execute("SET IDENTITY_INSERT Persons OFF");

             

            Still facing same error.

             

            org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]SET[*] IDENTITY_INSERT DSSQLSERVER20160707154322565.TDM.DBO.Persons" at line 1, column 1.

            Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...

             

            Is it possible to SET IDENTITY_INSERT  ON or OFF  using this property <property name="SupportsDirectQueryProcedure" value="true"/> ?

            As this property supports the direct execution of commands on database.


            Also, can you please guide how we can use Parameterizable native query to ON and OFF SET IDENTITY_INSERT.


            Thanks,


            Nishant

            • 3. Re: Facing issues while executing queries through teiid
              Steven Hawkins Master

              > Is it possible to SET IDENTITY_INSERT  ON or OFF  using this property <property name="SupportsDirectQueryProcedure" value="true"/> ?

               

              With the value as true the translator exposes a direct query procedure, which default to the name native, so that you can issue queries such as:

               

              call source.native('SET IDENTITY_INSERT Persons ON')

               

              > Also, can you please guide how we can use Parameterizable native query to ON and OFF SET IDENTITY_INSERT.

               

              You can parameterize on the direct procedure call:

               

              call source.native('insert into tbl (col) values (?)', val')

               


              1 of 1 people found this helpful
              • 4. Re: Facing issues while executing queries through teiid
                Nishant Agrawal Novice

                Hi Steve ,

                 

                Thanks,

                 

                I tried using above way in below manner :

                 

                stmt.execute("call source.native('SET IDENTITY_INSERT Persons ON')");

                String insertTableQuery = "insert into Persons (id,LastName,FirstName,Address,City)values(9,'testLname','TestFname','testAddress','testCity')";

                stmt.executeUpdate(insertTableQuery);

                stmt.execute("call source.native('SET IDENTITY_INSERT Persons OFF')");

                 

                But got this error :

                 

                org.teiid.jdbc.TeiidSQLException: TEIID30352 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30352 SOURCE.NATIVE does not exist.

                 

                Thanks ,

                 

                Nishant

                • 5. Re: Facing issues while executing queries through teiid
                  Steven Hawkins Master

                  source was just a placeholder for the target schema/model name, so you would actually use:

                   

                  ModelSQLSERVER20160707.native

                  1 of 1 people found this helpful