Microsoft Cloud Authors: Jim Kaskade, Lori MacVittie, Janakiram MSV, Andreas Grabner, Pat Romanski

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.

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
For basic one-to-one voice or video calling solutions, WebRTC has proven to be a very powerful technology. Although WebRTC’s core functionality is to provide secure, real-time p2p media streaming, leveraging native platform features and server-side components brings up new communication capabilities for web and native mobile applications, allowing for advanced multi-user use cases such as video broadcasting, conferencing, and media recording.
The Internet of Things (IoT), in all its myriad manifestations, has great potential. Much of that potential comes from the evolving data management and analytic (DMA) technologies and processes that allow us to gain insight from all of the IoT data that can be generated and gathered. This potential may never be met as those data sets are tied to specific industry verticals and single markets, with no clear way to use IoT data and sensor analytics to fulfill the hype being given the IoT today.
Established in 1998, Calsoft is a leading software product engineering Services Company specializing in Storage, Networking, Virtualization and Cloud business verticals. Calsoft provides End-to-End Product Development, Quality Assurance Sustenance, Solution Engineering and Professional Services expertise to assist customers in achieving their product development and business goals. The company's deep domain knowledge of Storage, Virtualization, Networking and Cloud verticals helps in delivering ...
SYS-CON Events announced today that Enzu will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Enzu’s mission is to be the leading provider of enterprise cloud solutions worldwide. Enzu enables online businesses to use its IT infrastructure to their competitive advantage. By offering a suite of proven hosting and management services, Enzu wants companies to focus on the core of their online busine...
In the next five to ten years, millions, if not billions of things will become smarter. This smartness goes beyond connected things in our homes like the fridge, thermostat and fancy lighting, and into heavily regulated industries including aerospace, pharmaceutical/medical devices and energy. “Smartness” will embed itself within individual products that are part of our daily lives. We will engage with smart products - learning from them, informing them, and communicating with them. Smart produc...
November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Penta Security is a leading vendor for data security solutions, including its encryption solution, D’Amo. By using FPE technology, D’Amo allows for the implementation of encryption technology to sensitive data fields without modification to schema in the database environment. With businesses having their data become increasingly more complicated in their mission-critical applications (such as ERP, CRM, HRM), continued ...
OnProcess Technology has announced it will be a featured speaker at @ThingsExpo, taking place November 1 - 3, 2016, in Santa Clara, California. Dan Gettens, OnProcess’ Chief Analytics Officer, will discuss how Internet of Things (IoT) data can be leveraged to predict product failures, improve uptime and slash costly inventory stock. @ThingsExpo is an annual gathering of IoT and cloud developers, practitioners and thought-leaders who exchange ideas and insights on topics ranging from Big Data in...
SYS-CON Events announced today that SoftNet Solutions will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. SoftNet Solutions specializes in Enterprise Solutions for Hadoop and Big Data. It offers customers the most open, robust, and value-conscious portfolio of solutions, services, and tools for the shortest route to success with Big Data. The unique differentiator is the ability to architect and ...
SYS-CON Events announced today that Transparent Cloud Computing (T-Cloud) Consortium will exhibit at the 19th International Cloud Expo®, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. The Transparent Cloud Computing Consortium (T-Cloud Consortium) will conduct research activities into changes in the computing model as a result of collaboration between "device" and "cloud" and the creation of new value and markets through organic data proces...
SYS-CON Events announced today that Cloudbric, a leading website security provider, will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Cloudbric is an elite full service website protection solution specifically designed for IT novices, entrepreneurs, and small and medium businesses. First launched in 2015, Cloudbric is based on the enterprise level Web Application Firewall by Penta Security Sys...
SYS-CON Events announced today that Roundee / LinearHub will exhibit at the WebRTC Summit at @ThingsExpo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. LinearHub provides Roundee Service, a smart platform for enterprise video conferencing with enhanced features such as automatic recording and transcription service. Slack users can integrate Roundee to their team via Slack’s App Directory, and '/roundee' command lets your video conference ...
Successful digital transformation requires new organizational competencies and capabilities. Research tells us that the biggest impediment to successful transformation is human; consequently, the biggest enabler is a properly skilled and empowered workforce. In the digital age, new individual and collective competencies are required. In his session at 19th Cloud Expo, Bob Newhouse, CEO and founder of Agilitiv, will draw together recent research and lessons learned from emerging and established ...
SYS-CON Events announced today that Coalfire will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Coalfire is the trusted leader in cybersecurity risk management and compliance services. Coalfire integrates advisory and technical assessments and recommendations to the corporate directors, executives, boards, and IT organizations for global brands and organizations in the technology, cloud, health...
As ridesharing competitors and enhanced services increase, notable changes are occurring in the transportation model. Despite the cost-effective means and flexibility of ridesharing, both drivers and users will need to be aware of the connected environment and how it will impact the ridesharing experience. In his session at @ThingsExpo, Timothy Evavold, Executive Director Automotive at Covisint, will discuss key challenges and solutions to powering a ride sharing and/or multimodal model in the a...
In his general session at 19th Cloud Expo, Manish Dixit, VP of Product and Engineering at Dice, will discuss how Dice leverages data insights and tools to help both tech professionals and recruiters better understand how skills relate to each other and which skills are in high demand using interactive visualizations and salary indicator tools to maximize earning potential. Manish Dixit is VP of Product and Engineering at Dice. As the leader of the Product, Engineering and Data Sciences team a...
A completely new computing platform is on the horizon. They’re called Microservers by some, ARM Servers by others, and sometimes even ARM-based Servers. No matter what you call them, Microservers will have a huge impact on the data center and on server computing in general. Although few people are familiar with Microservers today, their impact will be felt very soon. This is a new category of computing platform that is available today and is predicted to have triple-digit growth rates for some ...
DevOps is being widely accepted (if not fully adopted) as essential in enterprise IT. But as Enterprise DevOps gains maturity, expands scope, and increases velocity, the need for data-driven decisions across teams becomes more acute. DevOps teams in any modern business must wrangle the ‘digital exhaust’ from the delivery toolchain, "pervasive" and "cognitive" computing, APIs and services, mobile devices and applications, the Internet of Things, and now even blockchain. In this power panel at @...
SYS-CON Events announced today that Numerex Corp, a leading provider of managed enterprise solutions enabling the Internet of Things (IoT), will exhibit at the 19th International Cloud Expo | @ThingsExpo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Numerex Corp. (NASDAQ:NMRX) is a leading provider of managed enterprise solutions enabling the Internet of Things (IoT). The Company's solutions produce new revenue streams or create operating...
SYS-CON Events announced today that MathFreeOn will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. MathFreeOn is Software as a Service (SaaS) used in Engineering and Math education. Write scripts and solve math problems online. MathFreeOn provides online courses for beginners or amateurs who have difficulties in writing scripts. In accordance with various mathematical topics, there are more tha...
The best way to leverage your Cloud Expo presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering Cloud Expo and @ThingsExpo will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at Cloud Expo. Product announcements during our show provide your company with the most reach through our targeted audiences.