Teiid Transaction support in Virtual Procedures

Question

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

score 0 · Accepted Answer · answered Nov 29 '17 at 15:15

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.

Thanks a lot for the reply – Sanjewa Ranasinghe Dec 07 '17 at 11:08 — Sanjewa Ranasinghe, Dec 07 '17 at 11:08

Teiid Transaction support in Virtual Procedures

1 Answers1