-
1. Re: Compiling virtual procedure in plain SQL
rareddy Jul 27, 2012 8:26 AM (in response to nsabina)Sabina,
Did you check out the Dynamic VDBs? they can be used now to define the views, where you provide the virtual procedure tranformation and its will be compiled by the engine when you deploy.
Otherwise can you explain what you are trying to do, I will try to offer a solution based on your usecase.
Thanks
Ramesh..
-
2. Re: Compiling virtual procedure in plain SQL
nsabina Jul 27, 2012 11:55 AM (in response to rareddy)Thank you for your response Ramesh.
I understood one way of defining procedure DDL is when VDB definition is built at startup.
To better explain why I asked the question I can give an example. I am looking for an easy way to add virtual procedure utilizing temp table if the last one defined and created after VDB is already started.
You have dynamic SQL so looks like there is a way to bind parameters.
From current code samples that I work with temp. table DDLs are built within Java code with SQL code snippets and variables concatenations. I was wondering if there is a way to provide DDL as a separate my_virtual_procedure.sql file and parameter binding with ? for example so in my Java code when I know temp table name I can pass it as binded parameter, something like
CREATE VIRTUAL PROCEDURE
BEGIN
SELECT A FROM ?;
... do some other staff
END
and then execute it something like:dbExecutor.execute(SELECT * FROM (EXEC my_virtual_procedure(curr_temp_table_name)...) AS x);
Thank you,
-
3. Re: Compiling virtual procedure in plain SQL
rareddy Jul 27, 2012 4:48 PM (in response to nsabina)Sabina,
You looking for something like Dynamic SQL Command. See this for details. https://docs.jboss.org/author/display/TEIID/Procedure+Language
Thanks
Ramesh..
-
4. Re: Compiling virtual procedure in plain SQL
nsabina Jul 27, 2012 4:56 PM (in response to rareddy)Appreciate the reply, is there any working code example I can look at?
Thank you
-
5. Re: Compiling virtual procedure in plain SQL
rareddy Jul 27, 2012 4:58 PM (in response to nsabina)There is an example in that link I provided. There may be test cases in source code too.
-
6. Re: Compiling virtual procedure in plain SQL
shawkins Jul 30, 2012 2:15 PM (in response to rareddy)Dynamic sql executes withint the scope of a procedure. So the temp table would have to be defined with-in the procedure to be referenced. We don't yet support cursor/table type references so you have to do fairly messy workarounds if you want to pass a resultset/table reference into a procedure as a parameter. So while you can do something like:
EXECUTE IMMEDIATE 'select * from ' || tblName AS x string, y integer INTO #TEMP;
fom within a procedure and then operate over #temp, tblName would have to reference a table created in the procedure or be a non-session table.