13 Replies Latest reply on Jun 18, 2012 9:58 AM by shawkins

    vararg function / UDF usage

    rajkota

      Hi,

       

      Could someone please educate me on how to create vararg functions using Dynamic VDB in Teiid?  We are using Teiid 7.7.

      If this is already covered in spec, please point me to the concen URL.

      I set FunctionParameter.setVarArg() and would like to know about the syntax of variable lenght method signature. This might already been documented, but somehow did not get expected information from Teiid forum / docs.

       

      Thanks,

      Raj

        • 1. Re: vararg function / UDF usage
          shawkins

          Raj,

           

          I don't think there is a consice bit of documentation for this, so I'll look at adapting this post into the developer's guide.  To create a vararg or any other source function, a dyanmic vdb Translator would need to return a pushdown function via the getPushDownFunction list.  The simpliest approach to add a pushdown function is with the call addPushDownFunctionMethod.  For example in your ExecutionFactory start method:

           

          {code}

          public void start() throws TranslatorException {

               ...

               FunctionMethod method = addPushDownFunction("source_name", "func_name", STRING, STRING, STRING);

               method.getInputParameter().get(1).setVarArgs(true);

               ...

          }

          {code}

           

          You can of course use whatever names, return and arg types that are appropriate.  A vararg function can be called just as any other function with the last parameter repeated 0 or more times.  The type for all vararg parameters is expected to be the same.  So with this example you would be able to call:

           

          func_name('a', 'b', 'c')

          or

          func_name('a')

          etc.

           

          Steve

          • 2. Re: vararg function / UDF usage
            rareddy

            Raj,

             

            Since there is no Designer support for Dynamic VDBs, to support any PUSHDOWN UDF you would need to extend the translator and provide the definition for the function in there. Check out the Developer's Guide for that. NON PUSHDOWN UDF support is *not* available in Teiid 7.7, as Dynamic VDBs does not have capability to define the view models with in the XML. To define view models, it is coding exercise to develop a custom translator and supply a view definition form it.

             

            In Teiid 8.0/8.1 all th above issues are resolved.

             

            - You can define a view model in Dynamic VDB using the DDL. Check out https://docs.jboss.org/author/display/teiid80final/DDL+Metadata

            - You can define a UDF in the above view model, that is non pushdown. In this you need to supply the java implementation of the function.

            - You can also extend the source model, and using the above technique provide addtional metadata and define a pushdown function. See https://docs.jboss.org/author/display/teiid80final/User+Defined+Functions

             

            HTH

             

            Ramesh..

            • 3. Re: vararg function / UDF usage
              rajkota

              Hi Steve / Ramesh,

               

              Thanks for providing me the information.

              I do have below concerns on var arg UDF usage still, could you please clarify?

               

              1.    On Java method signature to provide implementation to var-arg function and on the way to read the var-args.

              2.    Getting  an error ‘cannot be pushed to source’ while invoking var-arg function

               

              I made below changes in my custom translator:

               

              a.    Created definition as custom function under start method:

               

                   public void start() throws TranslatorException

                  {

                      super.start();

                      final FunctionMethod myCustomFunction = addPushDownFunction("myowntranslator", " myCustomFunction ", "string" , "string"     );       

                         myCustomFunction.getInputParameters().get(0).setVarArg(true);       

                         myCustomFunction.setDeterministicBoolean(false);

                      myCustomFunction.setPushDown("REQUIRED");          

                   ------------

                  }

               

               

               

              b.    In  method definition  (having two methods here as no idea on actual java method signature for var-args)

                              public static String myCustomFunction (String str){

                 

                   System.out.println(" MYTRANS Single Argument ");

                  return "test";

                  }

               

                   public static String myCustomFunction (String… str){

                 

                   System.out.println(" MYTRANS VAR Argument ");

                  return "test one";

                  }

               

              Thanks,

              Raj

              • 4. Re: vararg function / UDF usage
                rareddy

                Raj,

                 

                Are you trying to push this function to source? if yes, adding in the translator as you did above is correct. Now, if this is pushdown ("required") then there is no need to supply Java implementation, the expectation here is the created function is pushed to source for evaluation.

                 

                Teiid also supports non-pushdown UDF, those get evaluated in Teiid engine, however they are not supported in Teiid 7.7 in Dynamic VDBs, as mentioned in my previous post. These need Java implementations.

                 

                For the error, did you deploy the overridden translator. Also, are you using the new translator name in your -vdb.xml file?

                 

                Ramesh..

                • 5. Re: vararg function / UDF usage
                  rajkota

                  Hi Ramesh,

                   

                  I defined my translator in model file like:

                   

                  <model name="my_translator_name">

                          <translator type="my-translator-name" datasource=""/>

                  </model>

                   

                  Please observe here that datasource is given as none. And I deployed this translator in cargo environment.

                   

                  In (dynamic)VDB file, teiid has created model like:

                   

                  <model type="PHYSICAL" visible="true" name="my_translator_name">

                          <property name="importer.useFullSchemaName" value="false"/>

                          <source name="my_translator_name" connection-jndi-name="" translator-name="my_translator_name.delegated-translator"/>

                      </model>

                   

                  What I am looking is that:

                   

                  The custom translator 'my-translator-name' contains a single UDF, i.e.,myCustomFunction which is a var-arg type and it should construct an XML based on the parameters being passed to it and returns the XML as a string.

                   

                  And if it is achievable:

                   

                  Would like to know the method signature of myCustomFunction() to iterate through it's parameters.

                   

                  Thanks,

                  Raj

                  • 6. Re: vararg function / UDF usage
                    rareddy

                    Raj,

                     

                    What you really want is UDF that is not pushdown, or just a procedure. You can not do it above way for Dynamic VDBS. The workaround is, you can override the method "getMetadata" and add the function/procedure definition in there. Take look at FileExecutionFactory for an example. Also look at "createProcedureExecution" function in there as to how to implement the procedure execution.

                     

                    The above is round about way, a true non-pushdown UDF is better which can be accomplished with out any custom translator code, however here your options are

                     

                    1) Move to Teiid 8.0/8.1

                    2) Use the Teiid Designer based VDB and define non-pushdown UDF

                     

                    if you are just tryting to put toghether XML, Teiid does support SQLXML functions with which you can generate XML document.

                     

                    Ramesh..

                    • 7. Re: vararg function / UDF usage
                      rajkota

                      Hi Ramesh,

                       

                      Thanks for the response.

                      Can I understand it in this way  'non-pushdown UDF and pushdown UDF would not support my requirement with var arg'?

                      In case if simple 'non-pushdown' and 'var-arg' UDF supports my requirement, could you please let me know the java method signature which provides the implementation of UDF?

                      And I have overridden my 'getMetadata' in my translator and had the UDF definition under it.

                       

                      Like:

                       

                      @Override

                      public void getMetadata(final MetadataFactory metadataFactory, final Object conn) throws TranslatorException {

                              final FunctionMethod myCustomFunction = metadataFactory.addFunction("myCustomFunction");  

                              List<FunctionParameter> inParamList = new ArrayList<FunctionParameter>();  

                              FunctionParameter inParam = new FunctionParameter("name", "string");

                              inParam.setVarArg(true);   

                              inParamList.add(inParam);            

                              FunctionParameter outParam = new FunctionParameter("result", "string");         

                              myCustomFunction.setDeterministicBoolean(false);

                              myCustomFunction.setPushDown("ALLOWED");      

                              myCustomFunction.setInvocationClass("com.sample.translator.MyTranslatorClass");      

                              myCustomFunction.setInvocationMethod("myCustomFunction");          

                              myCustomFunction.setCategory("string");          

                              myCustomFunction.setInputParameters(inParamList);

                              myCustomFunction.setOutputParameter(outParam);

                      }

                       

                      My requirement is that either procedure of function should support variable arguments. But Teiid 7.7 does not support procedure with var-args. So non-pushdown and var-arg UDF function is the only option left out for me.

                      In this case, I would like to know the method signature of com.sample.translator.MyTranslatorClass.myCustomFunction() that takes variable arguments.

                       

                      Thanks,

                      Raj

                      • 8. Re: vararg function / UDF usage
                        rareddy

                        Raj,

                         

                        package com.sample.translator;
                        public static class MyTranslatorClass {
                        
                           public static String myCustomFunction (String… str){    
                                System.out.println(" MYTRANS VAR Argument ");
                                return "test one";
                            }
                        
                        }
                        

                         

                        Should do. Package it as jar and place it in the <jboss-as>/server/default/lib directory.

                         

                        Ramesh..

                        • 9. Re: vararg function / UDF usage
                          rajkota

                          Hi Ramesh,

                           

                          Thank you. I tried this way also, got  the below error message:

                           

                          select  my_translator_source_name.myCustomFunction('A')

                          argument type mismatch

                          Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.

                           

                          --

                           

                          And got the same error for select  my_translator_source_name.myCustomFunction('A', 'B')

                           

                          Thanks,

                          Raj

                          • 10. Re: vararg function / UDF usage
                            rareddy

                            Raj,

                             

                            Can attach your project sets, including the vdb. I want to try it out locally.

                             

                            Thanks

                             

                            Ramesh..

                            • 11. Re: vararg function / UDF usage
                              shawkins

                              Raj/Ramesh,

                               

                              7.7.1 implementaion functions for varargs only support Object type variable arguments.  8.0 as part of https://issues.jboss.org/browse/TEIID-1560 corrects that.

                               

                              So in this case your function would need to be

                               

                              package com.sample.translator;
                              public static class MyTranslatorClass {

                                 public static String myCustomFunction (Object… str){   
                                      ...
                                 }

                              }

                               

                              Any other parameters can be their actual types.

                              • 12. Re: vararg function / UDF usage
                                rajkota

                                Thanks Steve, Above approach worked fine.

                                 

                                Thanks Ramesh.

                                • 13. Re: vararg function / UDF usage
                                  shawkins

                                  Thanks for the update Raj,  I have also expanded the 8.1 developer's guide translator capabilities section dealing with scalar functions.

                                   

                                  Steve