Is there any way to parametrize endpoint url using invokeHTTP?

[m.ardito]

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

[rareddy]

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.

[shawkins]

> 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.

[m.ardito]

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?

[rareddy]

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..

[m.ardito]

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

[rareddy]

single quotes missing around your url in the final string.

[m.ardito]

..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

[m.ardito]

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

[shawkins]

> 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?