Automatically Generate Upgrade Scripts

Snippet of PeopleCode that takes a Project and creates an upgrade script for all the Records in that Project. Does 90%+ of the work for you.

Requires a Prompt to select the Project, a button to put this code under, and a HTML Area to output the results to.

If None(NS_MN_TEST.PROJECTNAME.Value) Then
   MessageBox(0, "", 0, 0, "Enter a Project Name");
Else
   
   Local string &title, &result, &fields;
   Local integer &f;
   
   Local Record &recPSPROJECTDEFN = CreateRecord(Record.PSPROJECTDEFN);
   &recPSPROJECTDEFN.PROJECTNAME.Value = NS_MN_TEST.PROJECTNAME.Value;
   &recPSPROJECTDEFN.SelectByKey();
   
   Local Record &recPSOPRDEFN = CreateRecord(Record.PSOPRDEFN);
   &recPSOPRDEFN.OPRID.Value = &recPSPROJECTDEFN.LASTUPDOPRID.Value;
   &recPSOPRDEFN.SelectByKey();
   
   &title = &title | "/*" | Char(13);
   &title = &title | "     WORK UNIT:     " | &recPSPROJECTDEFN.PROJECTNAME.Value | Char(13);
   &title = &title | "     DEVELOPER:     " | &recPSOPRDEFN.OPRDEFNDESC.Value | Char(13);
   &title = &title | "     DATE:          " | String(%Date) | Char(13);
   &title = &title | "     DESCRIPTION:   " | &recPSPROJECTDEFN.PROJECTDESCR.Value | Char(13);
   &title = &title | "     INSTRUCTIONS:  EXECUTE IN THE TARGET" | Char(13);
   &title = &title | "     DEPENDENCIES:  " | Char(13);
   &title = &title | "*/" | Char(13) | Char(13);
   
   &title = &title | "-- List of Records that have data converted by this script:" | Char(13);
   
   
   Local SQL &sql = CreateSQL("SELECT OBJECTVALUE1 FROM PSPROJECTITEM A, PSRECDEFN B WHERE A.PROJECTNAME = :1 AND A.OBJECTTYPE = 0 AND A.OBJECTID2 = 0 AND B.RECNAME = A.OBJECTVALUE1 AND B.RECTYPE = 0", NS_MN_TEST.PROJECTNAME.Value);
   
   Local string &recName;
   While &sql.Fetch(&recName)
      
      If Left(&recName, 3) = "NS_" Then
         
         &title = &title | "--    " | &recName | Char(13);
         
         &result = &result | Char(13) | "-- " | &recName | Char(13);
         &result = &result | "Delete From ps_" | &recName | ";" | Char(13);
         Local Record &rec = CreateRecord(@("record." | &recName));
         &result = &result | "Insert Into ps_" | &recName | "(" | Char(13);
         For &f = 1 To &rec.FieldCount
            If &f = 1 Then
               &fields = "  " | &rec.GetField(&f).Name | Char(13);
            Else
               &fields = &fields | ", " | &rec.GetField(&f).Name | Char(13);
            End-If;
         End-For;
         &result = &result | &fields | ")" | Char(13);
         &result = &result | "Select " | Char(13) | &fields | "From ps_" | &recName | "@nsconv.unsw.edu.au;" | Char(13);
         
      End-If;
      
   End-While;
   
   &title = &title | Char(13) | "SPOOL ON;" | Char(13);
   &title = &title | "SET ECHO ON;" | Char(13);
   &title = &title | "SPOOL 'c:\temp\" | Substring(&recPSPROJECTDEFN.PROJECTNAME.Value, 4, 7) | "_data_conversion.log' REPLACE;" | Char(13);
   
   
   NS_MN_TEST.HTMLAREA1.Value = &title | Char(13) | Char(13) | &result | Char(13) | Char(13) | "commit;" | Char(13) | Char(13) | "SPOOL OFF;";
   
End-If;
Paste your text here.