Thursday, January 08, 2009

Exporting Attachments Part 2

In my post Export PeopleSoft Attachments using PL/SQL, I mentioned that PeopleSoft provides the File Attachment API for storing, retrieving, and viewing file attachments. These API functions comprise the recommended method for working with attachments. If you want to process an attachment, use the GetAttachment function to move that attachment into a file so you can access it from your app server. The ViewAttachment function, on the other hand, will send a copy of an attachment to a client browser. The ViewAttachment function is the only method provided by the Attachment API that allows a user to view the contents of an attachment. What if you want more control over the way PeopleSoft displays attachments? For example, let's say you use the File Attachment API to allow selected users to upload audio files (news, recorded training sessions, etc) and you have another page that allows other users to listen to those recordings. Should the "view" page display a "View Attachment" link or should it play the selected clip as embedded audio?

If you store attachments in the database using a RECORD:// style URL, then you can extract those attachments using PeopleCode. Here is an IScript that demonstrates this:

Function IScript_GetAttachment()
Local any &data;
Local string &file_name = "attachment.doc";
Local SQL &cursor = CreateSQL("SELECT FILE_DATA FROM PS_EO_PE_MENU_FILE WHERE ATTACHSYSFILENAME = :1 ORDER BY FILE_SEQ", &file_name);

REM Set the following header if you want a download prompt. Don't set it if you want inline content;
%Response.SetHeader("content-disposition", "attachment;filename=" | &file_name);

While &cursor.Fetch(&data);
%Response.WriteBinary(&data);
End-While;
End-Function;

This method allows you to embed audio/video, provide user configurable background images, display inline PDF files, etc.

If you follow my blog closely, you may remember that my IScripts post claims there is no way to write binary data to an HTTP response. Notice the use of the %Response.WriteBinary method above? I was wrong. I was reading the comments of the post Browse the App Server Filesystem via iScript and noticed Joe's reference to %Response.WriteBinary(). Even though it isn't documented in PeopleBooks, I tried it and it worked. Thanks Joe!

Note that I hard coded my SQL statement. I did this for simplicity in this blog post. For production systems, I encourage you to use SQL definitions. Of course, if you implement this solution, you will need to change the SQL select table name to the name of your attachment table. Likewise, you will probably derive your file name from a parameter rather than hard code it as shown here.

As with any custom development, make sure you write secure code. For example, when executing SQL based on parameters, use SQL binds. Failure to use SQL binds can result in SQL injection flaws. Likewise, if you accept an attachment from a user and then display that attachment using a means other than the provided ViewAttachment function, then make sure you either validate that input or validate the output. For example, let's say you have a page that allows users to upload JavaScript or other HTML that will be executed on other pages. Failure to restrict this type of usage provides malicious users with an opportunity to create HTML injection and cross site scripting vulnerabilities.

151 comments:

Ciphersbak said...

Hey Jim,

Thanks a lot..
Interesting to see the WriteBinary method ...

Dan Kibler said...

Jim

I've not tried WriteBinary with database binaries, but have been unable to find a way to use it to write binary files from the file system, .doc, .pdf, etc.

Jim Marion said...

@Ciphersbak, I consider it a pleasure to contribute to the PeopleSoft community.

@Dan, you are talking about reading files from the file system and serving them to a browser, right? Before you can write binary files from the file system, you need to be able to read those binary files. I have not found a practical means for reading binary files using PeopleCode's File object. I believe this is the first step.

Did you read this post: Browse the App Server Filesystem via iScript? Notice that Joe Ngo expresses the same frustration as you. I know it can be done. I just haven't taken the time to figure it out. When I do, I'll be sure to post the solution. Since Java can read binary files, I think it might be part of the solution.

Graham said...

If your file attachment is done via an FTP server then you an still achieve the same result by reading in the file (even binary) into a local string variable and then using the same method as Jim shows for writing out to the response object.

For example

&sfile = GetFile(&filename, "R", "ASCII", %FilePath_Relative);
While &sfile.ReadLine(&pdfbinary);
&output = &output | &pdfbinary;
End-While;

Dan Kibler said...

Jim

You are right that I have been unable to read a binary file into peoplecode. I even tried using java io classes without success.

Graham - I already tried doing almost exactly what you suggest but end up with a "damaged" pdf. Here's my code. If you look at the file in a text editor, it looks very different than the source file.

Function IScript_ViewFile()
Local string &fileName = "HelloWorld.pdf";
%Response.SetContentType("application/pdf");
%Response.SetHeader("content-disposition", "attachment;filename=" | &fileName);
Local JavaObject &streamTest = CreateJavaObject("test.StreamTest2");
Local File &MyFile = GetFile(&filepath, "R", "ASCII");
While &MyFile.ReadLine(&pdfbinary);
rem &output = &output | &pdfbinary | Char(10) | Char(13);
&output = &output | &pdfbinary;
End-While;
&MyFile.Close();
%Response.WriteBinary(&output);

End-Function;

Jim Marion said...

@Dan, thank you for testing this. I was wondering the same thing about Graham's code. If you use the same encoding and line termination character as the PDF generator, then I think you would be OK. The ReadLine method strips the line termination character. I noticed that you added it back in as CRLF. It might be just LN. Likewise, the encoding might be UTF-8 rather than ASCII. When opening the file in text mode, this is very important because PeopleSoft will translate the binary characters according to the encoding specified. When reading bytes, it doesn't matter. Likewise, PDF is a text based file format, but can contain binary data (images, compressed data, etc). If you read a binary PDF, then you would get different results than reading a text based PDF.

@Graham, do you have a test scenario where you could look at the PDF to see if it is stored as a text file or a binary text file and its encoding?

Jim Marion said...

@Dan, one more thing... about using Java. I think you have to read the binary file into a record field and then you can write it out using %Response.WriteBinary. It might be possible to use a derived/work or in memory record set, but I'm not sure.

Graham said...

We use this exact code for reading in pdf binaries from the file system in tools 8.49. I realise now that my comment above ommitted the necessary appending of CR and LF.

This code is live in production and is used for several hundred pdf read and writes a day.

Function IScript_Open_PDF()
/* Get inbound filename parameter */
&fileparm = %Request.GetParameterValues("file");
&filename = &fileparm [1];

/* Set the MIME content type and header so the browser is set to expect a PDF file*/
%Response.SetContentType("application/pdf");
%Response.SetHeader("content-disposition", "attachment;filename=" | &filename);

/* Read the file into a local string. This works for binary or text files */
&sfile = GetFile(&filename, "R", "ASCII", %FilePath_Relative);

/**** OXF1476 PT8.21 FIX: SLOUGH 23/12/2004 ... LOOP TO READ FILE
This loop is required as the ReadLine method removes the Carriage return(char(13)) and new line (char(10)) characters
from each line. If the loop is not used only the first line of the pdf is returned. The above characters must be appeneded to the end of each line read.
***/

While &sfile.ReadLine(&pdfbinary);
&output = &output | &pdfbinary | Char(10) | Char(13);
End-While;

/*****END OXF1476 -- PT8.21 FIX ******/

&sfile.Close();

/* Send the file to client browser */
%Response.Write(&output);

End-Function;

Jim Marion said...

Thanks Graham.

Dan, check your encoding. Perhaps your PDF's were created with a different encoding.

Dan Kibler said...

Yes, our documents do contain images. I have not figured out how to determine what the encoding of the pdf is, but I have tried every combination of encoding, adding line termination chars or not, and using .Write and .WriteBinary that I can think of. The result is either a "damaged" pdf or a pdf that is all white, no text or images, but the right number of pages.

Since we have solved our problem otherwise, using FileAttachement, I going to drop this again, but appreciate the discussion.

Dan

fpickering said...
This comment has been removed by the author.
Jim Marion said...

In February, a developer posted a comment here asking what to do in the following situation:

"Our db is partitioned on EMPLID and the record that holds the actual attachment cannot contain EMPLID. (due to the requirements of the File Attachment API functions)

Our DBAs are concerned that the file sizes coupled with our large population will blow out the tablespace."

The poster removed the comment, but I thought it was a very good question so I'm adding it anonymously for the poster.

So, what's the answer? Well, let's look at the requirements again: "the record that holds the actual attachment cannot contain EMPLID." Piece of cake. The attachment record doesn't contain EMPLID. According to PeopleBooks, the attachment record must contain FILE_ATTDET_SBR and no other fields. When storing and accessing attachments, you provide the attachment record name (RECORD:// URL) and ATTACHSYSFILENAME. You, the developer, are responsible for maintaining the link between the attachment record and the transaction record (EMPLID).

As you can see, EMPLID isn't in FILE_ATTDET_SBR, so the issue raised by this question is a non issue. I trust the original poster realized this and that is why he/she removed the question.

Rama Naidu said...

Jim,

I want to display an image as the background for a mail notification, triggered using SendMail through an Application Engine.

The below method works, but with a problem. Any employee who receives this should be granted access to the image folder on the server, which is impossible (or else he will not be able to see the background image)

&MAIL_TEXT = "{html}{body background='" | GetURL(URL.ANNIVERSARY_MAIL_IMAGE) | "'} - - - - -
where, URL.ANNIVERSARY_MAIL_IMAGE = \\a.b.c.d\foldername\imagename.extension


Then I tried the following 2 ways, which i had earlier succesfully used in iScript peoplecode.
Now in AE, I get the error: First Operand of . is NULL

1) &MAIL_TEXT = "{html}{body background='" | %Response.GetImageURL(Image.ANN_MAIL_BG) | "'} - - - - -

2) &Image_Rec = CreateRecord(Record.ANN_MAIL_IMG); /* Image had been stored in this database table */
&Image_Rec.YEARS_OF_EXP.Value = &YEARS_COMPLETED;
&Image_Rec.EFFDT.Value = &EFF_DATE;
&Image_Rec.EFF_STATUS.Value = "A";
&Image_Rec.ANNV_MAIL_IMG.Value = &IMAGE_DATA;
&MAIL_TEXT = "{html}{body background='" | %Response.GetImageURL(&Image_Rec) | "'} - - - - -

Please suggest a solution.

Thanks,
Rama Naidu.

Fred said...

Jim,

I thought that adding clarification to my previous post which you alluded to might spawn some interesting discussion.

Here is the original post:

"Our db is partitioned on EMPLID and the record that holds the actual attachment cannot contain EMPLID. (due to the requirements of the File Attachment API functions)

Our DBAs are concerned that the file sizes coupled with our large population will blow out the tablespace."



The Issue:
The db is partitioned on EMPLID. According to our DBA, in order for db2 to realize performance gains using this partition, EMPLID must be in the same table (FILE_ATTDET_SBR) that holds the large sized files. However, the compiled file attachment API functions fail when adding EMPLID to FILE_ATTDET_SBR.

The fact that the record that holds the actual attachment cannot contain the field EMPLID is the source of the problem and not the requirement. Sorry for the confusion.

Anyway, I tried a couple of options to solve this including asking the DBA if DB2 could base its partioning on a view joining the two tables together (FILE_ATTDET_SBR and the transaction table) and was told that it cannot.

Also I investigated whether or not I could write my own class that would perform the functions of the file attachment api or import a java class to do it but then was given tasks with higher priorities so I placed it on the back burner.

Anyone have any good ideas other that these?

Jim Marion said...

@Rama Naidu, The %Response object is not available to App Engine PeopleCode. That is why you get the "operand is null" error. Using GetImageURL is very safe for e-mails anyway because GetImageURL copies the image to a web server cache directory and appends a version number to the image. Depending on how long your cache persists and how long the user keeps the e-mail, it is very possible that the image will not exist for the full time the user has that e-mail message. Your approach of using a URL definition and storage location is a good approach. Can you map that location into a web server? Then you can serve those images through http rather than through a network share. If not, can you pick a different location that you can access through http? If not, then the next approach is to use the e-mail attachment functions/methods to add the image as an attachment. A URL will give you a much smaller e-mail size, however.

@Fred, I'm not a DB2 user, and that might explain my ignorance (or innocence). You can build a table without EMPLID using App Designer. I know you can. PeopleSoft only accesses your FILE_ATTDET_SBR when you store or retrieve files. It does so using the file's ATTACHSYSFILENAME. The relationship between the FILE_ATTDET_SBR and your transaction is the ATTACHSYSFILENAME and that relationship happens through the record containing FILE_ATTACH_SBR. FILE_ATTACH_SBR will contain your transaction keys (EMPLID, etc). You have other tables in your PeopleSoft database without EMPLID. What is the difference between your FILE_ATTDET_SBR record and other tables, like PSCLASSDEFN? I think you should try it in DEV. I can't see any reason this would cause performance issues. Partitioned or not, I know you have tables in your database without EMPLID. This table would be no different than those other tables. The table is only used when fetching/storing attachments and is not accessed when loading a transaction. The FILE_ATTACH_SBR record is different. It does get accessed every time and should have EMPLID. I don't think you have an issue with creating a table without EMPLID. I'm sure you know there are many PeopleSoft tables without EMPLID. I think your concern is more with loading a table into a transaction that is keyed on EMPLID when the table doesn't have EMPLID. Rest easy. The FILE_ATTDET_SBR record isn't loaded into the transaction. It is accessed only when a user takes an action on the attachment itself. You can think of it more like a stand alone rowset than as a buffer record. In fact, the FILE_ATTDET_SBR record is never loaded into the buffer (at least as far as I can tell). Again, the FILE_ATTACH_SBR record is different. It does get loaded into the buffer and it should have EMPLID.

Rama Naidu said...

Hi,

I'm facing a problem with Worklists. When I enter a particular component through the menu navigation, the component launches. But when I try to enter the component through a worklist entry, it shows "you
are not authorized for this page". And this occurs only for a few
UserIds. Please suggest a solution.

BTW.. Is there a place in your blog where i can create a new Post/Topic. I feel bad posting my irrelevant queries amidst your discussions.

Thanks,
Naidu.

Jim Marion said...

@Naidu, I have no specific advice for you in regards to the error you are seeing. There are a variety of things that can cause this. You will be best served with this issue by opening a case with Oracle support.

Don't worry about posting off topic. I don't mind. I don't have a location for creating new threads or discussions. I monitor the OTN forums and recommend posting new threads here: OTN PeopleSoft General forum.

ap said...

Jim,

I am trying to use your above technique to access database attachments via an HTTP Iscript URL. The problem is however Unlike View/Detach Attachment non english like japanese file names have their name corrupted. I tried to set the charset ..still the file name gets corrupted. Please help its urgent....

ap said...
This comment has been removed by the author.
ap said...

Sorry for multiple posts for the same q..I figured out Detach attachment is using hex escapes %XX to render the file name in content disposition. So is it possible to read a DB attachment name field from database which is in non english and create the corresponding hex escape in peoplecode???

Jim Marion said...

@ap, what happens when you hard code the file name? Or, what if you select the file name from the database or use it as a query string parameter? My example has it hard coded, so I suggest trying a different approach. I don't know how character encoding is handled with headers.

ap said...

Jim,
Yes I do read the attachment name from attachment record and use it in the place of your hardcoding. If i replace it with corresponding %XX hex values (like detachattachment) for the unicode string, the browser shows the file name correctly. If I use the name from database as such, it is getting garbled. Is there a way i can convert unicde string to hex escapes in peoplecode?

ap said...

Ah..I figured it out...
we need to use a EncodeURL method to convert file name to hex escapes...

Here is the completed code ..hope it helps someone

import PTFILE_HTTP:HTTPFileURL;
import PTFILE_HTTP:Utils;

Function IScript_GetAttachment()
Local any &data;
Local string &attachfile_name, &attachrec_name, &uid, &content_type, &file_extn;
Local Rowset &attachrs;
Local number &row_count, &ret_code, &contentlength;
Local PTFILE_HTTP:HTTPFileURL &httpfileobject;
Local PTFILE_HTTP:Utils &filehttputilsobject;


&httpfileobject = create PTFILE_HTTP:HTTPFileURL();
&filehttputilsobject = create PTFILE_HTTP:Utils();

/*Authorize the user*/
If (IsUserInRole("HTTP File View")) Then
/*Read and validate the request*/
&uid = %Request.GetParameter("uid");
SQLExec("SELECT ATTACHSYSFILENAME, ATTACHFILERECNAME FROM PS_PTFILEHTTPGUID WHERE GUID =:1", &uid, &attachfile_name, &attachrec_name);
&ret_code = &filehttputilsobject.ValidateRequest(&attachrec_name, &attachfile_name);

Evaluate &ret_code
When 0
/*Determine attachment recordname and filename*/
&attachrs = CreateRowset(@("Record." | Upper(&attachrec_name)));
&attachrs.Fill("WHERE ATTACHSYSFILENAME = :1 ORDER BY FILE_SEQ", &attachfile_name);

/*Determine File extenstion and content-type*/
&file_extn = &filehttputilsobject.GetFileExtension(&attachfile_name);
&content_type = &filehttputilsobject.GetContentType(&file_extn);

/*Read the file BLOB from database record and write to response*/
For &row_count = 1 To &attachrs.ActiveRowCount
try
%Response.WriteBinary(&attachrs(&row_count).GetRecord(@("Record." | Upper(&attachrec_name))).FILE_DATA.Value);
catch Exception &ex
%Response.SetContentType("text/plain");
%Response.Write(MsgGetText(137, 182, "Exception in writing binary file data from database" | " " | &ex.ToString()));
end-try;
End-For;

/*Set the response headers*/
If (&content_type = "") Then
&content_type = "application/octet-stream";
End-If;
%Response.SetHeader("Content-Disposition", "attachment;filename=" | EncodeURL(&attachfile_name));
%Response.SetContentType(&content_type);

Break;

/*Handle errors in request*/
When 1
%Response.SetContentType("text/plain");
%Response.Write(MsgGetText(137, 183, "Invalid UID %1 in request", &uid));
Break;

When 2
%Response.SetContentType("text/plain");
%Response.Write(MsgGetText(137, 184, "Requested file attachment %1 is not available in database record %2", &attachfile_name, Upper(&attachrec_name)));

Break;

End-Evaluate;

Else
%Response.SetContentType("text/plain");
%Response.Write(MsgGetText(137, 185, "User %1 is not authorized for viewing attachments", %UserId));
End-If;


End-Function;

Jim Marion said...

@ap, thank you for sharing! I had not thought about encoding the file name.

Can you describe the PTFILE_HTTP app package?

ap said...

Jim,
That App package is newly added for tools 8.51 and will be responsible to generate attachment Iscript URL. It will create a URL based on a UID corresponding to the attachment record name/ file name combination. The UID in request will be read and resolved back to the original attachment name and record name. In addition it will help in identifyiong if the attachment exists on the requested record, get the content type mime info for the attachment etc..watch out for that with tools 8.51

Ciphersbak said...

Hi @ap, PTools 8.51!!! well we've just got the 8.50.05 tools patch and we already have an 8.51 in the queue. That's news to me!! thanks for the insight...

Karen said...

I have read through this blog and understand some of it as I am fairly new to PeopleSoft. I am re-tooling an application into PeopleSoft 8.48 from a Java based tool, SilverStream. I have many attachments in the old system that I can export to my local file system. I am looking for a way to automatically or progromatically upload/attach these files to the parent records so my customer does not have to manually attach close to 10k attachments as we migrate the application to PS. I am successfully using the File Attachment API to allow them to enter new attachments into PS. Any help would be greatly appreciated.

Jim Marion said...

@Karen, the way I would handle this is to use the PeopleCode PutAttachment function in an App Engine to iterate over files and import them into the database. The PutAttachment function is the app server version of AddAttachment. PutAttachment expects the file to be accessible by the app server (or process scheduler) whereas AddAttachment allows the user to add the attachment file.

Mohammed Hussain said...

Hi Jim,
i have to download all the applicant resume attachments through application engine and have to place the files in the system folder. i tried using the writeraw() method, but the output file genrated is containing the resume text with lot of junk characters, please provide your suggestions.

Thakns
Hussain

Subbu said...

Hi Jim,

First am not sure whether this the place where i can post my Q's.Need some suggestion.

I want to access an attachment from HTTPS link and add that as an attachment to the email using peoplecode. Link looks like Https:/dms....../231234.doc I want to access this document and put it in the appserver to send it as attachment in email. Pls give your suggestion.

Thanks,
Subbu

Jim Marion said...

@Subba, your comment sounds like you want to include a link in your e-mail, but the phrase, "I want to access this document and put it in the appserver to send it as attachment in email" makes me wonder if you want to actually attach the file to the e-mail, not include a link. The link option would work as described in this post. HTTP or HTTPS, it doesn't matter.

If you want to attach the file, then your e-mail generation PeopleCode will use the GetAttachment PeopleCode function to move the file to the app server, and then add the attachment to the e-mail.

Subbu said...

Thanks for the reply Jim.

Yes i want to attach the file as an attachment to the email. Probelm is using GetAttachment peoplecode function we are not able to transfer the file in oracle UCM (Https://ucm..../ABC.doc) to app server. Does peoplesoft supports secured FTP.?

At the same time if the file is in FTP (local file server) we were able to transfer the file to app server using GetAttachment peoplecode function and send it as an attachment in the email.

Jim Marion said...

@Subbu, PeopleTools 8.50 supports secure FTP.

If you are concerned about secure file transfer, your recipients have access to UCM, and you store files in UCM, then I would include an https link in the e-mail rather than the actual file. Since e-mail is not secure, requiring secure copy from UCM to app server in a protected network and then sending it in an unsecure protocol, possibly over an unsecure network, does not seem right. If your recipients do not have access to the UCM server, then transferring to your local file system first makes sense.

UCM supports WebDAV as well. Is your app server on a Unix style operating system? If so, you might try scripting the file transfer using something like Cadaver. You could even script the send e-mail part. Here is a post where I show how to control input and output for local batch processes: Exec Processes while Controlling stdin and stdout.

Subbu said...

Yes our recepients dont have the access. So we have to attach the file itself in the email.

We are looking for this to happen on click of a button - fieldchange event. Is it possible to do something using Webdav , cadaver (app server is on unix OS) in this scenario.?

Jim Marion said...

@Subbu, you need to get the file from UCM to your app server. Then you can use PeopleCode to attach the file and send it. PeopleTools 8.50 GetAttachment supports ftps so you can securely copy from UCM to your app server from a button click. In earlier versions, you could use something like Cadaver with an SSL URL to copy the file to your app server. You would call your Cadaver script from your button click PeopleCode.

Stan said...

We are implementing voucher attachment functionality. We process 75,000 invoices per year. Based on your advice in your "Attachment server configuration - database or ftp server" posting, we are going to implement this on the database. If this results in too much database bloat, we'd need to convert this data from the database back into files.
Based on Karen's posting above, is it possible to do just the opposite, convert attachment data from the database back into files.
If so, how?
Thanks
Stan

Jim Marion said...

@Stan, yes, if you need to export them later, you have a couple of options. If your process scheduler server has access to the file system location, then you can use the GetAttachment PeopleCode function to move the files from your database to your local app server. This is the opposite of the PutAttachment function I recommended to Karen.

If your process scheduler server does not have file system access to your FTP server, then you will use GetAttachment (record:// URL) to create a local copy and then PutAttachment (ftp:// URL) to move it to the FTP server.

Regardless of the method used, you can use an App Engine program to iterate over the attachments in your attachment (or transaction) record and then PeopleCode to move the attachment.

ap said...

Jim,
Is there a way to get binary data from the %Request object. i.e Im posting binary data to an Iscript from a java client. Is there a way to read thsi binary data in iScript peoplecode...there is no %Request.ReadBinary()..or is there one....

Jim Marion said...

@ap, this post on IT Toolbox tells you how to get the raw text content of a request, but I'm not aware of a way to get binary data from a request.

Robert said...

Hi Jim, I have been researching for how to copy an image file (BLOB) from a record to application server. Hope you could share some ideas.
For e.g image data from PSFILE_ATTDET.FILE_DATA to a specific app server directory such as ../datafiles/image.jpg Thanks much for any insights on this.

Jim Marion said...

@Robert, the easiest way is to use the PeopleCode GetAttachment function. That will put the file on your app server. Let me know if you had something else in mind.

Robert said...

Hi Jim, I saw your article in peoplesoft.ittoolbox.com: How to move and delete files using peoplecode using Java.

How you use PeopleCode/Java to transfer file from unix to NT since the file attachment functionality does not support SFTP.

Jim Marion said...

@Robert, there are many ways to share files between Windows and Linux/Unix. Probably the most common method is to use samba.

Siva said...

We are have PS Portal 91 and CRM HRHelpdesk module is integrated into protal.

I am able to upload a file and i am not able to view the file through protal.
The view attachment is opening a new window which is resolving to CRM instance and i need to view the attachment within portal.

Please suggest ....

Jim Marion said...

@Siva, For transactions, Enterprise Portal just provides a frame set for wrapping your transaction system (CRM, etc). All your transaction URL's should resolve to your transaction system. You can confirm this with a tool like Fiddler.

ViewAttachment opens attachments in a new window (which should point to your transaction system -- crm). Check your pop up blockers. If you are using Internet Explorer, then hold down the Ctrl (control) key when you click the button/link to view an attachment and keep the control button down until the attachment is fully open in the attachment's native app (even after the open/download prompt).

Aniruddha said...

Hi Jim
I came across your blog while searching for a solution of problem that we have.
The requirement is that the users should be able to download the file generated from PeopleSoft Application.
The file gets stored on application server and we need to implement some functionality wherein users will be prompted to save the file from browser.
Can i accomplish this using Iscripts? My idea is to use viewattachment function to generate the URL for this file and then use java to grab the file from URL and save it.We don't want users to read the file in browser but at the same time should be able to download the file.
Would like to hear from you on this.
Thanks

Jim Marion said...

@Aniruddha. I don't know what you mean exactly. ViewAttachment works with files on an FTP server or that are in the database, not with files that reside on the file system. If the file is a text file, then you can use the CreateFile function in an iScript to open the file and then write its contents to the web browser.

If the file is in binary form on the app server, not the web server, then PeopleCode won't really help you serve it to the browser. There are work-arounds. You could use PutAttachment to move the file into an attachment record in the database, and then ViewAttachment, etc.

Two things control what happens when the web server sends a file to the web browser:

1. HTTP headers
2. The web browser/user.

With HTTP headers, you can tell the web browser the content type and content-disposition. Both will help the web browser determine whether to display or prompt for download. Even then, the user will be prompted to save or view. If the user chooses view, and the content type has a registered plugin in the browser (PDF for example), then the browser will still display the contents in the browser, not download the file.

A signed Java applet or ActiveX (IE only) can control what happens, but that is really going quite far with it.

Unknown said...

Jim, I am wondering if you can help me? I am trying to display a PDF document (from the database) on a PeopleSoft page. So the IScript you descibe here seems perfect. Unfortunately I am unfamiliar with them. I have an HTML Area on my page and would like to retrieve the PDF into it. I understand the code here for the IScript function, but what is the code on the regular PeopleCode side to populate the HTML Area? I have;

GenerateScriptContentURL(%Portal, %Node, Record.WEBLIB_CUSTOMREC, Field.ISCRIPT1, "FieldFormula", "IScript_Test");

But not sure how to get the contents from the IScript after that.

Thanks!!
Casey

Jim Marion said...

@Casey, It sounds like you want to embed a PDF in a PeopleSoft page. Since the PDF is stored in the database, then using an iScript to fetch the PDF is appropriate. What you are missing, I believe, is the HTML to put in the HTML Area, and then the RowInit, FieldChange, etc PeopleCode. Here is what I would do:

1. Add an HTML Area to the page.

2. Bind the HTML Area to a derived/work record field (usually the HTMLAREA field because it is long enough).

3. Create an HTML definition in app designer that has the HTML to embed a PDF file. See this post from Adobe to learn how to embed PDF. Replace the embed tag's src attribute value with %Bind(:1).

4. Add RowInit (or whatever event should display the PDF) code to set the value of the derived work record to GetHTMLText(HTML.xxx, GenerateScriptContentURL(...))

Dan Kibler said...

You are too fast Jim!

I do the same except I use the <object> tag as it has a little more more control over reader.

I tried to add an example but blogger got all upset about the tags.

Jim Marion said...

Thank you Dan for helping out the community. The trick with blogger comments is to convert chevrons to entities: &lt;, &gt;, etc. Painful.

Pooja said...

Issue:I have an edit box for XML path on my page and i have created a Add attachment button on the page which is currently taking file from local dir to server dir path.my issue is to retrieve the path where the file has copied and assign that to edit box..which is an input for the app eng(My page is run control page).

Issue is If i am using GetEnv("PS_SERVDIR") its returning file path like C:\..but i need server path with location like \\Bursqa08\temp\. so that my app eng will be able to take the file from that location.With C:\ app eng is still looking into my local machine for the file.

Can you please give me an example for the same or suggest me the way to retrieve the path which works in both Windows and UNIX...Also where app server and web server are on diff locations too.

My existing code:
trial 1:
worked in windows and not unix-- PS_MACH

Declare Function add_attachment PeopleCode FILE_ATTACH_WRK.ATTACHADD FieldChange;

If GetEnv("PS_FILEDIR") <> "" Then
&filesyspath = GetEnv("PS_FILEDIR");
Else
&filesyspath = GetEnv("PS_SERVDIR") | "/files";
End-If;
rem Reset filename
&ATTACHSYSFILENAME = "";
add_attachment("record://PSFILE_ATTDET", "", "", 0, False, "", &ATTACHSYSFILENAME, &ATTACHUSERFILE, 2, &RetCode);
If &RetCode = %Attachment_Success Then
REM Create PS_SERVDIR/files directory if it doesn't yet exists;
CreateDirectory(&filesyspath, %FilePath_Absolute);
&Sysfilename = &filesyspath | "\" | &ATTACHSYSFILENAME;

REM Transfer data from DB table to local filesystem on Application server;
If GetAttachment("record://PSFILE_ATTDET", &ATTACHSYSFILENAME, &Sysfilename) = %Attachment_Success Then
&serverpath = GetEnv("PS_MACH");
&filepos = Find("\", &filesyspath);
&filepath = Substring(&filesyspath, &filepos, Len(&filesyspath));
SAD_PB_ICAS_RC.SAD_PB_XMLNAME = "\\" | &serverpath | &filepath | "\" | &ATTACHSYSFILENAME
Else
Error (MsgGet(14260, 76, "Message Not Found", &ATTACHSYSFILENAME));
/* Problem getting file */
End-If;
End-If;

trial 2:

This is working on server where app server and web server on same location but not working where they are on diff machines

Declare Function add_attachment PeopleCode FILE_ATTACH_WRK.ATTACHADD FieldChange;

Local string &filesyspath = GetEnv("PS_SERVDIR") | "\files";

rem Reset filename
&ATTACHSYSFILENAME = "";
add_attachment("record://PSFILE_ATTDET", "", "", 0, False, "", &ATTACHSYSFILENAME, &ATTACHUSERFILE, 2, &RetCode);
If &RetCode = %Attachment_Success Then
CreateDirectory(&filesyspath, %FilePath_Absolute);
&Sysfilename = &filesyspath | "\" | &ATTACHSYSFILENAME;

REM Transfer data from DB table to local filesystem on Application server;
If GetAttachment("record://PSFILE_ATTDET", &ATTACHSYSFILENAME, &Sysfilename) = %Attachment_Success Then
&serverpath = %Request.ServerName;

&filepos = Find("\", &filesyspath);
&filepath = Substring(&filesyspath, &filepos, Len(&filesyspath));
SAD_PB_ICAS_RC.SAD_PB_XMLNAME = "\\" | &serverpath | &filepath | "\" | &ATTACHSYSFILENAME;

Else
Error (MsgGet(14260, 76, "Message Not Found", &ATTACHSYSFILENAME));
/* Problem getting file */
End-If;

End-If;
Please help me with a platform independent solution. I will be thankful.

Pooja said...

Can you please suggest me if i can give server directory path in getattachment rather giving c:\ something like that.

Jim Marion said...

@Pooja, GetAttachment and PutAttachment REQUIRE the server file system path.

Pooja said...

Can you please provide me the sol where i want to copy file from local and paste to location provided by user in the form of IP or machine name location. it has to be platform independent(windows or unix) and also independent of whether the appserver and webserver on same location or the other). Thanks

Jim Marion said...

@Pooja,

If I understand correctly, you have a page where a user uploads a file, and then runs an app engine program to process the file. Once the file leaves the user's desktop (upload), you decide everything else. If you use an FTP URL for your upload, then the file will already be on the file system in a location you specify. It is up to you and your network administrators to make sure that location is accessible to both your windows and linux servers. If you use a record URL for the file attachment, then the file upload will copy the file into a table, and then your app engine can extract the file from the database using the GetAttachment function. If you use the RECORD:// approach, then the file system is irrelevant because the app engine can extract the file to a location accessible to the app engine (usually the temp/tmp directory).

Jim Marion said...

@Pooja, my comments assume you are using the File Attachment API for your attachment.

Ash said...

Hi Jim,
I have a hyperlink on the page, which should open a .pdf document that's residing on NT directory. On the filed change event, what kind of functions/commands can enable be to open a .pdf for viewing. we are not using attachment functionality to store the documents. Any advice is helpful.
Thanks
Ash

Jim Marion said...

@Ash, Serving a file from an NT share is a bit difficult. The problem is with reading the file and then writing it out to the Response. There really is no good way to read binary data from PeopleCode.

The alternatives are to use HTTP and FTP. If you can map the NT share into an HTTP server, then you can just redirect to the PDF file's URL. The other option is to map the NT share into an FTP server and then use the File Attachment API to serve the FTP file.

Anilkumar Rayala said...

when user upload the image and how to display on page dynamically

Cynthia Jesudurai said...

Hey Jim,

Thanks a Lot, its really helpful to me. And your Book also really good.

Cynthia Rachel. M

pranav said...

hey jim, I am using view attachement function to display the csv file created on app server to user. But when i get download dialogue box it gives file in excel. Is there any way to get csv file in csv format only ?

Jim Marion said...

@Pranav, what you are seeing is standard file association/content type mappings. When installed, Excel registers itself as the program for CSV files. Using Windows Explorer you can change the file association so that it opens with Notepad or some other program. If you don't want any program to open it, but want it to prompt for a download, then take a look at this solution: http://windowsxp.mvps.org/csvprompt.htm

kvr said...

Hi Jim,

Is there any way to read the file that is stored in data base record and load in to a table using regular file read and load techniques(File layout with AE)?

I am planning to store the file (that will uploaded by user from a ps page)in to a record but not in to file system.

Please guide me in this regard.

Thanking you

Jim Marion said...

@kvr, I think what you want to do is use a RECORD based URL (RECORD://MY_ATT_REC) so that the upload will automatically place the file in a table in the database rather than on the file system. In PeopleBooks, look for the File Attachment API. My book has a couple of very good step by step examples of using the File Attachment API as well.

kvr said...

Hi Jim

Good morning..

Basically I would like to use file attachment functions to upload file (CSV, XML or FIXED Format Files)and store in to a record and then read the file data and load in to a application table. Currently I am reading your book..if there is solution for this I will be happy.. if not please guide me..

Thank you

Jim Marion said...

@kvr, yes, chapter two describes what you are trying to accomplish.

CHETAN A G said...

Hi Jim

I have a new requirement

1> To Upload the file from a grid and grid can contains 'n' number of rows. Each row can attach a single file in run control page

Example: In a run control page i will add two rows in the grid and will upload one file for each row.

Question: What is the simple solution to do this and how will u do it?

2> In AE, to read the files without using File Layout

Question: What is the simple solution to do this and how will u do it?

Jim Marion said...

@Chetan, Just like you said, I would create a grid and add the AddAttachment, ViewAttachment, and DeleteAttachment buttons to a grid. I would also add the ATTACHUSERFILE field to the grid. The buttons will come from a derived work, the ATTACHUSERFILE will come from a physical record. I would use this physical record as the scroll's main record.

In your AppEngine, use GetAttachment to move those files into the file system for processing.

CHETAN A G said...

Hi Jim,

I have a requirement like to have a single file attachment row and button, when clicked on add attachment button user should be able to select multiple files, presently we can select only one file and upload only one.

Please let me know your comments on selecting and uploading multiple files from the run conrol page

Jim Marion said...

@Chetan, that is correct. PeopleSoft uses pure HTML. Pure HTML only allows one file selection per file input. Sites that allow multiple selections use flash to facilitate file uploads. You could modify a page to use the same approach, but will have to write your own server side processing logic for multi-file uploads.

Ap said...

@Chetan With peopletools 8.52 we have added MAddAttachment peoplecode API. This might be what you are looking for.

CHETAN A G said...

Yes correct i am looking for MAddAttachment function. I searched for an example how it works but didnt get any single example for this. Please let me know the processing logic code used if you know?

Ap said...

Here is our official documentation on MAddAttachment in 8.52 PeopleBooks. Hope your tools release is 8.52.

http://docs.oracle.com/cd/E28394_01/pt852pbh1/eng/psbooks/tpcl/book.htm?File=tpcl/htm/tpcl02.htm#H4492

CHETAN A G said...

Hi Jim,

I have a requirement to read the delimited file and fixed length files in an Application engine without using an file layout.

Please let me know how to do that with the sample code

Thank you in advance

Jim Marion said...

@Chetan, I do not have an example. Basically you open the file using the GetFile function and then iterate over the rows, using split to identify each field. Quotes can be a little troublesome in this approach.

CHETAN A G said...

Thank you jim for the response. And can we read the data from Excel file (NOT CSV)without using file layout? If so can you tell me the steps or sample code

Jim Marion said...

@Chetan, POI is a Java library that I've used to read Excel spreadsheets from PeopleCode. Unfortunately, I don't have published examples of this either. The way I worked with POI was to write Java Classes that used Func.SQLExec to move Excel spreadsheet data into PeopleSoft tables. Here is a blog post that explains how to use the PeopleCode data access functions from Java. I then called those Java classes from PeopleCode. You could also work with the POI Java library directly from PeopleCode.

Shail P said...

@Jim: I have tested your code(Code in the main article) and it does work great. However I had to add this line "%Response.SetContentType("application/pdf");
" to prevent the code from appending ".htm" to my output file name.

Well, now to a question; we are having a "File Not Found or File Length is Zero" error when we click on view attachment in our Expenses module. All other modules work fine except this one, if you try repeatedly, the view attachment would eventually open the file.
Based on trace files, we could see that the session gets shifted to second appserver during the request, whereas the file was downloaded in the first appserver.

I was wondering about using your solution to display the attachment instead of using view attachment functionality, this way I can retrieve the file in one request/response.

We use Load Balancer and our Jolt Polling turned on.

We have opened a support case and they have reccommended us to turn off Jolt Pooling.

I am very much interested in your thoughts on this issue.

Jim Marion said...

@Shail, your SetContentType call makes a lot of sense. In regards to connection pooling and the app server, what you are doing is the right way to do things. The app server is supposed to be stateless. It is not supposed to matter which app server a request hits. PeopleTools has changed the way it handles attachments, so it is hard to answer this directly. At one time the request for a file caused the system to write the file to a temporary location on the web server, and then redirect to that location. I believe the current approach is to "stream" the content directly from the app server without writing to a temporary file. In either case, theoretically, it shouldn't matter what happens at the app server. Obviously, however, the app server is doing more than just what I mentioned and it appears to be stateful in this case (which is probably a design flaw).

Regardless, my approach is stateless so it will work. If you are on an older version of PeopleTools, then you may want to go with my approach until you choose to get current with PT 8.52. If you are current, then continue to push for a resolution, and perhaps use my alternate approach until you have a resolution.

Viswa said...

Hi Jim,

I have a problem.

We are getting base64 content from thirdparty server using integration broker and we are able to convert that it into original file to some location.Now if we want to show it to user then again we have to put it to application server using putattachment and use viewattachment.

I feel its time consuming process.So i would like to know is there anyway that we can insert directly into fileattach records , before decoding?

raw content is in base64.Please suggest me on this.

Viswa.

Jim Marion said...

@Viswa, the approach you outlined is the way that PeopleCode would dictate that you code this solution. I don't like it either. I would just rather stream the result. If you prefer to use File Attachment records (as I prefer), then a way to accomplish this is to send the base64 directly to your database and have the database convert it to a raw data type and write it to the file attachment tables. The file attachment tables are very simple. They store two file names and chunked binary data. Just be sure to chunk the data using the Max Attachment Size value from the PeopleTools options tables. And be sure to update all the attachment metadata tables (usually two tables).

Viswa said...

Hi Jim ,

As you mentioned i used below code and i couldn't able to make it work.

Function IScript_GetAttachment() Local any &data; Local string &file_name = "attachment.doc"; Local SQL &cursor = CreateSQL("SELECT FILE_DATA FROM PS_EO_PE_MENU_FILE WHERE ATTACHSYSFILENAME = :1 ORDER BY FILE_SEQ", &file_name); REM Set the following header if you want a download prompt. Don't set it if you want inlintachme content; %Response.SetHeader("content-disposition", "attachment;filename=" | &file_name); While &cursor.Fetch(&data); %Response.WriteBinary(&data); End-While;End-Function;

when i opened the output atttachment document , in that word document whole page layout(from where i triggered this code) is present instead of data from fileattachment record.

Is anywhere aim doing mistake? and with this code can we open any file like .pdf/.vsd/.xls?

Jim Marion said...

@Viswa, I don't see anything wrong with the code. When you run the iScript, are you accessing it as a URL in your web browser? Did you use a /psc/ or /psp/ URL?

Yes, you can use this to access ANY type of attachment.

Viswa said...

Hi Jim,

No actaully i tried it just like peoplecode function.

Now i have followed all standars like created new WEBLIB record and used generatecontentscripturl function to generate the iscript url and used viewcontenturl to fire that url in browser .

I got authorization error , i beleive we need provide access to weblib record , i will try after granting the access and get back to you , in case of any issuess.

Thanks
Viswa.

Viswa said...

Hi Jim,

Its working as expected.Many thanks for your help.

Viswa.

bijay bhushan singh said...

hi jim,
i have a question that is there any method in peoplecode which search all files in a particular folder. my scenario is like i used addattachment function first to store that attacment to a record field then using getattachment method i transfered to my webserver folder like"\\Infpw03138\ps_cfg_home\webserv\CSSANN90\applications\peoplesoft\PORTAL.war\CSSAN90" .the main problem is that enduser will see video from webserver only so how can we know how many media file is there in that folder and in webserver it saved as system generated unique file name so pls suggest me how can i handle this problem

Jim Marion said...

@Bijay, You can use the FindFiles function. If that doesn't satisfy your needs, then you can use the Java File object.

Bijay Bhushan Singh said...

hi jim,
thanks for your reply.i have a question again .i am trying to embed video in peoplesoft using windows media player embeded code using iscript like
Function IScript_video()
Local string &emplid = %Request.GetParameter("ZL_ADD_LINK");
%Response.WriteLine("html");
%Response.WriteLine("body");
%Response.WriteLine("OBJECT id='playera' height=100% width=100% classid='clsid:22D6F312-B0F6-11D0-94AB-0080C74C7E95'");
%Response.WriteLine("param name='fileName' value=" | &emplid | " ");
%Response.WriteLine("PARAM NAME='AutoStart' VALUE='True' ");
and i called that iscript

&URLString = GenerateScriptContentURL(%Portal, %Node, Record.WEBLIB_EMAIL, Field.ZL_ISCRIPT1, "FieldFormula", "IScript_video", ZL_VIEW_ATTACH.ZL_ADD_LINK)
ViewContentURL(&URLString);
everything is working fine
but the problem is it will play video in new window and interner explore having save as option if someone save that page and he ll directly click that video is playing but my requirement is user can not download the video or after page save as it shld nt open that video.is there any method to disable save as button or not open that video from save as file
thanku in advance

Jim Marion said...

@Bijay, that sounds like it might be more about the player and the browser, then about the server and the video.

If you control the URL to the video and can secure the URL through PeopleCode or a reverse proxy, then you can check the referrer HTTP header or add a one-time use token or token that expires to the video's URL, etc. There are a handful of ways to secure the video.

Bijay Bhushan Singh said...

hi jim,
thanks for your reply
sorry still i am not able to find how to secure url using
GenerateScriptRelativeURL() .i seched for one time valid url but i am not able to get it. can i pass smthing with that url function so when it save as the password not get saved. i am not able to find secure a url using peoplecode or one-time use token.after save as page the url not saved or got expired or it asked password . pls can u suggest me some solution
thanks in advance

Jim Marion said...

@Bijay, it is up to you to create the token. One way is to create a GUID and add that as a query string parameter to the iscript URL as well as save it in a table. When someone accesses the URL with the GUID, the iscript would check the table for the GUID. If it exists, it deletes it and shows the video. If it doesn't exist, then it shows an error page.

Jack Smith said...

Hi Jim,
I am trying to write a file to an URL path in the app server and using the viewattachment to open the file to the local browser. I have file:///+the path director in PSNT and it worked fine but not in unix.

Below is the sample code

&file_url = GetURL(URL.AR_EXP_EXCEL);
&file_name = %UserId | "_" | &COLLECTOR | "_" | %Datetime | ".XLS";
&file = &file_url | "\" | &file_name;
&fileATT_url = "file:///" | &file_url;
&Export_File = GetFile(&file, "W", %FilePath_Absolute);
/* Write the rowset content into the file */
If &Export_File.IsOpen Then
/* Report Title */
&Export_File.WriteLine(&title);
&Export_File.WriteLine(&QryObj.FormatResultString(&l_File_Rowset, %Query_XLS, 1, &l_File_Rowset.ActiveRowCount));
/* Close the file */
&Export_File.Close();
End-If;

&ret = ViewAttachment(&fileATT_url, &file_name, &file_name);

Jim Marion said...

@Jack, I am wondering if it is the "\" as your path separator instead of "/". PeopleBooks ViewAttachment function says it requires "/". I'm not sure that is the problem, but it is suspicious since Unix uses "/" and Windows/NT uses "\".

Daniel Kibler said...

I was not aware that ViewAttachment supported file://

Jim Marion said...

@Dan, I only new about FTP and RECORD URL support. I was not aware of file support either. But Jack says it worked for him on PSNT (I assume a Windows-based App/web server?), so I thought the best I could do was point out why it might not work on Unix.

Daniel Kibler said...

I just tried this on a PT8.49 Windows App server. It works! It's interesting that even in the 8.53 PeopleBooks, the file:// prefix is not mentions.

&usrFilename = "APPSRV_0102.LOG";
Local string &path = "file:///D:/psfs/fsfstst/logs";
&rtn = ViewAttachment(&path, &usrFilename, &usrFilename);

John Ney said...

Hey Jim: I had a similar need, we create a graphical calendar for students via a java program and the end result is always a jpeg that we display directly on a page. We have used several methods over the past few years, but with tools 8.53, I found a really cool way to display this without needing an iscript. Here is the gist:
Assuming you have your jpg in a file on the app server:

Local File &imgFile = GetFile(&str_FileName, "R", "", %FilePath_Relative);

DERIVED.HTMLAREA1 = "<... src=""data:image/jpeg;base64," | &imgFile.GetBase64StringFromBinary() | """/>";

That's it! The image is base64 encoded and then the contents pushed directly to the browser.

The issue I had with using an iscript was the image was dynamic and one-time, so if I deleted it from the database before the page loaded, the image was broken.

Regards,
John

Jim Marion said...

@John, good use case for this new PeopleTools 8.52 method. GetBase64StringFromBinary is a new method that appeared in PeopleTools 8.52, primarily for Integration Broker. This is a very good use case for the method. Would you mind posting your use case as a comment on my Base64 Encoding Binary Files in PT 8.52 post as well?

Daniel Kibler said...

Do you think the GetBase64StringFromBinary technique would work with PDFs also? I might have to test that when I get a few minutes.

Jim Marion said...

@Dan, if you mean, can you read a PDF file with GetBase64FromBinary, the answer is yes, but I don't think that is what you meant. What were you planning to do?

St├ęphane Lapierre said...

Hi Jim,
as any one managed to make the file:/// work on Unix, or is it really something that only works on Windows ?
thanks a lot.

Jim Marion said...

@St├ęphane, from a web browser, yes, absolutely. From ViewAttachment, as some have mentioned here, I am not sure. As an alternative, you might try %Response.RedirectURL("file:///" | your file path)

Keep in mind that the URL path will be from the client's perspective (which is likely Windows or Mac), not the App Server's URL path.

Raptor said...

Hi Jim, I've always been looking into your blog for great solutions...I was wondring if there is any way you could transfer file via sftp on version 9.0 coz this version does not support sftp or ftps. Thanks in advance!

Raptor said...

Hi Jim, I always refer to your blogs when it comes for development solutions. I was wondring if there is any way you could transfer a file via sftp on version 9.0, since this version does not support sftp or ftps. Thanks in advance!

Jim Marion said...

@Raptor, it isn't the apps version, it is the tools version. Newer versions of PeopleTools (8.51+) support sftp and ftps. If you want this on older versions on Unix/Linux, PeopleBooks tells you how to modify the ftp script to support one of the FTP's secure cousins.

neeraj varshney said...

Jim,

I have one requirement, we have stored the voucher attachments in the database, now third party users want to access the attachments by clicking on the the URL, i believe this can be achieved by creating the Iscripts and using the code you provided, i just need to know the code for passing the run time variable to iscript.

This is what we want it to be :
1. users will click on the URL, it will ask users for Business unit and voucher id.

2. Once they give it, i can use your code to fetch the attachment for that voucher and BU and display it.

I need to know how can u pass BU and voucher id dynamically?

Jim Marion said...

@Neeraj, you would add the business unit and voucher ID as query string parameters (...?BUSINESS_UNIT=XXX&VOUCHER_ID=yyy.

In your PeopleCode, you will use %Request.GetParameter("BUSINESS_UNIT"), etc to get the parameters passed to the iScript.

rajchek said...

Jim,

I have a requiremnt to add mutiple file attachments in a single click. Currently with the AddAttachment function did't have option to pass source file path(local path). Is there any way it is possible to select multiple files and upload in one click ?
Appreciate your help. Thanks.

Jim Marion said...

@rajchek, the only way is to add multiple file upload controls to the same page.

Doug Treiber said...

Hi Jim,
I am working with a customer that is looking to move a couple million attachments out of PeopleSoft and into UCM/WCC. What would be the best method to migrate this large volume of documents?

Any advice you can provide would be very appreciated.

Thanks,
Doug

Jim Marion said...

@Doug, are you familiar with WebCenter Web Services? They are quite simple SOAP services. You use them to create all the Metadata for an entry, and then you POST the file content. Another option is to use a WebDAV client, such as caDAVer (horrible name) to set metadata and upload the files. I have used both.

Attachment metadata is pretty simple. I assume you will want to convert key fields from the attachment data to attributes. If I were doing this, I might be tempted to write an external program (not PeopleCode) that queries the database for rows of data, and then uses SOAP or WebDAV to send the information. If I were using Java, I could put the binary file data into a java.io stream and then send it to the target without writing anything to disk. I might also consider creating a shell script to query the database and send the data. Lots of options.

Paolo Andrada said...

Hi Jim,

Will it be possible to download a video/music files directly from the app server? I am using the viewattachment function and currently, it can only handle common file formats such as pdf, .doc, .txt, .ppt, etc. Thank you for your help.

Jim Marion said...

@Paolo, yes, using the method described here. Alternatively, you may be able to use ViewAttachment if you register your file type with your web server so it knows the content type to return to the browser.

BD said...

Jim -
Hoping for some assistance. I'm writing an App Engine that retrieves files/attachments that have previously been attached and are stored in the db (in a record and stored in FILE_DATA, and takes those files (could be .pdf, .doc, .html, etc) and attaches them to an email to send through the AE. I'm using the PT_MCF_MAIL:MCFOutboundEmail() and the SetAttachmentConent method, but am struggling with the methods/functions I need to use to get the file from the db before having it available to attach to the email during the AE process. Would you be able to quickly outline the order of functions/methods to do this? Thanks in advance -

Jim Marion said...

@BD, first use PutAttachment to move the file into the local file system of the process scheduler. Be careful to secure the file location if your attachments contain sensitive data.

BD said...

Thanks for the quick reply Jim.

So, what is the exact order of functions to grab the file from the attachment record in the db (i.e. FILE_ATT_REC), then have that file for email attachment in the AE? Is it as simple as

1. PutAttachment
2. SetAttachmentContent

.. or ..

1. GetFile
2. PutAttachment
3. SetAttachmenContent

Do you have to rewrite the raw date from the file if you use GetFile?

... or ...

is it much simpler than this? I'm not even able to keep the mail component out of this and grab the file from the record it is stored in in the db (from a previous file attach through the front-end browser interface using AttachAdd), and write/put it to a directory on the file server.

Jim Marion said...

@BD, GetFile is not necessary. PutAttachment creates the local file. If you want a temporary file name, then you can create a temp file and then give that to PutAttachment. Otherwise it is just a two step process.

Sandra Bond said...

Jim , I have an issue to understand. In one of our client site , we noted that on the appserver under a folder /datafile/crm/emailattach/popimap.wp.corpintra.net/EMEA_5C1237P there are a lot of file stored. Hope this are attachments from emails.
How is it stored there? Is there some setting somewhere that we can change this or can we delete this files on an regular interval?
We have peoplesoft CRM implemented at this client.
You answer is very much appreciated.

Jim Marion said...

@Sandra, great question. I don't know the answer. I suggest you open a case with My Oracle Support and ask them if this is configurable. Depending on the contents of the files, this may be a security concern as well since it may require special file/folder security to protect those files.

Pratik Munot said...

Hi Jim,

I tried your code and its working absolutely fine. It shows up an additional page downloads the file. But is there some way, we can show the document stored on the page itself like a inline doc viewer, instead of download option? The file can be of any format (txt,log, etc.)

Jim Marion said...

@Pratik, comment out the line: %Response.SetHeader("content-disposition"... This is what forces a download. Without this header, if the browser recognizes the content, it will display it.

Ur Kidney said...

Hi Jim,

I'm trying to send the email through Sendmail function and wanted to attach the attachments stored in Record "record://PTEST_TEST". should I need to call the Iscript function which you had provided? If so, please suggest me how I would be attaching the attachments.

Jim Marion said...

@Ur, no, you need to use GetAttachment to copy the file to the app server.

Ur Kidney said...

Jim, now I can able to receive a the attachment through Getattachment() and Sendmail() functions. But, I received the attachment with the random name and the user can't know whether they need to open with Doc or txt. it is coming with .bin extension with some random name.

Can you please suggest how we can get the actual name?

Thanks in advance!

Shiva said...

Hi Jim,

I have a query regarding uploading images and attachemtns through iscripts.
I am developing a student registration page with html and included a input type file to upload the student image and I want to store this image in the peoplesoft db when the form is submitted through iscript.
How can I achieve this? also I want to upload student docs into peoplesoft as attachments.
Please guide me on this.

Thanks,
Shiva.

Jim Marion said...

@Shiva, you can use iScripts to download attachments, but the upload process requires a regular PeopleSoft component with the File Attachment API.

Shiva said...

Thanks for the info Jim.

Luke Wallace said...

Hi Jim,
1. Im having trouble finding the Peoplebooks link to File Attachment API, are you able to provide (8.54)?

2. Ive created a discussion in MyOracleSupport Community (https://community.oracle.com/thread/3718872) attempting to compress images via JavaScript before uploading, would you be able to take a look at? I'm surprised this issue hasnt been attempted to be addressed!

Thanks
Luke

Jim Marion said...

PeopleTools 8.54 File Attachment API: http://docs.oracle.com/cd/E55243_01/pt854pbr0/eng/pt/tpcd/concept_UnderstandingtheFileAttachmentFunctions-ab7dac.html#topofpage

Arpan said...

Hi Jim,

Have you ever tried "Drag and Drop" file attachment in PeopleSoft??
I know it can be achieved through Jquery. But not sure about how to upload to database. Could your please share your thought on this..

Jim Marion said...

@Arpan, I have not, but it is a great idea.

Simon Chiu said...

Greetings Jim,

I've been doing some research within this thread and other places online, and am trying to gather options for our requirement of allowing other systems to interface with ours by providing them access to a flat file in a secure location: the challenge is that they would like to automatically retrieve the file: not have to generate say a CSV from Query Viewer. Looks like running an app engine with peoplecode to create the file based on the query and (s)FTP(s) the file to a landing zone is the most common way of doing this. There seems to be a way to email off a query too, once you schedule it.

We are interested if these are the only options, or if you have heard of or are aware of alternates: PeopleSoft Configuration / Third Party bolt ons that offer any solutions. (it would be nice instead of an email, FTP to a secure location could be added to the Scheduled Query.

Thanks again Jim.

Jim Marion said...

@Simon, perhaps a couple of other options include Query Access Service with file output type and integration broker MTOM.

choubeyjee said...

Hi Jim,

I am having a requirement where i have to upload any file on ftp server via drag and drop. I have gone through your post and got to understand that this can be possible. However, i have written a javascript and placed into HTML object and calling it from the page activate HS_DROPABLE_WK.HTMLAREA.Value = GetHTMLText(HTML.HS_DROPPABLE_HTML); Below is the JavaScript code.

(function(window) {
function triggerCallback(e, callback) {
if(!callback || typeof callback !== 'function') {
return;
}
var files;
if(e.dataTransfer) {
files = e.dataTransfer.files;
} else if(e.target) {
files = e.target.files;
}
callback.call(null, files);
}
function makeDroppable(ele, callback) {
var input = document.createElement('input');
input.setAttribute('type', 'file');
input.setAttribute('multiple', true);
input.style.display = 'none';
input.addEventListener('change', function(e) {
triggerCallback(e, callback);
});
ele.appendChild(input);

ele.addEventListener('dragover', function(e) {
e.preventDefault();
e.stopPropagation();
ele.classList.add('dragover');
});

ele.addEventListener('dragleave', function(e) {
e.preventDefault();
e.stopPropagation();
ele.classList.remove('dragover');
});

ele.addEventListener('drop', function(e) {
e.preventDefault();
e.stopPropagation();
ele.classList.remove('dragover');
triggerCallback(e, callback);
});

ele.addEventListener('click', function() {
input.value = null;
input.click();
});
}
window.makeDroppable = makeDroppable;
})(this);

(function(window) {
makeDroppable(window.document.querySelector('.demo-droppable'), function(files) {
console.log(files);
var output = document.querySelector('.output');
output.innerHTML = '';

for(var i=0; i';
}
});


})(this);


Its allowing me to drop any file in HTML Area but i am not able to upload that file to the server. Can you please help me out to do this miracle. I am in terrific need and you are my last hope. Thanks!!!

Jim Marion said...

@choubeyjee, the File Attachment API is very specific and involves a dialog used to upload files to the server. The user would have to first click an attach button to activate the dialog before dropping a file.

choubeyjee said...

Thanks Jim for your precious time.

As i have earlier told that I have created one HTML Area and have written some JavaScript code which is allowing me to drag and drop any file there and by using ajax code i am posting that file to the App Server.

I have two concerns.
1. As soon as we drop the file it's taking some time according to file size and getting uploaded to the server but after successfully uploaded, the session is getting expired. I have checked the app server log error - "Failed to execute GetCertificate request".
2. How can i either pass any javascript variable value to the peoplecode or call any peoplecode event?

Please help me. Thanks!!

AJAX Code -
function sendFileToServer(formData,status)
{
var uploadURL ="localhost"; //Upload URL
var extraData ={}; //Extra Data.
var jqXHR=$.ajax({
xhr: function() {
var xhrobj = $.ajaxSettings.xhr();
if (xhrobj.upload) {
xhrobj.upload.addEventListener('progress', function(event) {
var percent = 0;
var position = event.loaded || event.position;
var total = event.total;
if (event.lengthComputable) {
percent = Math.ceil(position / total * 100);
}
//Set progress
status.setProgress(percent);
}, false);
}
return xhrobj;
},
url: uploadURL,
type: "POST",
contentType:false,
processData: false,
cache: false,
data: formData,
success: function(data){
status.setProgress(100);

//$("#status1").append("File upload Done
");
}
});

status.setAbort(jqXHR);

}

Vamz said...

Hi Jim,
Its wonderful reading all your blogs providing advanced peoplesoft techniques. I have an issue in peoplesoft mobile expenses. I am trying to control the resolution of attachments uploaded through mobile. Pictures uploaded through mobile are having a higher resolution and as a result are completely zoomed up when viewed later. Is there any way I can control the resolution of images before displaying it in the mobile to the user.
Thanks
Vamsee.

Jim Marion said...

@Vamz, I'm suspecting these are high resolution photos from a mobile device? On the device, the only way I know to change this is to change the device camera settings. On the server, it is possible to use Java to reprocess high resolution photos. For viewing the photos, an alternative to the GetAttachment or ViewAttachment function is to write an iScript to read the image and then create an HTML page to view it. That HTML page would set display parameters for the image (height, width, max-height, etc)

Balamurali said...

Hi
We are having a page which is developed in XML/XSLT in peopletools. Now we are trying to upload a file by browsing from the user local machine. Please guide us to upload the file into the UNIX Server.
Thanks
BalaMurali

Jim Marion said...

@Balamurali, I suggest using the PeopleCode File Attachment API.

Ken said...

HiJim,

We have requirement to attach pdf and msg(mail files ) from NT server to the Vouchers.
Could you please let me know how can I attach the docs to created vouchers using Application engine process.

Thanks,
Ken

Jim Marion said...

@Ken, I would start with the PDF created. It can be bursted into a process scheduler folder. I would then use the PeopleCode function to send email, including PDF attachment. The​ key is the PDF file has to be available in the file system.

Lakshmana Katram said...

Thanks Jim

-Ken

rajchek said...

@Jim, We are storing pdf files related to employees in PS Records(using attachment framework). Now the requirement is that based on some report or process parameters we need to retrieve these employee pdfs, merge into one pdf and show in the process monitor so the users can print one form instead of many. How can I merge these pdf files from AE or SQR? I have tried with reading the FILE_DATA field for all Emplid's into a record object using AE process and write into a new file GetFile().WriteRaw. It works with one file. Writing more than one file results in an corrupted PDF. Is this the right way to merge PDF's? Please suggest me on this. Thanks for your help.

- Raj.

Jim Marion said...

@Raj, as you found out, combining PDF's through concatenation is incorrect. I recommend finding a PDF library you can use from Java. Working with the overloaded Java classes will be hard. My preferred work around is to create custom Java classes with helper methods to communicate with the Java library. I have worked with iText, but I see there are others now, including Apache PDFBox. I've never used it, but any project run by the Apache Software Foundation is worth investigating.

Daniel Kibler said...

@Raj - I have used the iText library that Jim mentions to combine pdfs into a single PDF and display to the user with FileAttachment.

rajchek said...

@Jim,@Daniel - Thank you for the quick response. I started looking into itext and gave info to client so they can purchase the library. Is there is a sample code you can share for calling these itext libraries from Peoplecode?

@Jim, downloaded Apache PDFbox and will research the library and post my findings.

Thanks
Raj.

Daniel Kibler said...

@Raj - here's my method for retrieving and combining PDFs using iText 5.4.0. pdfs variable is an ArrayList of PDF objects that is populated before calling this method. In this case PDFs are stored on Amazon S3.

/**
* Retrieve PDFs that have been added and concatenate them into one PDF.
*
* @param filePath
* Full path to concatenated file.
* @throws Exception
*/
public void getPdfs(String filePath) throws Exception {
PdfCopy copy = null;
Document document = null;
if (pdfs.size() > 1) {
for (Pdf pdf : pdfs) {
File tempFile = File.createTempFile("temp", ".pdf", new File(
tempPath));
tempFile.deleteOnExit();
// get file from s3 and save to the file system
sa.getEncryptedPdf(pdf.getBucket(), pdf.getFileName(),
tempFile.getPath(), pdf.getOwnerPassword());

PdfReader reader = new PdfReader(tempFile.getPath());
document = new Document(reader.getPageSizeWithRotation(1));
if (copy == null) {
copy = new PdfCopy(document, new FileOutputStream(filePath));
}
document.open();

// Add each pdf page to new pdf
for (int i = 1; i <= reader.getNumberOfPages(); i++) {
document.newPage();
PdfImportedPage page = copy.getImportedPage(reader, i);
copy.addPage(page);
}
tempFile.delete();
}
if (document != null) {
document.close();
}
if (copy != null) {
copy.close();
}
} else if (pdfs.size() == 1) { // only one pdf - just decrypt and store
Pdf pdf = pdfs.get(0);
sa.getEncryptedPdf(pdf.getBucket(), pdf.getFileName(), filePath,
pdf.getOwnerPassword());
}
}

rajchek said...

@Daniel - Thank you. So I need to write a java class using Itext pdf functions and then call this java from the peoplecode? Is this how it works? is there is a command line to simplify this, meaning I would just call the command line to merge the pdfs and handle the rest in peoplecode? Just wondering? thank you so much for your help.

Daniel Kibler said...

I prefer to use my Java classes directly in PeopleCode, but, as long as you have a main function, you could run the program from the command line.