Inserting files in Database record from a folder in appserver..

Requirement:

This is a part of data migration requirement. There is a folder in appserver which contains all CVs of applicants. These have to be inserted into database tables which store applicants' resumae. This should be a job like an App Engine.

Note: As per the specifications, one CV per applicant is given by the client.



Technical Design:

1) Initailly identify the tables which store appl CVs -- HRS_APP_RES

2) Create an AE with one section having a PeopleCode.

3) The folder path on appserver has to be identified.

4) The design needs two things --

• First loop through the Folder containing CVs


• Insert each file into the table

5) Here the CV files naming convention that is suggested as ApplicantID.doc. This makes the job easy in mapping the CVs against the applicants.

6) The peoplecode in the peoplecode event is as follows. This is very much reusable.

Local array of string &FNAMES;

Local File &MYFILE;

Local string &CurrFile;

Local Record &REC_RES_UPLD = CreateRecord(Record.ICI_RES_UPLD);

Local Record &REC_HRS_RES = CreateRecord(Record.HRS_APP_RES);
&pwd = "file://xyz.xyz.com/PQRS/ABCD/appserv/ASDFDEV/files/CV/";

&FNAMES = FindFiles(&pwd "*.doc", %FilePath_Absolute);

If &FNAMES.Len = 0 Then

/* No files in this directory - this must be a file instead */

MessageBox(0, "", 0, 0, "NO FILES IN THE DIRECTORY");

Else

&NEXT_RESUME_ID = GetNextNumberWithGapsCommit(HRS_APP_RES.HRS_RESUME_ID, 2147483647, 1);

&FILEEXTENSION = ".doc";

While &FNAMES.Len > 0

&CurrFile = &FNAMES.Shift();

&POS1 = Find("CV\", &CurrFile) + 3;

rem &POS2 = Find(".doc", &CurrFile) + 3;

&POS2 = Find(".doc", &CurrFile);

rem &applicant_id = substring(&CurrFile,&POS1,&POS2-&POS1+1);

&applicant_id = Substring(&CurrFile, &POS1, &POS2 - &POS1);

MessageBox(0, "", 0, 0, "EACH FILE NAME = " &CurrFile);

MessageBox(0, "", 0, 0, "EACH APPLICANT ID = " &applicant_id); &ATTACHSYSFILENAME = &applicant_id ".doc";

&ATTACHUSERFILE = &ATTACHSYSFILENAME;

&RETCODE = PutAttachment("record://ICI_RES_DOC_STR", &ATTACHSYSFILENAME, &CurrFile);

If &RETCODE = 0 Then

MessageBox(0, "", 0, 0, "File " &ATTACHSYSFILENAME " is inserted successfully.");

Else

MessageBox(0, "", 0, 0, "File " &ATTACHSYSFILENAME " is NOT inserted.");

End-If;

&REC_RES_UPLD.HRS_PERSON_ID.Value = &applicant_id; &REC_RES_UPLD.HRS_RESUME_ID.Value = &NEXT_RESUME_ID; &REC_RES_UPLD.ATTACHSYSFILENAME.Value = &ATTACHSYSFILENAME; &REC_RES_UPLD.ATTACHUSERFILE.Value = &ATTACHUSERFILE; &REC_RES_UPLD.Insert();

&REC_HRS_RES.HRS_PERSON_ID.Value = &applicant_id; &REC_HRS_RES.HRS_RESUME_ID.Value = &NEXT_RESUME_ID; &REC_HRS_RES.HRS_RESUME_TITLE.Value = "TEST"; &REC_HRS_RES.LANG_CD.Value = "ENG"; &REC_HRS_RES.ATTACHSYSFILENAME.Value = &ATTACHSYSFILENAME; &REC_HRS_RES.ATTACHUSERFILE.Value = &ATTACHUSERFILE; &REC_HRS_RES.HRS_ROW_ADD_DTTM.Value = %Datetime; &REC_HRS_RES.HRS_ROW_ADD_OPRID.Value = "PS"; &REC_HRS_RES.HRS_ROW_UPD_DTTM.Value = %Datetime; &REC_HRS_RES.HRS_ROW_UPD_OPRID.Value = "PS";

&REC_HRS_RES.Insert();

&NEXT_RESUME_ID = &NEXT_RESUME_ID + 1;

rem SQLExec("COMMIT");

End-While;

End-If;