Welcome!

Microsoft Cloud Authors: Janakiram MSV, Yeshim Deniz, David H Deans, Andreas Grabner, Stackify Blog

Related Topics: PowerBuilder, Microsoft Cloud

PowerBuilder: Article

Distributing Database Modification Scripts to DBAs

The need for ongoing enhancements

I recently read Berndt Hamboeck's article, NetStart' Your PowerBuilder Application after being kindly pointed to it by Bruce Armstrong. It made perfect sense to me to be able to distribute .pbd patches to client machines by having the client-application automatically install them from the application's database. In the lead-up in Berndt's article, he touched on a similar problem - the difficulties of distributing database modification scripts to DBAs prior to the installation of a new executable. In this respect, I had coded functionality for the application I develop to process database modifications from within the application without the need for the DBA's intervention. I tentatively mentioned this to Bruce. (I say ‘tentatively' because I'm a relatively inexperienced programmer). He asked me to write this article.

By way of background, I'm a self-taught PowerBuilder programmer, having been introduced to programming in an ancillary subject at university back in the days of mark-sense cards, Fortran, and Cobol. I am a law enforcement investigator by trade, and designed an application about 15 years ago to process telephone call charge records and financial statements for an Australian law enforcement agency at which I worked. I later engaged a skilled PowerBuilder programmer, Sue Stagg, to set up a commercial version as the New South Wales Police, the fifth-largest police force in the world, wanted to use the application. Unfortunately, Sue had to move on because she started a family, so I took over the programming. I mention this background for two reasons.

The first is as a caveat. Experienced programmers may be able further enhance or refine my methods, which I'll set out below.

The second is that the application I develop (CallRunner) has to be continually enhanced, which invariably includes database changes. The need for ongoing enhancement is because of rapid changes with the technology of mobile phone communications - such as the introduction of Internet access, email, SMS, video, photography, and GPS.

Cellphones have become a valuable source of intelligence for law enforcement. As a demonstration for a new extension to my application, the NSW Police extracted a file of 4400 pages of data from my cellphone. It included historical SMS messages, emails, photographs, and a myriad of GPS locations I'd plotted from all over the county while riding my Harley, as well as the GPS coordinates of where I took my photographs. This data has to be read in and linked to existing data within the application.

In distributing new executables or patches, I'd regularly experience the issues mentioned by Berndt, and I'd often have to spend time at locations where there was no luxury of a DBA.

To save my clients and myself this grief, I created a library entry - call it, say, database.pbl. It included an nvo called nvo_database. A function, of_initialise(), returned the build date and version number as instance variables from the Application Manager.

The fact that modifications were effected from a .pbd meant that if I needed to send out a .pbd patch that relied on database modifications, I could include a database.pbd as well.

Code in the pfc_open event of the Application Manager instantiates unvo_database and calls of_initialise(). Calls are then made from the pfc_open event to at least two like functions in the nvo to effect the database changes.

The first function called from pfc_open manages database changes for the preceding build of the application - just in case the user hasn't installed that version and has jumped to the current release. The second function call is relevant to database changes for the current build. There is an overlap - a ‘boots and braces' approach. You can see from the code below that, as the builds progress, obsolete function calls are remmed. The two valid functions do the work to effect the more recent database changes.

The database modifications typically include:

  • Creating a table
  • Adding a column to an existing table
  • Altering a column in an existing table
  • Adding data to an existing table, or
  • Adding an index

The functionality varies slightly for each but an underlying theme is to trap error codes in the first instance to confirm that a table or column does not exist. If they exist, as they would after the first run of the application, then the change routine is skipped.

The first job of this functionality was to create a table to hold a record of the SQL code used to modify the database. This table held the SQL code, the build date, and the version number of the application, and the date and time the changes were effected. The following is the code initiated from of_database_changes() function of unvo_database ...

The code in the Create Table function is:

The first process in this function is to determine whether the table exists; this is a key function. If the table doesn't exist and returns FALSE, a message is given asking the user whether they want to exit to backup the database.

If the table does not exist, dynamic SQL is used to create the table.

If an error is encountered at any point "Failed on:" is prefixed to SQL code which is saved, whether the changes were successful or not, by way of a call to the function of_save_database_changes(..) in the table just created.

If there is an error at any point, an instance variable, ib_fail, is set to TRUE and, after all subsequent modifications have been processed, a message is given to the user. The DBA can print a report from the database_changes table that will indicate exactly where the error occurred.

The following is the code in the key function of_check_for_existing_table():

A bogus SELECT COUNT call is made on the table. If the table doesn't exist, an error code will be returned. The error code for a non-existing table is 208 and this is compared to an instance constant of that value. The error is trapped and a Boolean value of FALSE is returned to indicate that the table does not exist. If the table exists, TRUE is returned - nothing else needs to be done.

The following is similar code used when adding a column to a table - in this case, column cue_word_sample_text:

Checking and implementing changes for adding indexes or entering data into a table are effected in much the same way. This involves either checking for an error code for the existence of a column or an index, or using SELECT to determine whether data already exists in a table. When using SELECT I found that I had to use SQL Code such that I did not get an error for returning more than one row.

An exception where SQL code could not be used involved the altering of a column width from, say, varchar(100) to, say, varchar(200). With my limited experience I could find no way to use SQL code to determine the attributes of a database column (‘describe' and ‘ColType' clearly only work on the DataWindow object, which is independent to, and may not always comply with, the column description in the table). In such cases, I simply checked the database_changes table to ensure that the SQL has not already been executed.

To guarantee that I was distributing the correct changes and that they will all work as expected, I test the SQL code in the database painter of an auxiliary application. I then copy/paste the tested SQL code into my PowerBuilder development application functions as described. Because my ongoing coding and testing will be contingent on the changes working correctly, I'll quickly establish if there is an error.

I have only had one hiccup and that was where a user had added an index to a column the application was attempting to alter. The DBA was able to print the Database Changes Report and we quickly established where the error occurred from the ‘Failed on' prefix. He dropped the index, re-booted the application to automate the change, and then reinstated the index. All was well.

Following from this, if you wish to alter a column that has an index, you need to code the dropping and reinstating of the index either side of the column alteration.

In this regard, there may be the prospect beyond my knowledge of using SQL to check first and get a handle on an unknown index before altering a column.

Summary
This process has been a tremendous time-saver for me. But more than anything, I can distribute my application with a great deal more confidence. Users are not frustrated or lose confidence in the application because of installation errors. Nor has there been any noticeable deterioration in the start-up time of the application on the first run when the changes are effected.

The sample code can be downloaded here.

More Stories By Bill Beale

Bill Beale is a Consultant/Investigation team leader in a special investigations unit. He studied computing as a sub-major in an Administration degree at the University of Canberra in the 1970s and is a self-taught PowerBuilder programmer, having taken over the ongoing coding and development of applications about five years ago

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.


@ThingsExpo Stories
SYS-CON Events announced today that Evatronix will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Evatronix SA offers comprehensive solutions in the design and implementation of electronic systems, in CAD / CAM deployment, and also is a designer and manufacturer of advanced 3D scanners for professional applications.
As businesses evolve, they need technology that is simple to help them succeed today and flexible enough to help them build for tomorrow. Chrome is fit for the workplace of the future — providing a secure, consistent user experience across a range of devices that can be used anywhere. In her session at 21st Cloud Expo, Vidya Nagarajan, a Senior Product Manager at Google, will take a look at various options as to how ChromeOS can be leveraged to interact with people on the devices, and formats th...
SYS-CON Events announced today that Taica will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Taica manufacturers Alpha-GEL brand silicone components and materials, which maintain outstanding performance over a wide temperature range -40C to +200C. For more information, visit http://www.taica.co.jp/english/.
Organizations do not need a Big Data strategy; they need a business strategy that incorporates Big Data. Most organizations lack a road map for using Big Data to optimize key business processes, deliver a differentiated customer experience, or uncover new business opportunities. They do not understand what’s possible with respect to integrating Big Data into the business model.
Enterprises have taken advantage of IoT to achieve important revenue and cost advantages. What is less apparent is how incumbent enterprises operating at scale have, following success with IoT, built analytic, operations management and software development capabilities – ranging from autonomous vehicles to manageable robotics installations. They have embraced these capabilities as if they were Silicon Valley startups. As a result, many firms employ new business models that place enormous impor...
Amazon is pursuing new markets and disrupting industries at an incredible pace. Almost every industry seems to be in its crosshairs. Companies and industries that once thought they were safe are now worried about being “Amazoned.”. The new watch word should be “Be afraid. Be very afraid.” In his session 21st Cloud Expo, Chris Kocher, a co-founder of Grey Heron, will address questions such as: What new areas is Amazon disrupting? How are they doing this? Where are they likely to go? What are th...
SYS-CON Events announced today that MIRAI Inc. will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. MIRAI Inc. are IT consultants from the public sector whose mission is to solve social issues by technology and innovation and to create a meaningful future for people.
SYS-CON Events announced today that Dasher Technologies will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Dasher Technologies, Inc. ® is a premier IT solution provider that delivers expert technical resources along with trusted account executives to architect and deliver complete IT solutions and services to help our clients execute their goals, plans and objectives. Since 1999, we'v...
SYS-CON Events announced today that NetApp has been named “Bronze Sponsor” of SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. NetApp is the data authority for hybrid cloud. NetApp provides a full range of hybrid cloud data services that simplify management of applications and data across cloud and on-premises environments to accelerate digital transformation. Together with their partners, NetApp emp...
SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
SYS-CON Events announced today that TidalScale, a leading provider of systems and services, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. TidalScale has been involved in shaping the computing landscape. They've designed, developed and deployed some of the most important and successful systems and services in the history of the computing industry - internet, Ethernet, operating s...
Infoblox delivers Actionable Network Intelligence to enterprise, government, and service provider customers around the world. They are the industry leader in DNS, DHCP, and IP address management, the category known as DDI. We empower thousands of organizations to control and secure their networks from the core-enabling them to increase efficiency and visibility, improve customer service, and meet compliance requirements.
SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
Join IBM November 1 at 21st Cloud Expo at the Santa Clara Convention Center in Santa Clara, CA, and learn how IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Cognitive analysis impacts today’s systems with unparalleled ability that were previously available only to manned, back-end operations. Thanks to cloud processing, IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Imagine a robot vacuum that becomes your personal assistant tha...
In his Opening Keynote at 21st Cloud Expo, John Considine, General Manager of IBM Cloud Infrastructure, will lead you through the exciting evolution of the cloud. He'll look at this major disruption from the perspective of technology, business models, and what this means for enterprises of all sizes. John Considine is General Manager of Cloud Infrastructure Services at IBM. In that role he is responsible for leading IBM’s public cloud infrastructure including strategy, development, and offering ...
In a recent survey, Sumo Logic surveyed 1,500 customers who employ cloud services such as Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform (GCP). According to the survey, a quarter of the respondents have already deployed Docker containers and nearly as many (23 percent) are employing the AWS Lambda serverless computing framework. It’s clear: serverless is here to stay. The adoption does come with some needed changes, within both application development and operations. Tha...
SYS-CON Events announced today that Avere Systems, a leading provider of enterprise storage for the hybrid cloud, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Avere delivers a more modern architectural approach to storage that doesn't require the overprovisioning of storage capacity to achieve performance, overspending on expensive storage media for inactive data or the overbui...
Widespread fragmentation is stalling the growth of the IIoT and making it difficult for partners to work together. The number of software platforms, apps, hardware and connectivity standards is creating paralysis among businesses that are afraid of being locked into a solution. EdgeX Foundry is unifying the community around a common IoT edge framework and an ecosystem of interoperable components.
SYS-CON Events announced today that TidalScale will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. TidalScale is the leading provider of Software-Defined Servers that bring flexibility to modern data centers by right-sizing servers on the fly to fit any data set or workload. TidalScale’s award-winning inverse hypervisor technology combines multiple commodity servers (including their ass...
SYS-CON Events announced today that N3N will exhibit at SYS-CON's @ThingsExpo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. N3N’s solutions increase the effectiveness of operations and control centers, increase the value of IoT investments, and facilitate real-time operational decision making. N3N enables operations teams with a four dimensional digital “big board” that consolidates real-time live video feeds alongside IoT sensor data a...