Example for File Transfer using JadeJob

h1. Example: File transfer using JadeJob

The following scenario is given:

Job Scheduler Reads a status from an Oracle Database table where we store triggers (every 5 minutes) (for demo treat oracle table as though there is only one row of data)

If the Oracle Table.Statush1. “GO” then

• Read Additional FTP Job Attributes from an Oracle Table (i.e. $SourceFilePath, %SourceFileName, $TargetFilePath, $TargetFileName, $EncryptedYN)
• Store FTP Attributes as Memory Variables for later use
• Call JADE to SFTP files using Memory Variables
• If $SourceFile does not exist send email indicating the error with the file properties (source file name, source file size, source file date/time created, date/time of failure, target file name, target directory, failure detail)
• If $SourceFile does exist
  • If $EncryptedYN Y
    • run through Encryption Routine
  • Perform SFTP to target
  • Confirm that File has been successfully received at destination
  • If File has not been successfully received at destination
    • Send email indicating the error that occurred with file properties (source file name, source file size, source file date/time created, date/time of failure, target file name, target directory, failure detail)
  • If File has been successfully received
    • send a confirmation email with file properties (source file name, source file size, source file date/time created, date/time of failure, target file name, target director)
• Log All transport activities
• Produce PDF report of all transport activities between two dates/times

Components of the Solution

Access to Oracle™ RDBMS DB using pl/sql (checkDB)

For the connection to the Oracle™ RDBMSDB we use the pl/sqlscriptplsql_script.txt which is executed by the JAVA-AdapterClass sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass .

The pl/sql script is defined as

  declare
	status 			varchar2(64);
	sourcefilepath 	varchar2(2048);
	sourcefilename 	varchar2(2048);
	targetfilepath 	varchar2(2048);
	targetfilename 	varchar2(2048);
	encryptedyn 	varchar2(16);

  begin 
	select STATUS, SOURCEFILEPATH, SOURCEFILENAME, TARGETFILEPATH, TARGETFILENAME, ENCRYPTEDYN
	into   status, sourcefilepath, sourcefilename, targetfilepath, targetfilename, encryptedyn
	from scenario1;
	
	dbms_output.put_line('status=' || status);
	dbms_output.put_line('sourcefilepath=' || sourcefilepath);
	dbms_output.put_line('sourcefilename=' || sourcefilename);
	dbms_output.put_line('targetfilepath=' || targetfilepath);
	dbms_output.put_line('targetfilename=' || targetfilename);
	dbms_output.put_line('encryptedyn=' || encryptedyn);
  end;

The parameters used by the adapter class are

• name of the pl/sql script and where is is found
• name and access parameters for the DB

For the interpretation of the table status "GO" a postprocessing routine is used (here programmed in javax.script:rhino)

function spooler_process_after(spooler_process_result)
\{
  var params = spooler_task.order().params();
  var status = params.value("status");
					
  if ( status == "GO" )
	\{
	spooler_log.info("Table status is \"GO\", continue!");
	\}
  else
	\{
	spooler_log.info("Waiting until table status is \"GO\" ...");
	spooler_task.order().set_state("WAITING");
	\}

  return spooler_process_result;
\}

Graphical Representation of the Solution