PowerBuilder Solutions

Bhaskar Azad

Subscribe to Bhaskar Azad: eMailAlertsEmail Alerts
Get Bhaskar Azad: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

PowerBuilder: Article

Converting from ASA to ASE

Providing an application that can run in both environments

Shifting the DataWindows
We did not want to change the DataWindow column names since they had been referenced in many places in the script both directly (i.e., [dwname].Object.[column_name].[rownum]) as well as by using Describe(), Modify(), SetItem(), GetItemString(), etc.

The solution was to manually change only the column data source through Edit Source. Here each DataWindow object was opened, a quick search and replace done for the changed names under dbname as well as in the PBSELECT - here we had to be careful that the column with the changed name might have been used for join or retrieval criteria under the WHERE clause, order by clause, or even sometimes computed columns (see the example script in Listing 2). We used order by and computed columns through PowerBuilder at the client level rather than at the server so the latter problem was rarely encountered.

After each edit source was completed, the DataWindow was rechecked by opening in PowerBuilder as well as by retrieval while the default database connection was ASE. By ensuring that the default connection was in ASE and doing a rebuild, the PBSELECT of each object removed the double apostrophes. While this was required wherever a table or column name occurred, it did not always happen. Generally the more complex DataWindows were found to have problems and failed during the SQL select action. These can now be listed out.

  • A change had been missed
  • Column name had not been changed in the WHERE/ORDER BY clause.
  • The WHERE clause had used a complex arrangement. This requires further explanation as defined below.

Interestingly, we also discovered that changes are best done in the graphical mode or at least the last screen viewed while reviewing the SQL should be the graphical screen. While a PBSELECT (i.e., SELECT through a GUI) is entered as a single line and will construct a DataWindow without problems (see Listing 1) through Syntax, the construct has end-of-line characters that result in an improper DataWindow construct at runtime.

  • Whenever PB encountered a complex arrangement or a function using non-column names, i.e., UPPER ( it did not remove the double apostrophes, e.g., WHERE ("admno" = :aiAdmno OR "admno" is Null) AND "crno" > 0. So manually tinkering with the syntax in graphical mode was required. Editing in Syntax mode had its own problems as outlined above.


  • Other functions not used by ASE as mentioned by Beck - like usage of DATE or TIME functions
  • Use of outer joins invariably caused problems. If there was more than one outer join, the exact syntax could never be worked out satisfactorily - all such constructs were in reports. Finally we felt that any report with more than one outer join had to be restructured so either the report will let go of some data from one table or the table entry ensured some data so that the second outer join was not required. This helped us, in retrospect, to make a better and more problem-free application.

Some more problems occurred only in preview mode or at runtime when some special data existed. One was the treatment provided to a 0 value in the WHERE clause. For example, all the patients were allotted a computer-generated serial number (called CR No) when they registered. For comparison with some normal values (e.g., growth charts), we created dummy rows using negative CR Nos. Since we did not want these rows to be visible, we retrieved the actual data as WHERE cr_no > 0. While this routine worked perfectly well in ASA, there was a serious error in ASE - causing the application to crash. Apparently the conversion was treating the 0 as a string value (i.e., enclosing it in single apostrophes - ‘0'). If we replaced this call with WHERE cr_no >= 1, no error occurred.

Another problem was in the nested reports. If we wanted a nested report based on today's values, the exact process was complicated by two factors. The WHERE clause allowed only GETDATE(), but RETRIEVAL arguments in the nested report did not recognize GETDATE(). It recognized Today() but for some inexplicable reason retrieving an entire report in which we used a today() to retrieve the nested report generated an error in ASE but not in ASA. The workaround was to create a computed column in the parent DW and use that column value as the retrieval argument. [As it turns out, Today() is a function in SQL Anywhere, it isn't in ASE. When you specified Today() in the SQL, you weren't using the PowerBuilder Today() function, you were using the SQL Anywhere one - Ed.]

Direct Syntax Correction
After the DataWindows were shifted, the next step was converting the entire application to be ready for use in both ASA and ASE.

Since we changed a lot of column names, a manual change of all the scripts where SQL syntax was used in the script was required (see Listing 2). This had to be of three or four types:

1. Change in the relevant column names (e.g., "name" to "ma_name").

  • Remove the apostrophes. A little complexity here since there are two ways of doing it: Change through edit mode (i.e., opening the script of each object, control, or function) by scanning for the SQL code and then changing it. This took a long time. However, all the changes were rechecked by PowerBuilder for script correction, even the SQL syntax. All possible errors were listed in the results window.
  • Scanning while using Edit Source was a faster process, but any changes in column name, etc., missed were not rechecked for authenticity so the chance of error was always present.

Our final recourse was to change in Edit Source but recheck (as a second pass) through the edit mode.

2. Removing the terminator character (";") was required where the SQL syntax was passed as a string argument to be used for database functions like stored procedures or SQLSA. Interestingly the terminator character was required if the SQL Syntax operated through PowerBuilder.

More Stories By SB Gogia

SB Gogia is a plastic surgeon in New Delhi. He has contributed to the software development efforts of his family-owned company - AMLA MEDIQUIP. Gogia has worked mostly with SQL and PowerBuilder, although he has dabbled in JavaScript, C++, VB and more.

More Stories By Amal Sharma

Amal Sharma is a PowerBuilder developer with 10 years of experience. Both Amal and SB Gogia started working formally as a team in 2005.

More Stories By Bhaskar Azad

Bhaskar Azad is a medical doctor doing post-graduate work at the School of Medical Science and Technology at IIT, Kharagapur. He did a short internship with Amla Mediquip.

More Stories By Rohit Tyagi

Rohit Tyagi is a medical doctor doing post-graduate work at the School of Medical Science and Technology at IIT, Kharagapur. He did a short internship with Amla Mediquip.

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.