Re: help getting systools.httpgetclob to work

[Charles Wilt <charles.wilt@xxxxxxxxx>]

First rule of SQL stored proc and function calls...

If you get a "NOT FOUND"  check your parameters...

In your case, httpgetclob() has two parms, URL and HTTPHEADER

try...
systools.httpgetclob(URL =>'
https://www.magayo.com/api/jackpot.php?api_key=xxx&game=us_powerball'
                                 , HTTPHEADER => CAST(NULL AS CLOB(1K)) )

Also consider the new non-Java HTTP functions...
https://www.ibm.com/docs/en/i/7.4?topic=functions-http-get

Charles

On Wed, Oct 19, 2022 at 9:56 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

> I'm trying to get the below to work on my IBM i.
> When I run in ACS run sql scripts I get the error that systools.httpgetclob
> is not found but this is false because I see it in qsys2.sysroutines and I
> have other similar web services like this working on my ibm i, but to other
> domains of course.
>
> If you just call the url in a browser (or in postman) it will even pull
> back the correct json response (though json response reports an error
> because I'm not sharing my api key)... but still a result set in json
> should be returned.
>
> {"error": 101,"next_draw": "","currency": "","jackpot": ""}
>
> Can anyone explain why this is happening?
> The api documentation is here...
> https://www.magayo.com/lottery-docs/api/get-next-jackpot/
>
>
> select error
>       ,next_draw
>       ,currency
>       ,jackpot
>
> from json_table(
> systools.httpgetclob(
> 'https://www.magayo.com/api/jackpot.php?api_key=xxx&game=us_powerball'
>                      ),
>
>   '$'
>   columns
>   (error                float           path '$.error'
>   ,next_draw       char(10)     path '$.next_draw'
>   ,currency          char(3)      path '$.currency'
>   ,jackpot            char(15)       path '$.jackpot')
>
>   error on error
>             ) as x;
>
> tia jay
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com