Welcome!

Microsoft Cloud Authors: Pat Romanski, Jnan Dash, Andreas Grabner, Lori MacVittie, Jim Kaskade

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
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 @...
Every successful software product evolves from an idea to an enterprise system. Notably, the same way is passed by the product owner's company. In his session at 20th Cloud Expo, Oleg Lola, CEO of MobiDev, will provide a generalized overview of the evolution of a software product, the product owner, the needs that arise at various stages of this process, and the value brought by a software development partner to the product owner as a response to these needs.
WebRTC services have already permeated corporate communications in the form of videoconferencing solutions. However, WebRTC has the potential of going beyond and catalyzing a new class of services providing more than calls with capabilities such as mass-scale real-time media broadcasting, enriched and augmented video, person-to-machine and machine-to-machine communications. In his session at @ThingsExpo, Luis Lopez, CEO of Kurento, introduced the technologies required for implementing these idea...
The WebRTC Summit New York, to be held June 6-8, 2017, at the Javits Center in New York City, NY, announces that its Call for Papers is now open. Topics include all aspects of improving IT delivery by eliminating waste through automated business models leveraging cloud technologies. WebRTC Summit is co-located with 20th International Cloud Expo and @ThingsExpo. WebRTC is the future of browser-to-browser communications, and continues to make inroads into the traditional, difficult, plug-in web co...
Internet of @ThingsExpo, taking place June 6-8, 2017 at the Javits Center in New York City, New York, is co-located with the 20th International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. @ThingsExpo New York Call for Papers is now open.
Who are you? How do you introduce yourself? Do you use a name, or do you greet a friend by the last four digits of his social security number? Assuming you don’t, why are we content to associate our identity with 10 random digits assigned by our phone company? Identity is an issue that affects everyone, but as individuals we don’t spend a lot of time thinking about it. In his session at @ThingsExpo, Ben Klang, Founder & President of Mojo Lingo, discussed the impact of technology on identity. Sho...
Technology vendors and analysts are eager to paint a rosy picture of how wonderful IoT is and why your deployment will be great with the use of their products and services. While it is easy to showcase successful IoT solutions, identifying IoT systems that missed the mark or failed can often provide more in the way of key lessons learned. In his session at @ThingsExpo, Peter Vanderminden, Principal Industry Analyst for IoT & Digital Supply Chain to Flatiron Strategies, will focus on how IoT depl...
Data is an unusual currency; it is not restricted by the same transactional limitations as money or people. In fact, the more that you leverage your data across multiple business use cases, the more valuable it becomes to the organization. And the same can be said about the organization’s analytics. In his session at 19th Cloud Expo, Bill Schmarzo, CTO for the Big Data Practice at Dell EMC, introduced a methodology for capturing, enriching and sharing data (and analytics) across the organization...
With all the incredible momentum behind the Internet of Things (IoT) industry, it is easy to forget that not a single CEO wakes up and wonders if “my IoT is broken.” What they wonder is if they are making the right decisions to do all they can to increase revenue, decrease costs, and improve customer experience – effectively the same challenges they have always had in growing their business. The exciting thing about the IoT industry is now these decisions can be better, faster, and smarter. Now ...
WebRTC is about the data channel as much as about video and audio conferencing. However, basically all commercial WebRTC applications have been built with a focus on audio and video. The handling of “data” has been limited to text chat and file download – all other data sharing seems to end with screensharing. What is holding back a more intensive use of peer-to-peer data? In her session at @ThingsExpo, Dr Silvia Pfeiffer, WebRTC Applications Team Lead at National ICT Australia, looked at differ...
The cloud market growth today is largely in public clouds. While there is a lot of spend in IT departments in virtualization, these aren’t yet translating into a true “cloud” experience within the enterprise. What is stopping the growth of the “private cloud” market? In his general session at 18th Cloud Expo, Nara Rajagopalan, CEO of Accelerite, explored the challenges in deploying, managing, and getting adoption for a private cloud within an enterprise. What are the key differences between wh...
"ReadyTalk is an audio and web video conferencing provider. We've really come to embrace WebRTC as the platform for our future of technology," explained Dan Cunningham, CTO of ReadyTalk, in this SYS-CON.tv interview at WebRTC Summit at 19th Cloud Expo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
In 2014, Amazon announced a new form of compute called Lambda. We didn't know it at the time, but this represented a fundamental shift in what we expect from cloud computing. Now, all of the major cloud computing vendors want to take part in this disruptive technology. In his session at 20th Cloud Expo, John Jelinek IV, a web developer at Linux Academy, will discuss why major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform are all trying to sidestep VMs and containers...
IoT is at the core or many Digital Transformation initiatives with the goal of re-inventing a company's business model. We all agree that collecting relevant IoT data will result in massive amounts of data needing to be stored. However, with the rapid development of IoT devices and ongoing business model transformation, we are not able to predict the volume and growth of IoT data. And with the lack of IoT history, traditional methods of IT and infrastructure planning based on the past do not app...
The many IoT deployments around the world are busy integrating smart devices and sensors into their enterprise IT infrastructures. Yet all of this technology – and there are an amazing number of choices – is of no use without the software to gather, communicate, and analyze the new data flows. Without software, there is no IT. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, Dave McCarthy, Director of Products at Bsquare Corporation; Alan Williamson, Principal ...
WebRTC has had a real tough three or four years, and so have those working with it. Only a few short years ago, the development world were excited about WebRTC and proclaiming how awesome it was. You might have played with the technology a couple of years ago, only to find the extra infrastructure requirements were painful to implement and poorly documented. This probably left a bitter taste in your mouth, especially when things went wrong.
SYS-CON Media announced today that @WebRTCSummit Blog, the largest WebRTC resource in the world, has been launched. @WebRTCSummit Blog offers top articles, news stories, and blog posts from the world's well-known experts and guarantees better exposure for its authors than any other publication. @WebRTCSummit Blog can be bookmarked ▸ Here @WebRTCSummit conference site can be bookmarked ▸ Here
A critical component of any IoT project is what to do with all the data being generated. This data needs to be captured, processed, structured, and stored in a way to facilitate different kinds of queries. Traditional data warehouse and analytical systems are mature technologies that can be used to handle certain kinds of queries, but they are not always well suited to many problems, particularly when there is a need for real-time insights.
Providing secure, mobile access to sensitive data sets is a critical element in realizing the full potential of cloud computing. However, large data caches remain inaccessible to edge devices for reasons of security, size, format or limited viewing capabilities. Medical imaging, computer aided design and seismic interpretation are just a few examples of industries facing this challenge. Rather than fighting for incremental gains by pulling these datasets to edge devices, we need to embrace the i...
Web Real-Time Communication APIs have quickly revolutionized what browsers are capable of. In addition to video and audio streams, we can now bi-directionally send arbitrary data over WebRTC's PeerConnection Data Channels. With the advent of Progressive Web Apps and new hardware APIs such as WebBluetooh and WebUSB, we can finally enable users to stitch together the Internet of Things directly from their browsers while communicating privately and securely in a decentralized way.