Problem with creating temporary tables explicit way

[gamvi01]

Iam trying to create temporary tables using explicit syntax. Below is the code used to creat the same. However iam getting exception during inserts to that temp table.Am i missing anything here. I followed the documentation from http://docs.jboss.org/teiid/7.6.0.Final/reference/en-US/html/ddl_support.html#temp_tables

http://docs.jboss.org/teiid/7.6.0.Final/reference/en-US/html/ddl_support.html#temp_tables

try

        {

          dbExecutor.execute("CREATE LOCAL TEMPORARY TABLE TEMP(zip string,phone string,name string,userid string,sysid string)");

            List<Object> values = new ArrayList<>();

            values.add("zip 1");

            values.add("phone 1");

            values.add("name 1");

            values.add("userid 1");

            values.add("sysod 1");

            dbExecutor.addBatch("INSERT INTO #TEMP(zip,phone,name,userid,sysid) VALUES(?,?,?,?,?)",

                                values);

            dbExecutor.commit();

        } catch (final SQLException e)

        {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (Exception e)

        {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

Caused by: [QueryResolverException] ERR.015.008.0026: Error Code:ERR.015.008.0026 Message:Expression 'INSERT INTO #hdvpWdhl (zip, phone, name, userid, sysid) VALUES (?, ?, ?, ?, ?)' has a parameter with non-determinable type information.  The use of an explicit convert may be necessary.

    at org.teiid.query.resolver.util.ResolverUtil.setDesiredType(ResolverUtil.java:313)

    at org.teiid.query.resolver.command.InsertResolver.resolveTypes(InsertResolver.java:184)

    at org.teiid.query.resolver.command.InsertResolver.resolveProceduralCommand(InsertResolver.java:120)

    at org.teiid.query.resolver.ProcedureContainerResolver.resolveCommand(ProcedureContainerResolver.java:140)

    at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:294)

    at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:147)

    at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:291)

    at org.teiid.dqp.internal.process.PreparedStatementRequest.resolveCommand(PreparedStatementRequest.java:87)

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

    at org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:138)

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

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

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

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

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

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

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

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

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

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

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

[rareddy]

Vineela,

What is "dbExecutor"? Looks like java coding error. Please refer to a JDBC tutorial on how to code a prepared statement.

Ramesh..

[shawkins]

Vineela,

I do not see this behavior on 7.7.  What version of Teiid are you using?

Steve

[gamvi01]

Ramesh,

Dbexecutor is our api... we internally execute them using the prepared statments.

[gamvi01]

Steve,

We are using teiid 7.7.1.

[shawkins]

I should clarify an assumption I'm making.  In your example you have a create statement creating a table named TEMP, then later you show an insert failing against #hdvpWdhl.  I assumed that was just a mistake in creating the post, however if you are inserting into a non-existant implicitly created temp table via a prepared statement that uses bind values for the insert values, then yes that is expected to fail as there will be no types associated with the columns.  If you actually are performing the insert against the same table in the create (or any previously existing temp table), then it should work - and I verified that on 7.7.

Steve

[gamvi01]

Steve,

Inserts are made to the same table .

[shawkins]

Vineela,

The table in the create and in the insert must have the same name.  Also from your example above, you are creating TEMP and inserting into #TEMP.  TEMP and #TEMP are two different tables.

Steve

[gamvi01]

Steve you are right . I found my issue. Thanks