Remove Trailing Blanks from Legacy Columns with the IBM OLE DB Providers

Published: August 11, 2010

by Michael Sansoterra

It's no secret that many legacy tables in DB2 for i contain fixed-width character columns. If you present this legacy data to users using common tools such as Excel, Access, or even an application data grid control, they're likely to be annoyed. Why? Because everyone hates fiddling with trailing spaces that are part and parcel of fixed-width columns. If you have a 50-character address column with only 20 characters filled, that leaves 30 annoying trailing spaces to remove.

Here is a picture of this annoying phenomenon shown using the classic QIWS/QCUSTCDT table displayed within an editable Windows Presentation Foundation (WPF) DataGrid:

Notice that for the "LSTNAM" column the spaces are intrusive. Depending on the grid tool, these trailing spaces often prevent insertions in the data until the spaces are removed. Furthermore, using the End key will take you to the end of the spaces instead of the end of the data. Yuck!

One workaround for this irritation has been to create a query or view that wraps all fixed-width columns with the RTRIM function. This solution is acceptable, but it's cumbersome to produce a query or view for this ignoble purpose. Moreover, sometimes (again depending on the tool) using a view or query in this manner makes the data read-only.

The good news is, if you're using System i Access V6R1 or higher and your middleware to the IBM i (a.k.a. AS/400) is either of the IBMDA400 or IBMDASQL OLE DB providers, there is a new property called "Keep Trailing Blanks." When set to False, this setting will cause the provider to automatically remove all trailing blanks on fixed-character columns before delivering it to the application.

In other words, the provider treats fixed-character columns as though they were trimmed variable-length columns. In cases where this data is sent back to the database, of course, the trailing spaces will automatically be appended so there are no negative side-effects.

This new custom property lives within the connection object and can be set in the connection string:

Provider=IBMDA400;System=iSeries.mycompany.com;Keep Trailing Blanks=False;

Fortunately this new property is set to False by default so it really doesn't need to be specified. However, it is still a good idea to specify this property for the purpose of documentation, just in case developers are unaware of this new feature and its behavior.

While most often you'll want this property set to False, there may be times when the trailing characters should come through from the database (one such possibility includes making a fixed width export file). If you need both behaviors within your application, you'll have to maintain two separate connections: one connection with this property set to False, and the other with it set to True. It would be nice if this property could be overridden at the command object or recordset object level to have the best of both worlds while using the same connection.

Finally, I've looked at the documentation for the ODBC driver and .NET managed providers and have not yet discovered a similar property. Hopefully IBM will add this utilitarian feature to these System i Access components as well.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot