Re: FW: json_table httpgetclob and header data

[Jose Perez <joseenocperez.jp@xxxxxxxxx>]

Something must be wrong in the way you are building your GET request,  I
tried this
from ACS and is working fine.
Note: I ran with and without Read Committed and it worked fine.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
select *
from JSON_TABLE(systools.httpGetClob(
'https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=EUR', null),

'$'
columns(price_char varchar(10) path '$.*',
        price_dec dec(10, 2) path '$.*',
        price_json varchar(25) format JSON path '$')) as x

On Thu, May 17, 2018 at 3:00 PM Jose Perez <joseenocperez.jp@xxxxxxxxx>
wrote:

> Paul in ACS try running this command before running your sql statement
> SET TRANSACTION ISOLATION LEVEL READ COMMITTED, try with a null or blank
> header
> and make sure the token is correct.
>
> On Thu, May 17, 2018 at 12:28 PM Therrien, Paul <ptherrien@xxxxxxxxxxx>
> wrote:
>
> > Cross posting from Midrange list .. .
> >
> > We are on IBM I V7R2.  I am using ACS SQLScripts v. 1.1.7.2
> >
> > I am attempting to use httpgetclob and json_table to go to a vendor's
> > (GetResponse), rest API.
> > I have tested the API in RPG usinig SK's LIBHTTP and it works like a
> > charm.
> > However, when attempting it via an ACS SQL session using HTTPGETCLOB it
> > is failing.
> >
> > Here is the statement:
> >
> > select * from JSON_TABLE(
> > SYSTOOLS.HTTPGETCLOB('
> > https://api3.getresponse360.com/v3/contacts?query[email]=@worldnet.att.net'
> >
> > ,'Content-Type: application/json
> > X-Auth-Token: api-key 5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx3
> > X-Domain: email.ourdomain.com'
> > ),'$'
> > Columns(contactid varchar(4) PATH 'lax $.contactid',
> >         email varchar(100) PATH 'lax $.email',
> >         changedOn varchar(20) PATH 'lax $.changedOn',
> >         createdOn varchar(20) PATH 'lax $.createdOn')
> > ) as t       ;
> >
> > I am getting this error:
> > SQL State: 38000
> > Vendor Code: -4302
> > Message: [SQL4302] Java stored procedure or user-defined function
> > SYSTOOLS.HTTPGETCLOB, specific name HTTPG00005 aborted with an exception
> > "Content is not allowed in prolog."
> >
> > I am thinking that header statements:
> > 'Content-Type: application/json
> > X-Auth-Token: api-key 5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx3
> > X-Domain: email.ourdomain.com'
> > are somehow causing the error and this is possibly a formatting issue.
> >
> > 1. Is there something obviously wrong with my statement?
> > 2. Is there a debugging option I can turn on in the ACS SQL client to
> > help me debug HTTPGETCLOB?  I would love to determine the issue via my own
> > resources if possible.
> > 3. Perhaps HTTPGetClob is not what I really want to do?
> >
> > Any and all help appreciated.
> >
> > Paul
> >
> > --
> > This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> > mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/rpg400-l.
> >
> > Please contact support@xxxxxxxxxxxx for any subscription related
> > questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link: http://amzn.to/2dEadiD