Click here to close now.

Welcome!

.NET Authors: Kathy Thomas, Greg O'Connor, Esmeralda Swartz, Jayaram Krishnaswamy, Adine Deford

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
The WebRTC Summit 2014 New York, to be held June 9-11, 2015, at the Javits Center in New York, NY, announces that its Call for Papers is 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 16th International Cloud Expo, @ThingsExpo, Big Data Expo, and DevOps Summit.
@ThingsExpo has been named the Top 5 Most Influential M2M Brand by Onalytica in the ‘Machine to Machine: Top 100 Influencers and Brands.' Onalytica analyzed the online debate on M2M by looking at over 85,000 tweets to provide the most influential individuals and brands that drive the discussion. According to Onalytica the "analysis showed a very engaged community with a lot of interactive tweets. The M2M discussion seems to be more fragmented and driven by some of the major brands present in the M2M space. This really allows some room for influential individuals to create more high value inter...
The world's leading Cloud event, Cloud Expo has launched Microservices Journal on the SYS-CON.com portal, featuring over 19,000 original articles, news stories, features, and blog entries. DevOps Journal is focused on this critical enterprise IT topic in the world of cloud computing. Microservices Journal 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. Follow new article posts on Twitter at @MicroservicesE
Wearable technology was dominant at this year’s International Consumer Electronics Show (CES) , and MWC was no exception to this trend. New versions of favorites, such as the Samsung Gear (three new products were released: the Gear 2, the Gear 2 Neo and the Gear Fit), shared the limelight with new wearables like Pebble Time Steel (the new premium version of the company’s previously released smartwatch) and the LG Watch Urbane. The most dramatic difference at MWC was an emphasis on presenting wearables as fashion accessories and moving away from the original clunky technology associated with t...
SYS-CON Events announced today the IoT Bootcamp – Jumpstart Your IoT Strategy, being held June 9–10, 2015, in conjunction with 16th Cloud Expo and Internet of @ThingsExpo at the Javits Center in New York City. This is your chance to jumpstart your IoT strategy. Combined with real-world scenarios and use cases, the IoT Bootcamp is not just based on presentations but includes hands-on demos and walkthroughs. We will introduce you to a variety of Do-It-Yourself IoT platforms including Arduino, Raspberry Pi, BeagleBone, Spark and Intel Edison. You will also get an overview of cloud technologies s...
SYS-CON Events announced today that SafeLogic has been named “Bag Sponsor” of SYS-CON's 16th International Cloud Expo® New York, which will take place June 9-11, 2015, at the Javits Center in New York City, NY. SafeLogic provides security products for applications in mobile and server/appliance environments. SafeLogic’s flagship product CryptoComply is a FIPS 140-2 validated cryptographic engine designed to secure data on servers, workstations, appliances, mobile devices, and in the Cloud.
Containers and microservices have become topics of intense interest throughout the cloud developer and enterprise IT communities. Accordingly, attendees at the upcoming 16th Cloud Expo at the Javits Center in New York June 9-11 will find fresh new content in a new track called PaaS | Containers & Microservices Containers are not being considered for the first time by the cloud community, but a current era of re-consideration has pushed them to the top of the cloud agenda. With the launch of Docker's initial release in March of 2013, interest was revved up several notches. Then late last...
SOA Software has changed its name to Akana. With roots in Web Services and SOA Governance, Akana has established itself as a leader in API Management and is expanding into cloud integration as an alternative to the traditional heavyweight enterprise service bus (ESB). The company recently announced that it achieved more than 90% year-over-year growth. As Akana, the company now addresses the evolution and diversification of SOA, unifying security, management, and DevOps across SOA, APIs, microservices, and more.
After making a doctor’s appointment via your mobile device, you receive a calendar invite. The day of your appointment, you get a reminder with the doctor’s location and contact information. As you enter the doctor’s exam room, the medical team is equipped with the latest tablet containing your medical history – he or she makes real time updates to your medical file. At the end of your visit, you receive an electronic prescription to your preferred pharmacy and can schedule your next appointment.
GENBAND has announced that SageNet is leveraging the Nuvia platform to deliver Unified Communications as a Service (UCaaS) to its large base of retail and enterprise customers. Nuvia’s cloud-based solution provides SageNet’s customers with a full suite of business communications and collaboration tools. Two large national SageNet retail customers have recently signed up to deploy the Nuvia platform and the company will continue to sell the service to new and existing customers. Nuvia’s capabilities include HD voice, video, multimedia messaging, mobility, conferencing, Web collaboration, deskt...
The Open Compute Project is a collective effort by Facebook and a number of players in the datacenter industry to bring lessons learned from the social media giant's giant IT deployment to the rest of the world. Datacenters account for 3% of global electricity consumption – about the same as all of Switzerland or the Czech Republic -- according to people I met at the recent Open Compute Summit in San Jose. With increasing mobility at the edge of the cloud and vast new dataflows being predicted with the growth of the Internet of Things (and The Coming Age of Many Zettabytes) in the near...
The list of ‘new paradigm’ technologies that now surrounds us appears to be at an all time high. From cloud computing and Big Data analytics to Bring Your Own Device (BYOD) and the Internet of Things (IoT), today we have to deal with what the industry likes to call ‘paradigm shifts’ at every level of IT. This is disruption; of course, we understand that – change is almost always disruptive.
SYS-CON Events announced today that Cisco, the worldwide leader in IT that transforms how people connect, communicate and collaborate, has been named “Gold 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 City, NY. Cisco makes amazing things happen by connecting the unconnected. Cisco has shaped the future of the Internet by becoming the worldwide leader in transforming how people connect, communicate and collaborate. Cisco and our partners are building the platform for the Internet of Everything by connecting the...
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
Temasys has announced senior management additions to its team. Joining are David Holloway as Vice President of Commercial and Nadine Yap as Vice President of Product. Over the past 12 months Temasys has doubled in size as it adds new customers and expands the development of its Skylink platform. Skylink leads the charge to move WebRTC, traditionally seen as a desktop, browser based technology, to become a ubiquitous web communications technology on web and mobile, as well as Internet of Things compatible devices.
SYS-CON Events announced today that robomq.io will exhibit at SYS-CON's @ThingsExpo, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. robomq.io is an interoperable and composable platform that connects any device to any application. It helps systems integrators and the solution providers build new and innovative products and service for industries requiring monitoring or intelligence from devices and sensors.
Docker is an excellent platform for organizations interested in running microservices. It offers portability and consistency between development and production environments, quick provisioning times, and a simple way to isolate services. In his session at DevOps Summit at 16th Cloud Expo, Shannon Williams, co-founder of Rancher Labs, will walk through these and other benefits of using Docker to run microservices, and provide an overview of RancherOS, a minimalist distribution of Linux designed expressly to run Docker. He will also discuss Rancher, an orchestration and service discovery platf...
Sonus Networks introduced the Sonus WebRTC Services Solution, a virtualized Web Real-Time Communications (WebRTC) offer, purpose-built for the Cloud. The WebRTC Services Solution provides signaling from WebRTC-to-WebRTC applications and interworking from WebRTC-to-Session Initiation Protocol (SIP), delivering advanced real-time communications capabilities on mobile applications and on websites, which are accessible via a browser.
SYS-CON Events announced today that Aria Systems, the leading innovator in recurring revenue, has been named “Bronze Sponsor” of SYS-CON's @ThingsExpo, which will take place on June 9–11, 2015, at the Javits Center in New York, NY. Proven by the world’s most demanding enterprises, including AAA NCNU, Constant Contact, Falck, Hootsuite, Pitney Bowes, Telekom Denmark, and VMware, Aria helps enterprises grow their recurring revenue businesses. With Aria’s end-to-end active monetization platform, global brands can get to market faster with a wider variety of products and services, while maximizin...
SYS-CON Events announced today that Vitria Technology, Inc. will exhibit at SYS-CON’s @ThingsExpo, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Vitria will showcase the company’s new IoT Analytics Platform through live demonstrations at booth #330. Vitria’s IoT Analytics Platform, fully integrated and powered by an operational intelligence engine, enables customers to rapidly build and operationalize advanced analytics to deliver timely business outcomes for use cases across the industrial, enterprise, and consumer segments.