Automated FTP from within dBASE
by John Staub, President, Staub & Associates, Inc. and Dan Wright, DCS-FL.COM

The author would like to thank Flip Young, John Fried, David L. Stone and Jean-Pierre Martel for their proofreading and assistance in preparing this article.
Why automate FTP?

One of our clients needed to ensure they had an off-site current backup of their data at the end of each workday. While the backup was no problem, storing it in an off-site location was important. As with any company whose business relies on its information management system, current off-site backups are an absolute. There are plenty of “glitches” that can cause loss of business data, and off-site backups are another tool to help prevent data disasters.

The solution was easy to implement, and added another service we could provide for our client.

What is required?

We use several routines from FUNCky, which is a set of tools available on the Web, for such tasks as sending SMTP mail, etc., from within the dBL app we built for this client. We also use a licensed, command line version of PKZIP25 for differing tasks in other applications we’ve built. Putting the two of them together was a natural marriage. Since this client’s server is running Win2K Server, we use the Windows Task Scheduler to automatically run a batch file at the end of the day to build the necessary .zip file. Similarly, we run a Win2K server, and use the Windows Task Scheduler to run our app from our server to FTP the backup file to us. While it could be argued that our normal FTP software (WS_FTP Pro) has a scripting language which could have been used, we felt more comfortable building the routine in dBL.

First the batch file

The batch file is just a simple set of instructions to copy the necessary files to a temporary folder on the client’s server, create the .zip file and then delete the data files included in the .zip.
 
 
copy "D:\Data\pcsis\*.dbf" "E:\FTP_Temp\JohnsFTP\Temp\*.*"
pkzip25 -add E:\FTP_Temp\JohnsFTP\Temp\pcsisbackup.zip E:\FTP_Temp\JohnsFTP\Temp\*.dbf
erase "E:\FTP_Temp\JohnsFTP\Temp\*.dbf"
   

Note the use of quote marks in the copy and erase lines. These are especially important if you are using “long” names in the path statement.

Now the first dBL app (connect and grab a file).

The actual dBL program is nothing more than a fairly simple set of commands with a message form so the user can monitor the progress. This first app connects to the user's server and grabs the file. For necessary security reasons, I’ve not included the actual server settings, usernames and passwords, but the code is fairly well-documented. Please note the use of FUNCky.cc and Message.wfm (the latter available from the dUFLP library).
 
 
/*
Program: pcstosaiftp.prg
Purpose: set up to connect to an FTP server and download a particular
         file to a local or networked drive
Author: John Staub for Staub & Associates, Inc.
Date: 24 November 2001
Usage: just call the prg as do pcstosaiftp.prg -
       you can also compile the program and call it
       using the Windows Task Scheduler for scheduled
       runs
*/
// set procedures to the necessary programs/files
set procedure to FUNCky.cc additive
// Open a message form then reduce it to the taskbar
set procedure to message.wfm additive
fMessage = new MessageForm()
fMessage.text = "PCSIS Automated Database Backup"
fMessage.title.text = "PCSIS Automated FTP Backup"
fMessage.message.text = "Now connecting to the server"
fMessage.Open()
// if you want the message form to remain visible,
// comment out the next line
fMessage.windowstate = 1

/// now set up the necessary FUNCky object
Local FUNCky,Ftp
FUNCky = CreateObject("FUNCky")
// create the FUNCky FTP object using server, username, password, port
// and proxy server if necessary
Ftp = FUNCky.CreateFTP("ftpserver","username","password", 21,"")
If (Ftp.Status = 0)  // connected to the server
   fMessage.message.text = "Now connected to the server"
   If (Ftp.ChangeDir("JohnsFTP\temp")) // change to the appropriate
                                       // folder on the server - use
                                       // caution to respect the paths
                                       // used on an FTP server
      fMessage.message.text = "Now in the correct folder"
         If Ftp.Get("pcsisbackup.zip","x:\pcsbackupdata\pcsisbackup.zip",0)
            fMessage.message.text = "Now downloading the file"
         endif  // Ftp.Get the file
   else  // changedir did not work
   Endif

   If (Ftp.Disconnect()) // disconnect from the server
   endif
   // now rename the backup file so you can store incremental backups
   local oldDir,OldFile
   private NewFile
   oldDir = set ("DIRECTORY")
   OldFile = "pcsisbackup.zip"
   set directory to "x:\pcsbackupdata"
   if file (OldFile)
      fMessage.message.text = "Now renaming the backup file"
      // the next line renames the file - note the conversion of the date to a string
      NewFile = "pcsisbackup" + dtos(date()) + ".zip"
      rename pcsisbackup.zip to &NewFile
   endif
   set directory to (oldDir)
else // Ftp.Status # 0 - not connected to the server
endif

fMessage.Close()
fMessage.Release()
fMessage = null
close procedure message.wfm
close procedure FUNCky.cc
if "RUNTIME" $ upper( version(1) )
   quit
endif

   

Now the second dBL app (connect and send a file)
 
 
/*
Program: ftptosai.prg
Purpose: set up and connect to an FTP server and send a particular
         file from a local or networked drive
Author: John Staub for Staub & Associates, Inc.
Date: 24 November 2001
Usage: just call the prg as do ftptosai.prg -
       you can also compile the program and call it
       using the Windows Task Scheduler for scheduled
       runs
*/
/// first, check for the existence of the previous zip file if it exists, delete it
private OldFile,NewFile,oldDir,NewDir
OldFile = "pcsisbackup.zip"
NewFile = "pcsisbackup" + dtos(date()) + ".zip"
NewDir = "E:\FTP_TEMP\JOHNSFTP\TEMP"
/// now create the new zip file
run(.f.,"backup.bat") 

oldDir = set ("DIRECTORY")
set directory to ( NewDir )
// Open a Message form then reduce it to the taskbar
set procedure to message.wfm additive
fMessage = new MessageForm()
fMessage.text = "SAI DoD Cert Automated Create & FTP"  // form text titlebar
fMessage.title.text = "SAI Automated FTP" // large text at top
fMessage.message.text = "Now starting FTP process"
// if you want the message form to remain visible,
// comment out the next line
fMessage.windowstate = 1
fMessage.Open()
// set procedures to the necessary programs/files
set procedure to FUNCky.cc additive
fMessage.message.text = "Now connecting to the server"

/// now set up the necessary FUNCky object
Local FUNCky,Ftp
FUNCky = CreateObject("FUNCky")
// create the FUNCky FTP object using server, username, password, port
// and proxy server if necessary

Ftp = FUNCky.CreateFTP("servername","username","password", 21,"")
If (Ftp.Status = 0) //connected to the server
   fMessage.message.text = "Now sending the file - Please stand by......"
   /// send the file
   If( Ftp.Put("E:\FTP_TEMP\JOHNSFTP\TEMP\pcsisbackup.zip","pcsisbackup.zip"))
   else
   endif
   // now rename the file sent to the server
   If( Ftp.Rename("pcsisbackup.zip", "pcsisbackup" + dtos(date()) + ".zip"))
   endif
   If (Ftp.Disconnect())  // disconnect from the server
   endif
   /// now erase the old file
   if file(OldFile)
      Erase (OldFile)
   endif

   set directory to ( oldDir )
else // not connected to the server
endif

fMessage.Close()
fMessage.Release()
fMessage = null
close procedure message.wfm
close procedure FUNCky.cc
return

   

These processes are fairly simple and straightforward. Although this process could have been implemented as a prg rather than as
a form, we decided to use a minimized form whose name thus appears on the taskbar. The advantage is that if someone is working on the server, they will see that a process is running. If you prefer, just comment out the line this.windowstate = 1 and the form will stay up on the screen. We do rename the file we receive as this allows us to maintain incremental backups. If you have several clients requiring this service, you could expand the program to handle movement of all backup files.

Why FTP the backup??

As a consulting, information system development, and web site development company, we try to offer a wide range of services to our clients. Some clients have large IT infrastructures with on-line, off-site backup data servers. Others cannot afford such a luxury. Either way this is another service we can offer our clients to demonstrate our dedication to meeting their IT needs. While this process can fail, we do a daily check to ensure it runs and that we have received the backup. Should a client ever experience a catastrophic incident related to their data, we can provide them with their most recent backup.


For more information on  Staub & Associates, Inc., please visit http://www.staubassociates.com
For information on hosting your dBASE web applications or other web sites, please contact us at: wrightd_dBulletin_@dcs-fl.com (take out “_dBulletin_”).
This article is Copyright(c) 2001, Staub & Associates, Inc. - All Rights Reserved