An American CF developer's adventure to Holland (part 1)
ColdFusion, Jobs
I've resigned my current position to move to Holland to work at PeopleXS.com as the Lead CF Developer. So, this is part 1 of the following blog series I plan to write.
- How does one decide to completely uproot one's family, home, and job to dare move to a country on another continent?
- How difficult is it to move to Holland?
- What is it like to live in Holland as an American expat?
- How is being a Web Developer different in Holland than in America?
Obviously, parts 2-4 will have to wait until I get to Holland. And if there's interest in the subject, I may write additional related entries.
Part 1:
How did I decide to completely uproot my family, home, and job to dare move to a country on another continent? Not easily!
While reading through blogs (I forget which one), I noticed a blog that described a Lead CF Developer position for PeopleXS.com. BTW - there's still some openings for additional developers. I emailed it to myself to look over later, as I had always been curious about what it would be like to live in Europe (especially a progressive, liberal country like the Netherlands). A week later, I finally got around to emailing my resume in.
PeopleXS was glad I had applied, was open to hiring developers from outside the country, and really piqued my interest. So, the next evening at the dinner table at home, not expecting a good reaction from my wife, I tried to casually mentioned that I had found an "interesting opportunity" in Holland. Without blinking she smiled and said, "let's do it!"
.... (back to earth) ....
I was completely floored by her reaction, but love her all the more for it. I hadn't even told her anything about it really, so obviously we had a lot more "figuring out" to do, but it gave me enough of a boost to forge onward.
Where? The Hague
When? October 1st
Who? me, my wife, and 2-year old son
What? Lead Developer of a small team of CF developers and a QA team that will expand to meet the needs of a fast-growing SaaS company.
How? Fly us to The Hague a few days early to get set up in temporary housing before my first day on the job.
Paid time off? 5 weeks, instead of 2 weeks here at most jobs in the US
Transportation? bikes and public transit (see video below)
How long? indefinitely - as long as I like the country and job
But what about everything I have to leave behind? Family? Job? House? Friends? Material things? Austin, the live music capital of the world, for God's sake?!!
- Family and friends - this is obviously the most difficult, and it really hasn't "set in", yet. But I'm sure we'll get very emotional as it gets closer, and we say our last good-byes. And our families took the news much better than expected, and have been very supportive. Not to mention we'll use much of our vacation/holiday time to visit the US as much as we can.
- House - in the process of being sold. (cross your fingers)
- Job - also pretty scary, since I had a very solid, stable job in a fast growing company. I may look back in a few months and think, "what the hell did I do?!!!"
- Austin - yes, it's where I grew up and I love it. But still it's in *Texas*. I don't want to look back at my life, when I'm barely able to lift my own head, and wonder why I spent my entire in one place. I'd like to think I'm a bit adventurous at heart. We'll see if I can handle a real adventure...
- Material things - aside from a few personal items, gifts, and family stuff, I'm honestly not really *attached* to much that I own, man. My hippie parents passed this attribute to me, and I guess it's finally paying off. ;-) [er, oh wait - I can't really bare to give up my Wii and Wii Fit, though... and maybe it'll be a way to lure a few Dutch people to be my friends...
After over a month of interviewing, long strings of emails to PeopleXS, mulling it over and over with my wife, and rationalizing that this was "too big of an opportunity to pass up", we took the plunge and I accepted the position. Only patience, positivity (is that the right word?), hard work, and an open mind will keep us on the right track for this crazy ride. Let me know if you think I'm crazy, or if you have any questions?
What does "The Hague/Den Haag" look like? see this video
Experienced CF Developer needed
ColdFusion, SQL Server 2005, JobsMy job is up for grabs, and it's the best development job I've ever had (and I've had quite a few ;-).
I'm *only* leaving it because I'm moving to Holland (more on that in another post). I have no complaints, but want to make it clear that the job is for people who want to work in a small ".com style" team. The owners are so nice to work for, and it's truly been a pleasure to have the freedom to design the code creatively, use the new "Enterprise" CF 8 features, see my work directly increase revenue, and get things done quickly without corporate red tape and umpteen levels of middle-management. Below is the job description.
"We are looking hard for an experienced ColdFusion Developer opening we have to fill immediately. This would be for the lead programmer for the LandsofAmerica.com Network which is the nation’s largest rural real estate website. We have many cutting-edge applications we are working on and implementing with the latest in ColdFusion 8 in exciting and new ways.
This position needs experience in high traffic websites to manage our 16 million page views across 300 websites including a new partner that has the potential to add another 30% to our traffic numbers in the next 3 months. We have started powering the Farm & Ranch sections on the Houston Chronicle and Texas Monthly as well as new Hearst properties soon. High traffic website management as well as web server/database server optimization experience is a must. Work experience with CF8 and SQL Server 2005 is a big plus.
We are a hard working, small team that gets things done quick and can see results fast. It’s a lot of fun! We are growing at a phenomenal rate with our number of listings at 63,000 and just two years ago they were at 2,500.
If you are interested please send your resume and any work you’ve done in the past two years. A detailed description of the exact work done will be very beneficial in moving this process forward ASAP. If you know of anyone this opportunity might sound interesting to, please forward this on to them.
Great pay, full health coverage, and monthly bonuses. "
send resumes to allen@landsofamerica.com
Add Comment
RegEx to find SQL in code without CFQueryParam
ColdFusion, CFEclipse, RegexI know it's far from perfect (that's partly why I'm posting - so you can show me the light), but here's a RegEx I'm using in Eclipse to find all the places in our code where we didn't use CFQueryParam around variables (shame, shame). Please let me know if you see ways to improve it.
\s+(where|set)\s+[a-z_\.]+\s+(like|\=)\s+'?%?#[a-z"_'\.\(\)\+\/]+#%?'?
And can someone help me figure out how to make this Regex include CF variables passed in to INSERT statements?
TIA
Paginating Records in CF with One SQL Server 2005 call (update 1)
ColdFusion, SQL Server 2005I've been meaning to update folks on the excellent use I've been getting out of this way of paginating records with SQL Server 2005 via CF, and James Allen's comment today gave me an excuse to finally update folks:
- Note that I've been using this method in production at www.LandsofAmerica.com - see some example search results, as well as all our sister sites: www.landsofTexas.com, www.landsofCalifornia.com, etc. Our combined network gets upwards of 15 ColdFusion requests per second, and this dynamic stored procedure has held up extremely well, despite the enormous load and wide variety of possible WHERE clauses in the search.
- And now to answer James's comment, "I've been using your SP extensively around my new site and it's working great. However, I am now tring to get it to work with a table join and I just can't get it to work with a table or alias prefix (in the columns parameter)." - The "sample DAO CFC method" code below should give you what you need to fix the issue you refer to. :)
First, the Stored Procedure SQL again:
USE [propertyControlCenter]
GO
/****** Object: StoredProcedure [dbo].[sproc_pagination] Script Date: 06/09/2008 11:28:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================
-- Author: Aaron Longnion
-- Create date: 10/18/2007
-- Description: A gereral-use way of dynamically
-- creating SQL to easily paginate the results;
-- also includes a total record count based on
-- the search criteria in the WHERE clause
-- ==================================================
CREATE PROCEDURE [dbo].[sproc_pagination]
-- Add the parameters for the stored procedure here
@SqlColumns VARCHAR(MAX),
@SqlFriendlyColumns VARCHAR(MAX),
@SqlTableClause VARCHAR(MAX),
@StartRow INT,
@EndRow INT,
@SqlWhere VARCHAR(MAX),
@SqlRowNumOrderBy VARCHAR(MAX),
@SqlOuterOrderBy VARCHAR(MAX)
AS
DECLARE @rsSQL NVARCHAR(MAX)
DECLARE @rcSQL NVARCHAR(MAX)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- build pagination SQL, using StartRow and EndRow to determine
-- which results to output
SET @rsSQL = N' WITH tempTable AS ( ' +
N' SELECT ' +
@SqlColumns +
N' , ROW_NUMBER() OVER(ORDER BY ' +
@SqlRowNumOrderBy +
N' ) AS RowNumber ' +
N' FROM ' +
@SqlTableClause
IF @SqlWhere + '' <> ''
BEGIN
SET @rsSQL = @rsSQL +
N' WHERE ' +
@SqlWhere
END
SET @rsSQL = @rsSQL +
N' ) SELECT ' +
@SqlFriendlyColumns +
N' FROM tempTable ' +
N' WHERE RowNumber >= ' +
CAST(@StartRow AS NVARCHAR(32)) +
N' AND RowNumber <= ' +
CAST(@EndRow AS NVARCHAR(32)) +
N' ORDER BY ' +
@SqlOuterOrderBy
-- uncomment PRINT to debug
--PRINT @rsSQL
EXEC sp_executesql @rsSQL
-- build second recordset simple for the count
SET @rcSQL =
N'SELECT COUNT(*) AS CountAll FROM ' +
@SqlTableClause
IF @SqlWhere + '' <> ''
BEGIN
SET @rcSQL = @rcSQL +
N' WHERE ' +
@SqlWhere
END
EXEC sp_executesql @rcSQL
SET NOCOUNT OFF;
END
PRINT 'sproc_pagination created'Second, some sample Gateway CFC method code:
<cffunction name="searchInventory" output="false" access="public" returntype="struct" hint="Returns total count and recordset based on search criteria"> <cfargument name="SearchCrit" required="true" type="struct" hint="Search criteria, usually passed in from the URL scope" /> <cfset var retStruct = StructNew() /> <cfparam name="Arguments.SearchCrit.SiteStateID" type="string" default="" /> <cfparam name="Arguments.SearchCrit.State_ID" type="string" default="" /> <cfparam name="Arguments.SearchCrit.Region_ID" type="string" default="" /> <cfparam name="Arguments.SearchCrit.County_ID" type="string" default="" /> <cfparam name="Arguments.SearchCrit.Style" type="string" default="" /> <cfparam name="Arguments.SearchCrit.Style_ID" type="string" default="" /> <cfparam name="Arguments.SearchCrit.SqFt" type="string" default="" /> <cfparam name="Arguments.SearchCrit.Price" type="string" default="" /> <cfparam name="Arguments.SearchCrit.AuctionDateStart" type="string" default="#DateFormat(Now(), 'yyyy-mm-dd')#" /> <cfparam name="Arguments.SearchCrit.AuctionDateEnd" type="string" default="" /> <cfparam name="Arguments.SearchCrit.ListingType" type="string" default="" /> <cfparam name="Arguments.SearchCrit.PageNum" type="numeric" default="1" /> <cfparam name="Arguments.SearchCrit.RecordsPerPage" type="numeric" default="15" /> <cfparam name="Arguments.SearchCrit.sortby" type="string" default="sqft" /> <cfparam name="Arguments.SearchCrit.sortbyDirection" type="string" default="DESC" /> <!--- derive start and end rows ---> <cfset Arguments.StartRow = ( (Arguments.SearchCrit.PageNum-1) * Arguments.SearchCrit.RecordsPerPage )+1 /> <cfset Arguments.EndRow = (Arguments.StartRow + Arguments.SearchCrit.RecordsPerPage)-1 /> <cfset retStruct = DAO.searchInventory( ArgumentCollection=Arguments ) /> <cfset retStruct.PagingData = Server.Utilities.Toolbox.getPagingLinks( RecordsPerPage = Arguments.SearchCrit.RecordsPerPage , RecordCount = retStruct.RC , NumberOfLinks = 10 , RecordLimit = 1000000 , QSItemsToStrip = "messageCode,searchIt" ) /> <cfreturn retStruct /> </cffunction>
Third, a sample DAO CFC method. This is the key: see how the "friendly" (no table aliases) names of the columns are in the "local.SqlFriendlyCols" section. Notice how the column names in the "local.SqlCols" clause contain references to table aliases in "local.SqlTableClause". The only caveat for this, which I haven't found a work-around yet, is that you'll get an "ambiguous column" SQL error if you have a JOIN and try to sort by a column that does not have a unique name :
<cffunction name="searchInventory" access="package" output="false" returntype="struct">
<cfset Var local = StructNew()>
<!--- columns --->
<cfsavecontent variable="local.SqlCols">
I.listing_city,
ST.State_name,
ST.State_shortCode,
S.listing_type,
I.listing_price,
I.listing_photo,
I.listing_id,
I.listing_state_id,
I.listing_lot_size,
I.listing_county_id,
C.county_name
</cfsavecontent>
<!--- friendly cols (if no JOINs/aliased table names, inherit from SqlCols above) --->
<cfsavecontent variable="local.SqlFriendlyCols">
listing_city,
State_name,
State_shortCode,
listing_type,
listing_price,
listing_photo,
listing_id,
listing_state_id,
listing_lot_size,
listing_county_id,
county_name
</cfsavecontent>
<!--- tables --->
<cfsavecontent variable="local.SqlTableClause">
listing_(nolock) I
INNER JOIN
listing_types(NOLOCK) S
ON S.listing_type_ID = I.listing_type_ID
INNER JOIN
counties(nolock) C
ON C.County_ID = I.listing_County_ID
INNER JOIN
states(NOLOCK) ST
ON ST.State_ID = I.listing_State_ID
</cfsavecontent>
<!--- conditions --->
<cfoutput>
<cfsavecontent variable="local.SqlWhere">
I.listing_Active = 1
AND I.listing_Acct_Active = 1
AND I.listing_Status = 0
<!--- dynamic info --->
<cfif Len(Arguments.SearchCrit.siteStateID)>
AND I.listing_state_id = #Arguments.SearchCrit.siteStateID#
</cfif>
<cfif ListLen(Arguments.SearchCrit.State_ID)>
AND I.listing_state_id IN(#Arguments.SearchCrit.State_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.Region_ID)>
AND C.listing_Region_id IN(#Arguments.SearchCrit.Region_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.County_ID)>
AND I.listing_County_id IN(#Arguments.SearchCrit.County_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.listing_type)>
AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type#)
<cfelseif ListLen(Arguments.SearchCrit.listing_type_ID)>
AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.SqFt)>
AND I.listing_size >= #Val(ListFirst(Arguments.SearchCrit.listing_size))#
AND I.listing_lot_size <= #Val(ListLast(Arguments.SearchCrit.listing_size))#
</cfif>
<cfif ListLen(Arguments.SearchCrit.listing_Price)>
AND I.listing_Price >= #Val(ListFirst(Arguments.SearchCrit.listing_Price))#
AND I.listing_Price <= #Val(ListLast(Arguments.SearchCrit.listing_Price))#
</cfif>
</cfsavecontent>
</cfoutput>
<!--- order --->
<cfoutput>
<cfsavecontent variable="local.SqlRowNumOrderBy">
#Arguments.SearchCrit.sortby# #Arguments.SearchCrit.sortbyDirection#
</cfsavecontent>
<!--- used for order column that has an alias prefix --->
<cfsavecontent variable="local.SqlOuterOrderBy">
#ListLast(local.SqlRowNumOrderBy, ".")#
</cfsavecontent>
</cfoutput>
<!--- dynamically get the data based on the SQL built above --->
<cfset local.returnStruct = Server.DAO.Common.getPaginatedData( SqlCols = local.SqlCols,
SqlFriendlyCols = local.SqlFriendlyCols,
SqlTableClause = local.SqlTableClause,
StartRow = Arguments.StartRow,
EndRow = Arguments.EndRow,
SqlWhere = local.SqlWhere,
SqlRowNumOrderBy = local.SqlRowNumOrderBy,
SqlOuterOrderBy = local.SqlOuterOrderBy ) />
<cfreturn local.returnStruct />
</cffunction>
And finally, the getPaginatedData() method:
<cffunction name="getPaginatedData" access="public" output="false" returntype="struct" hint="generic way to get pagination data from the dB">
<cfargument name="SqlCols"
type="string"
required="true"
hint="list of table columns ('col1, col2, col3', 'a.col1, b.col2, c.col3', or 'a.col1 AS acol, b.col2 AS bcol, (SELECT c.col3 FROM c_table) AS subCol3' are all acceptable" />
<cfargument name="SqlFriendlyCols"
type="string"
required="true"
hint="list of table columns ('col1, col2, col3' is acceptable" />
<cfargument name="SqlTableClause"
type="string"
required="true"
hint="list of table columns (most JOINs and sub-SELECTs are acceptable" />
<cfargument name="StartRow"
type="numeric"
required="true"
hint="beginning row number to display" />
<cfargument name="EndRow"
type="string"
required="true"
hint="last row number to display " />
<cfargument name="SqlWhere"
type="string"
required="true"
hint="WHERE clause filters" />
<cfargument name="SqlRowNumOrderBy"
type="string"
required="true"
hint="usually the PK of the primary table" />
<cfargument name="SqlOuterOrderBy"
type="string"
required="true"
hint="chose from the columns from SqlFriendlyCols" />
<cfset var returnStruct = StructNew() />
<cfstoredproc procedure="sproc_pagination" datasource="#Variables.DSNs.primary#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlColumns" value="#Trim(Arguments.SqlCols)#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlFriendlyColumns" value="#Trim(Arguments.SqlFriendlyCols)#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlTableClause" value="#Trim(Arguments.SqlTableClause)#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="SqlStartRow" value="#Arguments.StartRow#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="EndRow" value="#Arguments.EndRow#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlWhere" value="#Trim(Arguments.SqlWhere)#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlRowNumOrderBy" value="#Trim(Arguments.SqlRowNumOrderBy)#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlOuterOrderBy" value="#Trim(Arguments.SqlOuterOrderBy)#">
<!--- return data + count --->
<cfprocresult name="rs" resultset="1">
<cfprocresult name="recordcount" resultset="2">
</cfstoredproc>
<cfset returnStruct.rs = rs />
<cfset returnStruct.rc = recordcount.countAll />
<cfreturn returnStruct />
</cffunction>
Let me know what you think.
<cffunction name="searchInventory" access="package" output="false" returntype="struct">
<cfset Var local = StructNew()>
<!--- columns --->
<cfsavecontent variable="local.SqlCols">
I.listing_city,
ST.State_name,
ST.State_shortCode,
S.listing_type,
I.listing_price,
I.listing_photo,
I.listing_id,
I.listing_state_id,
I.listing_lot_size,
I.listing_county_id,
C.county_name
</cfsavecontent>
<!--- friendly cols (if no JOINs/aliased table names, inherit from SqlCols above) --->
<cfsavecontent variable="local.SqlFriendlyCols">
listing_city,
State_name,
State_shortCode,
listing_type,
listing_price,
listing_photo,
listing_id,
listing_state_id,
listing_lot_size,
listing_county_id,
county_name
</cfsavecontent>
<!--- tables --->
<cfsavecontent variable="local.SqlTableClause">
listing_(nolock) I
INNER JOIN
listing_types(NOLOCK) S
ON S.listing_type_ID = I.listing_type_ID
INNER JOIN
counties(nolock) C
ON C.County_ID = I.listing_County_ID
INNER JOIN
states(NOLOCK) ST
ON ST.State_ID = I.listing_State_ID
</cfsavecontent>
<!--- conditions --->
<cfoutput>
<cfsavecontent variable="local.SqlWhere">
I.listing_Active = 1
AND I.listing_Acct_Active = 1
AND I.listing_Status = 0
<!--- dynamic info --->
<cfif Len(Arguments.SearchCrit.siteStateID)>
AND I.listing_state_id = #Arguments.SearchCrit.siteStateID#
</cfif>
<cfif ListLen(Arguments.SearchCrit.State_ID)>
AND I.listing_state_id IN(#Arguments.SearchCrit.State_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.Region_ID)>
AND C.listing_Region_id IN(#Arguments.SearchCrit.Region_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.County_ID)>
AND I.listing_County_id IN(#Arguments.SearchCrit.County_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.listing_type)>
AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type#)
<cfelseif ListLen(Arguments.SearchCrit.listing_type_ID)>
AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.SqFt)>
AND I.listing_size >= #Val(ListFirst(Arguments.SearchCrit.listing_size))#
AND I.listing_lot_size <= #Val(ListLast(Arguments.SearchCrit.listing_size))#
</cfif>
<cfif ListLen(Arguments.SearchCrit.listing_Price)>
AND I.listing_Price >= #Val(ListFirst(Arguments.SearchCrit.listing_Price))#
AND I.listing_Price <= #Val(ListLast(Arguments.SearchCrit.listing_Price))#
</cfif>
</cfsavecontent>
</cfoutput>
<!--- order --->
<cfoutput>
<cfsavecontent variable="local.SqlRowNumOrderBy">
#Arguments.SearchCrit.sortby# #Arguments.SearchCrit.sortbyDirection#
</cfsavecontent>
<!--- used for order column that has an alias prefix --->
<cfsavecontent variable="local.SqlOuterOrderBy">
#ListLast(local.SqlRowNumOrderBy, ".")#
</cfsavecontent>
</cfoutput>
<!--- dynamically get the data based on the SQL built above --->
<cfset local.returnStruct = Server.DAO.Common.getPaginatedData( SqlCols = local.SqlCols,
SqlFriendlyCols = local.SqlFriendlyCols,
SqlTableClause = local.SqlTableClause,
StartRow = Arguments.StartRow,
EndRow = Arguments.EndRow,
SqlWhere = local.SqlWhere,
SqlRowNumOrderBy = local.SqlRowNumOrderBy,
SqlOuterOrderBy = local.SqlOuterOrderBy ) />
<cfreturn local.returnStruct />
</cffunction>Does your email validation allow plus signs?
ColdFusionGo ahead and check. I'll wait.
I'm not kidding, go ahead. It'll be alright. You're not the only one whose says made_up_account_here+do_not_spam_me@gmail.com is an invalid address.
"er, are you sure a plus sign is valid?", you ask? Yep. I'm certain. All the time, I use my gmail accounts nifty feature that allows me to use, say, made_up_account_here+twitter@gmail.com to filter emails from twitter to a twitter "label". But some sites, especially blog sites, always tell me my email is invalid when I put in the plus sign. Well, your site is wrong, and I'd appreciate it if you'd fix it. ;-) (when you get time of course)
anyways, after you checked, what did you find?
Bad Day at the Office video
ColdFusion, VideosI usually don't post stuff like this, but since I had a good laugh (cuz can't we all relate?) and wanted to try the embed feature on the new InstantSpot 2.0 software.
Loading....