Retrieve a Sharepoint 2013 document from an RPG program?

[evawebrez02]

I need to programmatically retrieve a document from Sharepoint 2013 for use in an RPGLE program. Has anyone ever done anything similar? At the very least, if I can get the document out of Sharepoint and onto a network file share (ideally the IFS) I know how to grab it from there. I've explored a lot of different suggestions and possibilities but many are in C# or .NET which I don't know and I'm struggling to find something that I can adapt to work for me.

[Viking]

I don't have the answer for you, but I'm curious in case I need to do something similar in the future... do you have on-premise Sharepoint or Sharepoint online? Also, what kind of document are you planning to retrieve? Will you be parsing it and using the data, or just retrieving a PDF file or something so that you can show it or attach it to an email, or something like that?

[Scott Klement]

I know absolutely nothing about Sharepoint. I don't even know what it does... therefore, I did not reply to this post. But... being a nice guy, when the OP complained about not getting an answer, I did a quick Google search and found some examples. (Not sure why the OP didn't find them.)

Here's one using curl or wget:

http://stackoverflow.com/questions/24924014/download-file-folder-from-sharepoint-using-curl-wget-automatically

Or here's another example that might be simpler?

http://www.commandlinefu.com/commands/view/7005/get-a-file-from-sharepoint-with-curl

Both curl and wget are available on IBM i to run in PASE. So the simplest thing would be to call them from your RPG program... Of course, you'll need to know how to deal with a mixed IBM i and PASE environment and the performance issues that go with it.

Alternately you could do the same thing from a native RPG solution, like my own (open source) HTTPAPI. If it's just retrieving a URL that should be fairly simple to do, I would think! That last example sure makes it look simple, anyway, it says all you need is to use HTTP and login with your domain credentials.

Get HTTPAPI here:

http://www.scottklement.com/httpapi/

Here's an example using the current HTTPAPI (released yesterday)

     H dftactgrp(*no) bnddir('HTTPAPI')

      /copy httpapi_h

     d rc              s             10i 0
     D url             s            200a   varying
     D outfile         s            200a   varying
     D basic           s              1n
     D digest          s              1n
     D ntlm            s              1n
     D realm           s            124a
     D pwtype          s              1a
     D userid          s             50a
     D password        s             50a

      /free

       http_debug(*on);

       url = 'https://sharepoint.domain.com/path/to/file';
       outfile = '/tmp/myfile.xlsx';

       rc = http_req('GET': url: outfile);


       // A login was required. Learn what type of login, and send back
       // the appropriate userid/password

       if rc = 401;

          http_getAuth(basic: digest: realm: ntlm);

          select;
          when ntlm;
            pwtype = HTTP_AUTH_NTLM;
          when digest;
            pwtype = HTTP_AUTH_MD5_DIGEST;
          other;
            pwtype = HTTP_AUTH_BASIC;
          endsl;

          // you may want to ask the user for a password, etc?
          //  "realm" contains the name of what you're signing into...

          userid = 'MYDOMAIN/MYUSERID';
          password = 'SecretStuff';

          http_setAuth(pwtype: userid: password);

          rc = http_req('GET': url: outfile);
       endif;


       // If there is still an error, give up now.

       if rc <> 1;
         http_crash();
       endif;

       // otherwise... success!  Should have retrieved the file!

       *inlr = *on;

Again, this is a guess, and I know absolutely nothing about sharepoint. But it should do the same thing as the curl example from the last link in native RPG.

[evawebrez02]

Thank you very much, Scott! I am sorry I haven't been able to respond until now. Looks like my Code400 profile is finally "authorized" and I can utilize all of the features of the forum successfully. Woohoo!

Believe me when I say I spent a great deal of time researching and experimenting with various solutions before posing a question on this forum. I may have actually come across the link you posted regarding curl but I wasn't familiar enough with it to understand that I might be able to use it. In any case, your HTTPAPI works beautifully for so many needs. Another developer in our shop has used it in a few of his applications and several of us have used the knowledge gained from your "Working with the IFS" eBook many times over the years. Thank you for providing so many great tools and valuable information to the IBM community!

That said, I downloaded the newest release of HTTPAPI and was able to use it to retrieve the "test" document from your website as described in your example program source. However, I ran into an unexpected stumbling block when I tried to use the same technique to retrieve a document from SharePoint. Research showed that SharePoint requires a special "access token" in the Authorization header when using its REST API. I found a thread in your website archives describing how to set an additional header, but obtaining this elusive SharePoint access token is proving to be another significant challenge. There is a lot of documentation about it to sift through... I've got my work cut out for me.

One last thing.. I just want to make sure you (and other readers) know that I don't take any assistance, guidance or other information that I may get from this forum for granted and I certainly don't want anyone to think I was complaining about not getting an answer to this question about SharePoint. That would be just plain rude. When you mentioned me complaining that I wasn't getting an answer (presumably from you noticing my other post)... please know that I was referring to not hearing back from a site admin as to why I couldn't reply to my own posts or navigate the various features of the site without getting "not authorized" messages. It seems that it has all been straightened out now, though, and I'm able to use the site successfully. This gal is a happy camper!

[evawebrez02]

I don't have the answer for you, but I'm curious in case I need to do something similar in the future... do you have on-premise Sharepoint or Sharepoint online? Also, what kind of document are you planning to retrieve? Will you be parsing it and using the data, or just retrieving a PDF file or something so that you can show it or attach it to an email, or something like that?

Viking, we have an on-premise Sharepoint site. My need is (mostly) to retrieve PDFs, but I could be going after the occasional Word doc or Exce file. I won't need to parse it... I just want to copy it out of SharePoint and into our document imaging system so that it can be indexed and accessed from our Accounting system software.

[evawebrez02]

Update:

I discovered that I can run the following curl command after calling QP2TERM to get the SharePoint document and I don't need to provide an access token. It works just like I want it to by retrieveing the file and putting it in a location on the IFS.

curl -k --ntlm -u user:password http://sharepoint/path/to/file/thefile.pdf  >  /temp/myfile.pdf

However, due to my lack of understanding about curl and the inner workings of HTTPAPI, I don't understand what's different about the curl command above as opposed to what I'm doing in my RPGLE program (below) using HTTPAPI, which I can't get to work.

http_debug(*ON) ;

url = 'http://sharepoint/path/to/file/thefile.pdf';
ifs = '/temp/myfile.pdf';

rc = http_req('GET': url: ifs);  

if rc <> 1;

     http_getAuth(basic: digest: realm: ntlm);
     select;
         when ntlm;
             pwtype = HTTP_AUTH_NTLM;
         when digest;
             pwtype = HTTP_AUTH_MD5_DIGEST;
         other;
             pwtype = HTTP_AUTH_BASIC;
     endsl;

     userid = 'domain/username';
     password = 'mypass';

     http_setAuth(pwtype: userid: password);

     rc = http_req('GET': url: ifs);

endif;

if rc <> 1;
    http_crash();
endif;

When I run the RPG pgm, I get "401 UNAUTHORIZED" in the debug log and an empty "myfile.pdf" is created in the IFS directory. By stepping through the program in debug, I can tell it's definitely detecting that the NTLM authentication method is necessary.

My next thought was.. ok... can I run the curl command in RPG or CL? I found this, http://www.mcpressonline.com/program...rnet-with-curl which seems to say yes, but I can't get it to work that way either. The parm on the QP2SHELL2 call is not clear to me. I have a lot more options in my curl command than the example shows and I can't figure out how to set them up. I'd prefer to use the HTTPAPI anyway because I like the idea of keeping it all native.

[Scott Klement]

Could it be a diffrence in the userid/password? Are there any unusual characters in them? Are they the same in HTTPAPI vs. curl? If you used a domain in curl, did you specify it the same way in HTTPAPI? Or, if you did not use a domain, make sure you're also not using it in HTTPAPI...

[evawebrez02]

Username and password are the same in both. No special characters. I'm not specifying domain in curl so I tried mimicking that in HTTPAPI... didn't change the result.

The good news is I was finally able to get the curl command to work in a CL yesterday! I honestly would still prefer to use HTTPAPI though...

In case anyone is ever interested... here's how I got it to work in a CL:

             Pgm

             Dcl  &Cmd  *Char  Len(100) Value('/QOpenSys/usr/bin/curl')
             Dcl  &Auth  *Char  Len(6) Value('--ntlm')
             Dcl  &UArg  *Char  Len(2) Value('-u')
             Dcl  &User  *Char  Len(21) Value('user:pass')
             Dcl  &OutArg  *Char  Len(2) Value('-o')
             Dcl  &HideArg *Char  Len(2) Value('-s')
             Dcl  &Null  *Char  Len(1) Value(X'00')
             Dcl  &Link  *Char  Len(200) +
                          Value('http://sharepoint/path/to/file/thefile.pdf')
             Dcl  &Output  *Char  Len(21) +
                          Value('/temp/myfile.pdf')

             Chgvar     Var(&Cmd)  Value(&Cmd *Tcat &Null)
             Chgvar     Var(&Auth)   Value(&Auth *Tcat &Null)
             Chgvar     Var(&UArg)  Value(&UArg *Tcat &Null)
             Chgvar     Var(&User)  Value(&User *Tcat &Null)
             Chgvar     Var(&OutArg)  Value(&OutArg *Tcat &Null)
             Chgvar     Var(&Output)  Value(&Output *Tcat &Null)
             Chgvar     Var(&Link)  Value(&Link *Tcat &Null)
             Chgvar     Var(&HideArg)  Value(&HideArg *tcat &Null)

             Sbmjob     Cmd(Call Pgm(QP2SHELL) Parm(&Cmd &Auth &UArg &User +
                          &OutArg &Output &Link &HideArg))
             EndPgm

[evawebrez02]

GOT IT! Had a typo in my URL in the program using HTTPAPI. Not sure why it was throwing out authorization errors but, in any case, it's working. A TYPO. Grrrr. Hate it when I do that.
Thanks so much for your help, Scott!

Eva

[Scott Klement]

Eva,

If you're willing, please post the HTTPAPI code as well. That way, if someone else ever needs to do this, they'll be able to look at what you figured out.

Thanks!

[evawebrez02]

Sure, no problem. Here it is:

       Ctl-opt DftActGrp(*No);
       Ctl-opt BndDir('HTTPAPI');

      /include libhttp/qrpglesrc,httpapi_h

       Dcl-s rc       Int(10);
       Dcl-s url      Varchar(300);
       Dcl-s ifs      Varchar(256);
       Dcl-s pwtype   Char(1);
       Dcl-s userid   Char(50);
       Dcl-s password Char(50);

      // Turn on debugging for troubleshooting. It will write a debug log file
      // to the IFS in /tmp/httpapi_debug.txt
       http_debug(*ON);

       url = 'http://sharepoint/path/to/file/thefile.pdf';
       ifs = '/temp/myfile.pdf';

      // Set password type for SharePoint's NTLM authentication requirement
       pwtype = HTTP_AUTH_NTLM;

      // Set user and password
       userid = 'theuser';
       password = 'thepassword';

      // Set credentials for authentication
       http_setAuth(pwtype: userid: password);

      // Call HTTPAPI's routine to download the file to the IFS
       rc = http_req('GET': url: ifs);

      // End gracefully if error
       if rc <> 1;
         http_crash();
       endif;

       *inlr = *on;

[evawebrez02]

I have a follow up question regarding the NTLM authentication when using HTTPAPI. Is there a "best practice" for defining the password? I don't mind the password being stored in the RPGLE source but in doing so is there any way someone could find it during the job run? I don't see it in the joblog when I run my program but I don't know what I don't know so is there some other way it can be retrieved?

[tomliotta]

I don't like storing in source. I prefer storing such things in *USRIDXs. Earlier I preferred *DTAARAs; but a *USRIDX entry is a little more flexible in that lengths may change and multiple items can be stored/retrieved while simplifying ownership/authority. Either way, it's stored (and authorized) externally and can be changed without modifying source.

Retrieval can be done within a separately defined procedure. You might start by using a *DTAARA and enhance it later to use a *USRIDX. Pass the *DTAARA name in and return the value. When later enhanced, pass an index key in instead of the name. That way the interface won't change.

[Viking]

Eva, thanks for posting your code!

In mine, I replaced

rc = http_req('GET': url: ifs);

with

rc = http_url_get(URL:IFS);

because I guess my older version of HTTPAPI doesn't have that new procedure, but I'm thinking that's probably ok...

Anyway, I gave this a try but have not been successful so far. I'm getting:

SetError() #13: HTTP/1.1 401 Unauthorized
recvresp(): end with 401
recvdoc parms: identity 0
interpret_auth(): entered
SetError() #36: This page requires a user-id & password
http_close(): entered

I did set my userid and password. Perhaps a relevant difference is that our Sharepoint is in the cloud, i.e. not on-premise...? I do have another program that uses HTTPAPI to retrieve a csv file from a website and that URL also starts with https (as opposed to http), but it doesn't require any credentials since I don't specify a userid or password in that one yet retrieve the file just fine.

So, same error you were getting it seems, and I also have a blank pdf in my IFS folder as a result. I'll double-check my URL, but I did copy/paste it. It does have spaces in the path and therefore has some %20's in it...

Hmmm.... Scott, any thoughts?

[evawebrez02]

Viking,

We had the older version of HTTPAPI as well but based on Scott's recommendation to download the latest release in his original reply to me, that's what I did before I started working on this. Maybe the http_req vs. http_url_get does make a difference? Don't know. Maybe he'll be able to provide some insight on the difference and whether or not it matters.

Regarding your %20's ... I've got spaces in my paths as well. That doesn't cause any problem. In fact, when I ran the job through debug, I could see that Scott's API puts %20's in the URL if it detects any spaces anyway.