PeopleCode to access the other databases from JDBC and Java

Below code is based out of java JDBC classes and executes the SQL queries on Oracle databases, Provided that JDBC is installed and TNS entry is know,

 

&TEMP = Z_PRC_PRM_TBL.USER1.Value;

Z_PRC_PRM_TBL.USER1.Value = "";

Z_PRC_PRM_TBL.USER1.Value = &TEMP;

DoSaveNow();

 

Local JavaObject &driverManager = GetJavaClass("java.sql.DriverManager");

&driverManager.registerDriver(CreateJavaObject("oracle.jdbc.OracleDriver"));

 

Local string &QueryString;

Local Rowset &PrcsMonRS;

 

Local JavaObject &driver = CreateJavaObject("oracle.jdbc.OracleDriver");

Local JavaObject &info = CreateJavaObject("java.util.Properties");

 

&info.put("user", Z_PRC_PRM_TBL.USER1.Value);

&info.put("password", Z_PRC_PRM_TBL.PASSWD.Value);

 

Local JavaObject &conn = &driverManager.getConnection(Z_PRC_PRM_TBL.CONNECTSTRING.Value, "sysadm", "ad%sy8");

 

&QueryString1 = "SELECT R.RUNSTATUS ,SERVERNAMERUN ,R.RUNDTTM ,(CASE WHEN R.RUNSTATUS IN (16,5,4) THEN to_char(floor(((RUNDTTM-SYSDATE)*60*24/60)/24))||'d'|| to_char(MOD(floor( (RUNDTTM-SYSDATE)*60*24/60),24)) ||'h'||to_char(MOD(FLOOR((RUNDTTM-SYSDATE)*60*24),60)) ";

&QueryString2 = " ||'m' WHEN R.RUNSTATUS IN (10,3,17,1,8) THEN '0' WHEN R.RUNSTATUS IN (6,7) THEN to_char(CEIL((SYSDATE-BEGINDTTM)*60*24)) WHEN R.RUNSTATUS IN (9) THEN to_char(CEIL((ENDDTTM-BEGINDTTM)*60*24)) ELSE TO_CHAR(R.RUNDTTM,'mm-Mon-yyyy') END) AS TTE_ETA ,DECODE(R.PRCSTYPE,'Application Engine','AE','SQR Process','SQR P','SQR Report','SQR R',R.PRCSTYPE) AS  PRCSTYPE ";

&QueryString3 = " ,R.PRCSNAME,NVL(P.DESCR,J.DESCR) AS PRCS_DESCR ,R.RUNCNTLID AS RUNCNTLID ,R.RECURNAME AS RECURNAME ,R.PRCSINSTANCE AS PI ,R.RQSTDTTM AS RQSTDTTM ,SYSDATE AS CURDTTM ,ENDDTTM FROM PSPRCSRQST R LEFT OUTER JOIN PS_PRCSJOBDEFN J ON R.MAINJOBNAME = J.PRCSJOBNAME LEFT OUTER JOIN  PS_PRCSDEFN P ON R.PRCSTYPE = P.PRCSTYPE AND R.PRCSNAME = P.PRCSNAME ";

&QueryString11 = "WHERE R.OPRID LIKE ':1' AND R.RQSTDTTM BETWEEN SYSDATE-DECODE(R.RUNSTATUS,:2,4)  AND SYSDATE AND R.PRCSTYPE LIKE '%' AND R.PRCSJOBSEQ=0 AND R.RUNSTATUS IN (:3) ORDER BY 1 ASC,RUNDTTM ASC";

 

SQLExec("SELECT CONNECTSTRING, RTRIM(XMLAGG (XMLELEMENT (b, RUNSTATUS|| ','||DAYSTOCACHE || ',')).EXTRACT ('//text()'), ',') AS EXPR FROM  PS_Z_PRC_STS_TBL WHERE CONNECTSTRING = :1 AND Z_PRC_NAME = :2 AND FLAG = 'Y' GROUP BY CONNECTSTRING", Z_PRC_PRM_TBL.CONNECTSTRING.Value, Z_PRC_PRM_TBL.Z_PRC_NAME.Value, &dMY1, &StatusSting);

SQLExec("SELECT CONNECTSTRING, RTRIM(XMLAGG (XMLELEMENT (b, RUNSTATUS||',')).EXTRACT ('//text()'), ',') AS EXPR FROM  PS_Z_PRC_STS_TBL WHERE CONNECTSTRING = :1  AND Z_PRC_NAME = :2 AND FLAG = 'Y' GROUP BY CONNECTSTRING", Z_PRC_PRM_TBL.CONNECTSTRING.Value, Z_PRC_PRM_TBL.Z_PRC_NAME.Value, &dMY1, &StatusSelSting);

 

&QueryString14 = Substitute(&QueryString11, ":1", Z_PRC_PRM_TBL.OPRID.Value);

 

&QueryString13 = Substitute(&QueryString14, ":2", &StatusSting);

 

&QueryString12 = Substitute(&QueryString13, ":3", &StatusSelSting);

 

&QueryString = &QueryString1 | &QueryString2 | &QueryString3 | &QueryString12;

rem MessageBox(0, "", 0, 0, &QueryString12);

 

 

&PrcsMonRS = GetLevel0()(1).GetRowset(Scroll.DUMMY_MSG_TBL);

&PrcsMonRS.Flush();

&rowcounter = 0;

 

Local JavaObject &stmt = &conn.createStatement();

Local JavaObject &rs = &stmt.executeQuery(&QueryString);

Local number &rowIdx;

While &rs.next()

   &rowIdx = &rowIdx + 1;

   &rowcounter = &rowcounter + 1;

   &PrcsMonRS.InsertRow(&rowcounter);

   rem MessageBox(0, "", 0, 0, "Row " | &rowIdx | " column 1: " | &rs.getObject(1).toString());

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.RUNSTATUS.Value = &rs.getString("RUNSTATUS");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.SERVERNAMERUN.Value = &rs.getString("SERVERNAMERUN");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.RUNDTTM.Value = &rs.getString("RUNDTTM");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.DESCR.Value = &rs.getString("TTE_ETA");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.PRCSTYPE.Value = &rs.getString("PRCSTYPE");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.PRCSNAME.Value = &rs.getString("PRCSNAME");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.DESCR1.Value = &rs.getString("PRCS_DESCR");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.RUNCNTLID.Value = &rs.getString("RUNCNTLID");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.RECURNAME.Value = &rs.getString("RECURNAME");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.PROCESS_INSTANCE.Value = &rs.getString("PI");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.CURRENT_DTTM.Value = &rs.getString("CURDTTM");

   &PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.END_DTTM.Value = &rs.getString("ENDDTTM");

  

End-While;

If &rowcounter > 1 Then

   &PrcsMonRS.DeleteRow(&rowcounter + 1);

End-If;

&PrcsMonRS.Sort(Z_PRC_MON_TBL.RUNSTATUS, "A", Z_PRC_MON_TBL.RUNDTTM, "D");

 

&rs.close();

&stmt.close();

&conn.close();

 

 

************************************************************************************************************************************************************************************
"This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error please notify the mail.admin@birlasoft.com immediately without opening the mail and attachment . Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email
************************************************************************************************************************************************************************************