Search code examples
teiidredhat-datavirt

Teiid Transaction support in Virtual Procedures

I'm trying to execute few SQL SELECT statements inside a teiid Virtual Procedure. Does teiid have transaction support for virtual procedures. If so does it guarantee that the same database connection from the connection pool is used to execute all SELECT statements within that virtual procedure. My code would look like bellow.

CREATE VIRTUAL PROCEDURE GetFlightRecordsByID(IN p1 integer) RETURNS (xml_out xml) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'GET', "REST:URI" 'GetFlightRecordsByID')
            AS
            /*+ cache(pref_mem ttl:14400000) */
            BEGIN
                SELECT  XMLELEMENT("",  XMLAGG(XMLELEMENT("", XMLFOREST(.....))) ) as xml_out  FROM (...) A;  
 SELECT  XMLELEMENT("",  XMLAGG(XMLELEMENT("", XMLFOREST(.....))) ) as xml_out  FROM (...) B;           
 SELECT  XMLELEMENT("",  XMLAGG(XMLELEMENT("", XMLFOREST(.....))) ) as xml_out  FROM (...) C;                    
            END
Solution

  • Does teiid have transaction support for virtual procedures.

    Yes, but it is largely dependent on your datasources.

    If so does it guarantee that the same database connection from the connection pool is used to execute all SELECT statements within that virtual procedure.

    Yes, when a transaction is started (which can be XA or local from the client, a request scoped transaction, or even a block level) the WildFly/EAP transaction manager is relied upon to coordinate the transaction - so generally you'll need XA or transactional sources.