Simple MySQL Stored Procedure

February 14, 2009 – 10:39 am

Here's a simple stored procedure I find myself using for MySQL over and over again.  If you use a person's email address as a database key, you may need to occasionally update their email address, and as such, you should update all references.  Here's a simple stored procedure to accomplish this:

DELIMITER $$

DROP PROCEDURE IF EXISTS changeEmail $$
CREATE PROCEDURE changeEmail (oldEmail varchar(100), newEmail varchar(100))
BEGIN
 -- First check our arguments and that the old email exists
 IF (oldEmail IS NOT NULL) AND (newEmail IS NOT NULL) AND
    (EXISTS (SELECT * FROM users WHERE email=oldEmail))
 THEN
   -- Update all references of the old email to the new one here
   UPDATE users set email=newEmail WHERE email=oldEmail LIMIT 1;
   UPDATE billing set email=newEmail WHERE email=oldEmail;
   UPDATE cards set email=newEmail WHERE email=oldEmail;
   UPDATE orders set user_email=newEmail WHERE user_email=oldEmail;
 END IF;
END $$

DELIMITER ;

Enjoy!

By admin | Posted in Software Design | Comments (0)

Bailout Perspective

November 26, 2008 – 9:35 am

Some economists are estimating the final government bailout number to be around 3 trillion dollars when all is said and done, with stop reserves going out to 7 trillion.  According to census information, the total U.S. population is around 300 million.  So, if for some reason the government decided to give the taxpayers back their money (what a crazy concept), that would equate to $10,000 for every person living in the U.S.  That's $10,000 for every man, woman, and child, regardless of income, citizenship, or whether or not they even pay taxes!  I am no economist, but I think $40,000 in the hands of a middle-class family of 4 would go much farther than bailing out the corporations that started this mess...

By admin | Posted in Off the Wall | Tagged , , | Comments (0)

Mainstreet Bailout

October 22, 2008 – 6:57 am

I've come up with a plan to help main street while helping wall street at the same time.  No one wants to give anyone (mainstreet or wallstreet) a complete get out of jail free card, but the necessary evil is that something must be done, or we must struggle through an enormous recession and downturn in the economy.  Since the decision has already been made to do something, this article focuses on what should have been done to help mainstreet instead of just bailing out wall street's bad debt.

Step 1: Government buys all "upside down" loans and loans where the LTV is greater than 80%.  The central reason for this economic downturn is the decline in housing prices.  Average homeowners see their largest asset suddenly turn into their largest debt as they owe more on thier house than it is worth.  If the government is going to spend a massive amount of money, I would argue that money would better be spent on something that would primarily help the average tax payer while providing an outlet for banks to get rid of potentially toxic loans at the same time.  The most risky loans out there are loans for more than the house is worth, or loans where the loan to value is greater than the historical standard of 80%.  If the government is given the option to buy these loans from the banks (at a discount that the banks would pay for), the banks would suddenly have a way to get all of these loans off of their books -- help for wall street, but at a penalizing discount too.

Step 2: Government resets these loans to 0.00% interest.  Yes, that's right zero percent, zilch.  Suddenly homeowners are paying 100% principle in their monthly payments, and their monthly payment go down by hundreds of dollars a month.  People who were tempted to take the foreclosure route before, would now be crazy not to take advantage of 100% principle payments -- the opportunity of a lifetime, so these people will more than likely stay in their homes.  There would be no reduction in principle owed so main street doesn't get a complete bail out either.

Step 3: When LTV reaches 80%, loans are reset to original amoritization schedule.  When the loan to value is back down to a reasonable level, like 80%, the interest rate kicks back in.  Yes, the payments go back up, BUT there is an incentive to make the payments now that you have 20% of your home's value.  If you can't make the payments, you should be able to sell your home and at least break even.  This works because presumably these loans will reset at different times, thus avoiding a sudden influx of home inventory on the market.

Step 4: Change the rules.  Writing bad loans, wall street creating credit insurance worth many more times than the actual loan values, and preditory lenders all need to be curtailed.  Capitalism is great, but only with laws and rules to ensure that no one cheats the market or each other.

Is this fair?  No, not totally.  Those who have not indulged or who have wisely paid down the balance on their home won't get any direct help.  However, everyone will see their home values stablize and begin to trend upward again with the deflux of inventory from the market.  This won't be a permanent solution, unless things are done to ensure that homes are not being bought that shouldn't be and people are not getting the kind of loans they shouldn't be, otherwise we will be revisiting this situation again in the near future.

By admin | Posted in Off the Wall | Comments (1)

App.config Settings for a Project with no Output

October 17, 2008 – 1:58 pm

Sometimes more than just connection strings need to be stored to a settings or configuration file of some kind. If you are building a mult-tier application and your project is a class library with no run-able code, you can't just throw settings into an app.config file. Likewise, you wouldn't want to put your tier-specific settings in a different project just because that project contains the output (and the app.config).

A simple way around this problem is to create a settings file for your project.  To do so, right click on your project and select "Properties"

Navigate to the "Settings" tab and click the link to create a new settings file

Add the application specific parameters to your settings file via the designer.  Save and rebuild your project.

To use your settings, reference them via the following syntax:

Properties.Settings.Default.MySetting

Enjoy!

By admin | Posted in Software Design | Tagged , , | Comments (0)

SQL Server Find Text in Any Field

October 17, 2008 – 7:06 am

In working with SQL server 2005, I came across this useful query which will loop through every table and every field in a database searching for specified text.  This can really come in handy when you're debugging someone else's un-documented database and you know the text, but you're not sure where it is stored in the database.  Note that this will store the results of this query in a table called "Results", so if you have a table with that name, you'll want to rename the table below.  Also be sure to change your search criteria...  Enjoy!

DROP TABLE #Results
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('<YOUR_SEARCH_DATA_HERE>','''')
 
WHILE @TableName IS NOT NULL
BEGIN
  SET @ColumnName = ''
  SET @TableName =
  (
    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
    FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(
        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
      ), 'IsMSShipped'
    ) = 0
  )
 
  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
    SET @ColumnName =
    (
      SELECT MIN(QUOTENAME(COLUMN_NAME))
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
      AND TABLE_NAME  = PARSENAME(@TableName, 1)
      AND DATA_TYPE IN (
        'char', 'varchar', 'nchar', 'nvarchar', 'uniqueidentifier', 'smallint', 'identity'
      )
      AND QUOTENAME(COLUMN_NAME) > @ColumnName
    )
 
    IF @ColumnName IS NOT NULL
    BEGIN
    INSERT INTO #Results
    EXEC
    (
      'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
      FROM ' + @TableName + ' (NOLOCK) ' +
      ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    )
    END
  END
END
 
SELECT ColumnName, ColumnValue FROM #Results
By admin | Posted in Software Design | Comments (0)

Stress Breakdowns

September 12, 2008 – 12:34 pm

It's been my experience lately that there's two main circumstances in which developers can inccurr a significant amount of unhealthy work-related stress:

Nothing to do.  This doesn't sound stressful, but when you go to work and don't have anything to do it is extremely unfullfilling.  Not to mention the temptation to pull up inappropriate material from the web...

Working for other people's mistakes. When working on a team project, if you're having to work on code that is not yours, not your design, or dependant on others, this can be an extremely stressful environment.  If you find that your project/tasks are simply garbage because of other people's inability to write clean code, it is sometimes difficult to show others the fault is not your own.

At least those have been the two main culprits of my work-related stress as of late, what's yours?

By admin | Posted in Off the Wall | Tagged | Comments (0)

Firefox 3 RC2 Available

June 5, 2008 – 8:04 am

Firefox version 3, Release Candidate 2 is now available for download!  The immediate improvement I noticed: speed.  Version 3 appears to start up a bit faster, load pages faster, and handle javascript-laden pages faster.  Also nice to see were the numerous tasteful UI enhancements.  Firefox has always had a simple UI before, and I'm glad they didn't make the mistake of overhauling the interface for some whiz-bang features (see IE7).  There are subtle enhancements that the average user probably won't even consciously notice like fading in of highlighted tabs, site icons next to sites in the URL, smooth tab scrolling, and subtle toolbar color differences that better define action areas.  It's great to see smooth and subtle changes like this to further enhance this great browser.

Disclaimer: as a company that provides web development services, Holley Grove is partial to browsers which actually attempt to adhere to web standards and valid HTML/CSS.  Firefox 3 is a great browser to use to make sure you're doing your part to promote web standards and make the Internet a better place to visit!

Download Day - English

By admin | Posted in Off the Wall, Web Development | Tagged , | Comments (1)

SSHFS: A Perfect Remote File Manager?

March 26, 2008 – 7:26 pm

SSHFS is a Fuse-based Linux filesystem that allows you to connect to an SSH server, and mount it as you would any other mountable device.  The difference is... this "device" is a remote server using an SSH connection.  At Holley Grove, we use SSHFS on all of our servers, SSHFS might be something you should consider as well... here's just a few reasons why:

  1. You're basically using SSH to communicate.  SSH is encrypted, and as such can be one of the most secure methods of communication available.
  2. The setup is relatively simple, as you only need a few libraries on the client computers, and an SSH server running on the remote server.
  3. Permissions, users, and groups are all preserved as they would be using normal SSH.
  4. You can turn off in-secure, un-encrypted FTP completely.
  5. Fast access to files via your file manager application as if they were stored locally on your computer.  For Linux Gnome users, your SSHFS mounted shares will show up in Nautilus and act accordingly.

HowTo:

For Ubuntu, simply install sshfs via the package manager.  You'll then need to add your Ubuntu user account to the Fuse group so that you'll have permission to use Fuse.  To do this, go to System -> Administration -> Users and Groups.  Click on your username and click the "Properties" button.  Make sure the checkbox for "fuse" is checked and save your settings.  You may need to log out and log back in for these changes to take effect.  Now you can mount an SSH server in the directory called "remote":

sshfs landon@holleygrove.com: remote

And to disconnect:

fusermount -u remote

SSHFS Desktop ExampleIt's great to be able to run a command to mount an SSH server, then pop open nautlius and have all the remote files and directories listed right there just as any other files!

By admin | Posted in Linux Hosting, Web Development | Tagged , | Comments (0)

Sweet Relief

January 17, 2008 – 10:57 am

Starbucks Coffee CupI am not a smoker, but I can identify with those who are...  I would imagine lighting up your first cigarette of the day would be a lot like having your first triple ventii soy chai latte (or whatever the case may be).  It's a bad habit, yes, but for many it is almost impossible to break.

Yesterday I was stuck in Hartsfield Hell for about 6 hours while I waited for the snow to "let up."  I can indeed bear witness to the fact that the very first sip of this magical beverage somehow instantly made me feel better.  When you think about it, it's kind of scary that a company's product has such an addictive influence on so many of it's consumers.  In fact, a Google search on "Starbucks Addiction" turned up a whopping 6,490 results.  There are even howto articles written by actual M.D.s on how to break your Starbucks addiction.  Over-exaggerated product enjoyment, or mass consumer addiction?

By admin | Posted in Off the Wall | Comments (0)

Simple Candidate Aptitude Test

January 14, 2008 – 10:27 am

When looking for new hires, many companies make the mistake of requiring all candidates to complete a simple skill test.  At first, these tests seem like a good idea, and a quick way to eliminate a huge chunk of possible applicants who aren't as technically proficient as you need them to be.  However, often times, these "simple" tests offer no real benefit to the employer, waste time for both the employer (and it's developers who have to sift through the results) and the applicant, and can actually have detrimental results on your hiring campaign.

Reason #1: Insulting the Wrong Crowd

Let's suppose that you're looking to hire a few mid to senior level developers for an object-oriented PHP position.  So, you may spend the course of a week developing some really great, practical PHP questions and problems.  After you've posted the job on dice, you've got a few offers, yay!  Kindly, you'll send them an email with your "simple" questionnaire attached and give them a 24-hour deadline to complete the problems.  Well, right off the bat, you've turned off a number of qualified candidates by requiring them to take time out of their day to submit a blind PHP test that is admittedly "simple".  Many of the most qualified and highly sought-after candidates may view your test as beneath them, too basic, boring, and a waste of time.  Some of the most qualified candidates may have years and years of experience (some may have even more than you!); they may have built some extremely successful and robust projects far beyond the range of what you are even hiring them for; they may have advanced degrees, have started their own companies, done consultant work for even larger companies; they may also be recognized leaders in the industry... Those are precisely the kind of applicants your company needs, but also precisely the kind of applicants that feel an aptitude test (with basic aptitude questions) is well beneath their skill level.  "Any position that may require completing such a simple test, is probably a low-level code-monkey style position anyway."

Reason #2: Logistics

Let's take a second to think about the types of people are most likely to excel at a "take home" aptitude test.  Chances are you've run into these same people before...  These people are great test takers, and they excel at scouring the web and regurgitating information verbatim.  They will put in long hours, and they can afford to work all night on this test, mostly because they don't have as many other applications to fill out as the more qualified candidates.  They spend hours finding the exact right answers (which they did NOT already know and probably didn't even take the time to comprehend).  They'll polish up their answers, add some extra bells and whistles to their code (such as CSS styles and simple Javascript), and submit their results to you just before the deadline.  On the surface their results look great, but they are not there to explain their answers, describe to you their thought process, or even tell you how they arrived at their conclusion.  So, all you're really left with is some simple, regurgitated information from the web looked up by the same person who's cheated his or her way through the majority of his or her academic and professional life... probably not your ideal candidate.

Reason #3: Missing the Point

In the tech industry, code tools, languages, and platforms are often times stressed as the most important aspects of an applicant's resume.  This couldn't be a bigger misconception.  The truth is, an applicant's ability to think, design, problem-solve, and engineer overall solutions will determine his or her success in any technology-laden position, not his or her experience with a particular language.  Everyone knows a guy who has 20+ years experience with C programming, but has no concept of how to design a scalable, robust, object-oriented application.  His experience far out-matches that of a newly graduated Doctoral student, but when placed with the responsibility of designing a system from scratch, he has no idea which route would be best to take in order to solve the problem, he just immediately starts hacking his C libraries.  With an aptitude test, you may very well validate the applicants who've memorized all the built in functionality of their particular language, but you really haven't evaluated their ability to problem solve efficiently, which, is really what development is all about.

By admin | Posted in Design, Web Development | Comments (0)