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)
- If $EncryptedYN Y
- 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; \}