Part 1 described how to create a bare bones SQL Server automated backup plan for a SQL Express database using a ColdFusion scheduled task. I also wanted to truncate and shrink old log files on a schedule, so came up with a stored procedure to do it, cobbled together from various snippets. The new stored procedure takes in a database name as parameter, then looks up the log file name. If it's found (it should be if the db name is correct) it truncates and shrinks the file. Here's the script:

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_TruncateLogs] 
       @databaseName nvarchar(1024)
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @sql NVARCHAR(1000)
 DECLARE @logfilename NVARCHAR(1000)

 SELECT @logfilename = b.name
 FROM sys.sysdatabases a 
  INNER JOIN sys.sysaltfiles b
   ON a.dbid = b.dbid
 WHERE fileid = 2
  AND a.name = @databaseName

 IF @@rowcount = 1 BEGIN
 
  SET @sql = '
  USE ' + @databaseName + '
  BACKUP LOG ' + @databaseName + '
  with truncate_only
  dbcc shrinkfile (' + @logfilename + ', 1)'

  EXECUTE sp_executesql @sql
 END

END

Next, just add it to the loop in the original ColdFusion scheduled task script:

<cfstoredproc datasource="master" procedure="dbo.sp_TruncateLogs">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#ListGetAt(variables.databases, variables.i)#" />
</cfstoredproc>

I decided to start using ColdFusion builder alongside Dreamweaver for my development. At work, we use Eclipse exclusively, but it is severely limited, and much of it doesn't work right (code hints...what the heck is hamcrestassert and why does it get inserted when I move to the next line???). I'm much more comfortable coding in Dreamweaver -- especially since the enhancements of CS4 and CS5 -- but have started liking the Ant build process and the Subversion integration of Eclipse/ColdFusion Builder. So I decided to start setting up projects in both.

My work environment is a lot different from the typical development workflow that I am accustomed to, so I wanted to set up an Ant build script for my local use on my laptop, and a remote FTP development server. ColdFusion Builder has most of what is needed, with the exception of a couple of Java classes for the FTP functionality (commons-net-*.jar and jakarta-oro-*.jar). Download these and add to the ColdFusion Builder (or Eclipse) plugins /lib folder for Ant, located here typically: C:\Program Files\Adobe\Adobe ColdFusion Builder\plugins\org.apache.ant_1.7.1.v20090120-1145\lib

I haven't found a lot of useful information for the ColdFusion developer on setting up Ant build scripts, so hopefully this will help someone. Basically, a build script is XML that contains a project tag and one or more target tags under it. You can also set up a property file that contains details about your project. This makes it easier to set up a generic build script that can be reused. Simply change the properties in the property file.

I'm going to assume that the project will have all the files to be deployed at the root of the project. This is not always the case, and indeed not usually the case, but changing paths is easy enough in the script. I'll add a build folder to the root of the project, and add the build.properties and build.xml files to this directory.

My build.properties file looks like this:

#deploy to LOCAL server
deploy.dir.local=c:/inetpub/wwwroot/mysite

#deploy to DEV server
deploy.dev.ftp.host=myftpsite.com
deploy.dev.ftp.username=myusername
deploy.dev.ftp.password=mypassword
deploy.dev.ftp.basedir=/

#deploy to LIVE server
deploy.live.ftp.host=myliveftpsite.com
deploy.live.ftp.username=myusername
deploy.live.ftp.password=mypassword
deploy.live.ftp.basedir=/

It contains information on the local directory where I do local testing, the FTP site where the development server resides, and the live server where the final deployment will happen. My build.xml file looks like this:

<project name="My Project" basedir="../." default="deploy.local">
 <!-- Load properties -->
 <property file="build/build.properties"/>

 <target name="deploy.local" description="Deploy to local webserver">  
  <echo>Copying files to local webserver...</echo>
  <mkdir dir="${deploy.dir.local}"/>
  <copy todir="${deploy.dir.local}">
   <fileset dir=".">
    <include name="**/*" />
    <exclude name="build/*" />
   </fileset>
  </copy>  
 </target>
 
 <target name="deploy.dev" description="Release to remote dev webserver">  
  <echo>Copying files to dev FTP server ${deploy.dev.ftp.host}...</echo>
  <ftp server="${deploy.dev.ftp.host}"
   remotedir="${deploy.dev.ftp.basedir}"
   userid="${deploy.dev.ftp.username}"
   password="${deploy.dev.ftp.password}"
   depends="yes">
   <fileset dir=".">     
    <include name="**/*" />
    <exclude name="build/*" />      
   </fileset>
  </ftp>   
 </target>
 
 <target name="deploy.live" description="Release to remote webserver">  
  <echo>Copying files to live FTP server ${deploy.live.ftp.host}...</echo>
  <ftp server="${deploy.live.ftp.host}"
   remotedir="${deploy.live.ftp.basedir}"
   userid="${deploy.live.ftp.username}"
   password="${deploy.live.ftp.password}"
   depends="yes">
   <fileset dir=".">     
    <include name="**/*" />
    <exclude name="build/*" />      
   </fileset>
  </ftp>   
 </target> 
</project>

A couple of explanations: the fileset tag includes the directory outside of the build directory (project root in this case), and the exclude tag excludes the build directory from the build process. You don't want these in your deployment directories. The depends attribute in the FTP tag allow you to exclude unchanged files from the FTP process, otherwise the build will FTP the entire project each time.

With these files in place, you can right-click on the build.xml file in the project and click Run As > Ant Build.... This brings up the Run dialog (clicking the option without the elipsis runs the build using the last known configuration).

Here, you can simply check the deployment options you want to use. If you are doing only local testing, check only the deploy.local box. If you want only the dev server, check that box. You can also check any combination. Next time, you can click Run As > Ant Build without the elipsis and it will just run using the last configuration. For example, if doing only local testing, check that box, so every time you hit the Run button, only local files will be deployed. The process is fast. Only changed files are deployed.

Run button

When you run the build, the Console window will show the results of building

Buildfile: C:\Documents and Settings\tmuck\Adobe ColdFusion Builder workspace\myproject\build\build.xml
deploy.local:
     [echo] Copying files to local webserver...
[copy] Copying 1 file to c:\inetpub\wwwroot\myproject
BUILD SUCCESSFUL
Total time: 407 milliseconds

I hope that helps someone.

I've been lax in my blogging and my ColdFusion duties lately. I just today installed CF 9 for the first time, and I'm usually way ahead of the curve on that. Work has been super busy, and we are stuck on CF 8 at the office for the foreseeable future. So far, the upgrade has been quick and painless. Now I need to start diving into the new features. I think the main feature for me is the general performance enhancements, but I'm sure I'll get into the Office integration as well. One nice thing about upgrading is being able to try out the features as Enterpise version for a month before putting in the license key for Pro version and losing the functionality.

I was also thinking about a SQL Server upgrade. I'm running the Express version, which is perfect for most small-medium web sites. One thing it doesn't have is a way to do maintenance. I've been doing manual backups. Well, after looking at the price of a SQL Server license, I decided to look for a good automated backup. I found a backup script online at http://www.mssqltips.com/tip.asp?tip=1174 and modified it to meet my needs (made directory name a parameter)

CREATE PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName nvarchar(1024),
@type CHAR(1) = 'F',
@directory nvarchar(1024) = 'C:\SQLBackup'
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql NVARCHAR(1000)
DECLARE @currentDate NVARCHAR(20)

SELECT @currentDate = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/',') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':',')

IF @type = 'F'
SET @sql = 'BACKUP DATABASE ' +
@databaseName +
' TO DISK = ' +
@directory + '\' +
@databaseName + '\' +
@databaseName + '_Full_' +
@currentDate + '.BAK'

IF @type = 'D'
SET @sql = 'BACKUP DATABASE ' +
@databaseName +
' TO DISK = ' +
@directory + '\' +
@databaseName + '\' +
@databaseName + '_Diff_' +
@currentDate + '.BAK' WITH DIFFERENTIAL'

IF @type = 'L'
SET @sql = 'BACKUP LOG ' +
@databaseName +
' TO DISK = ' +
@directory + '\' +
@databaseName + '\' +
@databaseName + '_Log_' +
@currentDate + '.TRN'

EXECUTE sp_executesql @sql
END

Next, I created my own stored procedure for deleting old database backups. Basically, in a "real" SQL Server, the backup task will delete backups back to a date you specify. We'll pass in the database name, type of backup, timeframe (in weeks), and the directory name.

CREATE PROCEDURE [dbo].[sp_RemoveOldBackups]
@databaseName sysname,
@backupType CHAR(1) = 'F',
@howlong integer = 4,
@directory nvarchar(1024) = 'C:\SQLBackup'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @type nchar(3)
SET @type = 'BAK'
if @backupType = 'L' BEGIN SET @type = 'TRN' END
DECLARE @backupPath nvarchar(2048)
SET @backupPath = @directory + '\' + @databaseName
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(ww, -1 * @howlong, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

EXECUTE master.dbo.xp_delete_file 0, @backupPath, @type, @DeleteDate,1

END

Now, rather than use Windows scheduled tasks for this, I wanted a simple ColdFusion scheduled task. The stored procedures should be stored in the master database, so you'll need a datasource that will allow execute permissions on these two stored procedures. Finally, save the following into a ColdFusion file and store it in one of your web sites. Set the scheduled task to execute once a week.

<cftry>
<!--- List of databases to backup --->
<cfset variables.databases = "mydatabase,Northwind,myOtherDatabase" />
<!--- Path to the backup folder --->
<cfset variables.backupPath = "Z:\SQLBackup" />
<!--- How many weeks to keep backups --->
<cfset variables.howLong = "4" />

<!--- Loop through the databases --->
<cfloop from="1" to="#ListLen(variables.databases)#" index="variables.i">
  <cftry>
    <!--- First, backup the database --->
    <cfstoredproc datasource="master" procedure="sp_BackupDatabase">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#ListGetAt(variables.databases, variables.i)#" />
      <cfprocparam cfsqltype="cf_sql_char" value="F" />
      <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.backupPath#" />
    </cfstoredproc>
    <!--- Next, delete the old databases --->
    <cfstoredproc datasource="master" procedure="sp_RemoveOldBackups">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#ListGetAt(variables.databases, variables.i)#" />
      <cfprocparam cfsqltype="cf_sql_char" value="F" />
      <cfprocparam cfsqltype="cf_sql_integer" value="4" />
      <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.backupPath#" />
    </cfstoredproc>
    <cfcatch>
      <cflog application="yes" text="Database backup failed #now()# for #ListGetAt(variables.databases, i)#" />
    </cfcatch>
  </cftry>
</cfloop>
<cfcatch>
   <cflog application="yes" text="Database backup failed #now()# outside loop" />
</cfcatch>
</cftry>

Obviously, this is the bare bones of a backup system. You can modify the scripts to perform differential backups to meet your needs, log file backups, and add some error handling to the stored procedures, but this is a good start.

I haven't been posting a lot lately, but had to post to recommend the latest version of Dreamweaver -- CS5. I usually upgrade to the latest version, because for my business I have to maintain compatibility with current versions of the program, but this is the first time in a while that I feel like I have to heartily recommend the upgrade to people spending good money. CS5 has quite a few of the features I've been wanting in the program for years -- foremost among them, code hints for custom code, code completion, and context-sensitive help. Most good code editors have this built in, but DW has not had capabilities in this regard. CS5 changes that and turns DW into a quality coding environment.

For ColdFusion coding I have been using Eclipse at work for the past couple years, only because that is the standard that we have at the office and require it's Subversion support, but it's sadly lacking in many key areas. It doesn't have a design view, which is a pretty basic feature for a web development environment. Also, the most frustrating part is that the code hinting, code completion, and context-sensitive help is pretty poor, and often inaccurate. I don't know how many times I've hit a key and had some bizarre code completion happen that has no relation to what I'm typing. A good coding environment is two steps ahead of you and knows what you want to type next. Apple's Xcode IDE is brilliant in this regard, as is Microsoft's coding environments. Dreamweaver is finally starting to get there, after all these years. Not quite there yet, but it's much better than alternatives for ColdFusion programmers, in my view. For PHP coding, it's pretty great, too.

All of my available extensions seem to work without issue in Dreamweaver CS5. If anyone has any issues, please let me know.

I'm having a sale on all extensions from now until December 31, 2009 -- 30% off all purchases. Use promo code "take30" when checking out and the price will be reflected in the cart.

I haven't been posting much lately, mostly because I've been pretty busy with life. I got a new job back in Nov. 2007, after 8 years at my previous company, and have been trying to cut back on some of my other outside work. The biggest change has been that I've gotten back into music. I played in a band all through the 80's and 90's, but gave it up for about 10 years and didn't play much at all, not even at home. A couple years back I started getting buying new equipment -- guitars, mostly. I got rid of some of my older, loud equipment and got some stuff to use in smaller clubs. Back in January, I started going to local blues jams. This has been a lot of fun, and I usually go to 2 or 3 every week now -- Sunday nights at Bangkok Blues in Falls Church, VA, and Thursday nights at the Zoo Bar in Washington, DC are the two main ones for me. The music in both venues is varied, but mostly top-notch. Some nights are really great for blues music. I also occasionally trek down to the Country Store in St. Mary's County, MD or the Beach Cove in Chesapeake Beach, MD. There are some other local blues jams that I have not tried yet, but will likely get out to play at soon.

Bigger news is that through these blues jams I've joined a couple of local bands -- The Alpha Dog Blues Band and the Southside Georgetown Bluesbreakers. Alpha Dog plays regularly at the Cowboy Cafe, JVs, and 219 Restaurant. The Bluesbreakers have played one gig at the DC Blues Society fish fry this past month.

Through the jams I've met a lot of great people and fine musicians. Some cool local DC bands to check out if you can are Idle Americans, Big Boy Little Band, Swampkeepers, Hot Rods and Old Gas, and the Andy Poxon Band.

Alpha Dog Blues Band is playing the following dates:

Community MX released its 3000th article the other day. It's hard to believe we have been at it for 6 years now releasing 2 articles every day. In celebration, we're having a contest that ends March 10th. Sign up to win an Adobe software title or other prize at http://www.communitymx.com/3000/. My most recent articles there are below:

Conditional PHP: Using the Ternary Operator	09-Feb-09
Category: PHP
Description: There are many uses for conditional statements in programming. The basic principle of conditional programming is that you want to execute code based on a certain condition. The essence of conditional programming is executing code based on the condition being true, with an alternate piece of code being executed if the condition is not true. The article will explain the ternary operator, which creates if/else logic that is more concise than other methods.
Using Table Aliases	29-Jan-09
Category: SQL
Description: When writing SQL statements, using the table names in the code is often necesary to avoid confusion in the database server. For example, if you have a Customers table with a customerid field, and an Orders table with a customerid field, if you are joining both tables, you will need to use the table name on the customerid field in order to differentiate between the two tables. This can turn your queries into much longer, hard to read statements -- especially if you are using proper naming conventions and your table names are long and descriptive. Using a table alias in the code is a beneficial way to increase readability and performance. Readers of this article should understand basic SQL coding, including joins. It will apply to most databases (SQL Server, MySQL, Access, etc).
Using Subqueries	15-Jan-09
Category: SQL
Description: Most database systems support the use of subqueries. If you have never used them, don't know what they are, or are confused by joins, then this article is for you. I'll be using the Northwind sample database that is readily available for SQL Server and Access, and available as a download for MySQL from my site.
Adding an Auto-Logout Feature - Part 1	17-Dec-08
Adding an Auto-Logout Feature - Part 2	06-Jan-09
Category: Javascript
Description: If you have a site that is built using one of the server technologies, like ASP, PHP, or ColdFusion, that has a login feature, chances are you also have a logout function. What happens when the user keeps the browser open and doesn't log out? Many times he will go back to the page and attempt to click on something, only to find out that he has to login again. For example, the user might fill in a complex form and walk away, only to come back to try to submit the form and be kicked out of the site. Because this is a client-side JavaScript function, it will work with any server-side language.
HTML/CSS Crash Primer	02-Dec-08
Category: CSS, HTML
Description: This article is intended as a re-introduction to HTML and CSS, and something I have used in the past to give to any new employees who may have had HTML experience, but exhibited some bad habits in actual coding. You may think HTML is simple and you may think you know all you need to know, but HTML is misused daily. Following are some basics to help correct some of the common problems.
Creating a Formatted Unique ID in PHP	19-Nov-08
Category: PHP
Description: There are often times that you need a unique id in an application, but don't want to use an auto-incrementing integer. If an application is well designed and secure, an auto-incrementing number is usually fine, but to be on the safe side a unique identifier other than an integer is better as an id. For example, a customer number is a good use of a unique identifier, or an order number.
Blank Pages in PHP	06-Nov-08
Category: PHP
Description: If you are a PHP developer, you are likely familiar with the blank page -- an error page with no error message. PHP has some default settings that prevent any error messages from appearing when you install PHP and do not adjust the error display settings. This article will show how to show your error messages.
Creating MySQL Logins for a Web Site	20-Oct-08
Category: PHP, SQL
Description: MySQL is a popular database to use for your web site, allowing the power of a server-based database management system to be used for free. With the added power comes added danger-MySQL is frequently targeted by hackers. My article on preventing SQL injection attacks mentioned database login security briefly. In this article, I will expand on how to actually create users for your web site, which is vital knowledge when you are acting as a database administrator. If you have a PHP site using MySQL and you don't have a database administrator-you are it.
Creating SQL Server Logins for a Web Site	08-Oct-08
Category: SQL
Description: SQL Server is a popular database to use for your web site, especially since the Express version has been released, allowing the power of SQL Server to be used for free, making it a much more viable solution than some of the other free databases out there, like MySQL. With the added power comes added danger -- SQL Server is frequently targeted by hackers. My article on preventing SQL injection attacks mentioned database login security briefly. In this article, I will expand on how to actually create users for your web site.

Be on the watch for new SQL injection attempts, coming from China, using an ascii-encoded binary string. Essentially, what it does is find a vulnerable database and append a string to the fields in every table. The string is a closing </title> tag with a script, followed by a comment to hide the rest of the page:

http://www.bloombit.com/Articles/2008/05/ASCII-Encoded-Binary-String-Automated-SQL-Injection.aspx

I put something like this in my application.cfm file to re-rout the attackers temporarily:

<cfif FindNoCase('user>0',cgi.query_string) OR findNoCase('declare',cgi.query_string)
OR findNoCase('EXEC(@',cgi.query_string)>
    <cflocation url="http://www.ftc.gov">
</cfif>

We got hit by some kind of odd storm yesterday...tornadoes, wind, rain, and other assorted nastiness. My server has been out the entire time -- over 24 hours -- but I'm back now.

Adobe has released the latest version of Dreamweaver to public beta at http://labs.adobe.com/technologies/dreamweavercs4/. As always, I have had a love/hate relationship with the program. Having used it a while now, I can say the same for the new version. I like many of the new features, like the related files views, the Subversion integration, and the collapsable panels (it's about time for this feature!). I will definitely be using DW 10 in the future, and have no intention of going back to the previous version. The collapsable panels alone are worth the price of admission. DW has always had problems with the usability of panels from the earliest days, but this time they got it right.

On the other hand, some things look like they were tacked on as an afterthought, and the overall interface is not as user-friendly as a basic Windows program should be that has been around for as many years as it has. The title bar is gone and the path to the current file has been stuck into a little spot that is reserved for page tabs. It sounds like a minor thing, but it's one of the most important parts of coding in a multi-tab editor. As soon as you open more than a few pages, the file path is cut off, and with a few more open, it disappears. Major step backwards here:

The other obvious usability problem here is the black text on dark grey in the tabs. On a laptop it's impossible to see, as is the white text on the dark grey. Also, the individual pages have a close button (X) which is a great bonus, but it would have been nice to have a close button on the frequently used panels (like Find/Replace) so you don't have to right click to pick a menu item to close a panel. Also, the entire border of the program is gone, making it blend into other open programs on your desktop. I understand this is the new "owl" interface, but owls should be left in the woods to live in trees. We really don't need the artsy fartsy stuff in a code editor.

But all in all, I would recommend the program. I use Eclipse all day long at my place of employment, and it is simply not as intuitive or easy to use as DW. Code view in DW has come a long way since the days of Ultradev. Download it and try it out, and by all means make suggestions in the forums on things that can be made better.