• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL Cross Platform Interoperability: The Proper Function

    February 7, 2007 Michael Sansoterra

    The code accompanying this article is available for download

    This tip has one very easy point: There’s lot of reusable SQL code out there, and System i (AS/400) DB2 developers can take advantage of it. Even though SQL’s promise of cross platform independence has never materialized, there are still enough similarities to make conversion between platforms worthwhile. All it takes is a little practice.

    This tip assumes you have some knowledge of writing SQL modules (triggers, functions or stored procedures in the SQL language). If you’re new to this arena, review the Control State section in the IBM SQL Reference guide to get a handle on the basic elements of programming entirely in SQL. SQL control statements are simply statements (as in any other computer language) that control the flow of logic in a program: conditional and looping statements, error handling, etc.

    The seeds for this tip started a few years ago when I was working on a SQL Server project. I was merging address data from two databases into one. Unfortunately, one database contained mixed case (actually “proper” case) information and the other uppercase information. The customer, of course, wanted to have everything in the resulting database in proper case.

    Proper case is similar to title case in that the first letter in each word is capitalized as shown here:

    This Sentence Is Written In Proper Case.

    THIS SENTENCE IS WRITTEN IN UPPER CASE.

    It was time to go home, but I was able to wow the customer by having the case conversion done in a manner of minutes. How did I do that? Easy, I searched the Internet and found a pre-written user-defined function (written in SQL Server’s T-SQL) that converted a text string to proper case. Problem solved.

    In case you’re not familiar with user-defined functions, they’re basically little pieces of user-written code that can accept zero or more inputs, perform logic on those inputs and return a single output (similar to an RPG subprocedure). Here’s an example of how to use the Proper user-defined function in SQL Server:

    Select Proper(‘2431 BROADWAY AVE’)

    Returns:

    2431 Broadway Ave

    Just a little while ago, I had a similar issue come up for a DB2 database. I needed to keep names in proper case in an employee table instead of upper case. I knew a scalar UDF to convert a string to proper case would do the trick again. My first thought was to either find one written for DB2 or to write one from scratch. But why reinvent the wheel? I whipped out the SQL Server version and in under 15 minutes I had the routine converted from T-SQL to DB2 SQL. (It would have gone even quicker except I first decided to test with DB2 for Windows, which always manages to cause me to stumble at least once per session!)

    The code accompanying this article contains the DB2 code for user-defined function “Proper”. Unfortunately, for the sake of giving credit to the original T-SQL author, I don’t know who wrote the original code, but I did manage to find a Web site that still publishes the example. For reference and comparison, click here to see the original T-SQL version of the code.

    Here are a few of the things that were done to convert the code:

    • Code lines were delimited with semi-colons
    • The IF conditional and WHILE looping structures were changed
    • I specified some required DB2-specific clauses
    • I made a few other minor changes, such as substituting the LENGTH DB2 built-in function for the LEN T-SQL built in function

    When working with DB2’s modules, do not limit your Internet searches for DB2 code. You can start looking for DB2 specific code, but if you can’t find it, someone has probably written code for another database platform that can easily be ported to DB2. As a developer, the best thing about the Internet for me has been code samples and re-useable code. Hey, why do the work if someone else has already done it (or most of it) for you?

    Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.



                         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
    Krengeltech

    When it comes to consuming web APIs on your IBM i, your options often boil down to one of two things:

    First, you end up having to rely on a variety of open source and non-RPG solutions. This adds developer complexity, taking away time that could have been better spent invested in other projects. Of course, open source software is free, but generally comes at the cost of no professional support, which adds an element of risk in your production environment. RXS is completely professionally supported, and is complemented by a staff of trained IBM i developers who can address your nuanced development challenges, head on.

    Second, if you choose not to pursue an open-source solution, you’re often left having to shake up your current program architecture with proprietary software, external dependencies, and partial RPG implementations – many of which are sub-par compared to RPG-XML Suite’s wide range of features. RXS aims to simplify the efforts of developers with tools like code generators, useful commands, and subprocedures written in 100% RPG – no Java. Because they are entirely RPG, the RXS subprocedures are easy to add to new or existing ILE programs and architecture, helping to cut your development time. RPG-XML Suite offers powerful capabilities in an accessible, easy-to-implement format.

    With RPG-XML Suite, you can accomplish a variety of complex tasks, such as:

    • Calling REST and SOAP web services from your IBM i
    • Offering APIs from your IBM i
    • Creating JSON & XML
    • Parsing JSON & XML
    • Text manipulation, Base64 encoding/decoding, CCSID handling, hashing and encryption functions, and more.

    To try RXS for yourself, we recommend a free proof of concept, which not only gives you access to all of RPG-XML Suite’s subprocedures and utilities but also includes a tailor-made software demonstration that can be used as a starting point for your future API implementations.

    For a free proof of concept, contact us at sales@krengeltech.com, or visit our website for more information.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Aldon:  ALM solutions to accelerate your application development
    COMMON:  Join us at the 2007 conference, April 29 – May 3, in Anaheim, California
    Maximum Availabilty:  Secure, cost-effect, real-time iSeries replication software

    Books on Sale at the IT Jungle Store: 30 Percent Off for 30 Days

    The System i Pocket RPG & RPG IV Guide: List Price, $69.95; Sale Price, $49.00
    The iSeries Pocket Database Guide: List Price, $59.00; Sale Price, $41.00
    The iSeries Pocket Developers' Guide: List Price, $59.00; Sale Price, $41.00
    The iSeries Pocket SQL Guide: List Price, $59.00; Sale Price, $41.00
    The iSeries Pocket Query Guide: List Price, $49.00; Sale Price, $34.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00; Sale Price, $27.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00; Sale Price, $34.00
    iSeries Express Web Implementer's Guide: List Price, $59.00; Sale Price, $41.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95; Sale Price, $56.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00; Sale Price, $62.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00; Sale Price, $34.00
    WebFacing Application Design and Development Guide: List Price, $55.00; Sale Price, $38.00
    Can the AS/400 Survive IBM?: List Price, $49.00; Sale Price, $34.00
    The All-Everything Machine: List Price, $29.95; Sale Price, $21.00
    Chip Wars: List Price, $29.95; Sale Price, $21.00

    Bottomline Hooks Into J.D. Edwards Via Oracle Fusion Middleware Faster i5 595 Rumored to Be Imminent

    Leave a Reply Cancel reply

Volume 7, Number 5 -- February 7, 2007
THIS ISSUE SPONSORED BY:

WorksRight Software
SEQUEL
RPG & DB2 Summit

Table of Contents

  • Opportunities, Not Problems!
  • SQL Cross Platform Interoperability: The Proper Function
  • Admin Alert: Selectively Sending Break Messages to Active Users

Content archive

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

Recent Posts

  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners
  • IBM Delivers More Out-of-the-Box Security with IBM i 7.5
  • Groundhog Day For Malware
  • IBM i Community Reacts to IBM i 7.5
  • Four Hundred Monitor, May 11
  • IBM i PTF Guide, Volume 24, Number 19

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.