Click here to close now.

Welcome!

.NET Authors: Liz McMillan, Elizabeth White, Pat Romanski, Jaynesh Shah, Carmen Gonzalez

Related Topics: PowerBuilder, .NET

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
With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo in Silicon Valley. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 17th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal an...
P2P RTC will impact the landscape of communications, shifting from traditional telephony style communications models to OTT (Over-The-Top) cloud assisted & PaaS (Platform as a Service) communication services. The P2P shift will impact many areas of our lives, from mobile communication, human interactive web services, RTC and telephony infrastructure, user federation, security and privacy implications, business costs, and scalability. In his session at @ThingsExpo, Robin Raymond, Chief Architect at Hookflash, will walk through the shifting landscape of traditional telephone and voice services ...
The 17th International Cloud Expo has announced that its Call for Papers is open. 17th International Cloud Expo, to be held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, APM, APIs, Microservices, Security, Big Data, Internet of Things, DevOps and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding business opportunity. Submit your speaking proposal today!
Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at Internet of @ThingsExpo, James Kirkland, Chief Architect for the Internet of Things and Intelligent Systems at Red Hat, described how to revolutioniz...
All major researchers estimate there will be tens of billions devices - computers, smartphones, tablets, and sensors - connected to the Internet by 2020. This number will continue to grow at a rapid pace for the next several decades. With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo, June 9-11, 2015, at the Javits Center in New York City. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be
The security devil is always in the details of the attack: the ones you've endured, the ones you prepare yourself to fend off, and the ones that, you fear, will catch you completely unaware and defenseless. The Internet of Things (IoT) is nothing if not an endless proliferation of details. It's the vision of a world in which continuous Internet connectivity and addressability is embedded into a growing range of human artifacts, into the natural world, and even into our smartphones, appliances, and physical persons. In the IoT vision, every new "thing" - sensor, actuator, data source, data con...
Container frameworks, such as Docker, provide a variety of benefits, including density of deployment across infrastructure, convenience for application developers to push updates with low operational hand-holding, and a fairly well-defined deployment workflow that can be orchestrated. Container frameworks also enable a DevOps approach to application development by cleanly separating concerns between operations and development teams. But running multi-container, multi-server apps with containers is very hard. You have to learn five new and different technologies and best practices (libswarm, sy...
SYS-CON Events announced today that DragonGlass, an enterprise search platform, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. After eleven years of designing and building custom applications, OpenCrowd has launched DragonGlass, a cloud-based platform that enables the development of search-based applications. These are a new breed of applications that utilize a search index as their backbone for data retrieval. They can easily adapt to new data sets and provide access to both structured and unstruc...
There's Big Data, then there's really Big Data from the Internet of Things. IoT is evolving to include many data possibilities like new types of event, log and network data. The volumes are enormous, generating tens of billions of logs per day, which raise data challenges. Early IoT deployments are relying heavily on both the cloud and managed service providers to navigate these challenges. In her session at Big Data Expo®, Hannah Smalltree, Director at Treasure Data, discussed how IoT, Big Data and deployments are processing massive data volumes from wearables, utilities and other machines...
Buzzword alert: Microservices and IoT at a DevOps conference? What could possibly go wrong? In this Power Panel at DevOps Summit, moderated by Jason Bloomberg, the leading expert on architecting agility for the enterprise and president of Intellyx, panelists will peel away the buzz and discuss the important architectural principles behind implementing IoT solutions for the enterprise. As remote IoT devices and sensors become increasingly intelligent, they become part of our distributed cloud environment, and we must architect and code accordingly. At the very least, you'll have no problem fil...
SYS-CON Events announced today that MetraTech, now part of Ericsson, has been named “Silver Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9–11, 2015, at the Javits Center in New York, NY. Ericsson is the driving force behind the Networked Society- a world leader in communications infrastructure, software and services. Some 40% of the world’s mobile traffic runs through networks Ericsson has supplied, serving more than 2.5 billion subscribers.
The 4th International Internet of @ThingsExpo, co-located with the 17th International Cloud Expo - to be held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA - announces that its Call for Papers is open. The Internet of Things (IoT) is the biggest idea since the creation of the Worldwide Web more than 20 years ago.
The worldwide cellular network will be the backbone of the future IoT, and the telecom industry is clamoring to get on board as more than just a data pipe. In his session at @ThingsExpo, Evan McGee, CTO of Ring Plus, Inc., discussed what service operators can offer that would benefit IoT entrepreneurs, inventors, and consumers. Evan McGee is the CTO of RingPlus, a leading innovative U.S. MVNO and wireless enabler. His focus is on combining web technologies with traditional telecom to create a new breed of unified communication that is easily accessible to the general consumer. With over a de...
Disruptive macro trends in technology are impacting and dramatically changing the "art of the possible" relative to supply chain management practices through the innovative use of IoT, cloud, machine learning and Big Data to enable connected ecosystems of engagement. Enterprise informatics can now move beyond point solutions that merely monitor the past and implement integrated enterprise fabrics that enable end-to-end supply chain visibility to improve customer service delivery and optimize supplier management. Learn about enterprise architecture strategies for designing connected systems tha...
Cloud is not a commodity. And no matter what you call it, computing doesn’t come out of the sky. It comes from physical hardware inside brick and mortar facilities connected by hundreds of miles of networking cable. And no two clouds are built the same way. SoftLayer gives you the highest performing cloud infrastructure available. One platform that takes data centers around the world that are full of the widest range of cloud computing options, and then integrates and automates everything. Join SoftLayer on June 9 at 16th Cloud Expo to learn about IBM Cloud's SoftLayer platform, explore se...
SYS-CON Media announced today that 9 out of 10 " most read" DevOps articles are published by @DevOpsSummit Blog. Launched in October 2014, @DevOpsSummit 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. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time to wait for long development cycles that produce softw...
15th Cloud Expo, which took place Nov. 4-6, 2014, at the Santa Clara Convention Center in Santa Clara, CA, expanded the conference content of @ThingsExpo, Big Data Expo, and DevOps Summit to include two developer events. IBM held a Bluemix Developer Playground on November 5 and ElasticBox held a Hackathon on November 6. Both events took place on the expo floor. The Bluemix Developer Playground, for developers of all levels, highlighted the ease of use of Bluemix, its services and functionality and provide short-term introductory projects that developers can complete between sessions.
From telemedicine to smart cars, digital homes and industrial monitoring, the explosive growth of IoT has created exciting new business opportunities for real time calls and messaging. In his session at @ThingsExpo, Ivelin Ivanov, CEO and Co-Founder of Telestax, shared some of the new revenue sources that IoT created for Restcomm – the open source telephony platform from Telestax. Ivelin Ivanov is a technology entrepreneur who founded Mobicents, an Open Source VoIP Platform, to help create, deploy, and manage applications integrating voice, video and data. He is the co-founder of TeleStax, a...
The Internet of Things (IoT) promises to evolve the way the world does business; however, understanding how to apply it to your company can be a mystery. Most people struggle with understanding the potential business uses or tend to get caught up in the technology, resulting in solutions that fail to meet even minimum business goals. In his session at @ThingsExpo, Jesse Shiah, CEO / President / Co-Founder of AgilePoint Inc., showed what is needed to leverage the IoT to transform your business. He discussed opportunities and challenges ahead for the IoT from a market and technical point of vie...
Grow your business with enterprise wearable apps using SAP Platforms and Google Glass. SAP and Google just launched the SAP and Google Glass Challenge, an opportunity for you to innovate and develop the best Enterprise Wearable App using SAP Platforms and Google Glass and gain valuable market exposure. In his session at @ThingsExpo, Brian McPhail, Senior Director of Business Development, ISVs & Digital Commerce at SAP, outlined the timeline of the SAP Google Glass Challenge and the opportunity for developers, start-ups, and companies of all sizes to engage with SAP today.