Author: digitaleagle

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

JDBC From PeopleCode — Disadvantage/Advantage

digitaleagle Great Links, Java, PeopleCode, Uncategorized 4 Comments

Jim Marion’s post on JDBC made me think a little more.  (By the way, thanks, Jim, for linking me.)  The one disadvantage about accessing the PeopleSoft database via JDBC is that you have to supply the password to make the connection.

I have thought about trying to read the password from the application server or batch server configuration file, but if I remember right it only has the connect id and the connect password.  With the connect password, I might be able to use it to read the Access id and password from the database, but I have would have to be able to unencrypt it.

One advantage that SQL Server might have is that you can use Window’s security.  Assuming that the account running the application server or batch server has access to the database, you could just use the integrated security instead of an actual user name or password.

The advantage that JDBC has is that you don’t have to know the number of fields/columns that you want until run time.  With both the SQL object and SQLExec, you have to have a variable for each field you return or a return that contains all of those fields.

For example, I am trying to loop through a group of tables in a linked server and copy all of their fields to a table in the current database.  I have a problem using INSERT … SELECT, and I have to read the values in and then write them out.  I can’t figure another way to do it other than use JDBC.

Please correct me if I am wrong on any of this.  Maybe these thoughts will give someone else an idea that I overlooked.

Windows Update Tip

digitaleagle Server Administration, Uncategorized, Windows Tip Leave a comment

Yesterday, I was told of a great tip regarding Windows updates. I have had trouble many a time when Windows inadvertently rebooted while I left something to run overnight.

Step-by-Step: Windows Update – Disabling Auto-Restart for Automatic Updates

Here is a quick summary just in case the link goes away:

Open the group policy editor: Start > Run, gpedit.msc

Computer Configuration –> Administrative Templates –> Windows Components –> Windows Update

Enable the setting: No auto-restart for scheduled Automatic Updates installations

Documenting an Install

digitaleagle Great Links, Server Administration, Uncategorized 3 Comments

I have been recommended to document a PeopleSoft installation by printing out the installation notes and writing down in the margins what I did and what problems I ran into.  I think that is a great idea.

But, what if I could convert the installation notes into Microsoft Word or Open Office so that I could take my notes directly in the document.  The advantage is that I would have something I could search through for reference or in case of problems.  Also, I could easily include screen shots and such.

The problem is that I haven’t found a way to convert the PDF.  Here is a link that I tried, it and might help you if you have a smaller PDF.  I think the installation notes are too large.  It didn’t work for me.  Let me know if you get it to work.

I am exploring some other options, too.

Please let me know if you have any other ideas for PDFs or for installation documentation.