Any Interest in Program?

digitaleagle Meta-SQL, SQL, Uncategorized 2 Comments

I have been tinkering around with a little Java application I started for running queries against a PeopleSoft database.  I was just curious if there was any interest in such an application.  Here is a screenshot of what I have so far:screenshot

So far the program connects to SQL Server databases and Apache Databases.  It runs queries and loads the data into the JTable at the bottom.

I have a SQL “preprocessor” that evaluates the SQL right before it passes it to the database.  Currently, the only thing it does is to pull the semi-colon off of the end.  But, I plan to have it resolve meta-sql such as %Table() and %CurrentDateIn, etc.

Also, I would like to build SQL tools in that understand the SQL syntax.  I would like tools that automatically generate effective date criteria.  Another feature would be nice to have where it would join a table into a SQL statement.

Agile Development Links

digitaleagle Great Links, Uncategorized Leave a comment

MyOraclePortal published a couple of links to interesting videos.  Check out the page there:

Agile Development

I thought the videos were very well done.

What I am curious about is anyone’s experience in using Agile Development with PeopleSoft.  Has anyone tried to use this development style on a PeopleSoft project?

I was involved in a project that attempted something similar.  It was not successful, but I don’t believe it was because of the agile development.  A number of things could be blamed such as a lack of automated testing, limited involvement during the end-user testing, too long of a duration between iterations, and users requesting more of PeopleSoft than it was designed to do.  Also, the company was bought out by a larger company before the project was completed and I think they wanted to go another direction.

COBOL Compile Error

digitaleagle COBOL, Uncategorized 4 Comments

In an attempt to draw more attention to an error, let me copy a comment here.  If you have any ideas on what ps_rookie should do, please comment here.

I suggested taking the dash out of the compile directory, and he said that did not make a difference.

Below is the comment:

Hi DE,

I am trying to Install COBOL for my HCM 9.0 Installation. I have downloaded and installaed the Net Express Cobol Comipler and now I am doing the exact same steps as you have discussed above. I am getting the Object Compiling Error. Pasted below.

ASCII Cobol Compilations Proceeding
Creating Directory C:\TEMP\COBOL-COMBILE
Logging progress to file C:\TEMP\COBOL-COMBILE\CBLBLD.LOG
COBOL compiler found in “C:\Program Files\Micro Focus\Net Express 5.1\Base”
Target directory (C:\PS_HOME\CBLBINA) exists and is writable

Copying source files …

Building the COBOL targets …

File(s) that failed to compile or link:
.
Compile error for AVPACCTL.obj
Compile error for AVPCAE.obj
Compile error for AVPGCLUB.obj
Compile error for CCPMNTPD.obj
Compile error for EGPCSRVC.obj
Compile error for EGPUDTGN.obj
Compile error for FAP00SLD.obj
Compile error for FAPAUTHP.obj
Compile error for FAPBUDMV.obj
Compile error for FAPCLICR.obj
Compile error for FAPCLIOR.obj
Compile error for FAPCLOCX.obj
Compile error for FAPCLOOR.obj
Compile error for FAPCMPR1.obj
Compile error for FAPCNAS9.obj
Compile error for FAPCOR00.obj
Compile error for FAPDISBP.obj
Compile error for FAPDLINB.obj
Compile error for FAPDLOCH.obj
Compile error for FAPDLODB.obj
Compile error for FAPDLOOR.obj
Compile error for FAPEQPAR.obj
Compile error for FAPEQRUN.obj
Compile error for FAPINAS5.obj
Compile error for FAPINAS6.obj
Compile error for FAPINAS7.obj
Compile error for FAPINSS2.obj
Compile error for FAPLBOG1.obj
Compile error for FAPLCAD1.obj
Compile error for FAPLCIA4.obj
Compile error for FAPLCOA4.obj
Compile error for FAPLCOX4.obj
Compile error for FAPLDAD1.obj
Compile error for FAPLDAD2.obj
Compile error for FAPLDOA4.obj
Compile error for FAPLDPN2.obj
Compile error for FAPLORG0.obj
Compile error for FAPPIDA0.obj
Compile error for FAPPKASN.obj
Compile error for FAPPKCTL.obj
Compile error for FAPPKENG.obj
Compile error for FAPPKPST.obj
Compile error for FAPPLBL0.obj
Compile error for FAPPLINB.obj
Compile error for FAPPLODB.obj
Compile error for FAPPLOOR.obj
Compile error for FAPSAR00.obj
Compile error for FAPTALRT.obj
Compile error for FAPTRMU1.obj
Compile error for FAPUPTGT.obj
Compile error for FGPACCRL.obj
Compile error for GPPDPDM5.obj
Compile error for GPPSERVC.obj
Compile error for GPPUERND.obj
Compile error for GPPUSYPN.obj
Compile error for GPPUTAKE.obj
Compile error for PAPCABAL.obj
Compile error for PAPCAERN.obj
Compile error for PAPCOPTF.obj
Compile error for PAPCRCTL.obj
Compile error for PAPCRSQL.obj
Compile error for PAPCSRVC.obj
Compile error for PAPCSSEC.obj
Compile error for PAPPCERN.obj
Compile error for PAPPCHRS.obj
Compile error for PAPPEXPT.obj
Compile error for PAPPPYMT.obj
Compile error for PAPPRSQL.obj
Compile error for PAPUALTL.obj
Compile error for PAPUDTGN.obj
Compile error for PAPUPROJ.obj
Compile error for PIPCMPAR.obj
Compile error for PIPMPORT.obj
Compile error for PIPSPRCS.obj
Compile error for PIPUTLTY.obj
Compile error for PSPBAELG.obj
Compile error for PSPBAELT.obj
Compile error for PSPBAENR.obj
Compile error for PSPBAOPT.obj
Compile error for PSPBASCH.obj
Compile error for PSPBATBL.obj
Compile error for PSPCNTAX.obj
Compile error for PSPCOBRA.obj
Compile error for PSPCPCAL.obj
Compile error for PSPEARRY.obj
Compile error for PSPECALC.obj
Compile error for PSPENANN.obj
Compile error for PSPENNHT.obj
Compile error for PSPFCEXT.obj
Compile error for PSPGCALC.obj
Compile error for PSPGPROR.obj
Compile error for PSPLDTL2.obj
Compile error for PSPMFLSA.obj
Compile error for PSPPARRY.obj
Compile error for PSPPFDED.obj
Compile error for PSPPFDST.obj
Compile error for PSPPFUND.obj
Compile error for PSPPTFND.obj
Compile error for PSPPYNET.obj
Compile error for PSPRPERY.obj
Compile error for PSPTCALC.obj
Compile error for PSPUSTAX.obj
Compile error for PTPSQLGS.obj
Compile error for SFP1098P.obj
Compile error for SFPAPLPY.obj
Compile error for SFPAPPFE.obj
Compile error for SFPAYROG.obj
Compile error for SFPBICOR.obj
Compile error for SFPBILGE.obj
Compile error for SFPCHECS.obj
Compile error for SFPCLCAN.obj
Compile error for SFPCRHST.obj
Compile error for SFPFEADJ.obj
Compile error for SFPFECAL.obj
Compile error for SFPGLINT.obj
Compile error for SFPLATFE.obj
Compile error for SFPLNADJ.obj
Compile error for SFPORGPY.obj
Compile error for SFPORGSH.obj
Compile error for SFPQKORG.obj
Compile error for SFPQKPST.obj
Compile error for SFPQPOST.obj
Compile error for SFPREFND.obj
Compile error for SFPSTDNT.obj
Compile error for SFPTPRCL.obj
Compile error for SRPCAGRP.obj
Compile error for SRPCAPRG.obj
Compile error for SRPCARPE.obj
Compile error for SRPCARQ1.obj
Compile error for SRPCARQL.obj
Compile error for SRPCARQM.obj
Compile error for SRPCAXR2.obj
Compile error for SRPCBART.obj
Compile error for SRPCCARS.obj
Compile error for SRPCCART.obj
Compile error for SRPCCONS.obj
Compile error for SRPCENVB.obj
Compile error for SRPCENVR.obj
Compile error for SRPCEPRD.obj
Compile error for SRPCEPST.obj
Compile error for SRPCERPT.obj
Compile error for SRPCTEXT.obj
The error list is in C:\TEMP\COBOL-COMBILE\ERROR.LIS
.
Aborting this process !!! EXEs/GNTs/INTs are not going to be copied/replaced…
.
Please fix the errors and rerun this batch file !!!!!!!
.

C:\Temp\COBOL-COMBILE>

When I check the Error.lis file, I find the same entries there.

Could you please inform, as to why am I getting this error?

PeopleSoft Home Subdirectories

digitaleagle PeopleTools Reference, Server Administration, Uncategorized 9 Comments

Lee requested a list of directories in a default PeopleSoft install.  Here is my two cents, and please add comments to correct or add anything I miss.  Keep in mind that this is just off the top of my head — I don’t know of any references for this information.

  • appserv — home to the Application Server and Process Scheduler Server; location of psadmin where you start, stop, and manage those servers
  • bin — location of all the binary programs such as Application Designer (pside.exe), Data Mover (psdmtx.exe), and so on; also location of SQR (sqrw.exe)
  • cblbina — location of compiled COBOL programs
  • class — location of Java packages
  • crw — location of Crystal Reports that will be run from online
  • data — location of Data Mover Data files that have been or will be loaded into the database
  • doc — location of Word documents used with the Process Scheduler
  • excel — location of Execl documents such as the ExcelToCI utility
  • maint — data mover scripts and such; typically associated with tax updates or bundles
  • nvision — NVision reports
  • PSEMAgent — home of the Enterprise Management Agent, which is the program that crawls the drive and reports installation information to the EMHub and also copies files to and from the home directory with the Hub
  • PSEMViewer — home of a utility application for working with teh EMHub
  • scripts — various data mover scripts typically associatedwith tax updates and bundles
  • sdk — Software Developement Kits for writing external applications that interface with PeopleSoft such as Java and VB kits for accessing Component Interfaces
  • sendmaster — an application for use with testing Integration Broker messaging
  • setup — location of various installers such as the PIA installer and the Database installer that builds the PeopleSoft database in your RDBMS system
  • sqr — location of the SQR reports
  • src — location of various source code; COBOL source code is the most prominent
  • tuxedo — home of the tuxedo application used by Application Designer, the Application Server, etc
  • verity — home of the Verity Search Engine used by PeopleBooks and the Portal Menu search
  • webserv — home of the PIA, which is the online web application that talks with the Application Server and sends the HTML to the browser for the application
  • winword — location of Word documents used with Process Scheduler

JDBC and SQL Server

digitaleagle Java, Uncategorized 2 Comments

Well, I have explored JDBC connections to Access in the past, but here is a connection to SQL Server via ODBC.

A driver for SQL Server exists, but using that driver requires adding a Jar file to the server.  Assuming the App Server or Batch Server is running on Windows, you have to have an ODBC connection setup already.

Here is the code; hope it helps:

Local SQL &sql;
 Local string &loadCommand;

/* Using JDBC */

/* Load the driver */
GetJavaClass("java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
Local string &connectionUrl = "jdbc:odbc:" | %DbName | ";
user="MY_ADMIN";
password="MY_PASSWORD";
Local JavaObject &conn = GetJavaClass("java.sql.DriverManager").getConnection(&connectionUrl);
Local JavaObject &connSource = GetJavaClass("java.sql.DriverManager").getConnection(&connectionUrl);
try
   &conn.setCatalog("MY_DB");
catch Exception &changeCatalogWarning
end-try;
Local JavaObject &stTarget = &conn.createStatement(GetJavaClass("java.sql.ResultSet").TYPE_SCROLL_INSENSITIVE, GetJavaClass("java.sql.ResultSet").CONCUR_UPDATABLE);
Local JavaObject &stSource = &connSource.createStatement(GetJavaClass("java.sql.ResultSet").TYPE_FORWARD_ONLY, GetJavaClass("java.sql.ResultSet").CONCUR_READ_ONLY);
Local JavaObject &rsTarget;
Local JavaObject &rsSource;
Local number &rowsLoaded;
Local number &rowsUntilCommit;
Local JavaObject &metaData;
Local number &columnIndex;

&loadCommand = "";
&loadCommand = &loadCommand | "SELECT top 50 * ";
&loadCommand = &loadCommand | "FROM OPENQUERY([MY_LINKED_SERVER], ";
&loadCommand = &loadCommand | "'SELECT * FROM MY_REMOTE_TABLE')";
&rsSource = &stSource.executeQuery(&loadCommand);
&loadCommand = "";
&loadCommand = &loadCommand | "SELECT * ";
&loadCommand = &loadCommand | "FROM [TARGET_TABLE]";
&rsTarget = &stTarget.executeQuery(&loadCommand);
&rowsLoaded = 0;
&rowsUntilCommit = 50;
&metaData = &rsSource.getMetaData();
While &rsSource.next()
   GetJavaClass("java.lang.Thread").yield();
   &rsTarget.moveToInsertRow();
   For &columnIndex = 1 To &metaData.getColumnCount();
      Evaluate &metaData.getColumnType(&columnIndex)
         When = - 6
            rem &rsTarget.updateInt(&columnIndex, &rsSource.getInt(&columnIndex)); 
            &rsTarget.updateInt(&columnIndex, CreateJavaObject("java.lang.Integer", 0).intValue());
         When = 4
            rem &rsTarget.updateInt(&columnIndex, &rsSource.getInt(&columnIndex));
            &rsTarget.updateInt(&columnIndex, CreateJavaObject("java.lang.Integer", 0).intValue());
         When = 6
            rem &rsTarget.updateFloat(&columnIndex, &rsSource.getFloat(&columnIndex));
            &rsTarget.updateFloat(&columnIndex, CreateJavaObject("java.lang.Float", " ").floatValue());
         When = 12
            rem &rsTarget.updateString(&columnIndex, &rsSource.getString(&columnIndex));
            &rsTarget.updateString(&columnIndex, CreateJavaObject("java.lang.String"));
         When = 93
            rem &rsTarget.updateTimestamp(&columnIndex, &rsSource.getTimestamp(&columnIndex));
            &rsTarget.updateTimestamp(&columnIndex, CreateJavaObject("java.sql.Timestamp", CreateJavaObject("java.util.Date").getTime()));
         When-Other
            rem &rsTarget.updateObject(&columnIndex, &rsSource.getObject(&columnIndex));
            Error ("Unknown column type: " | &metaData.getColumnType(&columnIndex) | " -- " | &metaData.getColumnTypeName(&columnIndex) | " for field " | &metaData.getColumnName(&columnIndex));
      End-Evaluate;
   End-For;
   &rsTarget.insertRow();
   &rowsLoaded = &rowsLoaded + 1;
   &rowsUntilCommit = &rowsUntilCommit - 1;
   If &rowsUntilCommit <= 0 Then;
      &conn.commit();
      CommitWork();
      &rowsUntilCommit = 50;
   End-If;
End-While;
&rsSource.close();
&rsTarget.close();
&conn.commit();
CommitWork();
End-While;

Resources

Looking for Orphan Rows

digitaleagle PeopleTools, SQL, Uncategorized Leave a comment

Here is some SQL that you might find helpful:

SELECT ‘SELECT ”’ + C.RECNAME + ”’ AS RECNAME, OPRID FROM ‘ + D.TABLENAME +
‘ A WHERE NOT EXISTS (SELECT ”X” FROM PSOPRDEFN B WHERE A.OPRID = B.OPRID) UNION ‘
FROM PSRECFIELDALL C,
(SELECT RECNAME, SQLTABLENAME AS TABLENAME
FROM PSRECDEFN
WHERE SQLTABLENAME <> ‘ ‘
AND RECTYPE = 0
UNION
SELECT RECNAME, ‘PS_’ + RECNAME AS TABLENAME
FROM PSRECDEFN
WHERE SQLTABLENAME = ‘ ‘
AND RECTYPE = 0
) D
WHERE C.RECNAME = D.RECNAME
AND C.FIELDNAME = ‘OPRID’

This Query writes a huge SQL Statement that looks for rows in any table that has an OPRID that does not exist in the PSOPRDEFN table.

Here is how to use it:

  • Run the SQL Statement
  • Copy all of the rows as a new query
  • Remove the last Union from the last line
  • You might want to add “ORDER BY 1” at the end to get it in record order
  • Execute the newly generated query

Note, this pattern can be replicated for other fields or situations too.  It is not just limited to use with OPRID.

JSON

digitaleagle Great Links, Java, PeopleCode, Uncategorized 2 Comments

Here is another one of those technologies that I would love to play with but have to wait for a client that actually needs it: JSON.  I found Jim’s post on JSON very interesting and would love to have the need to come back to it.

One other solution that might be worth considering is this library for Java.  You would have to compile it and place it on the class path, but you could access it from PeopleCode.  Or, I wonder if it would be worth implementing the library in a PeopleCode Application Package?

And in conjunction, you can’t mention JSON, without mentioning jQuery.

PSUnit

digitaleagle Great Links, PeopleTools, Uncategorized 1 Comment

I just noticed Jim’s annoucement of PSUnit, a tool for automated testing in PeopleSoft.   I had to link it so I could come back and give it a try.  To download load it, look for the link at the bottom of the PeopleSoft Technology Blog post.

In my opinion, automated testing would be huge for PeopleSoft.  It is such a huge and complex system that a complete system test requires many users to perform many different actions.  Maintaining a series of automated tests would allow an organization to run a full test everytime they apply a tax update, bundle, or any sort of modification to their system.

Well, I will report when I get a change to play with it some more.

Resources

Jim’s PeopleSoft Journal: Test Driven Development for PeopleSoft

PeopleSoft Technology Blog: PSUnit