XML publisher report from online button and SQL cursor

XML All in One Desk Reference for Dummies

An Introduction to Object COBOL

Requirement:

Build and run xml publisher report from online button

Solution:

Button fieldchange event Peoplecode written

Xmlp Report uses PS Query which uses three tables joined together and builds output

One of the tables in the query is temporarily populated using the scroll and rowset properties.

Below is the whole logic.

  

import PSXP_RPTDEFNMANAGER:*;

Component string &c_transfer_sodca;

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;

Local Record &rcdQryPrompts, &rcdQryRunParms, &REC;

Local SQL &sqlSelectQryParms, &SQL;

Local string &reportid, &sDirName, &print, &ssn;

Local number &j;

SQLExec("delete from ps_nc_sodca_tmp1");

&SQL = CreateSQL("%INSERT(:1)");

&REC = CreateRecord(Record.<temp table>);

&SQL.BulkMode = True;

For &j = 1 To ActiveRowCount(Scroll.<view name>)

  

   &ssn = FetchValue(Scroll.<view name>, &j, <view name>.SSN);

   &print = FetchValue(Scroll.<view name>, &j, <view name>.NC_PRINT);

  

   rem MessageBox(0, "", 0, 0, "ssn = %1", &ssn);

   rem MessageBox(0, "", 0, 0, "Print = %1", &print);

   &REC.OPRID.Value = %OperatorId;

   &REC.SSN.Value = &ssn;

   &REC.NC_PRINT.Value = &print;

   &REC.DATETIME_STAMP.Value = %Datetime;

   &SQL.Execute(&REC);

  

   

End-For;

&SQL.Close(); /*crucial line*/

&reportid = <xmlp report name>;

/* create report definition */

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&reportid);

&oRptDefn.Get();

&oRptDefn.ProcessReport("xmlp report name", "ENG", %Date, "PDF");

&sDirName = &oRptDefn.OutDestination;

&oRptDefn.Publish("", &sDirName, "", 0);

CommitWork();

&oRptDefn.DisplayOutput();

Issues faced:

=è &SQL.Close(); line was missing in the original code and when I click on the print button, fieldchange event used to throw error that ‘think time function viewattachment() can not work while database transaction update happening’

Apparently &oRptDefn.DisplayOutput(); internally calls viewattachment() which checks if there are any open cursors present, so closing the SQL object did the trick.

It was a good learning experience. Always close the SQL cursor though they are usually harmless unless some situation like this occurs.