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

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

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

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

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

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

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

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

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