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.
Posts
