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 Database
We used PowerDesigner to transfer the database. While Timothy Beck's experience may be partly repeated here, our additional problem was the use of reserve verbs in almost all our tables.

Whenever it discovered a reserve verb, as well as any other problem, ASA had a convenient habit of enclosing all the column names in double apostrophes. This made it extremely friendly for new users like us. Whether it has been a good point (we would definitely say "yes") of ASA is debatable. Our application had been (and still is - in places where we installed it) working without us realizing the syntax problems that we faced during the transfer. ASE did not give us the luxury of using double apostrophes, since the created script would refuse to run, and the problems noted were:

  • Reserve Verbs. The reserve words we used are listed in Table 1.
  • Empty space (i.e., white space) in the table or column names (two instances), in one case it was the table name and in the other it was mistakenly at the end of the actual term used. The first error probably occurred because although white spaces are disallowed as part of column names while editing a table through PowerBuilder, the table name is only asked for at the end of the construction process while saving. Probably inadvertent typographic errors are accepted by ASA when it has already initiated the save or table construct. In both these instances the table was rarely used, otherwise it might have been discovered in ASA earlier.
  • ASA scripts add the term DEFAULT (e.g., DEFAULT NULL or DEFAULT AUTOINCREMENT) to every column. This term is not recognized by ASE so we had to manually rewrite every exported script if exported through PowerBuilder.
  • Columns with an auto-increment property could not be an integer, only numeric. The verb used was Identity rather than Autoincrement. Correctiing this, while simple in itself, had two significant side effects detailed below.
  • Index creation was a special problem not mentioned by Beck since he used a different tool (Microsoft's Data Transformation Service or DTS). The term Constraint was an additional requirement for all the key columns.
  • Other problems like dbo instead of dba and not accepting s semicolon have already been mentioned by Beck and will be discussed here only when we have something to add.
  • Not accepting outer joins and char field lengths were problems discovered at the DataWindow creation and scripting level. This is discussed later.

Our solution for the reserve word issue was standardized to adding a prefix to the reserve word. Since our application was called Medic Aid, we added the prefix "ma_" to the column names not allowed by ASE (see Listing 1). The white space was simply removed (although it took some time for us to understand the white space entry that was at the end of the column name). All this was done directly in PowerDesigner and once a rough-running SQL script was created, more specific problems were tackled to solve runtime issues. Since many versions of our application existed, a universal ready script meant we had to manually add some tables later after export from PowerBuilder. This ran tandem with the next stage, i.e., conversion of the DataWindows.

Like Beck, our goal was to provide an application that could run in both database environments (i.e., ASA as well as ASE). We changed only those column names that were required rather than the entire set. This decision was guided by the realization that the more changes, the greater the chance of inadvertent errors. To continue using a common application, all the column name changes also had to be reflected in the ASA database that had to be reconstructed. Due to the difference in the terminator character (;) as well as the use of terms like Identity/autoincrement, separate scripts were required for ASA and ASE. Later we discovered that "go" worked in ASA too and to some extent a common script was possible. For this you must create an ASA database with the "Emulate Adaptive Server Enterprise" option ticked when using Sybase Central to create the database. This option has a corresponding command-line option.

Since Identity columns had to be numeric, and many of these were the primary keys of the master tables, we had to change the related columns in the dependent tables to Numeric, otherwise the foreign key generation resulted in an error. A related issue was the length allotted to the Numeric columns. Initially we had used 12 but found that this length was longer than a long value when it was referenced as a variable through PowerBuilder. Then we shifted to nine - meaning the entire database had to be re-created again.

During our test runs, we found the identity columns frequently skipped values (e.g., one series went as 1, 2 ......25 and then started again from 10000025, 100000026. This was mostly found when we were using the pipeline or imports to transfer data). (Otherwise, we also believe that any auto-incremental values should not be used for key columns if a pipeline is going to be used anytime in the future. The numbers are known to change or skip, which means that the link to the dependent tables will be broken. We had used auto-incremental values only for the master tables, which were prefilled before the transaction tables were put to use, so a pipeline was not required. In this case however, we were attempting to prefill master data in a fresh database.)

The problem was overcome by setting the AUTOIDENTITY to NO for this one time data transfer. [This has been referred to as the "burn factor" See http://www.mtb.ee/~arno/Sybase_FAQ/ASE/section6.2.html#configureburn - Ed.]

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.