5 Replies Latest reply on Oct 10, 2012 8:52 AM by Steven Hawkins

    Using multi source Teiid VDB using JPA/parameterized queries

    Anil Allewar Newbie

      I noticed that the documentation for multi source states that SOURCE_NAME column value must be a literal. However this limits its use if we are using an ORM framework or JPA for implementing the entity as it would create a parameterized INSERT statement. Would it be possible to support setting up the SOURCE_NAME in a parametarized query? I also noticed that a new JPA translator got introduced as part of Teiid 8.1, I think it might make sense to enhance it to support multi source.

       

      Also would it be feasible to rename the multi source column SOURCE_NAME to maybe TEIID_SOURCE_NAME as that would lessen the possibilty of the data model having the same column name as the internal Teiid multi source column.

       

      I can add feature requests if you think these suggestions are valid.

        • 1. Re: Using multi source Teiid VDB using JPA/parameterized queries
          Ramesh Reddy Master

          Anil,

           

          1) You can use "Name in Source" property on source specific "SOUCE_NAME" column to rename it to some other column name by keeping the Teiid's implicit column name.

           

          2) Use of literal mean does not mean you can not use it in the prepared calls, it means that you have to supply a literal constant value for evaluation purposes. You can not use such way that "select ... from foo where SOUCE_NAME = <some column value>"

           

          3) There is no special handling needs to be done for multi-source at the translator level, so the JPA translator should work as is. Let us know if you see any issues with this translator.

           

          Thanks

           

          Ramesh..

          • 2. Re: Using multi source Teiid VDB using JPA/parameterized queries
            Anil Allewar Newbie

            Ramesh,

             

            1) I assume changing the  "Name in source" property can be done only if we use a static VDB. In our case we use dynamic VDB and the column name is used on the fly.

            2) Please see the logs below where a JPA persist with multi-source is failing with exception "The multisource column or parameter SOURCE_NAME requires a literal value."

             

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

            SELECT

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

            21:43:58,603 INFO  [stdout] (http--0.0.0.0-8080-2) [EL Fine]: 2012-09-10 21:43:58.603--ServerSession(553085112)--Connection(1323309739)--Thread(Thread[http--0.0.0.0-8080-2,5,main])--SELECT "tool_parm_name", SOURCE_NAME, "create_date", "create_user_id", "ref_value_source", "ref_value_status", "remark", "tool_parm_id", "unit_type_id", "update_date", "update_us
            er_id" FROM "r_logging_tool_parm" WHERE (SOURCE_NAME = ?)
            21:43:58,605 INFO  [stdout] (http--0.0.0.0-8080-2)      bind => [OWDSDS_FLOUNDER]
            21:43:58,611 INFO  [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue3708) Source sql: SELECT g_0."tool_parm_name", g_0."create_date", g_0."create_user_id", g_0."ref_value_source", g_0."ref_value_status", g_0."remark", g_0."tool_parm_id", g_0."unit_type_id", g_0."update_date", g_0."update_user_id" FROM "r_logging_tool_parm" g_0

             

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

            INSERT

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

            21:44:51,041 INFO  [stdout] (http--0.0.0.0-8080-2) [EL Finest]: 2012-09-10 21:44:51.041--UnitOfWork(1984567029)--Thread(Thread[http--0.0.0.0-8080-2,5,main])--persist() operation called on: {r_logging_sensor_parm}.

            21:44:51,042 INFO  [stdout] (http--0.0.0.0-8080-2) [EL Finer]: 2012-09-10 21:44:51.042--UnitOfWork(1984567029)--Thread(Thread[http--0.0.0.0-8080-2,5,main])--begin unit of work commit

            21:44:51,045 INFO  [stdout] (http--0.0.0.0-8080-2) [EL Finest]: 2012-09-10 21:44:51.044--UnitOfWork(1984567029)--Thread(Thread[http--0.0.0.0-8080-2,5,main])--Execute query InsertObjectQuery({r_logging_sensor_parm UNKNOWN2})

            21:44:51,047 INFO  [stdout] (http--0.0.0.0-8080-2) [EL Finest]: 2012-09-10 21:44:51.047--ServerSession(553085112)--Connection(1323309739)--Thread(Thread[http--0.0.0.0-8080-2,5,main])--Connection acquired from connection pool [default].

            21:44:51,049 INFO  [stdout] (http--0.0.0.0-8080-2) [EL Finer]: 2012-09-10 21:44:51.049--ClientSession(917273885)--Connection(1323309739)--Thread(Thread[http--0.0.0.0-8080-2,5,main])--begin transaction

            21:44:51,051 INFO  [stdout] (http--0.0.0.0-8080-2) [EL Fine]: 2012-09-10 21:44:51.05--ClientSession(917273885)--Connection(1323309739)--Thread(Thread[http--0.0.0.0-8080-2,5,main])--INSERT INTO "r_logging_sensor_parm" ("sensor_parm_name", SOURCE_NAME, "ref_value_source", "ref_value_status", "remark", "unit_type_id") VALUES (?, ?, ?, ?, ?, ?)

            21:44:51,053 INFO  [stdout] (http--0.0.0.0-8080-2)  bind => [UNKNOWN2, OWDSDS_FLOUNDER, null, null, null, null]

            21:44:51,063 WARN  [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue3717) TEIID30020 Processing exception 'TEIID30492 The multisource column or parameter SOURCE_NAME requires a literal value.' for request up7JtRJb6uDd.3.  Exception type org.teiid.api.exception.query.QueryValidatorException thrown from org.teiid.dqp.internal.process.Request.validateWithVisitor(Request.java:327). Enable more detailed logging to see the entire stacktrace.
            21:44:51,063 WARN  [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue3717) TEIID30020 Processing exception 'TEIID30492 The multisource column or parameter SOURCE_NAME requires a literal value.' for request up7JtRJb6uDd.3.  Exception type org.teiid.api.exception.query.QueryValidatorException thrown from org.teiid.dqp.internal.process.Request.validateWithVisitor(Request.java:327). Enable more detailed logging to see the entire stacktrace.
            21:44:51,083 INFO  [stdout] (http--0.0.0.0-8080-2) [EL Fine]: 2012-09-10 21:44:51.083--ClientSession(917273885)--Thread(Thread[http--0.0.0.0-8080-2,5,main])--VALUES(1)

            21:44:51,093 WARN  [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue3718) TEIID30020 Processing exception 'TEIID31100 Parsing error: Encountered "VALUES" at line 1, column 1.
            Was expecting one of:

            • 3. Re: Using multi source Teiid VDB using JPA/parameterized queries
              Ramesh Reddy Master

              Anil,

               

              That should be supported IMO, can log a JIRA for this?

               

              Ramesh..

              • 4. Re: Using multi source Teiid VDB using JPA/parameterized queries
                Steven Hawkins Master

                Anil,

                 

                > Also would it be feasible to rename the multi source column SOURCE_NAME to maybe TEIID_SOURCE_NAME as that would lessen the possibilty of the data model having the same column name as the internal Teiid multi source column.

                > 1) I assume changing the  "Name in source" property can be done only if we use a static VDB. In our case we use dynamic VDB and the column name is used on the fly.

                 

                Yes, it is a valid request to use a more specific name.  In the dynamic case there would be an issue as the planner will assume that source_name is the multi-source column.

                 

                > Use of literal mean does not mean you can not use it in the prepared calls

                 

                This is also possible, but will likey require quite a few changes to delay the final planning changes until execution.

                 

                Steve