Content

Overview

Unicode Character Support

Locale and Time Zone

User

Date and Time Formats

Database

About Database Time

Absolute versus Relative Dates

Date-Only Columns

Actions and Public APIs

Actions

The sapphire.* Packages

JavaScript

 

Translation

Upgrade Considerations

Coding Considerations

Guidelines

Ensuring an M18N-compliant Implementation

Testing an M18N-compliant Implementation

Pitfalls, Pratfalls, and Examples

 

Overview

Top ../images/arwup.gif (846 bytes)

This topic summarizes LabVantage support for M18N (Multinationalization). LabVantage provides user-specific language, time zone, and date/time/number formatting across multiple locales.

 

Unicode Character Support

Top ../images/arwup.gif (846 bytes)

LabVantage provides the following support for the UTF-8 encoded Unicode character set (8-bit Unicode character encoding according to the Unicode Transformation Format-8 standard):

Area of Functionality Description of Support
LabVantage Data Model Uses character length as the column width, regardless of database encoding.
Web Page Designer All LabVantage Page Types, Elements, Layouts, and their Properties use UTF-8 encoding.
Security and System Configuration All of these tools use UTF-8 encoding.

UTF-8 encoding is defined in:

ISO 10646-1:2000 Annex D
IETF RFC 3629
Section 3.9 of the Unicode 4.0 standard

 

Locale and Time Zone

Top ../images/arwup.gif (846 bytes)

User

 

Each user can specify his locale and time zone in the User Maintenance Page. When these are specified, each user sees retrieved data formatted in the locale defined by his local machine, with time adjusted to the time zone defined by his local machine (and shown in the header of the Layout that hosts LabVantage pages). Exceptions to this are that the Application Server's time is shown:

When defining a Schedule Rule inIn the Scheduler. Application Server time is displayed if LabVantage detects that the user's local machine differs from that of the Application Server.
When date fields are explicitly marked as not requiring a time zone adjustment. By default, these columns show only the date. If mistakenly overridden in a configuration, these fields would always show the time as 0:00.
When specifying the start and stop times for a recurring Task in the Task Maintenance page.

If a user's time zone is not specified in the User Maintenance Page, LabVantage uses the Application Server's time. This has been established as the default to maintain backward compatibility (prior to V3.4/R4.3, the Application Server specified locale and time zone for all users).

LabVantage provides a choice of all locales and time zones supported by Java (java.util.* and java.text.*).

When locale is set in the User Maintenance Page, all Web Page Designer Layouts, Page Types, and Elements show date and time according to the user's locale and time zone settings. Specifically, the following areas are sensitive to the user's locale and time zone:

Number and Date data types in all Page Types. This includes queries (and their arguments) executed in Web pages.
NOTE:Data Entry Page Types differ subtly in that number/date/time format is defined by the Parameter List (in the ParamListItem detail table).
List Element sorting. The sorting occurs after any language translation has been done.
Maint Element client-side Validation, i.e., the Validation feature dynamically generates a JavaScript regular expression.

Date and Time Formats

 

The Date Format Policy lets you define system-wide date and time formats.

Database

 

About Database Time

The Database and Application Server must be in the same time zone.

Database time and Application Server time must be synchronized.

The LabVantage database API calls use the database time (via the built-in SYSDATE function), since the database cannot determine the Application Server's time zone. Since LabVantage requires that the database time zone be set to the Application Server's time zone, the two will always be synchronized.

Dates can be forced or overridden in audit triggers. However, it is impossible to determine if a SQL statement might happen to contain a SYSDATE that would get the database time rather than the Application Server time.

The locale of the database machine can be set as you wish. Unless otherwise required, we recommend setting the database locale to that of the Application Server.

In the Connection table, the timezoneoffset column specifies the offset between the user's client and the Application Server. When a user logs in, timezoneoffset is specified as a number with units of minutes. This is useful to handle adjustments such as U.S. Daylight Savings Time.

Database date/time format is independent of LabVantage's locale-sensitivity. Remember that date and number are stored in the database as an object... not a string. The database has its own internal format for these. Interfaces such as SQLPlus format it for display purposes.

Absolute versus Relative Dates

Date-type Query arguments passed to queries are converted to the Application Server's locale and time zone, even though the user inputs reflect his locale and time zone. Accordingly, if a user enters an absolute date in response to a date-related query, it will be the same for all users. The requirement here is that the pattern used in the to_date function must be consistent with the Application Server's locale: to_date('[startdate]', 'mm/dd/yy hh:mi:ss am').

On the other hand, if a user enters a relative date, this could be different for users in different time zones. LabVantage provides two variables and the userdate function to convert the user's time zone to that of the Application Server:

To convert Application Server time zone (a persisted column date) to a user's time zone:
DBMS Function Syntax
Oraclelv_rset.userdate ('[%connectionid%]', <columnname>)
MS SQL Serversapphire.lv_rset_userdate ('[%connectionid%]', createdt)

The [%connectionid%] variable passes the connectionid to the RSet routine.

Note that MS SQL Server owners must specify the database owner. SQL Server functions are normally created as dbo, so that no owner is required. However, when owned by dbo, it cannot access a table not owned as dbo without qualifying the table as owner.table, i.e., hard-code the owner in the function. Although a function exists to return the session user, you can not dynamically build the owner.table query. SQL Server does not allow you to execute the call to do dynamic SQL from within a function (unless you've written it into a .dll). This means that you can define queries using this function (you know your owner name), but LabVantage can not provide standardized queries that include this function (we do not know your owner name).

To convert Application Server date to a user's time zone, use the [%usersysdate%] variable. This returns the equivalent of a sysdate constant but in terms of the user's local time zone.

Example 1:

To retrieve today's Samples:

lv_rset.userdate('[%connectionid%]', createdt) >= trunc([%usersysdate%])
and lv_rset.userdate('[%connectionid%]', createdt) < trunc([%usersysdate%] + 1)

Example 2:

The lv_rset.userdate function can be utilized in a query WHERE clause for adjusting a date column value (in the context of the query) to the user's session. You supply the connectionid and column. For example:

querywhere = 'lv_rset.userdate (''[%connectionid%]'', createdt) = trunc (sysdate)'

means created "today", as defined by the user's time zone.

LabVantage's relative date format is a Java Regular Expression.

Date-Only Columns

 

"Date-only columns" display date without time:

Date Date-Only
10/30/07 1:22:00 PM 10/30/07

Consider an entry such as "birthdate". If viewed from a user located west of the time zone that entered the birthdate, this would display the previous day.... unless you specify it as a date-only column.

Date-only columns are not adjusted to the user's time zone. This facilitates issues regarding presentation, persistence, and querying.

Here is a summary of date-only column implementation:

Date-only columns are available for use wherever dates are used.
By default, the maint and linkedsdimaint Elements use the date-only column format.
All LabVantage Core Elements and Actions support date-only columns... meaning they know to display the date column in date-only format without time zone adjustments. The Actions assume the inputs are in the Application Server's time zone, not the user's time zone).

Internally, date-only columns persist in a Date (date with time) column, but the time is set to 0:0:0.

Actions and Public APIs

 

Actions

Action input properties are locale-sensitive.

The sapphire.* Packages

The sapphire.* packages contain classes that support M18N by converting database format. However, if you use your own SQL query, use a prepared statement with a bind variable. The JDBC driver will then handle it.

Class Description
sapphire.util.ConnectionInfo An instance of the ConnectionInfo object contains the user's locale and time zone (pass it into the DataSet constructor). This can be applied to Actions, Web pages, and standalone Java clients.
sapphire.util.DataSet Pass a ConnectionInfo instance into the DataSet constructor, then call the getValue and setValue methods (sets/gets number into/from the DataSet object). Example usage: passing a Date object into a Calendar object.
sapphire.accessor.QueryProcessor
sapphire.accessor.SDIProcessor
The returned DataSet is locale- and time zone-sensitive. For example, data returned by the QueryProcessor.getSqlDataSet() method is locale-and time zone-sensitive:

QueryProcessor qp.getSqlDataSet()

These handle date formatting issues:

Class/Method Description
sapphire.util.DataSet.getValue See Date Format Policy.
sapphire.util.M18NUtil
JavaScript

LabVantage built-in client-side JavaScript validation routines are locale-sensitive.

LabVantage provides the following JavaScript variables for custom validation functions:

Variable Description
decimalSeparator Numeric delimiter for decimals
groupingSeparator Text delimiter for strings
sapdateformat LabVantage Date Format

 

Translation

Top ../images/arwup.gif (846 bytes)

LabVantage provides functionality to create and translate languages, with translation applied directly to data. For example, a Parameter List's data details can be defined in one language, but presented to the user in his own language during data entry.

For the entire LabVantage application, you can:

Define a new language (by adding a Language SDI)
Add, translate, edit, and search for text strings in the language

These features are described in the Translation page.

For the Web Page Designer Layouts, Page Types, and Elements, you can translate text of:

Property values
Column names

This is described in the Translating Properties section of the Web Page Designer Properties Overview.

Also see the gwtdataentrygrid Element for specialized translation functionality available in GWT Data Entry pages.

 

Upgrade Considerations

Top ../images/arwup.gif (846 bytes)

Only the purely out-of-the-box R4 Enterprise LIMS can assign locale and time zone after upgrading from V3.4/R4.4 or earlier.

LabVantage M18N support is backward-compatible, in that an upgraded system will have no locale or time zone associated with users (the default is the Application Server's settings). Accordingly, everything should behave as before the upgrade.

However, in custom implementations, you can not assign locale and time zone to users after upgrading. In order to work with user-specific locale and time zone profiles, changes must be made for certain kind of queries, custom Actions and custom JSPs on a per-case basis, i.e., you must analyze the system and determine whether M18N implementation is feasible based on the scope of the changes, retesting requirements, and potential benefits.

 

Coding Considerations

Top ../images/arwup.gif (846 bytes)

Guidelines

A summary and other tidbits for page developers:

Every LabVantage JSP, Action, and SDC Rule is executed on behalf of a connection with a connectionid. The connectionid is associated with a user. The user is associated with his own locale/time zone (unless defaulted to the Application Server's locale/time zone).
Your code does not need to handle locale/time zone issues if you use LabVantage's locale- and time zone-sensitive tools. For example, if your custom Action takes inputs from data entry and passes them to a System Action, all issues are automatically handled for you.
Web Page Designer Elements always present date and number fields formatted according to the user's locale and time zone.
LabVantage always assumes the date and number type of Action inputs is formatted according to the user's locale and time zone, regardless of where that call is coming from. In other words, regardless of if an Action is called from a JSP, the public API, or a stand-alone Java application, the Application Server knows where the call came from, so it appropriately formats the user's locale and time zone.
If a JSP adds an Action to the ToDo List, the database stores the locale and time zone of the user who added it to the ToDo List.

If an Action is added to the ToDo List, the database stores the Application Server's time.

Use objects whenever you can (the JDBC driver will then handle it). Format to a string only when absolutely necessary.
Format data immediately before presenting it to the user.
Ensuring an M18N-compliant Implementation

A check list to ensure M18N compliance:

Properly specify queries when using absolute and relative dates (as shown in Database).
Note that Action inputs are formatted according to the calling connection's locale and time zone.
If rendering data using a custom JSP, ensure data rendered to the user are formatted using his locale and time zone.
Client-side JavaScript validations must take user's locale into account.
Validate entered strings before passing them to the Application Server.
Convert any inputs/outputs to/from a 3rd-party API to LabVantage's locale-sensitive format.
Testing an M18N-compliant Implementation

A check list for testing your implementation:

Deploy the application to an Application Server set to the customer's locale. This has to be agreed upon well before starting the project. It would be best if you can develop the implementation using the same server locale.
Set up at least two users with different locales and time zones. One user utilizes "," as decimal separator, the other uses ".". You can mix and match locales and time zones. Also use this technique to test background tasks executed using a connection associated with users utilizing different locales and time zones.
Testing more than two different locales does not significantly help detect problems.

Pitfalls, Pratfalls, and Examples

Here are four examples of bad practice, good practice, and proper implementation:

1.
Consider the following bad coding practice. It's bad practice because java.util.HashMap is not locale-sensitive. This is written strictly for a US locale:

double estimate = (Double.parseDouble(estFinalActivity) * (newFermHr)) / (Float.parseFloat(fermDays) * 24);
// limits the precision of the estimate to 2 decimal places
double newEstimate = rint(estimate, 2);
//Sets up and executes AddSDI Action
HashMap hm = new HashMap();
hm.put("sdcid", "Ferm_Details");
hm.put("estimate", String.valueOf(newEstimate));//this is the pratfall
hm.put("ferm_masterid", keyId);
ab.setAction("addSDI" + i, "AddSDI", "1", hm);

To make this locale-sensitive, use connectionInfo to provide the "estimate" value (an instance of the connectionInto object contains connection information for the user):

hm.put("estimate", sapphire.util.FormatUtil.getInstance( connectionInfo ).format( new BigDecimal( newEstimate ) ) );

2.
Here's a code snippet showing another bad coding practice... the first line of code is hardcoded for US locale. This will not work for a user in Cucamonga:

SimpleDateFormat format = new SimpleDateFormat("MM/dd/yy hh:mm:ss aaa");
String getSamplingDate = df1.format(sdf.parse(samplingStart));//samplingStart from user input
Date date1 = format.parse(samplingStart);
Calendar c = Calendar.getInstance();
c.setTime(date1);
Date dt1 = c.getTime();

3.
The next example attempts to use JavaScript to calculate a date range from user input (assume that a hidden iframe will calculate and populate the field).

function calculateSum( fieldAid, fieldBid, sumfieldid ) {

fieldA = document.getElementById( fieldAid );

fieldB = document.getElementById( fieldBid );

fieldSum = document.getElementById( sumfieldid );

if ( fieldA.value.length > 0 && fieldB.value.length > 0 ) {

numA = fieldA.value.replace( decimalSeparator, '.' );

numB = fieldB.value.replace( decimalSeparator, '.' );

sum = new Number( numA ) + new Number( numB );

//skipped need to feature out precision

sum = sum.toFixed( 2 );

//format back

fieldSum.value = new String(sum).replace( '.', decimalSeparator );

}

}

The problem here is that a round-trip to the Application Server is required to convert locale and time zone. This is far too complex to code in a JavaScript function.

4.
Here's a slightly more advanced example with two date fields... one from user input, the other from a calculation based on the user input. When the user chooses a request date, the due date is automatically populated with request date plus 7 days and 8 hours:

The JavaScript (client-side):

document.write( "<div style=\"display:none\" id=\"validationDiv\"></div>" );

function calculateDueDate( requestDate, dueDateFieldId ) {

//document.getElementById( dueDateFieldId ).value = requestDate;
//If you tried the previous line, you would have to add 7 days and 8 hours, then consider that the user's locale and time zone be properly formatted. This is too complex for JavaScript... it has to go server-side...
document.getElementById( dueDateFieldId ).className = 'ValidationFail';
src = 'rc?command=file&file=CalculateDate.jsp&requestDate=' + requestDate + '&dueDateFieldId=' + dueDateFieldId;
//The adventurous could also use AJAX here.
document.getElementById( "validationDiv" ).innerHTML = "<iframe style=\"display:none\" id=\"validationFrame\" src=\"" + src + "\"></iframe>";

The JSP (server-side):

<sapphire:page>

<% //Construct locale timezone DataSet object
ConnectionProcessor cp = pageinfo.getConnectionProcessor();
DataSet ds = new DataSet( cp.getConnectionInfo( cp.getConnectionid() ) );
//parse input field into calendar object
ds.addColumn( "date", DataSet.DATE );
ds.addRow();
ds.setValue( 0, "date", request.getParameter( "requestDate" ) );
Calendar newDate = ds.getCalendar( 0, "date" );
//calculate duedt
newDate.add( Calendar.DATE, 7 );
newDate.add( Calendar.HOUR, 8 );
ds.setDate( 0, "date", newDate );
//render duedt to input field with correct locale and timezone
out.print( "<script>parent.document.getElementById('pr0_duedt').value = '" + ds.getValue( 0, "date" ) + "';" );
out.print( "\nparent.document.getElementById('pr0_duedt').className = ''" );
out.print( "</script>");
%>

</sapphire:page>

A user with Hindi locale and Asia/Calcutta time zone will then see this: