Obtaining session's JDBC connection in translator

[markaddleman]

I believe this has come up before in conversation but I couldn't find a follow up Jira for it, so I'm opening a new discussion:  We are finding more and more value for custom translators to "loop back" into Teiid to perform some operation.  Normally, we would have a translator make a new local connection into Teiid using the user's security credentials (passed through the payload).  Most recently, however, we are creating a Java-backed stored procedure to make a request of some back end system and then store the results into a teiid temp table.  Due to the session-scoped nature of temp tables, we need the translator to use the same JDBC connection instance as the client in order for the temp tables to be useful.  Our plan is to pass the JDBC connection through the payload (although it's a local connection, I'm hoping serialization doesn't bite us). 

This brings up the question:  What about providing an API on the ExecutionContext to obtain a JDBC connection within the same client session? 

[shawkins]

It is possible with some changes to have a connection back to the same session, but it won't quite be the same as the client connection as some of the state is held client side (execution properties primarily).  We haven't promoted that approach since ideally people would use a virtual procedure and have more efficient management of threads.

> Due to the session-scoped nature of temp tables, we need the translator to use the same JDBC connection instance as the client in order for the temp tables to be useful.

Are you creating a single table or several?  And from where are you calling the procedure?  If you are just creating a single table is it possible for your stored procedure to just return a result set - e.g.  "INSERT INTO #temp SELET * FROM (CALL PROC(...))"

[markaddleman]

It is possible with some changes to have a connection back to the same session, but it won't quite be the same as the client connection as some of the state is held client side (execution properties primarily)

This limitation doesn't concern me much.  I'm primarily interested in maintaining the caller's security context and visibility to temp tables.

We haven't promoted that approach since ideally people would use a virtual procedure and have more efficient management of threads.

I suppose this could be done through a virtual procedure; I'll have to investigate.  I am curious about the comment "and have more efficient management of threads"  - What is more efficient about a virtual stored proc?

Are you creating a single table or several?  And from where are you calling the procedure?  If you are just creating a single table is it possible for your stored procedure to just return a result set - e.g.  "INSERT INTO #temp SELET * FROM (CALL PROC(...))"

We're creating a single table and, in this particular case, INSERT INTO #temp SELECT... form would work (although, in the more general case, we'd like the temp table to be maintained using continuous queries which would cause problems with INSERT INTO as well as virtual procedures). 

Our real problem is the stored procedure client doesn't know that a temp table is being created when the stored procedure is invoked.  In our use case, we are using a stored procedure to model a command shell interaction.  The commands are opaque to our UI and can result in a different result set schemas, anything from a simple text message to an arbitrary result set of data.  Obviously, we can't have a polymorphic result set using vanilla SQL constructs (I haven't explored if XML result sets would offer a benefit here).  We've decided that the stored procedure will return a two column result set:  a code column and a data clob column.  The code tells the system how to interpret the data clob. If it returns a result set, the code column contains the keyword "QUERY" and the data clob would be a select statement indicating which temp table contains the results. 

So, the need to have the translator create the temp table is driven by the requirement that the stored procedure have a fxied result set. As I write this, I realize that an XML result set would provide the necessary flexibility to send arbitrary schemas back directly from the stored procedure.  The client could then decide to populate a temp table . 

[shawkins]

> What is more efficient about a virtual stored proc?

When a virtual stored procedure is being processed, the engine thread is typically not held by blocking operations (unless source parallelism is turned off or your transactional).  If you make a jdbc call through our driver through the normal jdbc api it will hold the calling thread (an engine thread assigned to the connector work) until the operation completes.

Can you log an issue for providing a session backed connection?

Steve

[markaddleman]

jira: https://issues.jboss.org/browse/TEIID-2577

Can you point me to some examples/tests where a translator returns an XML column?  I'm not clear what form the XML document should take:  a string?  some XML doc object?

[shawkins]

An XML document is expected to be a javax.sql.SQLXML object.  If you return a String or a javax.xml.transform.Source we'll handle the conversion for you.

You can see the ws translator logic return xml results as an example.  WSProcedureExecution expects to return a StAXSource to the engine.

[markaddleman]

Perfect.  Thanks!