9 Replies Latest reply on Aug 8, 2016 9:22 AM by Steven Hawkins

    Is there any way to parametrize endpoint url using invokeHTTP?

    Marco Ardito Master

      Hi,

      I'm on summer vacations, but since I recently solved (thanks Ramesh!) how to get data out of a web mailing service, through oauth2, (see Need to get data from Mailup email marketing platform), I can't help but trying to get out some real data when I need a pause from sun baths, walks and books

       

      I am trying some vdb-defined virtual views like

       

      CREATE VIEW GruppiLista1(
          id_grp string,
          id_lst string,
          nome string,
          notes string
      ) AS
      
          select 
              mup.id_group, mup.id_list, mup.nome, mup.notes
          from  
          (call 
              comunicazioni.invokeHTTP(
                  endpoint=>'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/List/1/Groups', 
                  action=>'GET', 
                  headers=>jsonObject('application/json' as "Content-Type", 'application/json' as "Accept",0 as  "Content-length")
              )
          ) w,   
          XMLTABLE('/Items/Items' passing JSONTOXML('Items', w.result) columns
          id_group string PATH 'idGroup',
          id_list string PATH 'idList',
          nome string PATH 'Name',
          notes string PATH 'Notes'
          ) mup;
      
      
      

       

      which is working like a charm, btw.

       

      If you look at the invokeHTTP endpoint, you see that I have to specify a list ID (1, in the example above) to get groups belonging to that list...

       

      Since I am putting this into VDB as virtual view, to hide ws complexities from SQL users, I need to find the better way to organize those views. Eg: I have 7 lists currently, and I could need to pass lists IDs, groups IDs, recipients IDs, messages IDs to get other infos from different ws calls...

       

      Is there any way to 'build' that REST GET endpoint from the client? Eg: using variables, or else?

      Or even better, some sort of stored procedure that use IDs from one call to build other calls... I'm just wondering...

       

      Atm, if I need to get all recipients in all lists, I have to issue 7 different calls, one for each list. I could also use a UNION statement to get a single, big, list with all the users from all current lists, probably (that would be huge, though - it would need materialization).

       

      But even with that trick, what if my colleagues working in Mailup create another list (or worse, delete one existing)? My vdb would either be out of date or broken, and I have to update and redeploy the vdb...

       

      How would you do? Is there any method, which I don't know about? Am I taking too much sun (I don't think so, it's cloudy here..)?

       

      Marco

        • 1. Re: Is there any way to parametrize endpoint url using invokeHTTP?
          Ramesh Reddy Master

          If this were a store procedure instead of View then it would have been a very easy task, being a view I am not sure. You can create Access Pattern on the view to force someone to provide the ID, but not sure how that can be captured in the transformation to generate the endpoint.

          • 2. Re: Is there any way to parametrize endpoint url using invokeHTTP?
            Steven Hawkins Master

            > but not sure how that can be captured in the transformation to generate the endpoint.

             

            If you can't use a procedure, then there isn't a great way to do that as you'd have to use session variables or a global temporary table.

            • 3. Re: Is there any way to parametrize endpoint url using invokeHTTP?
              Marco Ardito Master

              Ramesh Reddy wrote:

              If this were a store procedure instead of View then it would have been a very easy task

               

              I not yet used procedures in Teiid, and I'll do my homework reading docs pages like Virtual Procedures · Teiid Documentation, but can you give me a hint, as a general concept, about how this could be "very easily" done, in a similar case?

               

              I've read some doc and they usually have examples using conditionals to select SQL statements, or they process some data to finally pass a value to a SQL statement, but in my case, I already need procedures (invokeHTTP), and I could need to merge results from many of them as final result: I can't figure out how I could use them in my case, atm...

               

              I thought about this, referring to my example: in a procedure, in general terms:

              - use SQL / invokeHTTP to get IDs of all the lists

              - loop over all lists IDs and use SQL / invokeHTTP to get (eg) each list's groups using a syntax like above

              - but how?

              > should I use the loop to create a UNION View appending each list's results, and then finally outputting (how?) a big result (temporary) table? or should I use the loop to append each list's results to a temporary table directly?

               

              I think I need some advice...

               

              But before, about docs, I still don't fully understand  procedures parameters usage:

              - I can't find a good example about the full cycle of a IN, OUT, INOUT parameter

              - some example has parameters but without IN, OUT, INOUT keywords, and this is allowed by DDL (DDL Metadata · Teiid Documentation), but in that case how will this parameter behave?

              - some example (Virtual Procedures · Teiid Documentation) has complete procedure structure, but shows no procedure declaration (with parameters), while others show a complete procedure declaration, but no procedure structure (DDL Metadata · Teiid Documentation)

               

              ...so I still can't understand the full usage of procedures when using parameters, how they are defined and then used in the procedure body to get out some tabular data...

               

              Is there any better reference, or a better example? A quickstart using some of these concepts maybe?

              • 4. Re: Is there any way to parametrize endpoint url using invokeHTTP?
                Ramesh Reddy Master

                try something like

                 

                CREATE PROCEDURE getGroupList (IN listid string) returns table (id_grp string,   id_lst string,  nome string,   notes string)
                BEGIN
                declare string VARIABLES.url = 'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/List/'||listid||'/Groups';
                select 
                        mup.id_group, mup.id_list, mup.nome, mup.notes 
                    from   
                    (call 
                        comunicazioni.invokeHTTP( 
                            endpoint=>url, 
                            action=>'GET', 
                            headers=>jsonObject('application/json' as "Content-Type", 'application/json' as "Accept",0 as  "Content-length") 
                        ) 
                    ) w,   
                    XMLTABLE('/Items/Items' passing JSONTOXML('Items', w.result) columns 
                    id_group string PATH 'idGroup', 
                    id_list string PATH 'idList', 
                    nome string PATH 'Name', 
                    notes string PATH 'Notes' 
                    ) mup;
                END
                

                 

                yes, this area needs more examples. Can you create document JIRA for this?

                 

                Ramesh..

                • 5. Re: Is there any way to parametrize endpoint url using invokeHTTP?
                  Marco Ardito Master

                  Thanks Ramesh, that works (it was just missing the " AS " keyword before BEGIN).

                   

                  I tried to expand on that, trying to build a complete query to the webservice (using invokehttp) into a string, and then passing that string to EXECUTE IMMEDIATE, to get the procedure return the result.

                   

                  I started with a simple case (unuseful example, just to test if passing that string works)

                  like

                   

                  CREATE PROCEDURE getListsGroups () returns table (id_grp string,  groupName string)  AS
                                      BEGIN 
                                          declare string VARIABLES.listsurl = 'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/User/Lists'; 
                  
                                          declare string VARIABLES.listquery='
                                                  select 
                                                  mup.id_list, mup.name_list
                                                  from   
                                                  (call 
                                                          comunicazioni2.invokeHTTP( 
                                                                  endpoint=>' || listsurl || ', 
                                                                  action=>''GET'', 
                                                                  headers=>jsonObject(''application/json'' as "Content-Type", ''application/json'' as "Accept",0 as  "Content-length") 
                                                          ) 
                                                  ) w,   
                                                  XMLTABLE(''/Items/Items'' passing JSONTOXML(''Items'', w.result) columns 
                                                  id_list string PATH ''idList'', 
                                                  name_list string PATH ''Name''
                                                  ) mup; 
                                              ';
                  
                                         
                                          EXECUTE IMMEDIATE listquery AS list_id string, list_name string;
                                         
                                      END
                  

                   

                  and I got

                   

                  Error: TEIID30168 Remote org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE listquery AS list_id string, list_name string" with the SQL statement "

                                                  select

                                                  mup.id_list, mup.name_list

                                                  from  

                                                  (call

                                                          comunicazioni2.invokeHTTP(

                                                                  endpoint=>https:://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/User/Lists,

                                                                  action=>'GET',

                                                                  headers=>jsonObject('application/json' as "Content-Type", 'application/json' as "Accept",0 as  "Content-length")

                                                          )

                                                  ) w,  

                                                  XMLTABLE('/Items/Items' passing JSONTOXML('Items', w.result) columns

                                                  id_list string PATH 'idList',

                                                  name_list string PATH 'Name'

                                                  ) mup;

                                              " due to: TEIID31100 Parsing error: Encountered "=>https[*]:[*]:/" at line 7, column 112.

                  Was expecting: "and" | "between" | "in" | "is" | "like" | "like_regex" | "not" | "or" | "similar" | "," ...

                  SQLState:  50000

                  ErrorCode: 30168

                   

                  In my procedure definition I already escaped single quotes doubling them, and that seems to work, maybe, but even if I try to escape that ":" doubling it (as for single quotes) or preceding it with "\", it doesn't seem to work... I can't recall something specific in the docs...

                   

                  Can that endpoint url (or any other string) be escaped in procedures, and how? Or a workaround? A string escaping function maybe?

                   

                  Marco

                  • 6. Re: Is there any way to parametrize endpoint url using invokeHTTP?
                    Ramesh Reddy Master

                    single quotes missing around your url in the final string.

                    • 7. Re: Is there any way to parametrize endpoint url using invokeHTTP?
                      Marco Ardito Master

                      ..of course... I didn't catch it... thanks.

                       

                      That now works, but here is my real procedure, contructing the UNION of several lists groups into one single table:

                       

                      CREATE PROCEDURE getListsGroups () returns table (id_grp string,  groupName string)  AS
                      BEGIN
                          declare string VARIABLES.listsurl = 'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/User/Lists'; 
                      
                          declare string VARIABLES.listquery='
                                  select
                                  mup.id_list, mup.name_list
                                  from
                                  (call
                                          comunicazioni2.invokeHTTP(
                                                  endpoint=>''' || listsurl || ''',
                                                  action=>''GET'',
                                                  headers=>jsonObject(''application/json'' as "Content-Type", ''application/json'' as "Accept",0 as  "Content-length")
                                          )
                                  ) w,
                                  XMLTABLE(''/Items/Items'' passing JSONTOXML(''Items'', w.result) columns
                                  id_list string PATH ''idList'',
                                  name_list string PATH ''Name''
                                  ) mup;
                              ';
                      
                          declare string VARIABLES.grpquery_pre='
                                  select
                                  mup.id_grp, mup.name_grp
                                  from
                                  (call
                                          comunicazioni2.invokeHTTP(
                                                  endpoint=>''https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/List/';
                      
                          declare string VARIABLES.grpquery_post='/Groups'',
                                                  action=>''GET'',
                                                  headers=>jsonObject(''application/json'' as "Content-Type", ''application/json'' as "Accept",0 as  "Content-length")
                                          )
                                  ) w,
                                  XMLTABLE(''/Items/Items'' passing JSONTOXML(''Items'', w.result) columns
                                  id_grp string PATH ''idGroup'',
                                  name_grp string PATH ''Name''
                                  ) mup
                              ';
                      
                          EXECUTE IMMEDIATE listquery AS list_id string, list_name string INTO #temp;
                      
                          declare string VARIABLES.grpquery = '';
                      
                          LOOP ON (SELECT list_id, list_name from #temp) as lista           
                          BEGIN
                              IF (grpquery>'')
                                  BEGIN
                                      grpquery = grpquery || ' UNION ';
                                  END
                         
                              grpquery=grpquery || grpquery_pre || lista.list_id || grpquery_post;
                         
                          END
                      
                          grpquery=grpquery || ';';
                      
                          EXECUTE IMMEDIATE grpquery AS grp_id string, grp_name string;
                      
                      
                      END
                      
                      
                      
                      
                      

                       

                      and this, when executing

                      select * from (call getListsGroups()) as x

                       

                      fails again, it seems to be escaping again, but it should not be, since all other iterations are escaped identically... I cannot understand why it seems to happen only at last iteration (list 1, the "lists" list is reversed), on line 99... could be a too long sql string? is there any limitation here? around 4000 characters?

                       

                      [edit]

                      yes, it should be the string size limit... as in

                      Supported Types · Teiid Documentation

                      - confirmed: if I limit the "lists" query to 3 items, the union then works (since the final query string is less than 4000 chars long)

                       

                      eg:

                      this (limited to 3 lists)

                      select * from (call getListsGroups()) as x
                      

                       

                      gives the same result as this

                       

                      select * from (call getGroupList(7)) as x
                      UNION ALL
                      select * from (call getGroupList(6)) as x
                      UNION ALL
                      select * from (call getGroupList(5)) as x
                      

                       

                      should I use clob instead of string? but it seems I can't concatenate clobs as I do with strings...

                      [/edit]

                       

                      Error: TEIID30168 Remote org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE grpquery AS grp_id string, grp_name string" with the SQL statement "
                          select
                          mup.id_grp, mup.name_grp
                          from 
                          (call
                                  comunicazioni2.invokeHTTP(
                                          endpoint=>'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/List/7/Groups',   
                                          action=>'GET',
                                          headers=>jsonObject('application/json' as "Content-Type", 'application/json' as "Accept",0 as  "Content-length")
                                  )
                          ) w, 
                          XMLTABLE('/Items/Items' passing JSONTOXML('Items', w.result) columns
                          id_grp string PATH 'idGroup',
                          name_grp string PATH 'Name'
                          ) mup
                      UNION
                          select
                          mup.id_grp, mup.name_grp
                          from 
                          (call
                                  comunicazioni2.invokeHTTP(
                                          endpoint=>'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/List/6/Groups',   
                                          action=>'GET',
                                          headers=>jsonObject('application/json' as "Content-Type", 'application/json' as "Accept",0 as  "Content-length")
                                  )
                          ) w, 
                          XMLTABLE('/Items/Items' passing JSONTOXML('Items', w.result) columns
                          id_grp string PATH 'idGroup',
                          name_grp string PATH 'Name'
                          ) mup
                      UNION
                          select
                          mup.id_grp, mup.name_grp
                          from 
                          (call
                                  comunicazioni2.invokeHTTP(
                                          endpoint=>'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/List/5/Groups',   
                                          action=>'GET',
                                          headers=>jsonObject('application/json' as "Content-Type", 'application/json' as "Accept",0 as  "Content-length")
                                  )
                          ) w, 
                          XMLTABLE('/Items/Items' passing JSONTOXML('Items', w.result) columns
                          id_grp string PATH 'idGroup',
                          name_grp string PATH 'Name'
                          ) mup
                      UNION
                          select
                          mup.id_grp, mup.name_grp
                          from 
                          (call
                                  comunicazioni2.invokeHTTP(
                                          endpoint=>'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/List/4/Groups',   
                                          action=>'GET',
                                          headers=>jsonObject('application/json' as "Content-Type", 'application/json' as "Accept",0 as  "Content-length")
                                  )
                          ) w, 
                          XMLTABLE('/Items/Items' passing JSONTOXML('Items', w.result) columns
                          id_grp string PATH 'idGroup',
                          name_grp string PATH 'Name'
                          ) mup
                      UNION
                          select
                          mup.id_grp, mup.name_grp
                          from 
                          (call
                                  comunicazioni2.invokeHTTP(
                                          endpoint=>'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/List/3/Groups',   
                                          action=>'GET',
                                          headers=>jsonObject('application/json' as "Content-Type", 'application/json' as "Accept",0 as  "Content-length")
                                  )
                          ) w, 
                          XMLTABLE('/Items/Items' passing JSONTOXML('Items', w.result) columns
                          id_grp string PATH 'idGroup',
                          name_grp string PATH 'Name'
                          ) mup
                      UNION
                          select
                          mup.id_grp, mup.name_grp
                          from 
                          (call
                                  comunicazioni2.invokeHTTP(
                                          endpoint=>'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/List/2/Groups',   
                                          action=>'GET',
                                          headers=>jsonObject('application/json' as "Content-Type", 'application/json' as "Accept",0 as  "Content-length")
                                  )
                          ) w, 
                          XMLTABLE('/Items/Items' passing JSONTOXML('Items', w.result) columns
                          id_grp string PATH 'idGroup',
                          name_grp string PATH 'Name'
                          ) mup
                      UNION
                          select
                          mup.id_grp, mup.name_grp
                          from 
                          (call
                                  comunicazioni2.invokeHTTP(
                                          endpoint=>'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/List/1/Groups',   
                                          action=>'GET',
                                          headers=>jsonObject('application/jso" due to: TEIID31100 Parsing error: Encountered ">jsonObject([*]'[*]" at line 99, column 117.
                      Was expecting: "char" | "date" | "time" | "timestamp" | "cast" | "convert" | "all" | "any" | "array_agg" | "case" ...
                      SQLState:  50000
                      ErrorCode: 30168
                      
                      
                      
                      
                      
                      • 8. Re: Is there any way to parametrize endpoint url using invokeHTTP?
                        Marco Ardito Master

                        I restructured my procedure using a call to the "single list" procedure, passing each time an ID, because in this way the final sql string is shorter and does not hit the 4000 chars size limit above. It works.

                         

                        But, anyway, I would prefer a way to build a very long sql string (clob?) to pass to EXECUTE.

                        Otherwise could I append all lists results to a temp table, perhaps, and ultimately return a select * from that temp table?

                         

                        Before, I tried to define as clob  but it didn't validate, giving:

                         

                        WARN  [org.teiid.PLANNER.RESOLVER] (Worker45_async-teiid-threads95)  TEIID31080 MUP_data.getListsGroups validation error: TEIID30070 The function '(VARIABLES.grpquery || VARIABLES.unionstr)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions.

                         

                        note: this happened when I defined

                        - VARIABLES.grpquery  as clob and VARIABLES.unionstr as string

                        - VARIABLES.grpquery  as clob and VARIABLES.unionstr as clob

                         

                        while when they both were string, the resulting sql string hit the size limit above.

                         

                        So, now my procedure is like:

                         

                        CREATE PROCEDURE getListsGroups () returns table (id_grp string, id_lst string, groupName string, groupNotes string)  AS
                            BEGIN
                            declare string VARIABLES.listsurl = 'https://services.mailup.com/API/v1.1/Rest/ConsoleService.svc/Console/User/Lists'; 
                        
                            declare string VARIABLES.listquery='
                                    select
                                    mup.id_list, mup.name_list
                                    from  
                                    (call
                                            comunicazioni2.invokeHTTP(
                                                    endpoint=>''' || listsurl || ''',
                                                    action=>''GET'',
                                                    headers=>jsonObject(''application/json'' as "Content-Type", ''application/json'' as "Accept",0 as  "Content-length")
                                            )
                                    ) w,  
                                    XMLTABLE(''/Items/Items'' passing JSONTOXML(''Items'', w.result) columns
                                    id_list string PATH ''idList'',
                                    name_list string PATH ''Name''
                                    ) mup
                                    /*LIMIT 3*/;
                                ';
                        
                            declare string VARIABLES.grpquery_pre='select id_grp, id_lst, nome, notes from (call getGroupList(';  
                            declare string VARIABLES.grpquery_post=')) as mup';      
                        
                            EXECUTE IMMEDIATE listquery AS list_id string, list_name string INTO #temp;
                        
                            declare string VARIABLES.grpquery = '';
                            declare string VARIABLES.unionstr = ' UNION ';
                        
                            LOOP ON (SELECT list_id, list_name from #temp) as lista              
                            BEGIN
                                IF (grpquery>'')
                                    BEGIN
                                        grpquery = grpquery || unionstr;
                                    END
                                grpquery=grpquery || grpquery_pre || lista.list_id || grpquery_post;  
                            END
                        
                            grpquery=grpquery || ';';
                        
                            EXECUTE IMMEDIATE grpquery AS grp_id string, lst_id string, grp_name string, grp_notes string;
                        
                            END
                        
                        
                        • 9. Re: Is there any way to parametrize endpoint url using invokeHTTP?
                          Steven Hawkins Master

                          > But, anyway, I would prefer a way to build a very long sql string (clob?) to pass to EXECUTE.

                           

                          That is certainly a valid enhancement.  It would be easier to just allow clobs in the statement rather than to introduce a long varchar type.

                           

                          > Before, I tried to define as clob  but it didn't validate, giving:

                           

                          [TEIID-1248] Clob usability - JBoss Issue Tracker was logged a while ago to address this - with other lob types, like SQL/XML we've added this functionality, so it should be there with clobs as well.  Can you vote for that for inclusion in 9.1 or 9.2?