Connected Query Classes Examples
This section includes examples of connected query classes in various modes.
In scheduled mode, the connected query is run by Process Scheduler. In the case of RunMode_Sched_File, the output data is written to an XML file. In the case of RunMode_Sched_Web, the output data is accessible via a hyperlink in Process Monitor. In scheduled mode, the runProcessInfo parameter is required; the Prompts parameter should be set to Null.
The following example represents a PeopleCode step to be run via Application Engine:
import PT_CONQRS:CONQRSMGR;
import PT_CONQRS:SCHED_INFO;
Local PT_CONQRS:CONQRSMGR &cConQrsInst;
Local boolean &result;
Local string &PRCSFILENAME;
Local string &sPrcsName = "PSCONQRS";
Local string &sPrcsType = "Application Engine";
Local number &nOrigPSMessagesMode = %Session.PSMessagesMode;
%Session.PSMessagesMode = 1;
/* While working with connected queries, it is recommended to use a try- */
/* catch block */
try
/* Create and open an object. System tries to find a connected query */
/* with private ownership first for current user. If not found, it */
/* uses a public ownership */
&ConQrsInst = create PT_CONQRS:CONQRSMGR("", PSCONQRS_AET.CONQRSNAME);
&result = &ConQrsInst.Open(&ConQrsInst.Const.InitExisting);
&str = &ConQrsInst.ErrString;
If &str <> "" Then
WriteToLog(%ApplicationLogFence_Error, &ConQrsInst.ErrString);
%Session.PSMessagesMode = &nOrigPSMessagesMode;
Exit (1);
End-If;
If &result Then
/* Get an empty SCHEDINFO structure and populate it. */
&schedInfo = &ConQrsInst.SchedInfo;
&schedInfo.DIRLOCATION = %FilePath;
&schedInfo.OUTDESTTYPE = String(%OutDestType);
&schedInfo.RUN_CNTL_ID = PSCONQRS_AET.RUN_CNTL_ID;
&schedInfo.PROCESS_INSTANCE = PSCONQRS_AET.PROCESS_INSTANCE;
&schedInfo.OPRID = PSCONQRS_AET.OPRID;
If %OutDestType = 2 Then /* FILE */
Local string &sqlFile = "SELECT OUTDEST FROM PS_PRCSRUNCNTLDTL WHERE OPRID=:1 and RUNCNTLID =:2 and PRCSTYPE =:3 and PRCSNAME =:4";
SQLExec(&sqlFile, PSCONQRS_AET.OPRID, PSCONQRS_AET.RUN_CNTL_ID, &sPrcsType, &sPrcsName, &PRCSFILENAME);
End-If;
&schedInfo.PRCSFILENAME = &PRCSFILENAME;
/* Indicate the source of the scheduled request as CQR. */
&schedInfo.AE_ID = &ConQrsInst.Const.SchedRequest_CQR;
/* Schedule the query to run. Note that for scheduled connected */
/* queries, the Prompts parameter is Null. */
&result = &ConQrsInst.Run( Null, &schedInfo);
End-If;
If &result Then
%Session.PSMessagesMode = &nOrigPSMessagesMode;
Exit (0);
Else
/* Check for errors */
&str = &ConQrsInst.ErrString;
If &str <> "" Then
WriteToLog(%ApplicationLogFence_Error, &ConQrsInst.ErrString);
End-If;
/* check session message for errors */
If %Session.PSmessages.Count > 0 Then
&PSMessages = %Session.PSmessages;
For &i = 1 To &PSMessages.Count
If (&PSMessages.Item(&i).MessageType <= 1) Then
&MsgSetNbr = &PSMessages.Item(&i).MessageSetNumber;
&MsgNbr = &PSMessages.Item(&i).MessageNumber;
WriteToLog(%ApplicationLogFence_Error, MsgGet(&MsgSetNbr, &MsgNbr, "Message Not Found : " | &MsgSetNbr | "," | &MsgNbr));
End-If;
End-For;
End-If;
End-If;
%Session.PSMessagesMode = &nOrigPSMessagesMode;
catch Exception &Err
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());
end-try;
In preview mode, the connected query is run immediately on the application sever. The output data is displayed in a separate browser window. In preview mode, the Prompts parameter is required; the runProcessInfo parameter must be set to Null. The Prompts parameter should be retrieved using the QueriesPromptsArray property. If this array length is greater than 0, this array must be populated.
Similar to preview mode, running a connected query in background mode results in the output data being displayed in a separate browser window. However, in background mode, the connected query is run through Process Scheduler with a REN server. Running a connected query in this manner prevents blocking on the application server. While the report file is generated, the user can continue working with the PeopleSoft application.
In the following example, assume that an application calls the RunConnectedQuery function with all required parameters. The function returns an error string if any error occurs.
import PT_CONQRS:CONQRSMGR;
import PT_CONQRS:QUERYITEMPROMPT;
Function ShowException(&errString As Exception);
/* Perform exception processing */
End-Function;
Function RunConnectedQuery(&sOprId As string, &sCONQRSNAME As string, &RunMode As number) Returns string
Local boolean &result;
Local array of PT_CONQRS:QUERYITEMPROMPT &aPrompts;
Local string &str;
Local number &processID;
Local boolean &IsChangeDB, &IsNewCnt;
Local PT_CONQRS:CONQRSMGR &cConQrsInst;
/* While working with connected queries, it is recommended to use a try- */
/* catch block */
try
/* Create and open an object. System tries to find a connected query */
/* with private ownership first for current user. If not found, it */
/* uses a public ownership */
&cConQrsInst = create PT_CONQRS:CONQRSMGR("", &sCONQRSNAME);
&result = &cConQrsInst.Open(&cConQrsInst.Const.InitExisting);
/* Check for errors */
&str = &cConQrsInst.ErrString;
If &str <> "" Then
Error &cConQrsInst.ErrString;
End-If;
/* Validate the previously opened connected query object */
If Not &cConQrsInst.Validate() Then
&str = &cConQrsInst.ErrString;
If &str <> "" Then
Error &cConQrsInst.ErrString;
End-If;
End-If;
/* Preview Mode- AppServer execution */
/* Populate prompts. NOTE: It is required for 'interactive' execution */
/* using an application server. In the case of scheduled execution, */
/* prompts are read from the database */
If &RunMode = &cConQrsInst.Const.RunMode_Prev Then
&aPrompts = FillQueriesPrompts();
If None(&aPrompts) Then
/* User hit Cancel on a Prompt dialog */
&cConQrsInst.ResetQueriesPrompt();
Return &cErrIndicator;
End-If;
/* Restrict number of rows being displayed for each member query. */
/* If user does not set this value, the default number is used */
If All(PSCONQRS_WRK.QRY_MAX_FETCH) Then
&cConQrsInst.MaxRowsPerQuery = &numRows;
Else
&cConQrsInst.MaxRowsPerQuery = &cConQrsInst.Const.RunMode_Prev_DefRowNumber;
End-If;
/* Run the connected query. NOTE: The runProcessInfo parameter is Null */
&result = &cConQrsInst.Run(&aPrompts, Null);
End-If; /* end of Mode selection */
/* ‘Run to Window’ mode */
If &RunMode = &cConQrsInst.Const.RunMode_Window Then
&IsChangeDB = True;
&IsNewCnt = False;
/* You are required to set run control parameters for immediate */
/* execution using Process Scheduler */
&result = &cConQrsInst.SetRunControlData(&cConQrsInst.Const.RunCntlId_Auto, &IsNewCnt, &IsChangeDB);
If &result Then
&processID = &cConQrsInst.RunToWindow();
If All(&processID) Then
&result = True;;
End-If;
End-If;
End-If; /* end of Mode selection */
/* Check for errors */
&str = &cConQrsInst.ErrString;
If &str <> "" Then
Error &cConQrsInst.ErrString;
End-If;
Return &str;
catch Exception &Err
ShowException(&Err);
end-try;
End-Function;
This example demonstrates usage of the connected query ObjectRowset property. The program opens an instance from an existing connected query definition. The program retrieves the rowset from the ObjectRowset property, and changes some of its properties. It saves the rowset back to ObjectRowset property, and then saves the modified connected query definition back to the database.
import PT_CONQRS:CONQRSMGR;
Local PT_CONQRS:CONQRSMGR &cConQrsInst;
Local string &sOprId, &sCONQRSNAME, &str, &sQryParentName, &sQryChildName, &sFldNamePar, &sFldNameChild;
Local Rowset &rsObjectRowset, &rsObjectFields;
Local Record &rMapObjRec, &rFldObjRec;
Local number &i, &j, &seqNum, &fldNum;
Function ShowException(&errString As Exception);
End-Function;
/* While working with connected queries, it is recommended to use a try- */
/* catch block */
try
/* Create and open an object. System tries to find a connected query */
/* with private ownership first for current user. If not found, it */
/* uses a public ownership */
&sCONQRSNAME = "MyConQuery";
&cConQrsInst = create PT_CONQRS:CONQRSMGR("", &sCONQRSNAME);
&res = &cConQrsInst.Open(&cConQrsInst.Const.InitExisting);
&str = &cConQrsInst.ErrString;
If &str <> "" Then
Error &cConQrsInst.ErrString;
End-If;
/* Validate the previously opened connected query object */
If Not &cConQrsInst.Validate() Then
&str = &cConQrsInst.ErrString;
If &str <> "" Then
Error &cConQrsInst.ErrString;
End-If;
End-If;
/* Rowset retrieved from a connected query has the following */
/* structure: ObjectRowset = CreateRowset(Record.PSCONQRSMAPVW, */
/* CreateRowset(Record.PSCONQRSFLDRLVW)); */
&rsObjectRowset = &cConQrsInst.ObjectRowset;
/* The rowset contains the internal connected query structure. The */
/* user can iterate the rowset, retrieve and modify member query */
/* names, list the related fields, list object properties, etc. */
For &i = 1 To &rsObjectRowset.ActiveRowCount
&rMapObjRec = &rsObjectRowset(&i).PSCONQRSMAPVW;
&sQryParentName = &rMapRec.GetField(Field.QRYNAMEPARENT).Value;
&sQryChildName = &rMapRec.GetField(Field.QRYNAMECHILD).Value;
&seqNum = &rsObjectRowset(&i).PSCONQRSMAPVW.SEQNUM.Value;
&rsObjectFields = &rsObjectRowset(&i).GetRowset(Scroll.PSCONQRSFLDRLVW);
For &j = 1 To &rsObjectFields.ActiveRowCount
&rFldObjRec = &rsObjectFields(&j).PSCONQRSFLDRLVW;
&fldNum = &rFldObjRec.SELCOUNT.Value;
&sFldNamePar = &rFldObjRec.QRYFLDNAMEPAR.Value;
&sFldNameChild = &rFldObjRec.QRYFLDNAMECHILD.Value;;
End-For;
End-For;
/* The preceding for loop shows how to navigate a connected query's */
/* internal structure. It allows a report developer to change a */
/* connected query definition by modifying existing query members or */
/* by adding new ones -- queries or related fields. */
/* Need to set the connected query's rowset to the retrieved rowset */
/* after any changes are made to the retrieved rowset. */
&cConQrsInst.ObjectRowset = &rsObjectRowset;
&Errstr = &cConQrsInst.ErrString;
If &Errstr <> "" Then
Error &cConQrsInst.ErrString;
End-If;
/* Set the connected query properties. Set the object's status to */
/* active, so it will be available for reporting use */
&cConQrsInst.Description = "My Connected Query";
&cConQrsInst.Details = "Includes QryA, QryA1, and QryC";
&cConQrsInst.Status = "A";
/* Save the connected query instance */
If Not &cConQrsInst.Save() Then
If &cConQrsInst.ErrString <> "" Then
Error &cConQrsInst.ErrString;
End-If;
End-If;
catch Exception &Err
ShowException(&Err);
end-try;