Using File Attachments and PeopleCode

This chapter discusses how to use file attachments in applications.

Using File Attachments in Applications

This section provides an overview of file attachment architecture and discusses how to:

• Debug file attachment problems.

• Configure multiple application servers to support file attachments.

• View file attachments.

• Use chunking with attachments.

• Use the Microsoft Windows NT 4 FTP server.

• Use non-DNS URLs with UNIX.

• Use attachment functions.

• Name URLs and file attachments.

• Convert user filenames.

Understanding File Attachment Architecture

File attachments are supported by using PeopleCode built-in functions that implement the transfer of a file to or from a browser using the application server. The file is either stored on, or retrieved from, an FTP server or database table, referred to as the data storage system.

Files can be transferred back and forth from the browser to the data storage system (by way of the web server and application server) or transferred back and forth from the application server file system to the data storage system.

The browser-to-web server transfer is performed using a standard HTML form construct. This can be done securely in an encrypted fashion if the web server uses Secure Sockets Layer (SSL) to communicate to the browser.

When a file is uploaded, after the file is received at the web server, 1 megabytes chunks are brought from the web server to the application server and temporarily stored in a PeopleTools table in the database.

Note. The 1-megabyte transfer size cannot be customized.

Once the entire file is transferred, the application server reassembles the file as a temporary file on the application server's file system, deletes the temporary copy from the PeopleTools table in the database, and sends the file to its ultimate destination on either an FTP server the target database table

Note. When the user selects a file for uploading, file size is not checked until after the file is transferred to the web server. If the final destination of the file is a database table, then it gets rechunked (as specified by the value of Max Chunk File Size field on the PSOPTIONS page) when that insertion happens.

Once the file gets to the web server the file size is compared to the value of the AddAttachment function's MaxSize parameter.

See Using Chunking with Attachments, ContainsOnlyCharType.

The web server-to-application server transfer is performed by using Oracle Jolt, which is securely encrypted. Because this transfer is done using the standard Oracle Jolt mechanism, no additional settings to the firewall are required (you do not need to open additional ports).

By using separately named subdirectories for each attachment, support for the simultaneous transfer of identically named files is guaranteed, and attachments will not be mixed up.

The application server-data storage system transfer is performed using either FTP or directly to a database table.

Note. The file attachment functionality does not support SFTP.

No log file or script is involved for writing to database tables. For debugging, these commands leave traces in the PeopleCode trace files if PeopleCode statement tracing (2048) is on.

Note. The search API does not work with database attachments.

See Understanding Search Class.

File attachment architecture is designed to be used in a frame template. This is because when a file is being uploaded to a content site, the browser is communicating directly with the content site and its database. This means that the work being done is on the content site, while the portal site is unaffected. Frame templates enable you to direct the upload work to the site that it is intended for. There is no proxy work being done.

However, if you want to use HTML templates, you must first ensure that the portal site can handle the request. You should look into any performance issues that may occur on the portal site if the file is big or the network is slow.

Debugging File Attachment Problems

Error information will be written to log file. For more detailed logging you may enable Trace by setting TracePC to 2048 on application server.

See Specifying Trace Settings, TransformExCache.

If the file transfer wasn't successful, error messages in the log can help you determine the problem.

A common reason that a transfer fails is that the FTP server was not accessible from the application server. This error could be due to a wrong password or wrong account name, or because the application server was unable to resolve the FTP server hostname. Perhaps the FTP server was down and the application server was unable to route to it. Try pinging the FTP server from the application server system, and then try to FTP to the FTP server from the application server.

If you have a Microsoft Windows file server, the hostname for the system may not be associated with a fixed IP address and may not be resolvable using the Domain Name System (DNS). If the application server is on a UNIX machine (such as Solaris, HP-UX, or AIX), the application server can only resolve the hostname using DNS (or perhaps the Network Information System [NIS] or an /etc/hosts file), but not using WinBeui or WINS. Therefore, the application server will not be able to convert the hostname indicated for the Microsoft Windows file server into an IP address and route to it.

If you suspect this is the problem, try the following:

1. Telnet to the UNIX application server.

2. Try to ping the suspect hostname.

   For example:

   ping TCARREON052500

If this fails, you must resolve the problem by either specifying the IP address in the FTP URL or by putting your hostname into DNS, NIS, or a hosts file so that UNIX can resolve it.

Debugging Paths

In general, FTP servers require the full path from the top-level directory, rather than the path from the home directory (a relative path). If you've been able to add an attachment but can't view it, verify that you're using a full, absolute path, as opposed to a relative path.

Attachments with non-ASCII Filenames

To successfully upload attachments whose filename comes from a language which uses a non-ASCII string, such as Japanese, from your web browser to either an FTP server or database table, Oracle recommends running the application server in an environment that supports non—ASCII character languages.

If the destination for your attachment is an FTP server, Oracle recommends that the FTP server also be running in an environment that supports the same language or locale as the filenames used. (The web server can be running on either an English environment or a non—ASCII character language environment.)

If your environment does not fully support non-ASCII characters, then the file-processing system will convert file names into names that are fully ASCII strings that, at upload time, will be passed back to the calling PeopleCode program rather than the orginal name of the file as selected by the end-user. This means that it may be more difficult for an end-user to later identify the renamed file for further processing, such as viewing.

Viewing Error Messages

If you want your user to be able to view error messages (such as the file is too large, the file was not found, or the data in the file is corrupt) when attaching files, add one of the following lines of PeopleCode to the FILE_ATTACH_WRK record, to the ATTACHADD field, immediately above the line &ORIGSYSFILENAME = &ATTACHSYSFILENAME;:

&MESSAGE_LVL=1;

Or

&MESSAGE_LVL=2;

Configuring Multiple Application Servers to Support File Attachments

When you have multiple application servers, the /tmp/PSFTP/DomainIDxxx directory is created by the first application server process to run on your application server. The assumption is that all such application server processes run with the same user ID. However, a single computer might run application servers with many user IDs.

The default /tmp/PSFTP is created so that all users can access it (777 permissions, or drwxrwxrwx). For proper security, however, the DomainIDxxx directory is created so that only that application server can access that directory (711 permissions, or drwx--x--x). If multiple users need read and write access, you must change the permissions.

When using file objects, the service request can be sent to different application servers, so that if a file was created under one application server machine environment and that service is next serviced on a different application server machine, the file might not exist. Therefore, your application should check that the file is open, particularly when returning from a user interaction (since the process of the handling user interface results in a serialization/deserialization of all the transaction states.)

Note. The file attachment functionality does not support running multiple application servers concurrently to support the same application. This can happen if a load-balancer is used or your web server is configured to do Jolt Session Pooling. Because a file attachment operation is such a complex process, if the application switches application servers in the middle of such an operation, the operation may fail.

Viewing File Attachments

When viewing a file attachment, the browser invokes a viewer based on the content-type Multipurpose Internet Mail Extensions (MIME) category sent in the response header from the web server.

For example, if the user tried to view an MP3 file, the response header sent to the browser by the web server would indicate the audio/MPEG content type.

HTTP/1.1 200 OK 
Server: Microsoft-IIS/5.0 
Date: Mon, 01 Oct 2001 21:25:51 GMT 
Content-Type: audio/mpeg 
Accept-Ranges: bytes 
Last-Modified: Mon, 01 Oct 2001 21:00:26 GMT 
ETag: "78e21918bc4ac11:cc8" 
Content-Length: 60

Notice that the Content-Type is audio/mpeg. The browser uses this MIME type to determine that a viewer for audio/MPEG is appropriate. If the web server did not send this content-type header, the browser would not be able to determine the nature of the file being transmitted, and it would be unable to invoke the correct application. The browser would try to display the file as text/plain, which might be the wrong assumption.

The web server determines the MIME type by looking at the extension of the attachment file and mapping it to a MIME type. The mapping is done in the following ways, depending on the web server and platform:

• For Microsoft IIS, the mapping is done by using the standard Microsoft Windows extension association mechanism.

• For Apache, the mapping is done by editing a configuration file called mime.types.

• For WebLogic, a file called web.xml does the mapping.

The file web.xml is located in the WEB-INF subdirectory. This file contains a section that looks like this:

  </mime-mapping> 
  <mime-mapping> 
    <extension> 
      doc 
    </extension> 
    <mime-type> 
       application/msword 
    </mime-type> 
  </mime-mapping> 
  <mime-mapping> 
    <extension> 
      xls 
    </extension> 
    <mime-type> 
       application/vnd.ms-excel 
    </mime-type> 
  </mime-mapping>

Let's say you want to add an extension that causes log files to be interpreted as plain text files.

To determine the correct MIME type, check Request for Comments (RFC) documents 2045, 2046, 2047, 2048, and 2077, which discuss internet media types and the internet media type registry.

After checking the RFCs, you determine that the correct MIME type is text/plain. The following is an example of code you would add to the previous section of web.xml:

  </mime-mapping> 
  <mime-mapping> 
    <extension> 
      doc 
    </extension> 
    <mime-type> 
       application/msword 
    </mime-type> 
  </mime-mapping> 
  <mime-mapping> 
    <extension> 
      log 
    </extension> 
    <mime-type> 
       text/plain 
    </mime-type> 
  </mime-mapping> 
  <mime-mapping> 
    <extension> 
      xls 
    </extension> 
    <mime-type> 
       application/vnd.ms-excel 
    </mime-type> 
  </mime-mapping>

Once you save the file, the log extension is associated with the content type of text/plain.

Note. You must restart the WebLogic server before these changes are recognized.

Similar mappings can be achieved on other web servers, such as IBM WebSphere or Netscape Enterprise Server.

See your web server documentation.

Note. When trying to view the objects, the extension must exactly match what is set up in the web.xml file. This value is case-sensitive. If the object view appears garbled, chances are that either the extension is not set up in the web.xml file or there is a case mismatch.

Using Chunking with Attachments

When writing data to database tables, the data is automatically chunked, or stored, in different rows of the database table. The size of each chunk is determined by the Max Chunk File Size field on the PSOPTIONS page.

Because each file is chunked, you cannot pull data directly from the table. You should use the provided attachment functions, which automatically put the data back together into one file for you. Because the chunk size is stored with the file, if you change the system chunk size you can still retrieve files with different chunk sizes.

See PeopleTools Options.

See Also

CopyAttachments

AddAttachment

PutAttachment

PeopleTools Options

Using the Microsoft Windows NT 4 FTP Server

If you are using FTP and running Microsoft Windows NT 4 with the standard Microsoft FTP server, you must consider this point:

Non-Microsoft FTP server programs on Microsoft Windows NT, the Microsoft Windows 2000 FTP server, as well as all UNIX FTP servers, execute a chroot command to the home directory of the FTP server automatically.

However, the Windows NT 4 FTP server does not follow this behavior. When an FTP client connects to the Microsoft FTP server, the client can gain access to the entire drive, not just the FTP home directory.

For example, suppose you are running the Microsoft Windows NT 4 FTP server and the home directory is c:\inetpub\ftproot.

When you log in, your current directory is c:\inetpub\ftproot.

When you type the following, your current directory becomes c:\:

cd \

If you are not running the Microsoft Windows NT 4 FTP server and the home directory is the same—c:\inetpub\ftproot—then when you type the following:

cd \

Your current directory becomes c:\inetpub\ftproot.

You must know the FTP server associated with the URL.

If you are using a Windows NT 4 FTP server or another FTP server that shows this behavior, your URLs should have the following format: ftp://ftp:ftp@NT4system/inetpub/ftproot/tmp. Note that the home directory of the FTP server must be appended to the address.

If you are not using these sorts of servers, your URLs should have the following format: ftp://ftp:ftp@NT4system/tmp.

Using Non-DNS URLs with UNIX

The URL passed to file attachments generally has the following format: ftp://user:pass@systemname/dir1/dir2.

This format assumes that the domain name systemName can be resolved with DNS.

Some Microsoft Windows systems can resolve systemName even when it cannot be resolved with DNS. However, some UNIX systems cannot these resolve names.

If you are using a UNIX system and the domain name cannot be resolved with DNS, then use the IP address. The following example assumes systemName has the IP address of 123.123.123.123: ftp://user:[email protected]/dir1/dir2.

To determine the IP address, try to ping the system by typing the following:

ping systemName

You should use domain names that can be resolved. Only use IP addresses when absolutely necessary.

Using Attachment Functions

To store file references in a database, you must create a record that receives the file references. This record can be called anything, but it must have the FILE_ATTACH_SBR subrecord included in it. You must also include the FILE_ATTACH_WRK work record in your component when using the file attachment functions

Follow these steps to develop an application that uses file attachments:

1. Determine how the file will be stored.

   The file can be stored on an FTP server or in the database.

   • Storing the file on an FTP server.

     Although you can either have the user enter the FTP location or you can store the FTP location in a URL definition using the URL Maintenance page (People Tools, Administration, Utilities, URL Maintenance), Oracle recommends that you use a URL definition since that approach gives you the flexibility of later changing the storage location of your files without having to modify your PeopleCode.

     Use a URL definition if you need to mask the FTP user ID and password. If the URL object only contains a portion of the string that will be passed to the file-processing function, for example, if the the user ID and password are added the full URL at runtime, then you can use the GetUrl function to get the underlying FTP URL.

     You must use a URL definition with secure FTP (FTPS).

   • Storing the file in the database.

     To store the file in the database, create a new target record, for instance MYAPP_ATTACH_CONTENT, and insert the FILE_ATTDET_SBR subrecord into it. The target record must have no other fields. If you need to store other information, store it as part of the file reference, as described in the next step, or create another record and use it in the component.

2. Create a custom record to store the file reference information and any additional information, for instance MYAPP_ATTACH_REF. The new record must include the subrecord FILE_ATTACH_SBR. Add fields for any other information related to the transaction you want to store. Your program should populate this record with the file name, URL, and any other information you want to save.

3. Clone the FILE_ATTACH_WRK record to create a custom derived/work record. Give it a unique name, for instance MYAPP_ATTACH_WRK. Save the PeopleCode with the new record. You can use the sample PeopleCode on the record as the basis for your own application.

   Note. The FILE_ATTACH_WRK record is delivered as a sample only. It is not intended for use as part of an application running in production unless it is customized.

   It is important to create your own program. Oracle may change the delivered sample PeopleCode in future releases. Any application that directly uses the sample PeopleCode may fail. Customizing your application makes it easier to manage during upgrades and your PeopleCode can be reused in other components that use file attachment functionality.

4. Use the records you created in the previous steps to create the file attachment page and component.

   The custom derived/work record has fields with FieldChange PeopleCode that you can use for Add, Detach, Delete, and View buttons.

   Add PeopleCode, probably at the component record field level, to invoke the underlying functions in the custom derived/work record when the user clicks on one of the buttons.

5. Create a URL definition (PeopleTools, Administration, Utilities, URL Maintenance). Use one of these formats:

   • ftp://ftpuser:password@ftpMachineName/

   • ftps://ftpsuser:password@ftpsMachineName

   • record://MYAPP_ATTACH_CONTENT

   See URL Maintenance.

The following table summarizes the delivered record definitions for use in a file attachment application:

The FILE_ATTACH_SBR subrecord contains the following fields:

No PeopleCode is associated with this subrecord. You should include this subrecord in target records for using attachments.

The FILE_ATTACH_WRK work record contains the following fields:

For storing attachments in the database, you must create a record that receives the attachments. This record can be called anything, but it must have the FILE_ATTDET_SBR subrecord included in it, and it can contain no additional fields. The subrecord FILE_ATTDET_SBR has the following fields:

Secure File Transfer Using FTPS

When transferring a file using secure FTP (FTPS), the file attachment function expects the URL parameter to be a URL ID, not a string. Use the URL Maintenance page (PeopleTools, Utilities, Administration, URLs) to create a URL definition.

When you create a URL for FTPS you will need to enter values for Certificate Alias, Verify Host, Verify Peer and SSL Usage.

See Adding Secure FTP (FTPS) URLs.

Coding Considerations Using the Attachment Functions

All file attachments are performed using PeopleCode built-in functions, such as AddAttachment, ViewAttachment, GetAttachment, and so on. These functions move a file into and out of a file storage system, either the database or a file system. The file system movement is done using the FTP protocol.

Because these functions abstract the storage of the attachments, you can use either database or FTP file systems. The system to be used is determined by the URL passed as the first parameter in the attachment built-in function. The actual value of the URL is maintained on the URL Maintenance page. You don't know whether users will store attachments in a database or a file system.

The following PeopleCode functions are used with administration work:

• DeleteAttachment deletes unwanted attachments.

  This cannot be rolled back. For example, suppose a user selects to view an attachment, and then cancels before the attachment appears. A copy of the attachment may still be in the user's temp directory.

• CopyAttachments moves all of the attachments specified from one file storage system (database or FTP) to another (database or FTP).

• CleanAttachments is an audit-like script that removes attachments that exist in the database that are not referenced by any record field.

If you only have a few files to copy using CopyAttachments, you may want to use the CopyFiles page (part of the PeopleTools Administration pages). The CleanAttachments function is also available from this page.

To schedule a regular job to clean up orphaned file attachments, you can use the Application Engine program CLEANATT84.

Store all references to attachments using the standard ATTACHSYSFILENAME field. Do not reuse this field to store incomplete or nonstandard versions of the name. For example, you should not store the full URL version of the attachment in this column and then use PeopleCode to parse the URL before invoking attachment commands. While this method may work for get or put attachment calls, the Clean Attachments function deletes any file stored in a table that does not have a corresponding reference stored in ATTACHSYSFILENAME.

Scanning Attachments for Viruses

Files uploaded using AddAttachment can be scanned for viruses. See Enterprise PeopleTools 8.50 PeopleBook: PeopleCode Language Reference, “AddAttachment” for details.

See AddAttachment.

Copying Files Using the File Attachment Functions

Use the CopyAttachments function to move a group of attachments from one archive to another, such as when you want to move files from one FTP server to another or if you want to move all the files in a database record onto an FTP server. It does not create new system file links.

If you want to create a new file attachment by making a copy of an existing file attachment (both the system file link and the file itself), do the following:

1. Use GetAttachment to retrieve the file from the repository (either from the FTP server or from a database record) and place it on your application server (or process scheduler system if you are using Application Engine).

2. Use PutAttachment to move the file from the application server (or process scheduler system), and place it into the new archive location.

3. Save the new system file link that you passed as a parameter to PutAttachment so that you can subsequently use it. In general, this action is part of the page transaction when you save the record that has the FILE_ATTACH_SBR subrecord in it.

Errors in CopyAttachments

Because CopyAttachments is designed to work with multiple files, to track errors when using CopyAttachments set your PeopleCode trace to 2112 and your SQL trace to 15 so that errors will be written to the appropriate trace files.

See Also

Copy File Attachments

File Name Considerations

If a user specifies a local filename (using the functions GetAttachment , DetachAttachment , PutAttachment). with any of the following characters, the process will be stopped and an error (%Attachment_Failed) will be returned to user. The actual error message can be found in the logs.

• * (asterisk)

• : colon)

• " (quotation mark)

• < (less than symbol)

• > (greater than symbol)

• ? (question mark)

When the file is transferred, the following characters are replaced with an underscore:

• space

• semicolon

• plus sign

• percent sign

• ampersand

• apostrophe

• exclamation point

• pound sign

• dollar sign

Converting User Filenames

If a user attaches a file through the browser dialog box generated by the AddAttachment function, the filename may be converted before that filename is returned to the AddAttachment call for storage.

For example, the file My Resume.doc is returned through the AddAttachment parameter as My_Resume.doc, with the space changed to an underscore.

No comparable mapping occurs for user filenames with the PutAttachment function, which requires the user filename as an input parameter rather than an output parameter that is returned to the user.

Instead, before storing the ATTACHUSERFILE value, you should invoke code similar to this:

   &ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, " ", "_"); 
   &ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, ";", "_"); 
   &ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "+", "_"); 
   &ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "%", "_"); 
   &ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "&", "_"); 
   &ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "'", "_"); 
   &ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "!", "_"); 
   &ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "@", "_"); 
   &ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "#", "_"); 
   &ATTACHUSERFILE = Substitute(&ATTACHUSERFILE, "$", "_");

Naming URLs and File Attachments

If you change a URL on the URL Maintenance page from FTP to record, you may have to reboot your application server before you can use the URL in your PeopleCode program.