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

In this article we'll discuss connectivity requirements, Informix-specific data type processing, and the use of Informix stored procedures within the PowerBuilder environment.

PowerBuilder offers developers four specific connectivity options for IBM Informix databases:

  • Third-party Open Database Connectivity (ODBC) drivers
  • Third-party Java Database Connectivity (JDBC) drivers
  • Third-party OLE-DB providers
  • The "native" driver available in the PowerBuilder Enterprise version

In this article we'll focus on the last option and in Part 2 we'll discuss TEXT and BYTE data, Stored Procedures, and handling RAISE EXCEPTION.

Connecting to Informix
Supported Versions
The PowerBuilder native driver for Informix connectivity (IN9) is linked with the IBM Informix ESQL C product to provide connectivity to the following IBM Informix databases:

  • IBM Informix Dynamic Server (IDS)
  • IBM Informix Standard Engine (SE)
  • IBM Informix OnLine 5

PowerBuilder application developers and end users wishing to connect to an IBM Informix database server must install the IBM Informix Connect product containing the necessary files for runtime client use. IBM Informix Connect is also included as a component within the Informix Client SDK (Software Development Kit). The SDK offers additional development tools including JDBC and ODBC drivers, an OLE-DB provider, and libraries for including embedded SQL within C, C++, and Java programs.

As there is a wide range of features supported on the various IBM Informix products, not all of the topics in this article will apply to all database servers. This article is generally focused on the features provided in IBM Informix Dynamic Server, which IBM considers their offering for mission-critical applications.

TEXT and BYTE Data
The IBM Informix TEXT and BYTE data types can store data of up to 2 gigabytes in length. It is recommended that the TEXT data type store only printable characters to ensure interoperability with other IBM Informix tools; however, these two types are logically equivalent. In PowerBuilder, both are treated as blobs, which limits the ways you can access them to embedded SQL and OLE Blob objects within DataWindows. The use of BYTE and TEXT as stored procedure return values is also specifically not supported by the PowerBuilder IN9 interface.

IBM Informix also places additional restrictions on these data types, for example, they

  • Cannot be used in arithmetic or Boolean expressions
  • Cannot be included as part of an index
  • Cannot be used in a GROUP BY or ORDER BY clause

In Embedded SQL
To use blob data within PowerBuilder-embedded SQL, you must use the SELECTBLOB and UPDATEBLOB statements. SELECTBLOB retrieves a single blob variable from a given table, generally using a WHERE clause that uniquely identifies one row. UPDATEBLOB updates an existing row (or rows) in a table with the contents of the supplied blob variable. Because the target row must exist before using an UPDATEBLOB statement, that statement is generally preceded by an INSERT statement to create the new row with a null value for the blob column, but with another column value (or a SERIAL column, perhaps) to serve as a unique row identifier.

In Listing 1 you can see both PowerScript statements in action. First a new row is added to the catalog table (in the default stores_demo database) without specifying a value for the BYTE column, cat_picture. Note that we also don't specify a value for the catalog_num column since that is a SERIAL data type.

Assuming the INSERT was successful, the next step is using UPDATEBLOB on the inserted row, now identified by the newly updated value of catalog_num accessed via SQLCA.SQLReturnData. The UPDATEBLOB sets the cat_picture column to a PowerBuilder blob variable that would have been initialized to store image data, perhaps read from an external .GIF file.

To test the success of the UPDATEBLOB, another field of the transaction object, namely SQLNRows is used. In most cases blob functions should affect exactly one row, so the test for that condition is used to determine the success of the update.

To illustrate that the update was successful, the final section of code selects the BYTE column back into a PowerBuilder blob variable and displays that blob in a picture control to demonstrate that indeed the value was saved correctly. Note that again SQLNRows is used to determine the success of the SELECTBLOB statement.

In DataWindows
TEXT and BYTE support within DataWindows is confined to the use of OLE Blob columns. When you insert an OLE Blob column into a DataWindow within the painter, the dialog in Figure 1 appears. There are four steps to provide the information needed to display the TEXT or BYTE data within the confines of the DataWindow.

  1. Select the table and the TEXT or BYTE column from the table and column lists. In this case, we've selected the column catalog.cat_picture, which is the same column we used in the preceding embedded SQL example.
  2. Each OLE Blob column can only display one TEXT or BYTE value per detail row, so the Key Clause must be specified to uniquely identify the row from which the blob value should be selected. This corresponds directly to the WHERE clause in the embedded SQL example from Listing 1.
  3. Next, you must select the OLE server application that will display and optionally edit the data. In this case, Adobe Photoshop was selected to manage the .GIF or .JPG images that may have been stored in the cat_picture column.
  4. Finally, the Client Name Expression is provided to identify the particular data in the OLE server application, for instance in a title bar of an Adobe Photoshop document. This string value will typically contain some other identifying data element of the row for which the TEXT or BYTE element was selected. In this case, we've elected to use the unique catalog_num field as the identifier.

Other Data Types
There are additional IBM Informix data types that are not explicitly supported by PowerBuilder. Some of these data types may be used in certain scenarios within the PowerBuilder development or runtime environment, especially via embedded SQL and the dynamic SQL options available in PowerScript. Specific details on these data types follow.

  • Boolean - TRUE / FALSE - values are interpreted as CHAR(1) data in PowerBuilder and can be selected within DataWindows and embedded SQL. If the value of the DisableBind DBParm is set to 1, boolean fields can also be updated via PowerBuilder.
  • LVARCHAR, variable length characters strings up to 2048 characters, are not supported; however, with you can select their contents into a PowerBuilder DataWindow as a CHAR(2048) type or into embedded SQL as a string as shown in Listing 2.

    LVARCHAR columns can be updated via embedded SQL or DataWindows as well. Keep in mind when using embedded SQL that since Informix limits string literals to 255 characters, the PowerBuilder DisableBind DBParm cannot be set to 1 when updating LVARCHAR data types.

  • INT8 and SERIAL8 are 64-bit versions of the INT and SERIAL types, respectively. Although PowerBuilder includes a long data type of 64-bits, these large values are not supported by the PowerBuilder database interfaces or by the DataStore and DataWindow functions.
  • CLOB and BLOB are considered "smart" analogs of the TEXT and BYTE data types that are recoverable, can support up to 4 terabytes of data, and can be retrieved in whole or part. Since you can't cast CLOB or BLOB to TEXT or BYTE, these data types are not currently supported in PowerBuilder.
  • Opaque data types are encapsulated data types defined using the IBM INFORMIX SQL statement CREATE OPAQUE TYPE. Using the CREATE CAST statement, you can define functions that can translate an opaque data type into a supported data type and retrieve the resulting data into PowerBuilder.
  • Distinct data types, defined using CREATE DISTINCT TYPE, are translated to CHAR(n) data types when retrieved by PowerBuilder, where n is the number of bytes required to store the value. For character-based data this will generally work well; however, for numeric data the value should be cast into a supported data type before returning that data to PowerBuilder.
  • Row types, defined using CREATE ROW TYPE, are not supported; however, if the individual fields within a ROW TYPE are supported data types, they can be selected explicitly via dot notation in embedded SQL or by appropriately coding the select statement in the syntax view of the DataWindow painter. IBM Informix does not allow the update of ROW TYPES with dot-notated syntax, however.
  • Collection data types include LIST, SET, and MULTISET and are not supported in PowerBuilder.

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.