Technology and Community

Jim O'Neil

Subscribe to Jim O'Neil: eMailAlertsEmail Alerts
Get Jim O'Neil: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

PowerBuilder: Article

IBM & Informix

Moving beyond standard SQL statements

IBM Informix SPL includes a RAISE EXCEPTION statement that allows a UDR to signal an exception condition that may be handled within the UDR or propagated to the client that invoked the routine. The RAISE EXCEPTION statement allows the UDR author to return a system or user-defined error number. If the special error value of -746 is used, a custom message can also be returned to the client.

When invoking a UDR in embedded SQL that raises an exception, the PowerBuilder transaction object's properties are modified to reflect the error condition as follows:

  • SQLCode is set to -1.
  • SQLDBCode is set to the error number (a 2-byte integer) provided in the RAISE EXCEPTION call.
  • SQLErrText is set to the predefined error message for the specified error or to the error message provided in the UDR when the special error number -746 is used.

Within a DataWindow or DataStore errors are not propagated to the transaction object, but rather they trigger the DBError event. Within the DBError event, the error code is passed to the sqldbcode argument and the error message to the sqlerrtext argument. Although the retrieve call may fail, rows that were successfully retrieved before the exception was raised will still reside in the primary buffer of the DataWindow or DataStore.

Outer Join Syntax
When building a SQL-based DataWindow object, the IN9 interface provides two modes under which outer join syntax can be generated. This syntax is applied only to DataWindow objects saved in the graphical preview mode; if a DataWindow object is saved from within the syntax view, the exact syntax within that view is used without modification. The type of syntax used is determined by the value of the DBParm OJSyntax. In the Database Profile Setup dialog this setting is found on the Syntax tab. Listing 5 illustrates the differences in syntax generated for a query that returns a list of all customers and the number of orders they have placed.

Not all versions of IBM Informix database servers support ANSI outer joins, and there are restrictions even for those that do. For example, use of a RIGHT OUTER JOIN is not supported, but such joins can be generated within the PowerBuilder DataWindow painter and will generate errors upon retrieval. For more information on the specific ANSI outer-join syntax supported by your database server, consult the IBM Informix Guide to SQL - Reference.

Logging Modes
The effectiveness of the PowerBuilder transaction object's autocommit and lock properties depends on the target database's support for logging. Logging is specified for an IBM Informix database when it is created via the CREATE DATABASE statement. The syntax varies depending on the type of logging and the particular database server product; however, without logging, transactional processing and the associated PowerScript statement such as COMMIT and ROLLBACK cannot be used.

The autocommit property of the PowerBuilder transaction object is used to control whether or not SQL issued is included within the scope of a transaction. When autocommit is set to false, the default value, it is generally incumbent on the PowerBuilder programmer to COMMIT or ROLLBACK transactions explicitly.

As you can see in Table 1, how the autocommit property is interpreted depends on whether logging is defined for the database and furthermore whether the database is defined to be in ANSI mode.

Isolation Level
The available isolation levels for IBM Informix IDS and OnLine databases - isolation levels are not supported for IBM Informix SE - include the following, which are specified in PowerBuilder via setting the transaction object's lock property to the actual names of the levels described below.

  • Dirty read: The default for databases with no logging. With this isolation level, all client processes have visibility to all modifications made by other clients regardless of whether those changes have been committed (for those databases with logging enabled). The use of this level offers the highest level of concurrency, but can result in phantom rows - data read by one client during a pending transaction that is rolled back by a different client that is controlling that transaction.
  • Committed read: The default isolation level for databases that have non-ANSI logging enabled. This isolation level ensures that all reads of data return committed data, that is, the risk of phantom rows is eliminated.
  • Cursor stability: Causes the database server to issue a shared lock on the row as it's read, thus preventing any updates on that row (which requires an exclusive lock) to occur until the cursor advances past that row or the database server otherwise releases the lock.
  • Repeatable read: The default for databases with ANSI logging enabled. This is the most conservative isolation level and maintains a share lock on each row read until the transaction is either committed or rolled back. In terms of PowerBuilder applications using the default false value for autocommit, use of this isolation level will require issuing COMMIT or ROLLBACK statements even if just issuing retrieves of a DataWindow or DataStore.

As you have seen in this excerpt, PowerBuilder's IN9 interface supports the main features of the IBM Informix database. Moving beyond standard SQL statements, IN9 takes into account some of the unique facets of IBM Informix, including providing specific support for the SERIAL data type, handling TEXT and IMAGE data within script and DataWindows, and abstracting the complexities of invoking User Defined Routines (UDRs).


.  .  .

This article is based on PowerBuilder 9 Internet and Distributed Application Development by various authors (ISBN 0672324997), published by Sams Publishing.

More Stories By Jim O'Neil

Jim is a Technology Evangelist for Microsoft who covers the Northeast District, namely, New England and upstate New York. He is focused on engaging with the development community in the area through user groups, code camps, BarCamps, Microsoft-sponsored events, etc., and just in general serve as ambassador for Microsoft. Since 2009, Jim has been focusing on software development scenarios using cloud computing and Windows Azure. You can follow Jim on Twitter at @jimoneil

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.