6 Replies Latest reply on Jul 30, 2012 2:15 PM by shawkins

    Compiling virtual procedure in plain SQL

    nsabina

      Is there way to compile virtual procedures in plain SQL (as in http://docs.jboss.org/teiid/7.7.0.Final/reference/en-US/html/procedures.html#virtual_procedures ) by calling some Java API and including static resource file with plain text in SQL rather than using tool like Teiid Designer ?

       

      Thank you. 

        • 1. Re: Compiling virtual procedure in plain SQL
          rareddy

          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

            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

              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

                Appreciate the reply, is there any working code example I can look at?

                 

                Thank you 

                • 5. Re: Compiling virtual procedure in plain SQL
                  rareddy

                  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

                    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.