Archive for the ‘SQL’ Category

FiftyTwo Apps is a Go!

Posted: December 30, 2007 in Adobe, AIR, ColdFusion, Flash, Flex, SQL

Hello World,

Ever year so many people make New Year’s resolutions. Well I am no exception. This year I have dedicated myself to create fifty two apps. And thus was born the FiftyTwoApps web site.

For the next fifty two weeks I will create fifty two apps – from conception to completion, from design to delivery. You will be able to follow my progress as I design, develop, document, test, and deliver each app to … well, all of you. I will make available the full source of each app, a summary of each of the development tasks, time spent on each task, resources used, and lessons learned. Every Monday I will begin work on a new app and one week later, by Sunday at midnight, I will upload the completed app onto the FiftyTwoApps web site. During the week I will publish my progress on the site so you can see where I am in the process.

So visit the site often – if you wish. Starting Monday December 31st I will begin the first of the fifty two apps,
the FlexTimer. I hope to see you there. The next fifty two weeks will be a wild ride.

Ciao

Advertisements

Flipping Flags Using SQL

Posted: December 22, 2006 in ColdFusion, SQL

Have you ever wanted change the values in your database table from “on” to “off” or “true” to “false” using one compact SQL Statement? Well I did and thus began my journey …

As a long time ColdFusion developer I can remember times when I was presented with the following requirement;

I have a page that displays the results of a DB query. Those results are displayed in a table showing several columns. One of the columns contain a checkbox which, is used by the user to select one or more rows to be activated or deactivated. The user should be able to check one or more rows and click on a button that will execute a process to reverse the flag values for the rows selected. Each checkbox contains the value of the ID of its row.

Now, in the past I may have approached this solution by placing a SQL SELECT and UPDATE statement inside a CFQUERY inside a loop. If the user selected 10 rows then the process would hit the database twice for each row selected – once to get (SELECT … WHERE …) the current value of my flag column and then again to change (UPDATE … SET …) the value based on the value returned from the SELECT statement. It worked but boy was it so very inefficient.

Well, that was NOT going to be the case this time (case … heh … remember that now). So, I set about looking for my own “Holy Grail” (OK smarty … remember, one guy’s simple solution may be another guy’s … you get the point). I enlisted the help of a co-worker of mine (JoAnn) and Voila! she found it. So without further adu, I present to you … the solution:

 

UPDATE  tblMyTable
SET     tblMyTable.ActiveBit =
        CASE    tblMyTable.ActiveBit WHEN 1 THEN 0 ELSE 1 END
WHERE   tblMyTable.ID IN (#CBSelections#)

So what’s happening here? We used a CASE statement in our SQL query to determine if the value of ActiveBit (a Bit data type column) is 1. WHEN the column value equals 1 THEN we set the column to 0 ELSE we set the column value to 1 – the END

P.S.

The WHERE clause restricts our UPDATE only to those rows that have ID that are IN the CBSelections list. Selected checkboxes will return a list of its values separated by commas.