RE: [EXTERNAL] Web service example to retrieve city/state by ZIP?

Thanks Kevin
I cut and pasted

SELECT *
FROM
json_table(SYSTOOLS.HTTPGETCLOB('http://api.zippopotam.us/us/60010'
,''),
'$' columns("post code" VARCHAR(10) , nested '$.places[*]'
columns("place name" VARCHAR(30), "state"
VARCHAR(15),
"state abbreviation" VARCHAR(2))))
X

Into STRSQL session and it failed with the following
SQLSTATE 57017


Additional Message Information

Message ID . . . . . . : CPF503E Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 08/03/18 Time sent . . . . . . : 11:07:15

Message . . . . : User-defined function error on member QSQPTABL.
Cause . . . . . : An error occurred while invoking user-defined function
HTTPGETCLOB in library SYSTOOLS. The error occurred while invoking the
associated external program or service program B2RESTUDF: in library
SYSTOOLS.D, program entry point or external name
com.ibm.db2.rest.DB2UDFWrapper.httpGetClob, specific name HTTPG00005. The
error occurred on member QSQPTABL file QSQPTABL in library QSYS2. The error
code is 1. The error codes and their meanings follow:
1 -- The external program or service program returned SQLSTATE 57017. The
text message returned from the program is: ^ .
2 -- The external program failed before it completed.
3 -- The database timed out waiting for the program to return. The timeout
More...
Press Enter to continue.
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kevin Bucknum
Sent: Friday, August 03, 2018 11:01 AM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>; Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: [EXTERNAL] Web service example to retrieve city/state by ZIP?

SELECT json
FROM
(VALUES(SYSTOOLS.HTTPGETCLOB('http://api.zippopotam.us/us/60010' ,'')))
x(json);

That returns data, but only for the primary.
A cleaned up version of that is
SELECT *
FROM
json_table(SYSTOOLS.HTTPGETCLOB('http://api.zippopotam.us/us/60010'
,''),
'$' columns("post code" VARCHAR(10) , nested '$.places[*]'
columns("place name" VARCHAR(30), "state"
VARCHAR(15),
"state abbreviation" VARCHAR(2))))
x

Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550

-----Original Message-----

From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of

Alan

Shore
Sent: Friday, August 3, 2018 9:10 AM
To: RPG programming on the IBM i (AS/400 and iSeries); Midrange

Systems

Technical Discussion
Subject: RE: [EXTERNAL] Web service example to retrieve city/state by

ZIP?

Im looking for something similar
Does anyone have a web service example of retrieving the plus 4 of a

zip

code, given the address and zip code

Mark - be careful - as some zip codes can have multiple cities For

example,

600100 has 17
ZTZIP ZTCITY
60010 BARRINGTON
60010 BARRINGTON HILLS
60010 DEER PARK
60010 FOX RIVER GROVE
60010 FOX RIVER VALLEY GARDENS
60010 FOX RV VLY GN
60010 HOFFMAN EST
60010 HOFFMAN ESTATES
60010 INVERNESS
60010 KILDEER
60010 LAKE BARRINGTON
60010 NORTH BARRINGTON
60010 PORT BARRINGTON
60010 PT BARRINGTON
60010 SOUTH BARRINGTON
60010 SUTTON
60010 TOWER LAKES

Some are just different ways of spelling


Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
mlazarus
Sent: Friday, August 03, 2018 10:03 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Cc: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-
l@xxxxxxxxxxxx>
Subject: [EXTERNAL] Web service example to retrieve city/state by ZIP?

Does anyone have a web service example of retrieving a city/state by
passing the ZIP code (or country and ZIP code)? Preferably in RPG

and/or

SQL. TIA.

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

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

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