MySQL User Defined Function Example: Data Cleanup

The Challenge

One of my current projects involves migrating a large amount of data away from an old custom Borland Paradox application into a new CiviCRM system. As with too many quickly-constructed apps, this old Paradox database wasn’t especially well designed and, among other the other challenges in salvaging the data, there were no restrictions on how dates could be entered. This means that, in multiple fields within each of the 20,000+ database records, I might see any of the following:

03-14-96
5/20/1975
10/02/83

While CiviCRM does have a utility for importing data from CSV and other SQL tables, it was having quite a time with this collection and many of the dates were being mishandled. That’s if the records weren’t rejected entirely for other reasons. Data migration doesn’t often happen with just a few settings adjustments and a click of the Import button.

Continue reading

A Couple of Notes on Cloud Computing

2000px-Cloud_computing_icon.svgYears ago, when I was making do with the limited computer equipment that I could afford, I never dreamed that I would one day be able to login to a website, plug in a few specs about the machine I wanted and then, a few minutes later, log into that machine remotely and run whatever programs I needed to. Yet, that’s exactly what today’s cloud computing resources enable me to do.

Continue reading

Coding Basics – Deceptively Simple Programming

In my last post on coding basics, I talked about turning an algorithm into code and used Euclid’s Algorithm as an example of programming a sequence of steps. There’s an even simpler type of algorithm that I want to look at this time. A formula, such as the one for converting Fahrenheit to Celsius, is also a series of steps that must be performed in order to achieve the needed result. These calculations can be represented within C# or other programming languages to include the formula as part of a larger program.

If you’re doing a one-time conversion from Fahrenheit to Celsius, you’re probably going to pull out your pocket calculator or find a good conversion website and enter the necessary numbers. You can do that because either of those resources has the necessary interface to get the necessary input from you. If you’re including this calculation in a program, you need to design your own interface, process that user input from it and return the result to the user in some way. In the case of the pocket calculator or website, other programmers pondered these issues at some point and now it’s your turn.

Continue reading

A Return to Programming

I’ve been working on a number of different projects lately, from I.T. networking to a book on MySQL, so I haven’t had as much reason to break out the programming tools as I used to. If not used regularly, programming skills can get a little rusty or even disappear like old friends from your college days that you lost contact with.

So, I’ve decided to delve back into the subject and update my status as a .NET programmer. The first step is a quick review of the C# language.  One resource that I can recommend for this is The C# Programming Yellow Book by Rob Miles. It’s just $0.99 for the Kindle version on Amazon.com and you can even get a free PDF version from the author’s website.

Continue reading

From Algorithm to Code

In previous articles, I’ve talked about the importance of finding the right algorithm, or series of steps to follow, when coding a solution. Efficiency in terms of the amount of memory used and the amount of time taken by the operation are key factors for the program. Sometimes an appropriate algorithm is already available and in wide use and it’s just up to the programmer to turn it into code. There’s always the option of running to StackOverflow and grabbing some code but that does nothing to further your talent.

Continue reading

Job Opportunities – PHP Developer and Web / Graphic Designer

I’m currently screening applicants for two full-time, in-house jobs in Ocala, Florida. Both of these positions will work as part of a team of local and remote developers designing e-commerce solutions.

PHP Developer w/ MVC, HTML5, JavaScript and LAMP

  • Develop, customize and implement high-quality web solutions that meet company expectations and requirements.
  • Contribute to technical solutions and requirements definition; solve complex problems by contributing to analysis and development of technical solutions.
  • Deploy code into a variety of staging and production environments.
    Meet regularly to provide status updates, reports on development activity and discuss project/product roadmap planning.
  • Provide systems administrative assistance when needed for off-site servers, either remotely or by visiting the off-site data centers.
  • Write, update maintain technical procedures & documentation, user guides and troubleshooting manuals for company software, technical support needs and end users.
  • Effectively translate complex, technical concepts into easy to understand language to assist non-technically oriented employees.

Web Designer / Graphic Design – Adobe Suite, WordPress / Magneto Theming

  • Create and modify web pages to enhance the digital and mobile experience in line with internal stakeholders (Marketing, Programming, and sales)
  • Plan site design by clarifying goals; designing functionality.
  • Develop site navigation by categorizing content; funneling traffic through content.
  • Upgrade site by updating content and graphics; monitoring performance and results; identifying and evaluating improvement options; introducing new technology; maintaining links.
  • Develop site content and graphics by coordinating with copywriters and graphic artists; designing images, icons, banners, audio enhancements, and keeping with industry standards.
  • Utilize the Content Management System to upload creative elements
  • Help develop and integrate interactive social and digital media to drive show content
  • Participate in developing the concepts and content for assigned teases, sales features, promotions, roll-outs and special events to improve customer messaging and conversion.

If you or anyone you know is interested in these positions, you can find out more about them on my job board on ComeauSoftware.com .

MySQL Explained – Revised and Updated

MySQL Explained - Cover image

MySQL Explained – Available on Amazon.com and LeanPub.com

My big project for March was a complete review of MySQL Explained, a book that I published last year on the popular open source database software used to power content management systems such as WordPress and Joomla along with other web applications. MySQL is a fixture in web hosting accounts and on internet database servers so, if you’ve done any serious work with web development, you’ve probably encountered it at least once.

Aside from it popularity as a back-end for web applications, MySQL’s open source model and its ease of installation in a variety of environments make it a great tool for learning about database concepts. It can be installed simply in a local directory, as a service within the operating system or as part of an AMP stack for the local development of web applications. It also has a variety of interfaces from the fully-graphical MySQL Workbench to the command line interface for those who prefer the direct control that it can give.

Continue reading

Installing MySQL on Mac OS X El Capitan

A few months ago, I wrote about how to install MySQL on Windows with IIS. Now I want to show you how to create an AMP web programming stack (Apache, MySQL & PHP) on Mac OS X. As with Windows, you could just go with a ready-made development environment like MAMP but, if you’re going to be doing real database and web design work with MySQL, it’s best to know how everything works.

Background

For this demonstration, I’m going to be using Mac OS X El Capitan (v.10.11), the latest version as of this writing.  My installation environment is a hosted Mac server with 1 GB of RAM and 40 GB of disk space. I will be demonstrating the process using the Terminal commands.

Unlike Windows, OS X already includes the Apache web server and PHP language pre-installed so it’s a much simpler process to ensure that everything is working together. You will need root access in order to install and configure the components.

Continue reading

Splitting a Database in Microsoft Access

Microsoft Access is unique among all of the Microsoft Office applications in that it can be used to create entire applications that contain large storehouses of data, versatile data entry forms and sophisticated reports to present the data in a variety of ways. It also goes beyond other applications such as Microsoft Excel in that a single database is able to store and organize large amounts of data from different sources and on different subjects while enabling many users to easily find and work with that data as needed. While an Excel spreadsheet is excellent for analysis and can provide access to multiple users, Access takes it a few steps further and enables users to store and manage volumes of data and present it in many different ways based on the needs of the data users and the intended distribution.

Why Split a Database?

The multi-user environment provides its own challenges. Access is often used as an ad hoc development tool by people outside the I.T. department to design needed solutions that would otherwise have to be purchased for large amounts of money. These applications can end up being used by many people across a company and that means there can be many people accessing the same database file at the same time. If enough people are in the file at once, the performance of the application can degrade pretty quickly. Also, when one person has the database file open, the file is then read-only for anyone else who opens it which means that they will be unable to create or edit their own reports or database queries.

Continue reading

“Microsoft Access has stopped working” – fixing corrupt files

Summary

Occasionally, when you’re making a lot of design changes to a Microsoft Access database, Access might start crashing when you’re working with that specific database file or when trying to enter the VBA environment with ALT-F11. This leaves you unable to investigate the issue or make further changes. It can be a sign that there are errors within the project’s compiled VBA code.  One solution is to use the /decompile switch to remove the compiled VBA code from the project and then recompile the source code.

"Microsoft Access has stopped working" error dialog

Like other Office applications, Microsoft Access can stop working for various reasons. Sometimes, it’s due to a kind of file corruption that’s unique to Access.

Detail

If you’re doing ongoing design work with Access, I recommend creating a new shortcut to the MSACCESS.EXE file on your system with the decompile switch. You would use this only when trying to recover a file. The rest of the time, you should use the normal menu shortcut.

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" /decompile

You can also enter this with the Run command on the Start menu and include the path to your database file after the /decompile switch.

The first file that you open after running Microsoft Access with this switch will be decompiled. This will not delete the source code behind your forms or reports but it will remove the compiled version of the code and might enable you to get back into the VBA environment.

VBA Compile Database command

The Debug >> Compile Database command compiles all of the VBA code and alerts you to potentially fatal errors.

Once you’re in VBA, use the Debug >> Compile Database command to recompile the database project. This will enable you to find and correct any problems within the code.

As always, be sure to make regular backups of your files as you’re coding in case this doesn’t work and you have to recover. Also be sure to make a backup of the file before you try to decompile it in case something goes wrong.

If this doesn’t work, then the last resort is to rebuild your database by importing all of the objects within it into a new database file. This is time consuming, however, although it does seem to remove a lot of temporary data that Access stores in the file, reducing the database size substantially in some cases.