How do I pass a URL and save the csv results in a DB2 file?

IBMi / AS400  Hello Experts, I have a need to pass a URL address to the internet that contains zip codes by radius and return the results.  I have found a pretty good site that I can use to pass this URL address and the results are in a CSV format.   How do I pass the URL and save the results in a DB2 file?
LVL 2
CompTech810Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant Commented:
In general, you need to write a program or script that can invoke the page, passing the necessary data to get the results you want, receive the response, and then parse the resulting data into your DB2 table.  How to do this depends on the site, and the tools you have at your disposal.  Please provide site info for a more specific answer.
Gary PattersonVP Technology / Senior Consultant Commented:
I'll note that there is a PASE utility, cURL, that may simplify this process for you.  It is available, along with a bunch of other great open-source Linux-style (technically AIX-style) tools in V7R2 (TR6) and V7R3.  I think cURL is part of of product 5733-OPS (option 7, I think).

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Open%20Source%20Technologies

Traditional web services require you to send an XML or JSON document with your request - it typically contains parameters that the web service needs to fulfill the request, and the web service provides an XML or JSON document back to you as a response.  

https://smartbear.com/blog/test-and-monitor/understanding-soap-and-rest-basics/

For web services, you can use cURL, or the free IBM i DB2 HTTP functions, or native functionality in Java, Node.js, php, Python, or a host of other web-friendly programming languages and tools to easily access web services.  RPG, frankly, isn't my first choice for "web work", though it can certainly be done.

I like using the IBM i DB2 HTTP functions from RPG, but be aware they they use Java, so this is more appropriate for server or batch jobs.  You wouldn't want to use these in an interactive program that 100 users are going to run:

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/New%20HTTP%20functions%20added%20to%20SYSTOOLS

There are also the ILE Web Services client APIS:

https://www.ibm.com/developerworks/ibmi/library/i-send-receive-user-defined-soap-rest-messages-trs/index.html

But there are plenty of sites that aren't really intended for program access (don't comply to SOAP or REST conventions) - they are usually intended for end user interactive access in a browser.  And for those, cURL is pretty good way to simulate a browser conversation, capture the output, and parse it yourself in a program.

Also, here is a library that makes it easier to access interact with HTTP from ILE languages like RPG:

https://www.scottklement.com/httpapi/
CompTech810Author Commented:
Thanks for the quick response!

Wow!!  That is a lot of good information!  Thanks Gary!!
 
The site is: http://www.zipcodeapi.com/API#matchClose     and the API is: Zip Codes by Radius

I'm going to take baby steps and learn each part that you have laid out in your response.  

Thanks again!!
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Gary PattersonVP Technology / Senior Consultant Commented:
If you're on a recent release, curl is by far the easiest option, IMO:

curl -o /myfolder/myfile.csv http://www.zipcodeapi.com/rest/3WOuMbivhSEuwRkw66phm1gOpwCbmedTCzOwug0L6mzjdAIMCRJzicsfjLOmkaWh/match-close.csv/47172,40202,90210,33767/100/km

Invokes the web service and dumps the output to /myfolder/myfile.csv

On IBM i you could then use CPYFRMIMPF to import CSV to a DB2 database table with correct layout.

Here's the output I got:

zip_code,lat,lng,city,state,timezone_identifier,timezone_abbr,utc_offset_sec,is_dst
47172,40202,15.966

I ran this using a Windows version of curl, but same basic syntax should work on IBM i in PASE (CALL QP2TERM), assuming you have curl installed.

curl --help will get you help for the curl command
Gary PattersonVP Technology / Senior Consultant Commented:
Because curl is a PASE tool, there is some overhead to call it.  I wouldn't use this approach in a high-volume application, but for the occasional call, it is fine.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTech810Author Commented:
Gary you are da man!   Thanks sooo much!  I am on 7.2 and will be learning a lot about Curl!  I'm going to go ahead and accept your solution as it will take me sometime to learn. I may need to contact you later for additional support.

Thanks Again!
CompTech810Author Commented:
This is getting really interesting and cool.

I was able to pass the URL to the website and receive data back into myfile.csv using QP2TERM.  Now I'm automating it using a CL and QP2SHELL.  When I call the CL myfile.csv contains information that the website doesn't understand my URL I'm sending it.  How do I view the URL that QP2SHELL is sending it.  I did take all the information that is contained in the CL and put it together on a URL address line in chrome and it works.

I'm soooo close on getting the automation part done...
Gary PattersonVP Technology / Senior Consultant Commented:
Calling PASE using QP2SHELL is a little more complicated that you might think - it doesn't do all of the environment setup that QP2TERM does, and as a result, you have to do some setup work.  

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzalf/rzalfshell.htm
http://www-01.ibm.com/support/docview.wss?uid=nas8N1022523

Suggest trying QShell instead - it is generally easier to use from CL, and handles automatic CCSID conversion between the EBCDIC native environment and the ASCII PASE environment for you.

As always, helping is easier if you can show your code, and debug output.  

For example, if you are building a curl command in your CL, display the contents of the variable in debug containing the command (or use DMPCLPGM) before you pass it to Qshell.
CompTech810Author Commented:
Hi Gary!!  Thanks for the quick response and sorry I'm just now getting back to this, I had a fire to put out.  
Funny thing happened, and this happens a lot.  As I was changing the API key to use a temporary key because I don't want to give out our key I noticed that there was one character that was wrong.  Not sure how that happened because I copied and pasted the key.  Ugghh!  Anyhoo it is now working.  I'm going to paste the code for other Experts and you may have suggestions.  I haven't put in variable parms yet because I'm still testing, but I will.  Thanks again for all your help!!

0001.00 ZIPFILE:     PGM                                                        
0006.00                    CALL       PGM(QP2SHELL2) PARM('/QOpenSys/usr/bin/curl' +  
0006.01                                     '-o' '/zip/ZipFile.csv' +                    
0006.02                                    'http://www.zipcodeapi.com/rest/maC69V1yG20+  
0006.03                                     eSeqXCxXFwDF4a7aBsho8HcHAckoBNznfdmYiOV2JgE+  
0006.04                                     ELeQXW9SHq/radius.csv/16509/100/mile')        
0007.00 EOJ:           ENDPGM
Gary PattersonVP Technology / Senior Consultant Commented:
Thanks for sharing.  Nice work.
CompTech810Author Commented:
Well, just when I thought everything was going good......  

Why is it that when I use a variable I get the following error:  
CALL ZIPFILE                                                            
PASE for i program not found or in use. Path name is &CMD &PARM1 &PARM2  
  &PARM3.                                                                
Error loading PASE for i program &CMD &PARM1 &PARM2 &PARM3.  See previous
  messages.                                                              
CPFB9C0 received by ZIPFILE at 1800. (C D I R)                          

This is my code:
0001.00 ZIPFILE:     PGM                                                              
0002.00              DCL        VAR(&NULL) TYPE(*CHAR) LEN(1) VALUE(X'00')            
0003.00              DCL        VAR(&CMD) TYPE(*CHAR) LEN(22) +                      
0004.00                           VALUE('/QOpenSys/usr/bin/curl')                    
0005.00              DCL        VAR(&PARM1) TYPE(*CHAR) LEN(2) +                      
0006.00                           VALUE('-O')                                        
0007.00              DCL        VAR(&PARM2) TYPE(*CHAR) LEN(16) +                    
0008.00                           VALUE('/ZIP/ZIPFILE.CSV')                          
0009.00              DCL        VAR(&PARM3) TYPE(*CHAR) LEN(122) +                    
0010.00                           VALUE('http://www.zipcodeapi.com/rest/ByzIu+        
0011.00                           zf9U1DlMBAxhet3phpmD5WfufpHfGpGhUDtXFGg37zK+        
0012.00                           /radius.csv/16509/0200/mile')      
0013.00                         CHGVAR VAR(&CMD) VALUE(&CMD *TCAT &NULL)              
0014.00                         CHGVAR VAR(&PARM1) VALUE(&PARM1 *TCAT &NULL)          
0015.00                         CHGVAR VAR(&PARM2) VALUE(&PARM2 *TCAT &NULL)          
0016.00                         CHGVAR VAR(&PARM3) VALUE(&PARM3 *TCAT &NULL)          
0017.00              DMPCLPGM                                                        
0018.00              CALL       PGM(QP2SHELL) PARM('&CMD &PARM1 &PARM2 +              
0019.00                           &PARM3')                                            
0020.00  /*          CALL       PGM(QP2SHELL) PARM('/QOpenSys/usr/bin/curl' +      */
0021.00  /*                       '-o' '/zip/ZipFile.csv' +                        */
0022.00  /*                       'http://www.zipcodeapi.com/rest/ByzIuzf9U1D+     */
0023.00  /*                       lMBAxhet3phpmD5WfufpHfGpGhUDtXFGg37zKqx9pio+     */
0024.00  /*                       qWYt65CWgz/radius.csv/16509+                     */  
0025.00  /*                       /200/mile')                                      */  
0026.00 EOJ:         ENDPGM                                                            

Thanks!
Gary PattersonVP Technology / Senior Consultant Commented:
Error message says it all:  you are passing a literal containing list of variable names:

CALL       PGM(QP2SHELL) PARM('&CMD &PARM1 &PARM2 &PARM3')                

QP2SHELL takes one parameter, so I think you want to concatenate (*BCAT) these together to form a command, and then pass the command.  Note that all those intermediate nulls will cause problems, so drop all but the last one.

/* TCAT short operator is |<  */
/* BCAT short operator is |>  */
/* CAT short operator is || */

DCL &PASECMD *CHAR(512)    /* You will need to determine appropriate length */

...
/* BCAT together parms, then CAT null character */
CHGVAR &PASECMD VALUE(&CMD |> &PARM1 |> &PARM2 |> &PARM3 || &NULL)
CALL PGM(QP2SHELL) PARM(PASECMD)  /* Notice no quotes around parm */
CompTech810Author Commented:
Thanks for the quick reply Gary!

I put in the changes that you suggested and I'm still getting an error.  Here is the error:

Message . . . . :   CPFB9C0 received by ZIPFILE at 1600. (C D I R)            
Cause . . . . . :   Control language (CL) program ZIPFILE in library DSLIBR  
  detected an error at statement number 1600. Message text for CPFB9C0 is:    
  Error loading PASE for i program /QOpenSys/usr/bin/curl -O /ZIP/ZIPFILE.CSV
  http://www.zipcodeapi.com/rest/ByzIuzf9U1DlMBAxhet3phpmD                   
Recovery  . . . :   This inquiry message can be avoided by changing the      
  program. Monitor for the error (MONMSG command) and perform error recovery  
  within the program. To continue, choose a reply value.                      

These are the values of the parms:

 Variable               Type        Length         Value                      
                                                    *...+....1....+....2....+
 &CMD                     *CHAR           22       '/QOpenSys/usr/bin/curl'  
 &NULL                    *CHAR            1       ' '                        
 &PARM1                   *CHAR            2       '-O'                      
 &PARM2                   *CHAR           16       '/ZIP/ZIPFILE.CSV'        
 &PARM3                   *CHAR          122       'http://www.zipcodeapi.com'
                                     +26           '/rest/ByzIuzf9U1DlMBAxhet'
                                     +51           '3phpmD5WfufpHfGpGhUDtXFGg'
                                     +76           '/radi'
                                     +101          'us.csv/16509/0200/mile'  
&PASECMD               *CHAR          165       '/QOpenSys/usr/bin/curl -O'
                                    +26           ' /ZIP/ZIPFILE.CSV http://' 
                                    +51           'www.zipcodeapi.com/rest/B'
                                    +76           'yzIuzf9U1DlMBAxhet3phpmD5'
                                    +101          'WfufpHfGpGhUDtXFGg37zKqx9'
                                    +126          '/radius.csv/'
                                    +151          '16509/0200/mile'          
                                         * * * * *   E N D   O F   D U M P   *

I removed part of the API key for privacy.

Thanks!
Gary PattersonVP Technology / Senior Consultant Commented:
Please show your code.
CompTech810Author Commented:
Opps, I forgot that..

0001.00 ZIPFILE:     PGM                                                                  
0002.00              DCL        VAR(&NULL) TYPE(*CHAR) LEN(1) VALUE(X'00')                
0003.00              DCL        VAR(&CMD) TYPE(*CHAR) LEN(22) +                          
0004.00                           VALUE('/QOpenSys/usr/bin/curl')                        
0005.00              DCL        VAR(&PARM1) TYPE(*CHAR) LEN(2) +                          
0006.00                           VALUE('-O')                                            
0007.00              DCL        VAR(&PARM2) TYPE(*CHAR) LEN(16) +                        
0008.00                           VALUE('/ZIP/ZIPFILE.CSV')                              
0009.00              DCL        VAR(&PARM3) TYPE(*CHAR) LEN(122) +                        
0010.00                           VALUE('http://www.zipcodeapi.com/rest/ByzIu+            
0011.00                           zf9U1DlMBAxhet3phpmD5WfufpHfGpGhUDtXFGg37zK+            
0012.00                           /radius.csv/16509/0200/mile')          
0013.00              DCL VAR(&PASECMD) TYPE(*CHAR) LEN(165)                              
0014.00              CHGVAR &PASECMD VALUE(&CMD |> &PARM1 |> &PARM2 |> &PARM3 || &NULL)  
0015.00              DMPCLPGM                                                            
0016.00              CALL PGM(QP2SHELL) PARM(&PASECMD)                                    
0017.00  /*          CALL       PGM(QP2SHELL) PARM('/QOpenSys/usr/bin/curl' +      */    
0018.00  /*                       '-o' '/zip/ZipFile.csv' +                        */    
0019.00  /*                       'http://www.zipcodeapi.com/rest/ByzIuzf9U1D+    */      
0020.00  /*                       lMBAxhet3phpmD5WfufpHfGpGhUDtXFGg37zKqx9pio+    */      
0021.00  /*                       qWYt65CWgz/radius.csv/16509+                    */      
0022.00  /*                       /0200/mile')                                    */      
0023.00 EOJ:         ENDPGM
Gary PattersonVP Technology / Senior Consultant Commented:
Looks like case of -o is wrong.  Suggest you use the long lowercase parameter alternatives to avoid these problems
CompTech810Author Commented:
I'm not sure what you mean by "long lowercase parameter"  The -o works when not in a variable.   Lines 0017.00-0022.00 does work.
Gary PattersonVP Technology / Senior Consultant Commented:
AIX / PASE commands and parameters are case sensitive.  Check the case on your -o.  You are passing upper case, from the dump.  Needs to be a lower case o.

Curl has alternative paramater syntax that is all.lower case.  

--output

Makes it easier to avoid these mistakes if you use it.  Look at curl documentation.

https://curl.haxx.se/docs/manpage.html
CompTech810Author Commented:
That was actually an accident.  Sorry, I looked right past -O.  After fixing it, same error.  This time I striped it down to only &CMD and &PARM:

0003.00              DCL        VAR(&CMD) TYPE(*CHAR) LEN(22) +      
0004.00                           VALUE('/QOpenSys/usr/bin/curl')    
0009.00              DCL        VAR(&PARM3) TYPE(*CHAR) LEN(122) +            
0010.00                           VALUE('http://www.zipcodeapi.com/rest/ByzIu+
0011.00                           zf9U1DlMBAxhet3phpmD5WfufpHfGpGhUDtXFGg37zK+
0012.00                           /radius.csv/16509/0200/mile')
0015.00              CHGVAR &PASECMD VALUE(&CMD |> &PARM3 || &NULL)
0016.00              DMPCLPGM                          
0017.00              CALL PGM(QP2SHELL) PARM(&PASECMD)  

Same error:
Message . . . . :   CPFB9C0 received by ZIPFILE at 1700. (C D I R)            
Cause . . . . . :   Control language (CL) program ZIPFILE in library DSLIBR    
  detected an error at statement number 1700. Message text for CPFB9C0 is:    
  Error loading PASE for i program /QOpenSys/usr/bin/curl                      
  http://www.zipcodeapi.com/rest/ByzIuzf9U1DlMBAxhet3phpmD5WfufpHfGpGhUDtXFGg3 
Recovery  . . . :   This inquiry message can be avoided by changing the        
  program. Monitor for the error (MONMSG command) and perform error recovery  
  within the program. To continue, choose a reply value.                      

&PASECMD                 *CHAR          145       '/QOpenSys/usr/bin/curl ht'  
                                    +26           'tp://www.zipcodeapi.com/r'  
                                     +51           'est/ByzIuzf9U1DlMBAxhet3p'
                                     +76           'hpmD5WfufpHfGpGhUDtXFGg37'
                                     +101          '/radius'
                                     +126          '.csv/16509/0200/mile'      

Any other ideas?  Thanks!
Gary PattersonVP Technology / Senior Consultant Commented:
QP2SHELL can be tricky to call from CL - I usually avoid it and use QSH whenever possible.  IT doesn't do all the setup that QSH and CALl QP2TERM do, and as a result, you can get odd errors.

If you have a program in the first parameter, you may need to pass the command parameters in separate variables.  I think this is what you were trying to do initially, but you quoted the parms by mistake:

/* Note no quotes around the three parms */
/* CMD = /QOpenSys/usr/bin/curl + null */
/* PARM1 = '-o /ZIP/ZIPFILE.CSV' + null - lower case "o" */
/* PARM2 = url + null */
CALL QP2SHELL PARM(&CMD &PARM1 &PARM2)

If this doesn't work as expected, then you may need to call a shell, and have the shell run curl, for you - following this example:

http://www-01.ibm.com/support/docview.wss?uid=nas8N1022523

Which uses the Bourne shell "sh" to execute a command "-c" called "sed".  You would use the same first two parameters, and then put the entire curl command with all of its parameters into the third parm.
CompTech810Author Commented:
Hi Gary/All.   I took a break from this to do my regular job.  

I finally got it to work and now I have a screen where the zip code and radius can be entered and passed to the CL.   I will post the code when I get everything functioning 100%.  I'm running into issues with the CPYFRMIMPF using a numeric.  I'm going to post that issue in a new question since it pertains to the command CPYFRMIMPF.   Thanks again Gary for all your help!
CompTech810Author Commented:
Welp....  Here's the finished code, needs cleaned up though.  I changed the API key for privacy.

0001.00 ZIPFILE:     PGM                                                          
0002.00              DCL        VAR(&CO) TYPE(*CHAR) LEN(2)                      
0003.00              DCL        VAR(&WS) TYPE(*CHAR) LEN(2)                      
0004.00              DCL        VAR(&EDTF) TYPE(*CHAR) LEN(8)                    
0007.00              DCL        VAR(&ZIPCDE) TYPE(*CHAR) LEN(5)                  
0008.00              DCL        VAR(&MILES) TYPE(*CHAR) LEN(4)                    
0009.00              DCL        VAR(&URL1) TYPE(*CHAR) LEN(166)                  
0010.00              DCL        VAR(&NULL) TYPE(*CHAR) LEN(1) VALUE(X'00')        
0011.00              DCL        VAR(&CMD) TYPE(*CHAR) LEN(23) +                  
0012.00                           VALUE('/QOpenSys/usr/bin/curl')                
0013.00              DCL        VAR(&PARM1) TYPE(*CHAR) LEN(2) +                  
0014.00                           VALUE('-o')                                    
0015.00              DCL        VAR(&PARM2) TYPE(*CHAR) LEN(16) +                
0016.00                           VALUE('/ZIP/ZIPFILE.CSV')                      
0017.00              DCL        VAR(&PARM3) TYPE(*CHAR) LEN(107) +                
0018.00                           VALUE('http://www.zipcodeapi.com/rest/ByzIu+    
0019.00                           zf9U1DlMBAxhet3phpmD5WfufpHfGpGhUDtXFGg37zK+    
0020.00                           soe9gke2ng9en3jk/radius.csv/')                  
0021.00              DCL        VAR(&SLASH) TYPE(*CHAR) LEN(1) VALUE('/')        
0022.00              DCL        VAR(&PARM4) TYPE(*CHAR) LEN(5) +                  
0023.00                           VALUE('/mile')                                  
0024.00              DCL        VAR(&PARM5) TYPE(*CHAR) LEN(128)                  
0025.00              RTVDTAARA  DTAARA(*LDA (102 5)) RTNVAR(&ZIPCDE)                    
0026.00              RTVDTAARA  DTAARA(*LDA (107 4)) RTNVAR(&MILES)                        
0027.00              RTVDTAARA  DTAARA(*LDA (257 2)) RTNVAR(&WS)                            
0028.00              RTVDTAARA  DTAARA(*LDA (255 2)) RTNVAR(&CO)                            
0029.00              CHGVAR     VAR(&CMD) VALUE(&CMD *TCAT &NULL)                          
0030.00              CHGVAR     VAR(&PARM1) VALUE(&PARM1 *TCAT &NULL)                      
0031.00              CHGVAR     VAR(&PARM2) VALUE(&PARM2 *TCAT &NULL)                      
0033.00              CHGVAR     VAR(&PARM5) VALUE(&PARM3 *CAT &ZIPCDE *CAT +                
0034.00                           &SLASH *CAT &MILES *CAT &PARM4 *TCAT &NULL)              
0040.00              CHGVAR     VAR(&EDTF) VALUE('ZIPFLE' *CAT &WS)                          
0041.00              DLTF       FILE(('ZIPFLE' *CAT &WS))                                  
0042.00              MONMSG MSGID(CPF2105)                                                  
0043.00              CRTPF      FILE(*CURLIB/&EDTF) SRCFILE(MYLIB2/QDDSSRC) +              
0044.00                           SRCMBR(ZIPFLE) OPTION(*NOLIST *NOSOURCE) +                
0045.00                           SIZE(*NOMAX)                                              
0047.00               CALL       PGM(QP2SHELL2) PARM(&CMD &PARM1 &PARM2 &PARM5)              
0048.00 /*           DLYJOB     DLY(10)                                               */    
0049.00              CPYFRMIMPF FROMSTMF('/ZIP/ZIPFILE.CSV') +                              
0050.00                           TOFILE(*CURLIB/&EDTF) MBROPT(*REPLACE) +                  
0051.00                           RCDDLM(*LF) STRDLM(*NONE) +                    
0052.00                           RMVBLANK(*TRAILING) RPLNULLVAL(*FLDDFT) +      
0052.01                           RMVCOLNAM(*YES)                                
0053.00              CHGCURLIB  CURLIB(OTHERLIB)                                    
0054.00                                                                          
0055.00 EOJ:         ENDPGM
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
AS400

From novice to tech pro — start learning today.