By: Nathan Johnson

Recently, I was asked to help automate some time consuming database update tasks for one of our Real Estate clients. They needed to ensure that their MLS listings were always up to date, but also couldn’t afford to take their site down while they were manually updating the database (imagine the data entry headache brought on by 1000 new MLS listings every day!). To further complicate matters, their hosting only supports ColdFusion, and can’t parse ASP or PHP pages. Thankfully, the concepts presented here are relatively straight forward and translate to PHP and ASP easier than the other way around.

Simply put, I needed to integrate their site with a program that automatically and seamlessly runs on their Cold Fusion server by updating their database once daily. Here’s how I did it!

First, I need to FTP download the updated database, which is hosted offsite. Here’s my FTP code:

	<!--- BEGIN DOWNLOADING DATA FILE --->

	Downloading MLS Listings...
	<cfset thread = CreateObject("java", "java.lang.Thread")>
	<cfflush>
	<cfset thread.sleep(100)>

		<!--- OPEN FTP CONNECTION TO MLS LISTING FTP SERVER --->
		<cfftp action = "open"
			username = "USERNAME"
			password = "PASSWORD"
			server = "TEST.DATABASE.COM"
			connection="myFtpConnection"
			stopOnError = "Yes"
			passive="yes">

		<!--- NAVIGATE TO THE DIRECTORY THAT CONTAINS THE MLS LISTING TEXT FILE --->
		<cfftp action="changedir"
			connection="myFtpConnection"
			directory="/SOURCEFOLDER"
			passive="yes">

		<!--- FTP DOWNLOAD THE MLS LISTING TEXT FILE (Listings.txt) --->
		<cfftp action="getfile"
			connection="myFtpConnection"
			remotefile="Listings.txt"
			localfile="C:\Inetpub\wwwroot\mysite.com\database_dir\Listings.txt"
			failifexists="no"
			passive="yes">

		<!--- CLOSE THE FTP CONNECTION --->
		<cfftp action = "close"
			connection = "myFtpConnection"
			passive="yes">

	DONE!<br>
	<br>
	Updating Database...
	<cfset thread = CreateObject("java", "java.lang.Thread")>
	<cfflush>
	<cfset thread.sleep(100)>

Note the use of the following code lines throughout the page:

	<cfset thread = CreateObject("java", "java.lang.Thread")>
	<cfflush>
	<cfset thread.sleep(100)>

Since this is a program that runs on the server, the server will not automatically print the status messages out while the program is running. As such, the page appeared to be loading and frozen when in fact it is waiting for the download to complete. By inserting the above lines, the server will pause for 100 milliseconds, long enough to print out the status messages written on the lines above, but not long enough to cause a noticeable delay in the update process. Another point of interest from above is the failifexists=”no” attribute of the tag. This is important to ensure that the local file is being overwritten each day as this update is run. Now that we’ve downloaded the updated database listings to a local file, I need to input the data into the database. This site uses a Microsoft Access .MDB database file, so I first need to clear out the existing lines of data in that file to prevent any old listings from showing up after they’ve been removed from the updated MLS database:

	<!--- SQL COMMAND TO CLEAR THE DATABASE DATA, WILL BE REWRITTEN --->
	<cfquery name="qryInsert" datasource="MLS">
		  DELETE * FROM listing_table
	</cfquery>

Also note that I had already set up the datasource name of “MLS” to refer to my database file located on the server, which only involved a quick call to the web hosting company.

Now, I need to parse the new text file into usable chunks of data and input that information into the database. To accomplish this, I used a CFLOOP tag that loops through each line of the text file as it is read by the server. Also, the source text file is tab delimited and I don’t want to have to refer to tabs when parsing my data (Just because I’m picky!), so I will replace all the tabs with vert line characters (“|”):

	<!--- OPEN THE LOCAL COPY OF THE LISTINGS TEXT FILE --->
	<cffile action="read"
		file="C:\Inetpub\wwwroot\mysite.com\database_dir\Listings.txt"
		variable="txtFile">

	<!--- SET COUNT AT 0 TO SKIP FIRST LINE OF DATA FROM LISTINGS TEXT FILE (COLUMN HEADINGS) --->
	<cfset CountVar = 0>

	<!--- 
	SET UP INDIVIDUAL LINES OF DATA TO INPUT TO THE SQL COMMAND:
		FIX SINGLE QUOTE PROBLEMS
		REMOVE ANY VERT LINES '|'
		REPLACE ALL TABS WITH VERT LINE DELIMITERS
	--->
	<cfloop 
		index="record"
		list="#Replace(Replace(Replace(txtFile,'''','''''','all'),'|','','all'),chr(9),'| ','all')#"
		delimiters="#chr(13)##chr(10)#">

Note that by using the delimiters of “#chr(13)##chr(10)#”, the text file is read one line at a time. The ASCII characters of NewLine (chr13) and carriage return (chr10) is the standard for denoting new lines in .txt files. One of the tricky things I discovered was that the text source file that holds the updated MLS data has a first line for the column headings. Obviously there wasn’t a house listing for “$L,IST,PRI.CE” (haha), so I needed to skip the first line while reading the file’s information. To get around this, I set up an independent variable that is incremented on each pass through the loop. With a simple IF/THEN set, I avoid inserting the line of invalid data into the database:

	<!--- SKIP DATA IF THE FIRST LINE (COLUMN HEADINGS) --->
	<cfif (CountVar gt 0)>

	<!--- SETUP THE VALUES FOR THE SQL COMMAND --->
	<cfif trim(listgetat(record,1,'|')) is ''>
		<cfset MLSNumber = ' '>
	<cfelse>
		<cfset MLSNumber = '#trim(listgetat(record,1,'|'))#'>
	</cfif>
	<cfif trim(listgetat(record,2,'|')) is ''>
		<cfset PropertyAddress = ' '>
	<cfelse>
		<cfset PropertyAddress = '#trim(listgetat(record,2,'|'))#'>
	</cfif>
	<cfif trim(listgetat(record,3,'|')) is ''>
		<cfset ListingPrice = '0'>
	<cfelse>
		<cfset ListingPrice = '#trim(listgetat(record,3,'|'))#'>
	</cfif>

Here, I assign variables to the various chunks of data, while I validate them against being blank or invalid entries. The ListGetAt function is a handy way to refer to various chunks of the current data array and the IF/THEN statements simply check to make sure that the data being read isn’t blank. If it is, the function returns a space character that takes care of ColdFusion’s built-in “skip the field if it’s blank” attitude – if you’re new to this, all you need to know is that ColdFusion will ignore that a field exists if the data is blank. For instance, an array that contains “1,2,3,,5” is turned into the array “1,2,3,5”. Since the value in the 4th column is blank, the server automatically turns the next value into that column! DOH! As a side note – IMHO, space characters are the easiest NULL values to deal with, since the word NULL can’t simply be trimmed out of a query response.

Also note that required numerical fields are entered with a “0” instead of a space, as a blank value is invalid for a numerical database field in Access. During development, it is also handy to output the information that is being built on the fly, since it’s easier to read through an output than to try and troubleshoot database error messages. Here’s one of the print lines I used during development:

	<p><b>Line Number <cfoutput>#CountVar#</cfoutput>:</b> 
		<cfoutput>INSERT INTO listing_table (MLSNumber, PropertyAddress, ListingPrice) 
		VALUES ('#MLSNumber#', '#PropertyAddress #', '#ListingPrice #')</cfoutput></p>

This simply prints out the variables along with a handy line number (very useful when trying to figure out whether a problem in validation is due to the source information or the page’s coding!). Now, I simply plug the info into my database and end my IF/THEN statement that ignores the column headings:

	<!--- SQL COMMAND TO INPUT THE LISTINGS DATA FROM THE TEXT FILE TO THE ACCESS DATABASE --->
	<cfquery name="qryInsert" datasource="MLS">
		  INSERT INTO listing_table (MLSNumber, PropertyAddress, ListingPrice)
		  VALUES ('#MLSNumber#', '#PropertyAddress #', '#ListingPrice #')
	</cfquery>

Also, I increment the counter so the next lines are read and entered in the DB, then looped to the next line, etc:

	<!--- INCREMENT THE COUNTER TO SKIP THE FIRST LINE (COLUMN HEADINGS) --->
	<cfset CountVar = CountVar + 1>

	</cfloop>

	DONE!<br>
	<cfset thread = CreateObject("java", "java.lang.Thread")>
	<cfflush>
	<cfset thread.sleep(100)>

That’s the upload and update program now there’s the problem of how to make the server run the script. You can either set up something server side (neither fun nor easy on Windows IIS), or you can simply make sure that the first person to navigate the site each morning causes the script to run. For more details see the ‘Emulating Crontab using Coldfusion‘ article.

---