PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

The Mystery of the dbName DataWindow Column Property

A code change is not the only thing that may affect the way an application works

PowerBuilder has very good online help and documentation that extensively covers most aspects of programming with PowerBuilder. However, from time to time you'll come across a problem that you can't find an answer to in the online help or documentation. If a search on the Internet does not yield any results either, the only remaining option is to try to find the answer on your own. In this article I describe a problem I had with the dbName DataWindow column property and what I found out about it.

How It All Started
I was maintaining a project that had had no activity for quite a few months. I even had a hardware failure on the PC where the development environment was set up but didn't really worry about this: the project was stable and there were no potential extensions in sight. But you know how it is - one day I got a call from a customer and it turned out that a small extension was needed in one of the applications. I set up the development environment on another PC and restored the source code from a backup copy. The application was written in PB6.5 with SQLAnywhere 5 used as a back end. I created the needed OBDC data sources and database profiles for PowerBuilder and successfully connected to the database. Then I decided to refresh my memory and started exploring the functions of the application around the place where the change was going to be done. Imagine my surprise when I received a runtime error after starting one of the key operations of the application. I double-checked the source code, the database, and rebuilt the project in PowerBuilder, but it was all in vain. The application didn't work; it crashed each time I called one particular operation.

Stumbling Across a Mystery
As I said, the application was stable and it had been used for several years. The operation in question was called on a daily basis, so if there had been a bug in the code I would have known it much earlier. I was pretty sure that the source code I restored was okay and up-to-date. I had no clue as to what was going wrong, so I ran the application in the debugger. To cut a long story short, I found the line that caused the runtime error but still had no idea why. The line contained the call of the datastore function Update(), and the DataWindow object associated with the datastore was dynamically created from a SELECT statement. After investigating the created DataWindow object syntax I found out that all update properties were correctly set but one: no key column was defined. That led to an examination of the code that processed the result of the SyntaxFromSQL() function. After I spent some time reading the code it was clear that its logic was based on the assumption that the dbName column property contained only a name of a database column. However, in the constructed DataWindow object the value of this property also contained the name of the table. As a result the code could not find the DataWindow column that was to be defined as the key one.

So far so good. I found the problem but still didn't understand what was going on. A quick look in PowerBuilder helped confirm that the DataWindow object was built correctly. Quoting Help, "DbName is the name of the database column in the format tablename.columnname." On the other hand, the code was working fine in production and apparently the DataWindow objects that were built dynamically in runtime there had only database column names as values of the dbName property. A mystery, isn't it?

Revealing the Mystery
It looked like I was stuck in a dead end. Obviously, there was a difference in the result that was returned by SyntaxFromSQL() when the application was running in production and in my development environment, but I had no clue why. As a last resort I decided to have a look at the database trace and one thing caught my eye: right after describing the SELECT statement that was used for the DataWindow object creation, there were lines in the trace file that read as "GET EXTENDED ATTRIBUTES". Extended attributes are what you may specify for tables and columns, such as display formats and edit styles that are to be used by default in the DataWindow painter. These attributes are stored in the so-called PowerBuilder repository - a set of PowerBuilder system tables. That got me thinking - was it possible that the repository might have affected the result of SyntaxFromSQL()? I examined the database that the application was connecting to and discovered that it contained a separate database user that owned the PowerBuilder system tables only. I looked at the database user used by the application and it contained these tables as well. I dropped them in the application's database user and corrected the database connection profile in PowerBuilder that specified the PowerBuilder Catalog Table Owner so that PowerBuilder didn't re-create the tables again. I ran the application, started the troublesome operation and it worked without a problem.

Reproducing the Mystery
Apparently, whether or not the PowerBuilder system tables were accessible in runtime affected the way PowerBuilder constructed a DataWindow object, and in particular which value it used for the dbName column property. It was now clear that the production database didn't contain the PowerBuilder catalog tables. To model this in the development environment, a separate database user was created in the development database for storing the catalog. I broke this similarity when I set up the database connection profile and got this terrible result.

In order to make sure that I was not imagining things, I decided to write a small test application that would do exactly one thing once it connected to the database: create a DataWindow object from a SELECT statement. I created two test databases: dbacat.db and catcat.db, so that the PowerBuilder catalog tables were placed in the DBA user in dbacat.db and in the CAT user in the catcat.db. In both databases I created two tables in the DBA user:

create table a (
a   integer   not null,
primary key (a)

create table b (
b   integer   not null,
primary key (b)

In the catcat.db, I created another database user CAT and specified it as the PowerBuilder Catalog Table Owner in the database connection profile in PowerBuilder. After connection in PowerBuilder, the system tables were created in the CAT user. In the dbacat.db, I didn't specify the catalog owner so all the system tables were created in the DBA user.

The Open application script looked very straightforward. It connected to the database as the DBA user and called the SyntaxFromSQL() function with the following SELECT statement:

select * from a, b

and wrote the result in a file. I ran the test application using both databases and the results were identical to what happened in the real application. If the application was connected to the dbacat.db (where the system tables were created in the DBA user and were accessible in runtime), the dbName property contained both table and column names:

column=(type=long updatewhereclause = yes name=a_a  dbname="a.a")
column=(type=long updatewhereclause = yes name=b_b  dbname="b.b")
retrieve="select * from a, b")

However, if the application was connected to the catcat.db (where the system tables were created in a separate user schema and were not accessible in runtime), the dbName property contained only column names:

column=(type=long updatewhereclause = yes name=a  dbname="a")
column=(type=long updatewhereclause = yes name=b  dbname="b")
retrieve="select * from a, b")

The Lesson Learned
The job of a developer who's doing maintenance programming is about being very cautious about making changes in code. One must be sure that the change that is going to be implemented will not affect other parts of the application. As you can see from my experience, a change in the code is not the only thing that may affect the way the application works. It turned out that the way you set up your development environment may also lead to unexpected consequences. I checked my test application in PowerBuilder 9 and 10 with the same result, which probably means that this part of the PowerBuilder DataWindow engine had not been changed for a long time. I hope that my short story will help you avoid nasty surprises in your projects.

More Stories By Konstantin Goldobin

Konstantin Goldobin is a senior developer living in Voronezh, Russia. He has been working with PowerBuilder since 1995 version 4.0. Visit his web site at www.vsi.ru/~kgold.

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.