FTP in PeopleCode

I was recently working on an interface that needed to pull files from the vendor via FTP. More specifically, I needed to retrieve an unknown number of ASCII files from a vendor's server, with no specific naming convention. I have done this before using a 'call system' command to kick off a bat script that would handle staging the files. This worked pretty good, and I eventually got the bat script to perform fairly decent error handling. But this interface was being written in App Engine... well, more accurately, a PeopleCode program kicked off by an App Engine shell (I still hate writing App Engine programs the "PeopleSoft recommended" way).

PeopleCode does provide the GetAttachment function, but this seemed quite limiting in that it required me knowing the4name of the file I wanted to retrieve. I needed something more flexible. Searching the internet, I found a Slerp article by David Jen that explains how to use PeopleCode's external library declaration to utilize the FTP functionality in the wininet.dll library. This was great, but it still required me to know the name of the file I wanted to 'get'. But, at least, I now had another road to pursue. After understanding more of what was available to me in the wininet.dll and playing around with different configurations, I came up with the following:

/************************* DECLARE EXTERNAL FUNCTIONS *************************/
Declare Function GetLastError Library "kernel32"
      () Returns long As number;
Declare Function FormatMessageA Library "kernel32"
      (long Value As number, long Value As number, long Value As number, long Value As number, string Ref As string, long Value As number, long Value As number) Returns long As number;
Declare Function InternetOpenA Library "wininet.dll"
      (string Value As string, long Value As number, string Value As string, string Value As string, long Value As number) Returns long As number;
Declare Function InternetConnectA Library "wininet.dll"
      (long Value As number, string Value As string, integer Value As number, string Value As string, string Value As string, long Value As number, long Value As number, long Value As number) Returns long As number;
Declare Function FtpSetCurrentDirectoryA Library "wininet.dll"
      (long Value As number, string Value As string) Returns long As number;
Declare Function FtpGetFileA Library "wininet.dll"
      (long Value As number, string Value As string, string Value As string, long Value As number, long Value As number, long Value As number, long Value As number) Returns boolean;
Declare Function FtpDeleteFileA Library "wininet.dll"
      (long Value As number, string Value As string) Returns boolean;
Declare Function FtpCommandA Library "wininet.dll"
      (long Value As number, long Value As number, long Value As number, string Value As string, long Value As number, long Ref As number) Returns boolean;
Declare Function InternetReadFile Library "wininet.dll"
      (long Value As number, string Ref As string, integer Value As number, integer Ref As number) Returns boolean;
Declare Function InternetCloseHandle Library "wininet.dll"
      (long Value As number) Returns integer As number;
/*********************** END DECLARE EXTERNAL FUNCTIONS ***********************/
 
/* Program Variable Declarations */
Local array of string &gFileList;
Local number &gHostOpen, &gHostConnect;
Local string &gHostFileName;
Local number &gReturnCode;
Local boolean &gReturnStatus;
 
Function GetNTMessage(&inReturnCode As number) Returns string;
   Local number &l_MsgLength;
   Local string &l_ReturnMsg;
 
   &l_MsgLength = FormatMessageA(4096, 0, &inReturnCode, 0, &l_ReturnMsg, 256, 0);
   If &l_MsgLength > 0 Then
      Return &l_ReturnMsg;
   Else
      Return "";
   End-If;
End-Function;
 
Function FTPOpenHostConnection(&inLogFile As File) Returns number;
   Local number &l_OPEN_PRECONFIG = 0;
   Local number &l_Handle;
 
   /* Open connection to host */
   &l_Handle = InternetOpenA("Peoplecode FTP", &l_OPEN_PRECONFIG, "", "", 0);
   If &l_Handle = 0 Then
      &inLogFile.WriteLine("FTP ERROR: Unable to open Internet connection.");
   End-If;
   Return &l_Handle;
End-Function;
 
Function FTPConnectToHost(&inLogFile As File, &inHandleOpen As number, &inHostSystem As string, &inHostDirectory As string, &inHostFTPAccount As string, &inHostPassword As string) Returns number;
   Local number &l_FTP_PORT = 21;
   Local number &l_INET_FTP = 1;
   Local number &l_INET_PASSIVE = 134217728;
 
   Local number &l_HostDirSet, &l_Handle;
   Local number &l_ReturnCode;
 
   /* Login to Host */
   &l_Handle = InternetConnectA(&inHandleOpen, &inHostSystem, &l_FTP_PORT, &inHostFTPAccount, &inHostPassword, &l_INET_FTP, &l_INET_PASSIVE, 0);
 
   If &l_Handle = 0 Then
      &inLogFile.WriteLine("FTP ERROR: Unable to open connection to " | &inHostSystem | " !!!");
      Return 0;
   End-If;
 
   /* Change remote directory */
   If All(&inHostDirectory) Then /* If host directory passed, set it. */
      &l_HostDirSet = FtpSetCurrentDirectoryA(&l_Handle, &inHostDirectory);
   Else
      &l_HostDirSet = 1;
   End-If;
 
   If &l_HostDirSet = 0 Then
      &inLogFile.WriteLine("FTP ERROR: Unable to set directory to " | &inHostDirectory | " !!!");
      &l_ReturnCode = InternetCloseHandle(&l_Handle);
      Return 0;
   End-If;
 
   Return &l_Handle;
End-Function;
 
Function FTPGetFileList(&inLogFile As File, &inHandleConnect As number) Returns array of string;
   Local string &l_FTP_CMD = "NLST";
   Local number &l_ASCII = 1;
 
   Local array of string &l_FileList = CreateArrayRept("", 0);
 
   Local number &l_FTPHandle;
   Local string &l_Out, &l_Text_Buffer;
   Local number &l_Bytes = 100;
   Local number &l_Bytes_Read;
   Local boolean &l_ReturnStatus;
   Local number &l_ReturnCode;
 
   &l_ReturnStatus = FtpCommandA(&inHandleConnect, 1, &l_ASCII, &l_FTP_CMD, 0, &l_FTPHandle);
   If &l_FTPHandle = 0 Then
      &inLogFile.WriteLine("FTP ERROR: Unable to get directory !!!");
      Return &l_FileList;
   Else
      &inLogFile.WriteLine("Getting List of files from host.");
      &l_ReturnStatus = InternetReadFile(&l_FTPHandle, &l_Text_Buffer, &l_Bytes, &l_Bytes_Read);
      While &l_Bytes_Read > 0
         &l_Out = &l_Out | &l_Text_Buffer;
         &l_ReturnStatus = InternetReadFile(&l_FTPHandle, &l_Text_Buffer, &l_Bytes, &l_Bytes_Read);
      End-While;
      &l_ReturnCode = InternetCloseHandle(&l_FTPHandle);
   End-If;
   &l_FileList = Split(&l_Out, Char(13) | Char(10));
   &inLogFile.WriteLine(String(&l_FileList.Len) | " file(s) found.");
   Return &l_FileList;
End-Function;
 
Function FTPGetFile(&inLogFile As File, &inHandleConnect As number, &inHostFileName As string, &inLocalDirectory) Returns boolean;
   Local number &l_FTP_ASCII = 1;
   Local string &l_LocalFileName;
   Local number &l_ReturnCode;
   Local string &l_ReturnMsg;
   Local boolean &l_GetFile;
 
   /* Get (Session, local file, remote file, failexist, flags&attibutes, flags, context */
   &inLogFile.WriteLine("Getting " | &inHostFileName | "...");
   &l_LocalFileName = &inLocalDirectory | &inHostFileName;
   &l_GetFile = FtpGetFileA(&inHandleConnect, &inHostFileName, &l_LocalFileName, 0, 0, &l_FTP_ASCII, 0);
 
   If Not &l_GetFile Then
      &l_ReturnCode = GetLastError();
      &l_ReturnMsg = GetNTMessage(&l_ReturnCode);
      &inLogFile.WriteLine("FTP ERROR: " | String(&l_ReturnCode) | " : " | &l_ReturnMsg);
      &inLogFile.WriteLine("FTP ERROR: Unable to retrieve file !!!");
      Return False;
   End-If;
 
   /* Sucessful Get */
   &inLogFile.WriteLine("   Get Sucessful.");
   Return True;
End-Function;
 
Function FTPDeleteFile(&inLogFile As File, &inHandleConnect As number, &inHostFileName As string) Returns boolean;
   Local boolean &l_DeleteFile;
   Local number &l_ReturnCode;
   Local string &l_ReturnMsg;
 
   &l_DeleteFile = FtpDeleteFileA(&inHandleConnect, &inHostFileName);
 
   If Not &l_DeleteFile Then
      &l_ReturnCode = GetLastError();
      &l_ReturnMsg = GetNTMessage(&l_ReturnCode);
      &inLogFile.WriteLine("   FTP ERROR: " | String(&l_ReturnCode) | " : " | &l_ReturnMsg);
      &inLogFile.WriteLine("   FTP ERROR: Unable to delete file.");
      Return False;
   End-If;
 
   /* SUCCESS */
   &inLogFile.WriteLine("   File Deleted from HRSmart server.");
   Return True;
End-Function;
 
/*****************************************************************************/
/* MAIN PROGRAM
/*****************************************************************************/
 
/* Log File */
Local File &gLogFile;
 
/* Host information */
Local string &HOSTSERVER = "www.example.com";
Local string &HOSTACCOUNT = "usename";
Local string &HOSTPASSWORD = "password";
Local string &HOSTDIRECTORY = "";
Local string &FILE_MASK = "%";
 
/* A File Object &gLogFile needs to have already been open prior to calling this routine. */
/* Log information is written to this file */
 
&gHostOpen = FTPOpenHostConnection(&gLogFile);
If &gHostOpen > 0 Then
   &gHostConnect = FTPConnectToHost(&gLogFile, &gHostOpen, &HOSTSERVER, &HOSTDIRECTORY, &HOSTACCOUNT, &HOSTPASSWORD);
   If &gHostConnect > 0 Then
      &gFileList = FTPGetFileList(&gLogFile, &gHostConnect);
      While &gFileList.Len > 0
         &gHostFileName = &gFileList.Shift();
         If DBPatternMatch(&gHostFileName, &FILE_MASK, False) Then
            If FTPGetFile(&gLogFile, &gHostConnect, &gHostFileName, &LOCALDIRECTORY) Then
               &gReturnStatus = FTPDeleteFile(&gLogFile, &gHostConnect, &gHostFileName); 
            End-If;
         End-If;
      End-While;
      &gReturnCode = InternetCloseHandle(&gHostConnect);
      &gReturnCode = InternetCloseHandle(&gHostOpen);
   Else
      &gReturnCode = InternetCloseHandle(&gHostOpen);
   End-If;
End-If;

One thing to note... it seems that PeopleCode can only use DLL functions when they return datatypes that are understood by PeopleCode (strings, numbers, boolean). In trying to find a way to search the FTP directory, I came across a couple functions named FindFirstFile and FindNextFile. Unfortunately, these returned a pointer to a pointer to a WIN32_FIND_DATA structure. I could not figure out a way to use these. As a result, I had to take a slightly longer approach using the FTPCommand function.

Any ideas of another way to implement this?

PeopleCode Decoder

Updated Jan 5, 2010 - Fixed link to DECODEPC.sqr
Updated March 10, 2011 - Small change to add a couple more commands.
Updated October 1, 2011 - Additional updates, and moved the code to github

Several years ago, I came across an article title PeopleCode Secrets by Vijay Mukhi, Louis Fernandes and Sonal Kotecha. This provided a look behind the scenes of how PeopleSoft stores PeopleCode in the database. The article was never completed (at least not that I could find), but it did get me curious. DECODEPC.sqr was the result of the curiosity. I decided to try extending the information contained in the article and create an SQR that would be able to decode the PeopleCode BLOB.

This turned out to be more of a challenge than I though. PeopleCode is stored in a Long Raw field in the PSPCMPROG table... at least, up to 27,000 bytes of it. If the program goes over 27,000 bytes, it then flows over into an additional row, incrementing the PROGSEQ field by one. While SQR can read Long Raw fields well enough, it can only handle around 36,000 bytes. Since PeopleSoft only stores 27,000 bytes per field, this should be no problem then, right? Well, unfortunately, when SQR reads the Long Raw, it also converts it into a hexadecimal string. So, three bytes might end up looking like "A3 23 FF" (spaces are not actually returned, but there for clarity). These three bytes become a six byte string. So that 27,000 bytes, is actually more like 54,000 bytes. Yikes! OK... so I thought that maybe I could only deal with 32,000 byte 'chunks' of the data. Something like a substring function. Then, when I was done with that subset of the data, I could just go back a second time for the rest of the data. A fine idea, but ORACLE does not provide any easy way to substring a Long Raw. LOB fields, on the other hand, had a wealth of handy functions provided by Dbms_Lob. Since I couldn't figure out a way to cast the Long Raw into a LOB on the fly, I ended up created a work table (DLP_PCODE) which contained a BLOB field. I could then copy the Long Raw into the BLOB, converting it using the to_lob function. Nice, huh? Unfortunately the substring function that was delivered with Dbms_Lob could only return 2000 bytes at a time. This turned out to be not so much an issue... it just meant more slices. So now I have my PeopleCode program divided up into 2000 byte "slices", spread across multiple rows in 27000 bytes chunks. This is all handled by the NextByte and Get-Next-Segment functions in the SQR.

I won't go into a lot of detail about how PeopleCode is stored, since a lot of it is in the PeopleCode Secrets article. Fortunately not much has changed since the article was written (sometime pre version 8). The SQR also has many comments explaining things that I figured out that were not necessarily mentioned in the article. One thing that did change since the article, is that strings are now stored as double byte characters. In addition to string literals and comments, this includes almost all non-keywords and user defined names: variables, functions, methods, classes, etc. Currently, my program does not try to handle any extended characters... it simply ignores the second byte of the character.

My SQR is by no means complete. I still don't have all of the codes for the different PeopleCode elements. Indentions are still a mess. And I don't quite have the line feeds down right. But I was eager to share it with you all. As of this writing, I am posting my March 14th, 2005 version of the program. I will probably continue playing with it from time to time. If you figure out anything that is still missing, let me know. When the program encounters something it doesn't understand, it will simply stop. This has been the easiest way for me to determine what I am missing.

Also, try entering an "I" at the prompt. The "Interactive Mode" was a recent add-on... more to play with some ideas I had than anything else.