Wednesday, May 5, 2010

Inserting the data into 3 levels (0,1,2,3 levels) into database from flat file using Application Engine

Inserting the data into 3 levels (0,1,2,3 levels) into database from flat file using Application Engine Below is the sample code (Ive used 2 fields in all the 3 levels)

Level0 -> Organisational ID, Organisational Name
Level1 -> College ID, College Name
Level2 -> Department ID, Department Name
Level3 -> Employee ID, Employee Name, Age


Local Record &R0, &R1, &R2, &R3;

&R0 = CreateRecord(Record.A_ORG_TBL);
&R1 = CreateRecord(Record.A_COL_TBL);
&R2 = CreateRecord(Record.A_DEPT_TBL);
&R3 = CreateRecord(Record.A_EMPL_TBL);

&myfile = GetFile("C:\temp\harsha.txt", "R", %FilePath_Absolute);
&myfile1 = GetFile("C:\temp\har.txt", "W", "A", %FilePath_Absolute);
&myfile1.writeline("empty array creation");
&myarray = CreateArrayRept("", 0);

If &myfile.Isopen Then
   While &myfile.Readline(&str);
      &myfile1.WRITELINE("FILE OPENED");
      &myfile1.WRITELINE("string value is " | &str);
      &myarray = Split(&str, ",");
    
      SQLExec("SELECT A_ORGID FROM PS_A_ORG_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID);
      &myfile1.WRITELINE("array value of orgid is " | &ORGID);
      &myfile1.WRITELINE(" A_ORGID VALUE IS " | &ORGID);
    
    
      SQLExec("SELECT A_ORGID,A_COLID FROM PS_A_COL_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID1, &COLID1);
      SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID FROM PS_A_DEPT_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID2, &COLID2, &DEPTID);
      SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID FROM PS_A_DEPT_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID2, &COLID2, &DEPTID);
      SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID,A_EMPLID FROM PS_A_EMPL_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID3, &COLID3, &DEPTID3, &EID3);
    
      If &myarray [1] = &ORGID Then
         &R1.A_ORGID.Value = &myarray [1];
         &R1.A_COLID.Value = &myarray [2];
         &R1.A_COLNAME.Value = &myarray [3];
         &R1.Insert();
         &myfile1.WRITELINE("values inserted at level 1 ");
         rem End-If;
         rem SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID FROM PS_A_DEPT_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID2, &COLID2, &DEPTID);
      Else
         &R0.A_ORGID.Value = &myarray [1];
         &R0.A_ORGNAME.Value = &myarray [2];
         &R0.Insert();
         &myfile1.WRITELINE("values inserted at level 0 ");
      End-If;
    
      If &myarray [1] = &ORGID1 And
            &myarray [2] = &COLID1 Then
       
         &R2.A_ORGID.Value = &myarray [1];
         &R2.A_COLID.Value = &myarray [2];
         &R2.A_DEPTID.Value = &myarray [3];
         &R2.A_DEPTNAME.Value = &myarray [4];
         &R2.Insert();
         &myfile1.WRITELINE("values inserted at level 2 ");
       
       
      End-If;
      If &myarray [1] = &ORGID2 And
            &myarray [2] = &COLID2 And
            &myarray [3] = &DEPTID Then
         &R3.A_ORGID.Value = &myarray [1];
         &R3.A_COLID.Value = &myarray [2];
         &R3.A_DEPTID.Value = &myarray [3];
         &R3.A_EMPLID.Value = &myarray [4];
         &R3.A_EMPNAME.Value = &myarray [5];
         &R3.A_EMPLAGE.Value = &myarray [6];
         &R3.Insert();
         &myfile1.WRITELINE("values inserted at level 3 ");
       
      End-If;
    
    
   End-While;
 
   &myfile.Close();
   &myfile1.Close();
End-If;

No comments:

Post a Comment