Dynamic SQL & Transformation query

[lskv]

Is it possible to create a dynamic SQL in a Teiid View that would execute a query based on current views constraints.

for e.g If  I have a view "vw_MyCategories" having columns (c1,c2,c3), can I execute a dynamic query within that view's transformation query based on the constraints specified in columns c1,c2,c3 ?

String mydynSQL = "Select * from blah where blah.d1 = ?";

//bind d1 to value in c1

Exec mydynSQL;

[rareddy]

Sunil,

See this http://docs.jboss.org/teiid/7.6.0.Final/reference/en-US/html_single/#dynamic_sql

If this works for you, please post back how you solved the above query as example for others.

Ramesh..

[lskv]

Thanks but I did go through this before. From what I can infer this is an example of building dynamic SQL based on binding stored procedure parameters to your dynamic SQL. The documentation mentions that  "USING" variables must be fully qualified as "UVAR" but the example shows "DVARS". Not sure which is the right one.

Also I couldnt get the "EXECUTE STRING" working based on the example above as it keeps complaining about parser error. The moment I put in "EXECUTE STRING <my_sql_string>", the designer changes that to EXECUTE <my_sql_string> and gives a parser error. Maybe someone else will have better luck with this.

What I was looking for was a way to invoke a stored procedure with parameters based on the current views bound values.

EXEC sp_dummy (THISVW.COL1 => v1,THISVW.COL2 => v2)

[shawkins]

Sunil,

UVAR should be DVAR.  I'll update the doc.  The parsing error is occurring because of using a non-literal string, which is a regression error from making the STRING keyword optional.  I'll log a JIRA to cover requiring the toString to use the IMMEDIATE keyword.

I'm not sure what you mean by view bound values.  You currently cannot use dynamic sql in a view.  How would you expect that to work?

Steve

[lskv]

Thanks Steven. Yes I understand the Transformation SQL  for a "View" should contain only a "SELECT ..." or "EXEC <stored_proc>".

For my sample I had setup a view which invokes a stored procedure returning a resultset and this works fine.

I was wondering then if I can pass as stored procedure parameters the view query constraint values.

So if I query "select * from my_view where c1 = 'xyz' and c2 = 5" , is it possible to pass those constraints to the stored procedure getting executed in the transformation.

I assume for my current setup Teiid would first execute  the stored procedure and then filter the result (in-memory) based on the constraints? The stored procedure would return potentially large data and needs to be constrained first and hence this requirement.

[shawkins]

https://issues.jboss.org/browse/TEIID-1920 was logged to capture the issues you have seen.  However the fix will need to be picked up by designer to fully resolve the issue.

We support a concept called procedural relational invocation, which is covered in the reference.  Given a procedure, you are allowed to invoke it as if it were a table, e.g. "select * from proc where c1 = 'xyz'".  This is different from the notion of a nested table function, here you simply use the proc name as a table and then use equality predicates to pass parameters.  This need not be done through an explicit view, although there is nothing that prevents you from wrapping the procedural relational call "select .* from proc" in a view and then issuing something like "select * from my_view where c1 = 'xyz'"

Steve

[lskv]

Thanks Steve. This is exactly what I required and works like a charm!

This was what I did to validate the workflow:

Created a view "Categories_From_Sp" that invokes a SP "sp_GetCategories (P_CategoryID int) via the relational invocation "Select * from sp_GetCategories"

The created view has all columns including the input parameter "P_CategoryID ".

Executing a query on the view with the constraint on "P_CategoryID" triggered execution of SP with that constraint.

Ofcourse I can also directly invoke the SP instead of packaging it in a view .