Skip to main content

Sharing Dropbox Documents With RPG

Mike Larsen explains a new method for processing a shared document on IBM i.

Image of two documents on a dark blue circle surrounded by a red circular arrow.

Sharing data is a vital part of most businesses today and it seems the need is growing more each day. Web services have become a popular way to transfer data in XML or JSON format and they work great.  However, what if you wanted to exchange a CSV, PDF or Word document? Typically FTP or SFTP come to mind as solutions. These methods work great for a peer to peer transfer and are also sufficient in a peer to many peer model. So, then, why am I writing this article?    

A Better Solution

Recently, I was working on a project that required many customers (around 10) to send a CSV document to my client so it could be processed on their IBM i. My initial thought was to use FTP. Surely that would work, but as I thought about it more, it would require me to create an FTP script and deploy it to each of the ten clients. FTP still seemed like a logical choice at this point.  I mean, the script would be same; I’d just have to set it up on 10 different PCs. Then of course if the script ever needed to be changed, I’d have to change it and re-deploy it 10 times. Oh, and if a customer added a PC or two to their business, I’d also have to deploy the scripts there as well.
It became apparent that this method could be a maintenance challenge, so I started thinking of alternate approaches. My goal was to decrease the amount of maintenance and troubleshooting I may have to do and also to make sure the process was as seamless as possible to the client and their customers.
After kicking a few things around, I decided to take a look at Dropbox. I’ve used Dropbox to share documents with team members and it works great. After a quick search, I found an API I could use to get started. 

Streamlining With Dropbox

My plan was to create a folder for each customer on my clients Dropbox account, then grant each customer access to their own folder. That would ensure that the customers couldn’t access each other’s documents. Each customer would upload a document to their Dropbox folder then take a menu option from my client’s IBM i that would pull the document into the system and process it. Based on the customer’s IBM i user profile, the process would know which Dropbox folder to access.  

By using this method, I won’t need to work with the individual customers at all. They’ll just have a link to their Dropbox folder and will upload their document. All I need to do is build a process that will consume the Dropbox API, bring the document in and process it on IBM i.   

My first step was to create a Dropbox app in my client’s account. I won’t get into the details of setting this up, but Dropbox has really good instructions on how to set up an app (and it’s really easy). There are two important pieces of information you need from Dropbox: the URL and an access token. Once you have that, you’re ready to consume their API.  

I built an RPG program that will demonstrate how to consume the Dropbox app and load the document into the IFS where it can be processed further. I’ve tested this process with a CSV, PDF, and a Word document and it works perfectly. With that said, let’s take a look at the code. I’ll walk through snippets of the program and explain what each piece is doing.

Getting Started

I start with a few setup tasks. In the code snippet below, I retrieve the access token. For this example, I have it hard-coded, but in a production process, you’d likely soft-code this. 

dcl-proc getToken;
  accessToken = ‘your_access_token_here';
end-proc getToken;

Next, I set up the names of the documents I’m looking to retrieve. Again, these would be soft-coded in a production process. These documents reside in the folder (‘/Apps/Mike Larsen test app’) I set up for my demo. In the production process I spoke about earlier, I’d have 10 folders; one for each customer.

dcl-proc setupDropboxDocumentVariables;
 DropboxCsvDocument  = '/Apps/Mike Larsen test app/Duplicate NDC numbers.csv';
 DropboxPdfDocument  = '/Apps/Mike Larsen test app/11C_-_Advanced_SQL_DDL_-_+
 DropboxWordDocument = '/Apps/Mike Larsen test app/testWordDoc.docx';
end-proc setupDropboxDocumentVariables;  

Since I’m putting the documents I retrieve from Dropbox in the IFS, I define the names of those documents as well. I’ve also hard-coded the name of the folder in IFS where the documents will reside. We need to pass the access token and the name of the document we want to retrieve in the HTTP header. Make sure you don’t share your access token with anyone who shouldn’t have it.  

dcl-proc setupIfsDocumentVariables;
 ifsCsvDocument  = 'Duplicate NDC numbers.csv';
 ifsPdfDocument  = '11C_-_Advanced_SQL_DDL_-_More_than_just_physical_+
  ifsWordDocument = 'testWordDoc.docx';
end-proc setupIfsDocumentVariables;    

File sharing will continue to become more important in our industry and FTP is a great tool to use. As demonstrated in this article, using file sharing APIs can be just as effective and possibly easier to work with. It’s always good to have multiple options to allow you to choose the right method for the task at hand.    

dcl-proc setupWebServiceVariables;
  dcl-pi *N;
    inDocumentName char(150);
  // - - - -
  // this could be a soft-coded parameter passed to the program
  WebServiceHeader =
   '<httpHeader> ' +
    '<header name="Authorization" ' +
           'value="Bearer ' + %trim(accessToken) +  '"/> ' +
    '<header name="Accept" +
            value="application/octet-stream; charset=utf-8"/> ' +
    '<header name="Dropbox-API-Arg" ' +
           'value="{' + quote + 'path' + quote + ':' + quote +
                    %trim(inDocumentName) + quote + '}"/> ' +
  WebServiceUrl = 'https://URL_received_from_Dropbox';
end-proc setupWebServiceVariables;     

We need to pass the access token and the name of the document we want to retrieve in the HTTP header. Make sure you don’t share your access token with anyone who shouldn’t have it. I mentioned we’re writing the document we retrieve from Dropbox to the IFS. In order to do that successfully, we first need to create the shell of the document in the IFS. This will be an empty document until we consume the Dropbox API. The reason we want to create this now is to ensure this document is in ASCII format; assigning it an 819 CCSID will take care of that (see below).

dcl-proc createIfsFile;
  // make sure the file name doesn't have spaces in it. If it does, the
  // 'touch' command will create a separate file for each part of the
  // file name that is separated by a space.
  ifsDocumentName = %ScanRpl(' ' : '' : ifsDocumentName);
  CmdStr = *Blanks;
  // Run a shell script to create the file in the Ifs that will hold
  // the document. Using 819 ensures the file is created as Ascii.
  CmdStr = 'Qsh Cmd(''touch -C 819 ' + '/' +
                   %trim(ifsDirectory) + '/' +
                   %trim(ifsDocumentName) + ''')';
  Callp Run(Cmdstr:%Size(CmdStr));
end-proc createIfsFile; 

There’s one thing to watch out for when creating the shell document: Make sure there aren’t any spaces within the name of the document. If there are, the ‘touch’ command will create multiple documents. It interprets a space as the end of the document name.  

We’re finally ready to consume the Dropbox API. I used the SQL function HTTPGETCLOB to accomplish this task. 

dcl-proc consumeWs;
Exec sql
  Declare CsrC01 Cursor For
     Systools.HttpGetClob(:WebServiceUrl, :WebServiceHeader)
       from sysibm.sysdummy1;
  Exec Sql Close CsrC01;
  Exec Sql Open  CsrC01;
  DoU 1 = 0;
    Exec Sql
      Fetch Next From CsrC01 into :ifs_clob;
      If SqlCode < *Zeros or SqlCode = 100;
         If SqlCode < *Zeros;
            Exec Sql
              // perform error handling
              Get Diagnostics Condition 1
              :Text = MESSAGE_TEXT;
         Exec Sql
           Close CsrC01;
end-proc consumeWs;   

I use the HTTPGETCLOB function to consume the API, the use a fetch to process it. When I consume the API, I load the payload into a CLOB variable (ifs_clob) as I wanted to make sure it was large enough to hold the document. In addition, this also made it easier to load the document into the IFS using SQL (below). I haven’t coded any error handling in this sub-procedure, but you may want to in a production process.  

dcl-proc writeDocumentToIfs;
  fullIfsPath = '/' + %Trim(ifsDirectory) + '/' + %Trim(ifsDocumentName);
  // set up output parameters.  we'll be taking a document from the Clob field
  // and writing it to the Ifs.
  File_Out_fo   = Sqfovr;
  File_Out_name = %trim(fullIfsPath);
  File_Out_nl   = %Len(%trimR(File_Out_Name));
  // get the data from the Clob field and write it to the Ifs.
  Exec sql
    Select :ifs_clob
     Into :File_Out
     from sysibm.sysdummy1;
end-proc writeDocumentToIfs;     

This code requires a little explanation.  The first thing I did is to define the full path where I’m writing the document.  It’s important to identify and understand where the fields ‘File_Out_fo’, ‘File_Out_name’, ‘File_Out_nl’ come from.  In this program, I’ve defined a variable called ‘File_Out’ that is defined with a SQL_type (CLOB_file) data type (below).

dcl-s File_Out     Sqltype(Clob_file);

When the program is compiled, the ‘File_Out’ variable is converted to a data structure in the following format: 

// The SQLTYPE (CLOB_FILE) definition will be converted by the compiler
// into the following data structure:
// File_Out       DS
// File_Out_NL                   10U 0
// File_Out_DL                   10U 0
// File_Out_FO                  10U 0
// File_Out_NAME                 255A     

Let’s break down the meaning of each of these.

File_Out_NAME is the name of the IFS file I’m creating.  In this example, I made this the IFS output location plus the document name.

File_Out_NL is the length of the value from File_Out_NAME.

File_Out_DL is an output field and stands for ‘data length’.  I’m not doing anything with this field in this program.

File_Out_FO is the type of the file open.  I set this to ‘SQFOVR’ which means create a new file or replace an existing one.  

Once I populate the data structure with the proper values, I issue the SQL ‘select’ statement that will create the document in the IFS. I’ll leave it to you to decide what you want to do with the document from here.

IBM Systems Webinar Icon

View upcoming and on-demand (IBM Z, IBM i, AIX, Power Systems) webinars.
Register now →