1 2 Previous Next 17 Replies Latest reply on Jul 30, 2013 3:55 PM by rareddy

    Procedure in source model

    jane_lj

      Hi,

       

      We have registered a procedure in source model VDB, and we can see that procedure in source model now.

       

      In SELECT transformation in View Model, we want to call this procedure, let teiid push down the procedure call down to translator level.

       

      1. Do we need to create a procedure in VM with same name before we call it? Or we can directly use?

       

      2. What's the syntax to call the procedure in SELECT transformation sql? Tried so many things, didn't work.

       

       

      Thanks.

        • 1. Re: Procedure in source model
          rareddy

          EXEC proc(in_param=>'1', out_param=>var)

           

          see https://docs.jboss.org/author/display/TEIID/Procedure+Language

          • 2. Re: Procedure in source model
            jane_lj

            Thanks, Ramesh.

             

            Will try and see.

            • 3. Re: Procedure in source model
              jane_lj

              Ramesh,

               

              After deployed Source Model VDB to Teiid, I can see the procedure we created in SM.

              But if we deploy the View Model VDB which includes SM.xmi to teiid, we can't see that procedure.

               

              What extra we need to do in designer? Do we need to create a same name, same input/output parameter procedure (Simple Procedure) in VM with Teiid Designer?

               

              Thanks.

              • 4. Re: Procedure in source model
                rareddy

                Why would a source model procedure show up in the view model? A source model procedure means that procedure exists in your source, a view model procedure is virtual procedure and gets evaluated inside the Teiid query engine. two completely separate constructs.

                 

                Ramesh..

                • 5. Re: Procedure in source model
                  jane_lj

                  Thanks for clarification, Ramesh.

                   

                  So in Teiid Designer, is there a way to import the procedures from SM to VM? or we need to create them one by one in VM?

                   

                  Thanks.

                  • 6. Re: Procedure in source model
                    rareddy

                    You can import source procedures from your database using the Teiid Desinger into your source model. As per virtual model's procedures those you need to create them manually or transform from source model ( when you create a new view relational model, designer gives an option to transform an existing source model).

                     

                    Ramesh..

                    • 7. Re: Procedure in source model
                      jane_lj

                      Thanks, Ramesh.

                       

                      So I guess for an existing VM, I need to create them manually, right?

                      Cuz I right clicked VM.xmi, couldn't find the option to transform the procedure from source model.

                      • 8. Re: Procedure in source model
                        rareddy

                        You can create new virtual model and transform from your source, then copy and paste into existing one, then delete the one created.

                        • 9. Re: Procedure in source model
                          jane_lj

                          Ramesh,

                           

                          I matually created a procedure in our existing view model, the procedure has 2 IN parameters and returns List<Double>.

                           

                          From the teiid doc, it says, "Teiid virtual procedures can only be defined in Teiid Designer. They also cannot use IN/OUT, OUT, or RETURN parameters and may only return 1 result set.". So for return value, I didn't define RETURN parameter, I defined a Procedure ResultSet for this procedure. But when I save it, designer gave me error: "The Procedure ResultSet must reference 1 or more columns; there are none".

                           

                          In our VM, we don't have a column to map to this ResultSet, and the way we want to use this procedure is: after deploy the VM vdb, we run "EXECUTE Horizon3DRead(param1, param2)" in SQuirrel to see the results.

                           

                          Do we have to add a column in VM just for the ResultSet? Is there other way to get returned value without adding this column?

                           

                          Thanks.

                          • 10. Re: Procedure in source model
                            jane_lj

                            Hi Ramesh,

                             

                            In our View Model VM, I created the proedureA, and

                             

                            if I write Transformation SQL like this:

                             

                            SELECT

                                                *

                                      FROM

                                                (EXEC SM.procedureA(VM.procedureA.GUID, VM.procedureA.indexI)) AS slicevalues

                             

                            Designer complains "The query defining a virtual procedure can only be of type Virtual procedure."  (slicevalues is the name of my procedure result set column name).

                             

                             

                            if I write SQL like this:

                             

                            CREATE VIRTUAL PROCEDURE

                            BEGIN

                                      SELECT * FROM (EXEC SM.procedureA(VM.procedureA.GUID, VM.procedureA.indexI)) AS slicevalues;

                            END

                             

                            Designer complains "ERROR: Command must project at least one symbol".

                             

                             

                            if I write SQL like this:

                             

                            CREATE VIRTUAL PROCEDURE

                            BEGIN

                                      EXEC Petrel_SM.Horizon3DRead(PetrelCommonModel.Horizon3DRead.GUID, PetrelCommonModel.Horizon3DRead.indexI);

                            END

                             

                            Designer complains "

                            The SELECT transformation is valid, but NOT fully reconciled:

                            - The number of transformation output symbols is zero."

                             

                             

                            I'm out of ideas about the syntax. Can you please help me out?

                             

                            Thanks a lot.


                            • 11. Re: Procedure in source model
                              shawkins

                              >  From the teiid doc, it says, "Teiid virtual procedures can only be defined in Teiid Designer. They also cannot use IN/OUT, OUT, or RETURN parameters and may only return 1 result set.

                               

                              That is quite out of date.  I have corrected that now in the latest docs.  You are able to use all of the parameter types, but the 1 result set restriciton still holds.

                               

                              > Designer complains "The query defining a virtual procedure can only be of type Virtual procedure."  (slicevalues is the name of my procedure result set column name).

                               

                              A single statement to define a procedure is valid in Teiid, but Designer hasn't relaxed its validation yet.  An issue would be needed for that.

                               

                              The other errors need some clarification.  With Teiid DDL or with Teiid Designer you should be able to:

                               

                              1. create a procedure with just a return parameter and no result set

                               

                              {code}

                              CREATE VIRTUAL PROCEDURE

                              BEGIN

                                ...

                                proc.ret = ...;

                                ...

                              END

                              {code}

                               

                              Where proc.ret is a reference to the return parameter column name (which can be qualified by the procedure name).  As long no ResultSet child exists on the Teiid Designer metadata entry for the procedure then this should validate just fine.

                               

                              2. create a procedure with just a result set:

                              {code}

                              CREATE VIRTUAL PROCEDURE

                              BEGIN

                                ...

                                SELECT|EXEC|CALL ...

                                ...

                              END

                              {code}

                               

                              Here if no result set is defined, Designer should infer it from the returnable statements in the procedure defintion.  Although I believe there were some bugs with that in Teiid Designer 8.1.  If you have a result set defined, then Teiid Designer should validate the returnable statements match the expected result set.

                               

                              3. create a procedure with both a return parameter and a result set:

                               

                              {code}

                              CREATE VIRTUAL PROCEDURE

                              BEGIN

                                ...

                                proc.ret = EXEC otherProc ...

                                ...

                              END

                              {code}

                               

                              The above shows a compound opteration that assigns the return value and will use the result set (if any) from the EXEC as the result set from the procedure.  It is also valid to separately assign the return value.

                               

                              So based upon this if you are getting an unexpected exception, then an issue should be raised against Designer.

                              • 12. Re: Procedure in source model
                                jane_lj

                                Thank you, Steven and Ramesh. I figured out.

                                 

                                One more minor issue I want to ask you guys:

                                 

                                Usually if we have some changes in source VDB, after we update the source model in designer, we will see the changes.

                                 

                                But for procedure, if we add a new procedure in source VDB, after update SM, we only can see the procedure, but can't see the paramenters of that procedure. We have to manually add them. Is that right? or we miss something?

                                 

                                Thanks again.

                                • 13. Re: Procedure in source model
                                  rareddy

                                  But for procedure, if we add a new procedure in source VDB, after update SM, we only can see the procedure, but can't see the paramenters of that procedure. We have to manually add them. Is that right? or we miss something?

                                  That sounds like Designer bug to me. You should log a JIRA with Teiid Designer.

                                   

                                  Ramesh..

                                  • 14. Re: Procedure in source model
                                    shawkins

                                    Proceure parameter metadata isn't always available through JDBC DatabaseMetadata.  That said, if the initial import was able to import the parameters, then you would expect the update to as well.

                                     

                                    Steve

                                    1 2 Previous Next