× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. August 2018

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

What is your jobs and systems ccsid?




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 10:13 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?

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

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

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.