Our server still uses CF5 (can't afford the money to buy or the time to
wrestle with CF8) but it does the job for us most of the time.
Here's the basic method:
Your MySQL DB table is set up with appropriate fields and data types and in
the sequence as required by the MLS with which you are working, i.e.
MLSNumber (int)(ID_Field), bedrooms (vchar), baths(vchar), sq_ft (int),
price (int), etc. (The one I work with has almost 70 fields in each of two
separate tables, because they keep commercial listings separate from
residential.)
Once you have those set up, create and schedule your cron job in unix or
whatever is appropriate scripting language for your server for the
appropriate time when the new zip file will be available. If you can use CF8
to do the same thing, that is fetch the zip files, unzip them and rename
them to overwrite the previous day's download, do it. I just am unaware of
"if" or "how" that's done with any version of CF. I paid a local geek $20.00
to write it (10 lines of code) in unix for me and it works great.
Next, create the file(s) needed to do the task(s) It can be done in one file
but the more you ask the file to do, the more it is likely the server is
going to time out the job. The CF Administrator can be set to allow longer
times but that isn't always desirable. Better to schedule two or more tasks
and use less run time for each.
Here's a sample of a typical update.cfm:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Update the listings</title>
</head>
<body>
<!--- First, we delete the data from the reslist table --->
<cfquery name="removelist" datasource="DNS" dbtype="ODBC">
Delete
>From reslist
</cfquery>
<!--- Grab the data from the Residential text file --->
<cfhttp url="http://www.mysite.com/MLS/Residential.txt"; method="GET"
name="reslist"
columns="AccessRoad,AppliancesChattels,Attic,BasementType,NumberofBathrooms,
NumberofBedrooms,ComplexSubdivision,Construction,County,ElementarySchool,Ext
eriorFeatures,ExteriorFinish,NumberofFireplaces,FireplaceStoveTypes,Flooring
,Foundation,Fuel,HighSchool,WaterHeater,MLSNumber,InteriorFeatures,ListingAg
ent,ListingOffice,ListingOfficePhone,LotAcres,LotSqft,LotDepth,SiteInfluence
,LotWidth,Township,MiddleSchool,NumberofParkingSpaces,Garage,ZipCode,PriceCu
rrent,PropertyType,Remarks,RoadFrontage,Roofing,UtilitiesServices,WaterFront
age,Shoreline,Sqft1stFloor,Sqft2ndFloor,Sqft3rdFloor,SqftBsmt,SqftFinishedBs
mt,SqftGarage,SqftTotal,NumberofStoves,Style,AmountofTaxes,Title,PhotoID,Uni
tNumber,Sewer,Water,YearBuilt,Zoning,LakeorRiverName,StreetName,StreetNumber
,AgentName,AgentID,TaxYear,OriginalPrice,LakeChain,LakeAcres,DateEntered,Dat
eListed,DaysOnMarket" textqualifier=" " delimiter=" "
resolveurl="false"></cfhttp>
<!--- Now insert that data into the reslist table --->
<cfoutput query="reslist">
<cfquery name="insertres" datasource="DNS" dbtype="ODBC">
Insert into reslist
Values
('#AccessRoad#','#AppliancesChattels#','#Attic#','#BasementType#','#Numberof
Bathrooms#','#NumberofBedrooms#','#ComplexSubdivision#','#Construction#','#C
ounty#','#ElementarySchool#','#ExteriorFeatures#','#ExteriorFinish#','#Numbe
rofFireplaces#','#FireplaceStoveTypes#','#Flooring#','#Foundation#','#Fuel#'
,'#HighSchool#','#WaterHeater#','#MLSNumber#','#InteriorFeatures#','#Listing
Agent#','#ListingOffice#','#ListingOfficePhone#','#LotAcres#','#LotSqft#','#
LotDepth#','#SiteInfluence#','#LotWidth#','#Township#','#MiddleSchool#','#Nu
mberofParkingSpaces#','#Garage#','#ZipCode#','#PriceCurrent#','#PropertyType
#','#Remarks#','#RoadFrontage#','#Roofing#','#UtilitiesServices#','#WaterFro
ntage#','#Shoreline#','#Sqft1stFloor#','#Sqft2ndFloor#','#Sqft3rdFloor#','#S
qftBsmt#','#SqftFinishedBsmt#','#SqftGarage#','#SqftTotal#','#NumberofStoves
#','#Style#','#AmountofTaxes#','#Title#','#PhotoID#','#UnitNumber#','#Sewer#
','#Water#','#YearBuilt#','#Zoning#','#LakeorRiverName#','#StreetName#','#St
reetNumber#','#AgentName#','#AgentID#','#TaxYear#','#OriginalPrice#','#LakeC
hain#','#LakeAcres#','#DateEntered#','#DateListed#','#DaysOnMarket#')
</cfquery>
</cfoutput>
Mission accomplished!
</body>
</html>
And that's it! Save the file and you should be able to set it up in the CF
Administrator to run at least 15 minutes after your cron job. But check it
because your MLS source files may not be available every day and when they
aren't, your table will have NO records. Then you might have to get in touch
with the folks at the MLS to nudge them into fixing their end. I'm sure it
might be possible to have the update file check the file size of the source
..txt file before overwriting it but I don't know how to do it. If someone
reads this that does, let me know. It sure could come in handy sometimes.
Nate, I hope this is helpful. Like I said, I'm not familiar with any version
of CF beyond CF5 and that's often a mystery to this old carpenter.
Dave Long
http://www.northgoods.com
-----Original Message-----
From: Nathan C. Smith [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 17, 2008 1:09 PM
To: CF-Talk
Subject: RE: insert/retrieve image into database SQL Server 2005/CF8?
Hi Dave,
Are you using CF8? what SQL data type is the column you are inserting into
and what does the SQL query look like?
I was hoping to avoid the tired old in-DB v. Out-of-DB conversation. These
are small pictures and fewer than 100 so it shouldn't be a big deal.
Thanks,
-Nate
> -----Original Message-----
> From: Dave Long [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 17, 2008 12:56 PM
> To: CF-Talk
> Subject: RE: insert/retrieve image into database SQL Server 2005/CF8?
>
> I do it using a unix cron job timed to retrieve, unzip and
> rename the files
> soon after they're refreshed on the remote server, then set a
> CF scheduled
> task to run about 15 minutes later that deletes the existing
> records and
> inserts the new ones from today.
>
> The CF task takes about 10 minutes or less to install about
> 6,000 records
> and I have found it a good idea to put a link to my real
> estate site in the
> Windows startup folder so I can actually eyeball the app
> first thing every
> day... it has rarely been known to fail for various reasons.
>
> But it works great almost all the time. I even have a
> separate site to boost
> links to my hosted brokers' sites and listings at
> http://www.northgoodsrealestate.com. I'm sure it could be
> designed better
> but at my skill level, this seems to work ok for my folks and
> includes a
> number of different methods for visitors to search for a listing.
>
> Dave Long
>
>
>
> -----Original Message-----
> From: Nathan C. Smith [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 17, 2008 10:00 AM
> To: CF-Talk
> Subject: insert/retrieve image into database SQL Server 2005/CF8?
>
> Hi,
>
> I'm trying to insert an Image into MS SQL Server 2005
> database using CF 8.
> I started to work with the SQL Server 'Image' data type but
> I'm not sure
> this data type is the best/correct choice.
>
> Somebody has probably done this before, so what data-type
> should/could I use
> in SQL Server - The choices include Image, binary, varbinary, and
> varbinary(max). Should I be using the new image tags or
> resorting to older
> methods to achieve the path of least resistance?
>
> Here is a sample of what I am working on, here I am just
> trying to read a
> file and place it in the database....
>
> <cffile action="READBINARY" variable="ThisPicture"
> file="/var/www/images/staffpics/#staffID#.jpg">
> <cfset myImage=ImageNew(ThisPicture)>
> <cfquery name="qryinsertImage" datasource="#CMSDSN#">
> Update tblStaff
> Set staffphoto = <cfqueryparam
> value="#ImageGetBlob(myImage)#" cfsqltype="cf_sql_blob">
> Where StaffID = #staffID#;
> </cfquery>
> <!--- write out the image: check to make sure something
> works. --->
> <cfimage action="WRITETOBROWSER" source="#myImage#"><br>
>
> I'm getting
> "The source file should contain an extension, so that
> ColdFusion can
> determine the image format. Verify your inputs. The source
> file should
> contain an extension, so that ColdFusion can determine the
> image format."
> as an error. It isn't clear, at least to me, where I can be
> more specific
> about setting the image format - is this an error from SQL
> server? The
> manual says "If you do not specify a source image, an
> "unknown source image
> format" error is generated", am I not pointing to my variable
> correctly?
> The examples in the manual show an insert statement and I am
> trying to do an
> update, is my query syntax messed up?
>
> My next question is obviously about getting the images out of
> the DB, so if
> there are any tricks to that, please feel free to elaborate.
>
> Many thanks,
>
> -Nate
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296781
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
