• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Remove Trailing Blanks from Legacy Columns with the IBM OLE DB Providers

    August 11, 2010 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Maxava

    Migrating to New IBM i Hardware?

    Whether you are moving to POWER9, Power10, or a cloud hosted system MAXAVA HAS YOU COVERED!

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Maxava:  Know your HA status wherever you go with MAXview
    Linoma Software:  Secure and automate data transfers with GoAnywhere Director
    COMMON:  Join us at the Fall 2010 Conference & Expo, Oct. 4 - 6, in San Antonio, Texas

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Windows Trumps IBM i for Trucking Software Upgrade at C.R. England Power 750: Big Bang for Fewer Bucks Compared to Predecessors

    Leave a Reply Cancel reply

Volume 10, Number 24 -- August 11, 2010
THIS ISSUE SPONSORED BY:

ProData Computer Services
SEQUEL Software
WorksRight Software

Table of Contents

  • Remove Trailing Blanks from Legacy Columns with the IBM OLE DB Providers
  • How Did I Do That?
  • Admin Alert: Six Things You May Not Know About i/OS Passwords
  • Generic Database Access with .NET 2.0
  • Spaces, Braces, and Semicolons
  • Admin Alert: High Availability Eliminates Disaster Recovery. . . Right?

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • N2i Gains Traction Among IBM i Newbies
  • Realizing The Promise Of Cross Platform Development With VS Code
  • 2023 IBM i Predictions, Part 3
  • Four Hundred Monitor, January 25
  • Join The 2023 IBM i Marketplace Survey Webinar Tomorrow
  • It Is Time To Have A Group Chat About AI
  • 2023 IBM i Predictions, Part 2
  • Multiple Vulnerabilities Pop Up In Navigator For i
  • Participate In The 2023 IBM i Marketplace Survey Discussion
  • IBM i PTF Guide, Volume 25, Number 4

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.