Batch inserting records to a database

I was writing some code for a client that processed a csv file and inserted each row into a database, when there were a couple hundred rows the processing time was very quick, but when there are 8000 rows this can take quite a while even on a very fast database, so I wrote up some code to batch insert the records (which I've done too many times).

I was using MSSQL, so the max parameters I could send at one time was 2100 (210 rows x 10 columns), but your mileage may vary. I'd set rowsPerInsert as high as possible and decrease as necessary. You should be able to easily modify the pseudo code below.

view plain print about
1<cfscript>
2 totalrows = 5000; // this would come from a structCount or something similar
3
rowsPerInsert = 210;
4 //this creates a struct with alternating values of true/false for test purposes
5
stRows = {};
6 for (i=1; i
<=totalRows; i++) {
7 stRows[i] = i MOD 2 ? true : false;
8 }
9</cfscript>
10<cfoutput>
11<cfloop from="0" to="#totalRows - 1#" step="#rowsPerInsert#" index="totalRow">
12    <br><br>INSERT #totalRow#<br>
13    <cfloop from="1" to="#rowsPerInsert#" index="indRow">
14        <cfset rowNumber = totalRow + indRow>
15        <cfif rowNumber LTE totalRows>
16            #rowNumber# #stRows[rowNumber]# <!--- SELECT goes here --->
17            <cfif indRow NEQ rowsPerInsert AND rowNumber NEQ totalRows> UNION ALL</cfif>
18            <br>
19        </cfif>
20    </cfloop>
21</cfloop>
22</cfoutput>

This is mostly for me to steal from myself once I need this again, but hopefully you find it useful as well.

NCDevCon - Getting Started with Responsive Web Desgin

I announced this a while back on twitter, but I'm formally writing something up here. I've been chosen to speak at NCDevCon on Responsive Web Design. I spoke on this topic at cf.Objective(), but will be modifying my talk to include more information on em's and rem's and other available framework options.

I'll be talking about what exactly responsive design is and if you should use it'for your website. I'll also cover the most common misconceptions and explain exactly how it works.

I'll be covering many of the available frameworks and discuss the pros and cons of each one. I will also talk about emerging best practices and who the industry leaders are when it comes to designing a great responsive website.

My original talk is located here. If you attended my presentation and there was anything you particularly liked or disliked please let me know. Any and all feedback is welcome.

ColdFusion Query of Queries strips whitespace

I was tasked with tracking down a defect at work this week. We are creating a URL based on the results of a query and somehow a string of " ABC DEF 12345" was being turned into "ABC DEF 12345" (DB2 FTW!) The webservice we were using was unchanged and was still returning the whitespace, so I knew the issue was something on my end. We have a few pieces of code that strip out duplicate spaces and extra line breaks, so I removed those, but the issue still persisted. After working through about a dozen solutions I called over Scott Busche and we repeated everything I already gone through, but when we stumbled upon a Query of Queries, Scott suggested that could be the culprit.

The query of queries was used to filter a result set down to the past two years only (the webservice we're using just ignores end date, which is awesome in itself), so we googled and quickly found that QoQ trims leading and trailing whitespace from columns and has been doing this since ColdFusion 6, so I don't think this is considered a bug, just something to be aware of.

How to solve the issue? We googled and found this stack overflow question, but that solution only worked for Railo and then we found a post on Ben Nadel's blog (obviously) that pointed out a Java function that would do what we needed, but when removing more than one row in the query you would eventually run into an index out of bounds error the way he had it structured, but that's easy enough to fix.

view plain print about
1<cfloop from="#myQuery.recordCount#" to="1" step="-1" index="currentRow">
2 <cfif myQuery.runDate[currentRow] LT left(getStartDateTime(),8)>
3 <cfset myQuery.removeRows(currentRow - 1, 1)>
4 </cfif>
5</cfloop>

After implementing this fix our query was working correctly, but we still needed to add URLEncodedFormat to our URL parameters, so the whitespace wouldn't be stripped (and because it should have been done in the first place).

FWIW Railo 4.2 does NOT strip out whitespace. Test case below

view plain print about
1<cfset q = queryNew("columnA", "varchar", [[" AAA"]])>
2<cfquery name="test" dbtype="query">
3SELECT columnA
4FROM q
5</cfquery>
6
7<cfdump eval=test>
8<cfdump eval=len(test.columnA)>
9<cfdump var="#replace(test.columnA,' ','a space','all')#">

Returns
number 6
string a spacea spacea spaceAAA

You can also run this code on trycf.com

UPDATE: Adam Cameron pinged me on twitter (and Ray added in the comments) that this is fixed in ColdFusion 11.

cf.Objective 2014 - Getting Started with Responsive Web Design

I tweeted this out a while back, but I have been selected to speak at cfObjective() 2014! I blogged in early November about my proposal and the people have spoken! I hope to see all of you at the conference!

Responsive Web Design has been a buzzword since the term was first coined in May 2010. Come find out what this emerging technology is all about as you are introduced to the basics of Responsive Web Design. We'll cover your current options when it comes to designing your own website and we'll also discuss emerging best practices and a few examples from industry leaders. We will also take a look at how you can test your responsive design without owning one of each device out there.

Target audience

Anyone interested in creating one website for every device

Assumed Knowledge

Basic CSS and HTML experience

What the audience will learn

  • What is responsive Web Design and how does it work
  • When is responsive Web Design appropriate?
  • Pro/Cons of using a framework
  • Fixed vs Fluid layout
  • Best Practices for developing a site
  • Common Misconceptions
  • Who is using responsive web design?

If you're interested in attending head on over to cfobjective.com and register before March 1st to get an early bird discount.

Connecting MS SQL database to Railo datasource

Tonight was the 2nd time I've struggled getting Microsoft SQL Server set up to communicate with Railo (and really anything at all), so I thought I'd write up some quick tips on what I had to do. Once you think you have everything set up there are 2 steps that are left.

Go to Microsoft SQL Server 20XX > Configuration Tools > SQL Server Configuration Manager

From there open up TCP/IP and make sure Listen All is set to No

After that go to the IP Addresses tab and make sure Active and Enabled are yes (enabled defaults to no) and make sure you're using the IP and correct TCP Port when configuring your datasource.

Thanks to this MSDN post for getting me on the right track.

What tab is playing that sound?

One of internet users biggest annoyances has been resolved in Chrome 33. When a tab starts playing audio Google Chrome version 33 adds a speaker icon to the tab.

From all the testing I did, the speaker icon is the last thing to disappear when you have a bunch of tabs and it works whether you have multiple tabs with audio, if the audio is paused and isn't dependent on your system sound being turned on.

cf.Objective() Proposal - Getting started with Responsive Web Design

Interested in learning about responsive web design? Head on over to the cf.Objective() Trello board and vote for my talk on Responsive Web Design and any others that you would like to attend.

Here's a brief description of what my talk will be about

Target Audience: Anyone interested in creating one website for all devices

Assumed Knowledge: Basic CSS and HTML experience

Objective: Introduce responsive web design and why it's important

Why I'm Qualified: Part of a team that created a mobile website for a large insurance company.

What the audience will learn: What is responsive Web Design When is responsive Web Design appropriate Pro/Cons of using a framework Fixed vs Fluid layout Concept of columns and grids

Anything else you'd like to learn? Feel free to comment here and/or on the Trello board and if my talk is selected I'll try to work a few suggestions in as well.

Checking an HTML page for duplicate IDs using jQuery

At work our testers make use of the ID elements on HTML tags quite frequently and when they're not unique, it usually causes them issues with testing. To avoid the manual parsing of trying to find duplicate IDs in the page source. I found/updated a JavaScript function that loops through each ID found on the page and outputs any duplicates into your console.

This code does require that jQuery is defined on the page you are testing.

view plain print about
1javascript:(function () {
2    var ids = {};
3    var found = false;
4    $('[id]').each(function() {
5        if (this.id && ids[this.id]) {
6            found = true;
7            console.warn('Duplicate ID #'+this.id);
8        }
9        ids[this.id] = 1;
10    });
11    if (!found) console.log('No duplicate IDs found');
12})();

You can copy and paste the entire JavaScript function into a bookmark for quick access.

view plain print about
1javascript:(function () { var ids = {}; var found = false; $('[id]').each(function() { if (this.id && ids[this.id]) { found = true; console.warn('Duplicate ID #'+this.id); } ids[this.id] = 1; }); if (!found) console.log('No duplicate IDs found'); })();

Using ColdFusion to parse a list with empty values

I was parsing through a csv file today and having issues with empty values. The data I'm receiving is in a format the 3rd party cannot update, so my data looked something like this

1,,abc,32,,gef

Dumping the list provides the expected list, but looping through the list it skips over values

view plain print about
1<cfoutput>
2    <cfloop list="#list1#" index="i">
3        #i#<br>
4    </cfloop>
5</cfoutput>

produces

view plain print about
11
2abc
332
4gef

After googling this for a bit I gave up and asked my brother. He told me to use the 'includeemptyfields' attribute in listtoarray(). I knew I could turn a list into an array, but I wasn't aware of the third attribute until today, the default is obviously false, so blank values were never included.

The code below produces the desired output. Notice I am now looping through an array instead of a list.

view plain print about
1<cfset newlist = listToArray(list1,',',true) />
2<cfoutput>
3    <cfloop array="#newlist#" index="i">
4        #i#<br>
5    </cfloop>
6</cfoutput>
7
81
9
10abc
1132
12
13gef

get US Bank Holidays UDF

I was looking for an existing UDF on cflib.org for current bank holidays but the only one I could find was for German holidays only. With this function you can pass in a year otherwise it will assume the current year is to be used.

The following holidays are calculated using this UDF.

HolidayOfficial Date
New Year's DayJanuary 1
Independence DayJuly 4
Veterans DayNovember 11
Christmas DayDecember 25
Inauguration DayJanuary 20th (Year after election year - multiple of 4)
MLK's BirthdayThird Monday in January
George Washington's BirthdayThird Monday in February
Memorial DayLast Monday in May
Labor DayFirst Monday in September
Columbus DaySecond Monday in October
Thanksgiving DayFourth Thursday in November

view plain print about
1<cffunction name="getUSBankHolidays" access="public" output="false" returntype="struct" hint="general bank holidays for US">
2 <cfargument name="iYear" default="#Year(now())#" />
3
4 <cfset var currentYear = arguments.iYear />
5
6 <cfset var strResult =
7 { NewYears = createDate(currentYear,1,1),
8 Independence = createDate(currentYear,7,4),
9 Veterans = createDate(currentYear,11,11),
10 Christmas = createDate(currentYear,12,25)
11 } /
>

12
13 <cfif NOT (currentYear - 1) MOD 4>
14 <cfset strResult.Inauguration = createDate(currentYear,1,20) />
15 </cfif>
16
17 <cfset strResult.MLKBirthday = createDate(currentYear,1,GetNthOccOfDayInMonth(3,2,1,currentYear)) />
18 <cfset strResult.WashingtonsBirthday = createDate(currentYear,2,GetNthOccOfDayInMonth(3,2,2,currentYear)) />
19 <cfset strResult.MemorialDay = createDate(currentYear,5,DaysInMonthh(createDate(2012,5,1))) - DayOfWeekk(createDate(2012,5,DaysInMonth(createDate(2012,5,1)))) - 2)) />
20 <cfset strResult.LaborDay = createDate(currentYear,9,GetNthOccOfDayInMonth(1,2,9,currentYear)) />
21 <cfset strResult.ColumbusDay = createDate(currentYear,10,GetNthOccOfDayInMonth(2,2,10,currentYear)) />
22 <cfset strResult.Thanksgiving = createDate(currentYear,11,GetNthOccOfDayInMonth(4,6,11,currentYear)) />
23
24 <cfreturn strResult />
25</cffunction>
26<cfdump var="#getUSBankHolidays()#">

This function is long overdue for me as monitors that run on week days run on Holidays and result in a lot of false positive 'down' notifications. If the array returned is empty then the scheduled task should run. If the array has records then it's a holiday

This UDF requires the getNthOccOfDayInMonth function from cflib.org

More Entries