Thanks Kevin
A firewall makes sense

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 12:37 PM
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?

I think the 38000 is a timeout. Either the service is down, or you are blocked by a firewall are the usual causes of something like that.




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:47 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 for your reply Kevin
As to why our system is still on 65535 - I have no answer What should
it be is
my response

Anyway - I ran the command
chgjob ccsid(37)
Went into STRSQL
And ran the SQl

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

and it failed again
Query cannot be run. See lower level messages.
I F1 on that message

Message ID . . . . . . : QRY2293 Severity . . . . . . . : 30
Message type . . . . . : Information

Message . . . . : Query cannot be run. See lower level messages.
Cause . . . . . : An error occurred when the query to be run was
opened or
while output was being created. Either a cancel reply was received
to an
inquiry message about the problem, or the request was ended abruptly
without
use of an inquiry message. In most cases the problem involves data
that is
not correct or data that is not being used correctly.
Recovery . . . : To determine the cause of the problem, press F10
or use
the Display Job Log (DSPJOBLOG) command to view the messages that
were
logged immediately before this message. (If you are in a Work With
Queries
session, the diagnostic information will not be left in the job
queue unless
you cancel the session when you return to the display on which you
are
working.) Inspect any partially completed printer output before
discarding
it. Correct the problem, then repeat your request.

Bottom

I then F10 and see the following
4>> strsql
Current connection is to relational database NBTYS20.
Query options retrieved file QAQQINI in library QUSRSYS.
Query options retrieved file QAQQINI in library QUSRSYS.
Query options retrieved file QAQQINI in library QUSRSYS.
The query access plan has been rebuilt.
Query options retrieved file QAQQINI in library QUSRSYS.
**** Starting optimizer debug message for query .
Query options retrieved file QAQQINI in library QUSRSYS.
Query options retrieved file QAQQINI in library QUSRSYS.
The query access plan has been rebuilt.
Arrival sequence access was used for file *FUNCTION.
The query access plan has been rebuilt.
**** Ending debug message for query .
ODP created.
Blocking used for query.
Java Virtual Machine is IBM Technology for Java. PID(1203082)
JVM properties were loaded from a properties file.
User-defined function error on member QSQPTABL.
? C
Cancel reply received for message CPF503E.

When I F1 on the message
User-defined function error on member QSQPTABL.
The following is displayed
Additional Message Information

Message ID . . . . . . : CPF503E Severity . . . . . . . :
30
Message type . . . . . : Notify
Date sent . . . . . . : 08/03/18 Time sent . . . . . . :
11:39:22

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
38000.
The
text message returned from the program is:

SYSTOOLS.HTTPGETCLOBHTTPG00005java.net.SocketException:Unexpected
end

More...
Reply . . . : C
Press Enter to continue.


Looking at what SQLSTATE 38000. Means

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

That's it then. None of the http functions will run in 65535. You can
chgjob
ccsid(37) before you strsql and it should run. Is there any reason why
your
system is still on 65535?




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

System ccsid - 65535
As far as the job is concerned
Coded character set identifier . . . . . . . . . : 65535
Default coded character set identifier . . . . . : 37
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:23 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?

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

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 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].