PeopleSoft Tipster

Outputing ALL images from PS July 12, 2007

I was asked an unusual question yesterday and I thought I’d share it with you all.

The client wanted to view/extract all of the images held within PeopleSoft. My first reaction was that it couldn’t be done. As there are over 1000 images it’s not a task to be easily done manually. Also, only about 1/4 of the images are stored in the Web Server cache, so it’s not a task of just getting them from there.

So … how was it done?

The answer was to create a custom page with an HTML area on it, pointing to a record with a long field on. Then, in Page Activate PeopleCode, loop through all of the image names in the database appending and prepending with HTML and MetaHTML. Concatenate them all together and assign the resulting string to the Long field behind the HTML Area. Et Voila!

This is the Page Activate PeopleCode:

Local SQL &SQLGetImages;
Local string &ImageHTML, &ImageName;
&SQLGetImages = GetSQL(SQL.DMD_IMAGES);
While &SQLGetImages.Fetch(&ImageName)
&ImageHTML = &ImageHTML | "<img src='%Image(" | &ImageName | ")'/> " | &ImageName | "<BR />";
End-While;
DMD_HTML_AREA.HTMLAREA = &ImageHTML;
&SQLGetImages.Close();

and this is the SQL in the SQL Object:

SELECT CONTNAME
FROM PSCONTDEFN C
WHERE C.ALTCONTNUM = (SELECT MAX(C1.ALTCONTNUM)
FROM PSCONTDEFN C1
WHERE C1.CONTNAME = C.CONTNAME
AND C1.CONTFMT <> ' ')
AND C.CONTFMT = (
SELECT MAX(C1.CONTFMT)
FROM PSCONTDEFN C1
WHERE C1.CONTNAME = C.CONTNAME
AND C1.ALTCONTNUM = C.ALTCONTNUM
AND C1.CONTFMT <> ' ')

As an added bonus, all of the images will be available in the Web Server cache if you need the actual files.

I guess it’s not the sort of thing you get asked for every day, but you never know when you might need it …