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

PowerBuilder & Informix

Connectivity options

The TIME data type that is exposed in the Columns pane in PowerBuilder is actually not a defined IBM Informix data type; rather it's a synonym provided in the PowerBuilder interface to simplify creating such time values and is equivalent to the IBM Informix data type

DATETIME HOUR TO FRACTION(5)

If an alternative interval is required, you can enter and execute the appropriate SQL dynamic description language (DDL) statement directly within PowerBuilder's ISQL pane. You can also use the Database painter Columns pane to add a DATETIME column, copy the Pending Syntax via the context menu of that pane, paste that syntax into the ISQL pane, edit it to provide the desired qualification clause, and finally execute it.

INTERVAL Data Type
The IBM Informix INTERVAL data type stores a unit of time according to a field qualifier statement accompanying the declaration of that type. A column of INTERVAL type defined in the Columns pane of the PowerBuilder Database painter is automatically qualified as

INTERVAL DAY(3) TO DAY

which indicates it can store units of time in the range of -999 to 999 days. When selected into a DataWindow or fetched via embedded SQL, it's represented in PowerBuilder as a string of 29 characters. This data type can be used in conjunction with IBM Informix DATETIME data types to project future dates based on a duration or to calculate the span between two dates. For example, the following statement will return the number the days, hours, minutes, and seconds remaining until New Year's Day 2005.

SELECT DATETIME(2005-01-01 0:0:0) YEAR TO SECOND - CURRENT
FROM SYSTABLES WHERE TABID=1;

SERIAL Data Type
The IBM Informix SERIAL data type is a 4-byte integer value containing values that are automatically generated by the database server when a new row is inserted into the table containing the SERIAL column. Only one column within each table can be of this type, and the column cannot store null or non-positive values. This column is not guaranteed to be unique; therefore, if your intent is to use a SERIAL column to uniquely identify each row of data, the primary key or unique index constraint must be explicitly created for that table.

In Embedded SQL
If the SERIAL column is not specified as part of a SQL INSERT statement or if the value specified is 0, the database server generates a sequence number for that column automatically. If you specify a positive value, the database will use that number, but that can cause gaps in the number sequence as well as unique key constraint errors if the field is also marked as a primary key and the given SERIAL number is already in use.

When using PowerBuilder-embedded SQL, you can obtain the value of the SERIAL column just inserted into a table using the transaction object's SQLReturnData field. Since this field is a string, you may need to convert it to a PowerBuilder long variable before using it elsewhere with your code. This value is directly retrieved from the sqlcal.sqlerrd1 field within ESQL C.

The following code provides an example of inserting a new row into the region table of the superstores_demo database and then accessing the SERIAL value created for the region_num column.

// assume ls_region and li_regionCode have
// already been initialized for the new region
insert into region(region_num, region_name, region_loc)
values (0, :ls_region, :li_regionCode) USING SQLCA;
IF SQLCA.SQLCode = 0 THEN
MessageBox("Added New Region", ls_region + &
" was added with ID " + SQLCA.SQLReturnData)
END IF

In DataWindows
For DataWindow objects, the SERIAL type is a natural choice to serve as the unique key column for update purposes. When inserting rows into DataWindows, if the Identity Column dropdown list on the Specify Update Properties dialog (shown in Figure 4) reflects the name of the SERIAL column (e.g., region_num), then the newly inserted value for that row will automatically be displayed within the DataWindow following the invocation of the Update() function. This property only controls whether the new value is automatically displayed in the DataWindow (and added to the primary buffer of the DataWindow or DataStore) after the update. The fact the column type is defined as SERIAL ensures the database server will supply a new value when the SQL INSERT statement generated by the DataWindow or DataStore is executed.

The identity column attribute can also be set dynamically at runtime:

ds_region.object.region_num.identity = 'yes'
ds_region.update()

In Part 2 I'll discuss TEXT and BYTE data, Stored Procedures, and handling RAISE EXCEPTION.

.  .  .

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.