PowerBuilder Solutions

SB Gogia

Subscribe to SB Gogia: eMailAlertsEmail Alerts
Get SB Gogia: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

PowerBuilder: Article

Converting from ASA to ASE

Providing an application that can run in both environments

In places where the syntax was sent for database functions as a string value, the tilde had to be removed along with the double apostrophe (~"). You had to be careful when using the ampersand (&) for carrying on the script in the next line. The presence of an apostrophe meant that the next word was separate:

e.g., szData = "SELECT ~"medicines~".~"medicine~"
FROM ~"medicines~""&
+"WHERE (~"medicines~".~"category~" = ~'m~') ;"

would convert to

" SELECT medicines.medicine FROM medicines"&
+"WHERE (medicines.category = ~'m~') ;"

once the ~" combination is removed.

Convert this to a continuous text after & and + is removed dynamically and it becomes:

"SELECT medicines.medicine FROM medicinesWHERE (medicines.category = ~'m~') ;"

Note that Medicines and WHERE have become a single word. This will dynamically generate an error but PowerBuilder will accept it as valid script.

The process of transferring script was fairly simple though labor-intensive (after a learning period, the interns could manage to complete the entire exercise in less than a week). The process was:

  • Open each object (i.e. Application, Window, Menu, or Userobject) through Edit Source in serial order.
  • Do a <Find> for SELECT, UPDATE, or INSERT, etc. to find the SQL syntax.
  • Change the script by removing the apostrophes {") and occasionally the associated tilde too' ~"'. The best way was to highlight the entire SQL Syntax, and do a Find/Replace for the Apostrophe with a Null value (see Figure 1). When doing a replace, you have to delete the existing entry in the "With" (by default there will always be some string in that). Check "Search Selection Only". Now do Replace All.
  • Change the column names as required at the same time.
  • Once done, thoroughly scan the entire object in Edit mode by opening each script again for errors to see if any syntax had been missed - we found missed items six months later.

Since string values in the code will have a typical color (maroon in our application), a manual search is easy. Interestingly, we found errors not related to the SQL syntax in this manual search that helped us in debugging many long-standing problems.

A detailed explanation of the statement above is required. If PowerBuilder discovers an error in the script, PowerBuilder can easily check it and compilation or regeneration of the object stops. However, many times code for use dynamically is put in a string value (e.g., through Describe(), Modify(), etc.) as well as changes in string values. Errors in this code are frequently missed since there's no warning in the PowerBuilder environment. They are discovered only at runtime. In a very large application (like ours) there is a high probability that some pieces of code are not put to use frequently, so they can be missed for ages. Our careful reread of every string value found many such errors. Correcting them helped make our application more robust.

We recommend that rechecking the code by scanning string values be done by a senior member of the team familiar with the code.

A late runtime error noted was the difference in the data obtained while doing a SELECT if the string was of the type CHAR. For example, see the statement below:

/* isUser was a char value fed into the Table through gsUser. While in ASA it was obtained exactly as before; in ASE it had many white spaces as extra padding for a retrieve. A TRIM function was required after the statement to correct the difference. This was because ASA treats CHAR columns as VARCHARS - probably the only database that does so (Sybase Manuals).

SELECT crno , cr_time, cr_request
INTO :lNewNum, :ldt_done, :isUser
FROM pt_key WHERE rownum = 2;
isUser = TRIM(isUser)
/*required if ASE is being used else the next line will return false
*/If isUser = gsUser //gsUser is the original value to generate issuer.
//Procedure once the matching is successful

[This is because ASA treats CHAR columns as VARCHARS - probably the only database that does so, see Sybase Manuals - Ed.]

Note: Listing 2 was used to create identity values of our transaction tables. As mentioned, we had problems with using identity fields. This method has been mentioned as Method number 3 to solve problems in Identity tables.

We thank Mr. Bruce Armstrong, our editor, for providing his very relevant comments. Some of the problems were solved by discussions in the PowerBuilder Google group.

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.