1 2 Previous Next 23 Replies Latest reply on Dec 11, 2010 9:30 AM by rareddy

    How to generate ID using Teiid?

    jalen

      We need to generate the Unique ID using Teiid. Does Teiid support some functions to generate it?

        • 1. Re: How to generate ID using Teiid?
          rareddy

          There is no in-built functionality in Teiid to generate unique-id. However, you can add "user defined function", that generate a ID and provide its implementation in the Java. See the Teiid documents how to create it.

           

          Thanks

           

          Ramesh..

          • 2. Re: How to generate ID using Teiid?
            jalen

            Thank you, Ramesh.

             

            I am using Designer. I have read the document and created a  User Defined Function in Designer by doing:

            1) Create a new Model with type "Function"

            2) "New Child"->"Scalar Function"

            3) set the function properties, including: deterministic, invocation class, invocation method, name, push down. Add return parameters.

            Snap1.jpg

            4) Copy my Java jar into the directory of JBoss profile's "lib" directory.

            5) Deployed my VDB to JBoss.

            My precedure includes:

            insert into <table> (<my_id>) VALUES (uuid());

            (here, uuid() is my UDF)

             

            When accessing the VDB, it prompted the error:

            Error Code:ERR.015.008.0039 Message:Remote org.teiid.api.exception.query.QueryPlannerException: Error Code:ERR.015.008.0039 Message:Error Code:ERR.015.008.0039 Message:The function 'uuid()' is an unknown form.  Check that the function name and number of arguments is correct.

             

            I don't know what is wrong with my definition.

             

             

            PS. 1) What does "Category" mean in defining the UDF?

            2) What is the difference between "ALLOWED" and "NOT_ALLOWED" regarding "Push Down"?

            • 3. Re: How to generate ID using Teiid?
              rareddy

              Jalen,

               

              Please see this document for your questions http://docs.jboss.org/teiid/7.2.0.Final/developer-guide/en-US/html_single/#udfs

               

              Did you add the function model to the VDB in the Designer?

               

              Thanks.


              Ramesh..

              • 4. Re: How to generate ID using Teiid?
                jalen

                Yes, I had read the document and did just as the document said.

                 

                I have added this function model to my VDB in Designer.

                 

                PS. I didn't find in the doc regarding these two questions:

                1) What does "Category" mean in defining the UDF?

                2) What is the difference between "ALLOWED" and "NOT_ALLOWED" regarding "Push Down"?

                • 5. Re: How to generate ID using Teiid?
                  rareddy

                  Jalen,

                   

                  This is from the doc.

                   

                   

                  Pushdown - can be one of REQUIRED, NEVER, ALLOWED. Indicates the expected pushdown behavior. 
                  
                  If NEVER or ALLOWED are specified then a Java implementation of the function should be supplied. 
                  

                   

                  REQUIRED - means, MUST pushdown always to source

                  NEVER/NOT_ALLOWED - means, NEVER pushdown to source

                  ALLOWED - means, if source supports the UDF then use pushdown, otherwise evaluate in the Teiid query engine.

                   

                  Category - is a  "namespace" you can give to your UDFs, such that they are collated for you in the Designer tool. Other than that it does not impose any functionality.

                   

                  I agree we can use little more clear doc here. Log document enhancement JIRA for this we will fix it.

                   

                  Thanks.


                  Ramesh..

                  • 6. Re: How to generate ID using Teiid?
                    jalen

                    Thanks for your patience, Ramesh. I'd like to clarify that I have read the UDF related docs more than 5 times before opening this question in the forum.

                     

                    I repeat my steps here:

                    1) In Designer, create a new Model of Model Class "Function" and Model Type "User Defined Function" and name it "FunctionDefinitions.xmi"

                    2) "New Child"->"Scalar Function", change the function name to "uuid" and add a return parameter of String type.

                    3) Set the function properties, including: deterministic, invocation class, invocation method, name, push down.

                    http://community.jboss.org/servlet/JiveServlet/downloadImage/2-573326-10679/295-143/Snap1.jpg

                    4) Add the "FunctionDefinitions.xmi" to my VDB.

                    Snap1.jpg

                    5) Compress "com.aaa.tool.UUIDGenerator" to "jalen.jar". This Java class has a public static method named "generateUUID()" with no input parameter.

                    6) Copy this "jalen.jar" to <JBoss>/server/default/lib

                    7) Deploy and execute my VDB to JBoss

                    8) Run a SQL "insert into <myTable> (...) values(...)"

                     

                    Then the error prompts:

                    INSERT INTO item (ITM_ID, ITM_TITLE, ITM_CREATE_TIMESTAMP, ITM_DESCRIPTION) VALUES ('1', 'jalenTitle', '2010-11-11 00:00:00', 'jalenDescription')


                    Error Code:ERR.015.008.0039 Message:Remote org.teiid.api.exception.query.QueryPlannerException: Error Code:ERR.015.008.0039 Message:Error Code:ERR.015.008.0039 Message:The function 'uuid()' is an unknown form.  Check that the function name and number of arguments is correct.

                     

                     

                    And my insert procedure looks like:

                    NSERT INTO CIS_ARTESIA.TC_ASSET (CIS_ARTESIA.TC_ASSET.UOI_ID, CIS_ARTESIA.TC_ASSET.PROGRAM_TITLE, CIS_ARTESIA.TC_ASSET.TITLE, CIS_ARTESIA.TC_ASSET.CREATION_DATE, CIS_ARTESIA.TC_ASSET.COMMENTS) VALUES (uuid(), '1', INPUTS.ITM_TITLE, INPUTS.ITM_CREATE_TIMESTAMP, INPUTS.ITM_DESCRIPTION);

                     

                    I change it to "jalen.uuid()", it doesn't work either. What's wrong with my operation?

                    Thank you.

                    • 7. Re: How to generate ID using Teiid?
                      rareddy

                      Jalen,

                       

                      In the Designer, when you are building the VDB, you would need to add the "function" model file you generated for UUID function to the VDB. This will not be automatically added to the VDB when you add the model. Have you done this step?

                       

                      There is JIRA for the above here https://jira.jboss.org/browse/TEIIDDES-165

                       

                      Thanks.

                       

                      Ramesh..

                      • 8. Re: How to generate ID using Teiid?
                        jalen

                        Hi Ramesh,

                         

                        I use the Designer 7.1 now.

                         

                        1) the function model file is packaged into the VDB zip file automatically by Designer 7.1.

                        2) I deployed the jar under <jboss>/server/deplay/lib. I have checked the invocation class and method and it is ok.

                        3) Jboss has been re-started after deploying the jar.

                         

                        Attachement is the VDB file.

                        I notice that all the type of models in vdb.xml is type="TYPE"

                        • 9. Re: How to generate ID using Teiid?
                          rareddy

                          Jalen,

                           

                          I am not sure how is that your "vdb.xml" got generated with type="TYPE" in your VDB, but they are all wrong. The models "DR" and "CIS_ARTESIA" should say "PHYSICAL", "FunctionDefinition" should say "FUNCTION" and "Mappings" should say "VIRTUAL".  So, yes this VDB artifact is bad, thus your issue. I am not sure extent of support Designer provided for UDF in 7.1

                           

                          Good news is Designer 7.2 almost ready to be released today or tomorrow. I would suggest using that version to build the VDB. If you can not wait start with the lastest nightly. Under here choose a date and under all "all" directory find "jbosstools-3.2_trunk.aggregate-Update-SNAPSHOT.zip"

                           

                           

                          Thanks.


                          Ramesh..

                          1 of 1 people found this helpful
                          • 10. Re: How to generate ID using Teiid?
                            jalen

                            Ramesh,

                             

                            I upgrade my Designer to the latest nightly build: /jbosstools/builds/nightly/trunk/2010-12-01_18-33-58-H406/all/jbosstools-3.2_trunk.aggregate-Update-SNAPSHOT.zip.

                             

                            This problem still exists. I re-create a new VDB and add new UDF to this VDB, but the type is still "TYPE" instead of "FUNCTION".

                            • 11. Re: How to generate ID using Teiid?
                              rareddy

                              Jalen,

                               

                              I have just tried on fresh install of eclipse and then I applied the above nightly and created VDB and did not see the behavior you are reporting. I suspect some thing wrong with your setup. Can you install a fresh copy eclipse and apply the nightly and try? I have tried on Fedora Linux with Eclipse 3.6.

                               

                              Thanks.

                               

                              Ramesh..

                              • 12. Re: How to generate ID using Teiid?
                                jalen

                                Ramesh,

                                 

                                I had tried fresh eclipse. My eclipse is  3.6.1 of JEE win32 version.

                                 

                                I just unpress the nightly build zip into <eclipse>/dropin/.

                                • 13. Re: How to generate ID using Teiid?
                                  rareddy

                                  Jalen,

                                   

                                  You would need to install it as a "zip" archive, not just unzip on the eclipse directory. Like

                                   

                                  Help -> Install New Software --> Add --> Archive

                                   

                                  and select the "Data Services" section. I will also try with 3.6.1 JEE version on my end. Please let me know if you followed above procedure, if not please do and let me know the results.

                                   

                                  Thanks


                                  Ramesh..

                                  • 14. Re: How to generate ID using Teiid?
                                    jalen

                                    Ramesh,

                                     

                                    I did a fresh install (new Eclipse and the latest nightly build of Teiid Designer) just as you said, but vdb.xml still with type "TYPE" not "FUNCTION"

                                    1 2 Previous Next