Setting Data Field Length Checking

The database character set determines the way that PeopleTools interprets the column length that is defined in Application Designer.

If you create a Unicode database, the field length, as shown in Application Designer, indicates the maximum number of Unicode BMP characters that are permitted in the field, regardless of the Unicode encoding that is used by the database. Some database platforms, such as Oracle with byte semantics, use byte lengths to measure column sizes when operating in a Unicode database, while others use character lengths.

When the database uses byte-sized column lengths, the PeopleSoft system sizes the database columns based on the worst-case ratio between bytes and characters in the Unicode encoding that is used by your database. For example, if the AL32UTF8 character set is used by Oracle with byte semantics, the worst-case character-to-byte ratio when running against an Oracle Unicode database is 1:3. So, column size is tripled when creating a Unicode database on Oracle. A field that is defined in Application Designer as a CHAR(10) is created on an Oracle Unicode database with a type of VARCHAR2(30). This tripling of the maximum column size does not affect the actual size of the database, because variable length character fields do not reserve space in the database.

Other database platforms use character-based column lengths whose sizes represent the maximum number of Unicode characters instead of bytes that may be stored. Examples of this implementation are the NCHAR data type in Microsoft SQL Server and the GRAPHIC data type in DB2 UDB for Linux, Unix, and Microsoft Windows.

If you create a non-Unicode database, the field length in Application Designer represents the number of bytes that are permitted in the field, based on the character set that you used to create the database. Therefore, a PeopleSoft Unicode database enables you significantly more space for character data within the database when dealing with ideographic languages, such as Japanese, that require one or more byte to store per character.

The following tables show some of the possible database encodings for database platforms that the PeopleSoft system supports in Unicode and non-Unicode and their effects on database column sizes. Each table shows the database representation and the worst case number of characters allowed in the character field for a character field defined in Application Designer with a length of 10.

This table shows the information for an Oracle database with byte semantics (used by PeopleSoft 8.9 applications and earlier):

This table shows the information for an Oracle database with character semantics (used by PeopleSoft 9.0 applications and later):

*: SBCS and Shift JIS are supported only when using BYTE length semantics, while Unicode is supported using CHAR length semantics. SBCS and Shift JIS are listed in the above table for comparison purposes only.

This table shows the information for an Microsoft SQL Server database with VARCHAR semantics:

This table shows the information for a Microsoft SQL Server database with CHAR semantics:

This table shows the information for all other databases:

The maximum number of characters that are permitted in a PeopleSoft field varies, depending on the character set of the database. Because all components of PeopleTools use Unicode for internal storage, by default, field length checking occurs in terms of Unicode character counts. This calculation is appropriate for Unicode databases and for any SBCS databases.

However, if you are using a non-Unicode DBCS, special length checking must occur each time you move off a field to ensure that the string that you entered fits in the database column when the string is converted to the database’s character set.

For graphically sizing page fields, PeopleTools uses the Unicode length of the field as defined in Application Designer. For example, if a field is defined in Application Designer as a 10-character field, page fields in both the PeopleSoft Pure Internet Architecture and the PeopleTools clients for Microsoft Windows allow 10 characters to be displayed unless manually resized by the developer.

However, if the database is encoded in a non-Unicode DBCS character set, such as Japanese Shift-JIS, special length validation must occur because the database column size is created relative to a byte count, not to a character count as is used by the simple field length validation.

For example, if a user enters 10 Japanese characters into a field that is defined as CHAR(10) in Application Designer, this string needs 20 bytes of storage in a nonshifting DBCS character set and 22 bytes of storage in a shifting character set. This 10-character input would fail insertion in both of these databases.

To address this issue, the page processor checks the Data Field Length Checking option on the PeopleTools Options page and performs character-set specific length validation against the contents of each field when the field is validated. Typically length validation occurs when the field’s FieldChange PeopleCode event fires, so the actual time of validation may differ, depending on whether your page uses deferred mode processing.

To enable or disable data field length checking:

1. Select PeopleTools > Utilities > Administration > PeopleTools Options.

   The PeopleTools Options page appears.

2. From the Data Field Length Checking drop-down list box, select a value based on the character set that you are using for the database:

   Field or Control	Definition
   Others	Select if you are using a Unicode encoded database or a non-Unicode SBCS database. This option prevents special field length checking, which is not required by these types of databases.
   MBCS	Select if you are running a non-Unicode Japanese database on any other platform. This option enables field length checking based on a nonshifting DBCS.

   Alternatively, you can set this option by altering the PSOPTIONS.DBLENGTHTYPE field in the PSOPTIONS table. In the PSOPTIONS.DBLENGTHTYPE field in the PSOPTIONS table, select a value from the following

   Field or Control	Definition
   N	Select if you are using a Unicode encoded database or a non-Unicode SBCS database. This option prevents special field length checking, which is not required by these types of databases.
   M	Select if you are running a non-Unicode Japanese database on any other platform. This option enables field length checking based on a nonshifting DBCS.

   Note: The non-Unicode DBCS settings are specifically oriented to Japanese language installations, because Japanese is the only language that the PeopleSoft system supports in a non-Unicode DBCS encoding. All languages other than Western European languages and Japanese are supported by the PeopleSoft system only when using Unicode encoded databases.

   Note: The value to specify data field length checking must be set correctly in order for PeopleSoft applications to perform correctly in a browser.

3. Click the Save button.