ColdFusion: How to get the number of rows affected by a query

I’ve used ColdFusion for a great many years, a substantial number of them with Microsoft SQL Server. Not surprisingly, I’ve built up a significant collection of code snippets that I frequently reuse. One of the handiest is the use of @@rowcount to retrieve the number of rows affected by an insert, update or delete query. This can useful if we need to check, say, that only one row was updated or that a row was deleted as expected. For example:

<cfquery name="myQuery" datasource="myDatasource">
    set nocount off
    update ...
    select @@rowcount as numberOfRowsUpdated
    setnocount on
</cfquery>

<cfif myQuery.numberOfRowsUpdated GT 1>
    throw an error ...
</cfif>

As you can see, the number of rows updated by this query will, in this case, be returned in the variable myQuery.numberOfRowsUpdated. Simple enough, and this has worked for as long as I can remember (certainly before the pre-MX days).

Today, however, I had to do the same thing for a query that may run against MySQL or Oracle databases. My first impulse was to look for the equivalents of @@rowcount – for MySQL this is ROW_COUNT() and for Oracle it’s SQL%ROWCOUNT. Again, it’s easy enough to write a single query with a simple condition that returns the appropriate dbms variable depending on which database we are querying but it feels like a clumsy hack. Luckily, the ColdFusion cfquery tag (from version 7 onwards) offers a more elegant approach – the result property.

In my experience, the result property of cfquery is rarely used, which is a pity as it offers us quite a few benefits that are not immediately obvious from the documentation. By adding this property to our update query like so:

<cfquery name="myQuery" datasource="myDatasource" 
                        result="myQueryResult">
    update ...
</cfquery>

a number of properties are returned to us in the structure myQueryResult. The property we’re interested in is recordcount. When the query is a select it is set to the number of rows returned (as it usually is). However, for insert, update or delete queries it is set to the number of rows affected by the query i.e. the number of rows inserted, updated or deleted. Testing this value is no more difficult than in the first example:

<cfif myQueryResult.recordcount GT 1>
    throw an error ...
</cfif>

Altogether a simpler, cleaner and more reliable cross-platform method of getting at this value.

CheatSheet: instant access keyboard shortcut reference for Mac OSX

CheatSheet, a tiny, free app for Mac OSX from Media Atelier (aka Stefan Fürst), has one very simple, but useful, function – by holding down the cmd key just a touch longer than usual it will pop up a convenient overlay showing all of the active keyboard shortcuts in the current app. You can use this as a reference to help you memorise the key presses, print the sheet or, by clicking the label next to a shortcut, use it as a quick alternative to hunting for the option you need in the menu. CheatSheet builds the shortcut list by examining the active menu items of the current app so it’s always up-to-date and doesn’t require any further installs or downloads.

This isn’t the first app to offer this functionality – KeyCue works in much the same way and is a more mature and feature-rich version of the same thing though when I tried it out I didn’t really find any use for the extra functionality it provides. It’s also a lot uglier than CheatSheet and, at €19,99, seriously overpriced for a utility app. However, if you’re still delaying your inevitable upgrade to OSX Lion then KeyCue may be your only option – CheatSheet is Lion (or later) only.

I’d like to see the ability to assign any hot key to CheatSheet as I’d rather utilise one of my unused function keys and, at 3 seconds, the delay in popping up the sheet sometimes seems just a little too long. Those niggles aside, this is a great free app.

 

CheatSheet in action – displaying the hotkeys for Google Chrome

 

More:

Enhance and test your Google Search knowledge

I’ve never counted, but, at a conservative estimate, I probably use Google Search at least a couple of hundred times every day. Unfortunately, like most things that are so habitual, I’ve gotten into a rut when searching and, rather than using the most appropriate query to find exactly what I want, I usually search in the same way I did last time, and the time before that. Yes, I do sometimes remember to put quotes round an exact phrase I’m looking for, or restrict pages to only those updated in the last year but that’s about the extent of my efforts.

Of course, it doesn’t have to be that way. Google Search has an incredibly rich query language – dozens of options let you filter and refine your searches. Some of these options are tucked away behind rarely used menu items while others, like searching for number ranges, require the use of operators. Even those that appear prominently displayed in the sidebar can have unexpectedly powerful uses as I discovered today while viewing the lessons for the first class in a tutorial from Google entitled “Power Searching with Google”.

Launched earlier today this free, six class series looks certain to provide an great introduction to making the most of Google search. Between now and the 23rd of July a combination of video tutorials, activities, Google+ Hangouts with search experts and discussion forums will provide an immersive learning experience. I found the first class, presented by Google Senior Research Scientist Daniel M. Russell, to be excellent – Dan is a relaxed and confident presenter who clearly knows his subject and how to make it accessible. The material is clearly presented and the production values are high.

The whole of the first class took me about and hour including watching the videos (+/- 35minutes) and completing the activities. Being the first class, there wasn’t much that was new to me but, even as a refresher, it’s well worth the effort. Hopefully this will be the first of many such ventures from Google.

The classes can be taken any time to suit your schedule and the videos will continue to be available after the course ends on the 23rd of July. Only the mid-class and post-class assessments won’t be available after that time.

 

Once you’ve completed the course, why not test your new skills with the Smarterer Google Search test?

A word of warning though, there are a few tough questions in there…

 

More:

Geolocating IP Addresses for free using geoPlugin.net and ColdFusion

Geolocation is defined by Wikipedia as “the identification of the real-world geographic location of an object, such as a radar, mobile phone or an Internet-connected computer terminal.” One of the most useful (and common) applications of geolocation is to take an IP address and convert it to a real world location such as a city or country. Why would this be useful? Suppose we have a visitor to our web site with IP address 208.73.210.29 – by geolocating that IP we discover that our site visitor is in Los Angeles, California, U.S.A. From this we know we can show the visitor prices in U.S. dollars, events in the Los Angeles area or a local weather forecast. Simply put, geolocation gives us a low-risk way to add a layer of personalisation and convenience to our web sites.

As well as being very useful, gelocation is incredibly simple  – there are a number of free, public API’s that will take an IP address and convert it into more usable data. One of the best of these is geoPlugin.net, a site that has been offering a free and reliable geolocation service for more than five years. By providing a number of web services to suit different programming languages (PHP, Java, JSON, XML, even ASP) it’s likely they have your requirements covered.

 

The code

The following is an example function, written in ColdFusion, showing just how easy it can be to integrate location data into your site.


<cffunction name="ipLocation"
            access="remote"
            returntype="struct"
            displayname="ipLocation"
            output="no">

  <cfargument name="ip" type="string" required="no" default="">

  <cfset var local = StructNew()>

  <cftry>

    <cfset local.ip = Trim(arguments.ip)>
    <cfset local.url = "http://www.geoplugin.net/json.gp?ip=#local.ip#">

    <cfhttp result="local.ipRequest"
            url="#local.url#"
            method="get"
            timeout="5"
            throwOnError="yes"/>

    <cfset local.ipRequest =
           ReplaceList(local.ipRequest.filecontent,
                       "geoPlugin(,),geoplugin_",",,")>
    <cfif isJSON(local.ipRequest) EQ "No">
      <cfthrow message="geoPlugin response to #local.url# was not JSON format - #local.ipRequest#">
    <!--<span class="hiddenSpellError" pre=""-->cfif>

    <cfset local.return = StructNew()>

    <cfset local.return["status"] = "OK">
    <cfset local.return["geoplugin"] =
           DeserializeJSON(local.ipRequest,true)>

    <cfloop list="status,dmaCode,regionCode,areaCode" index="local.id">
      <cfif IsNumeric(local.return.geoplugin[local.id])>
        <cfset local.return.geoplugin[local.id] =
               Javacast("int",local.return.geoplugin[local.id])>
      <!--<span class="hiddenSpellError" pre=""-->cfif>
    <!--<span class="hiddenSpellError" pre=""-->cfloop>

    <!--- For consistency, we convert "null"s to empty strings --->
    <cfloop collection="#local.return.geoplugin#" item="local.id">
      <cfif local.return.geoplugin[local.id] EQ "null">
        <cfset local.return.geoplugin[local.id] = "">
      </cfif>
    <!--<span class="hiddenSpellError" pre=""-->cfloop>

    <cfreturn local.return>
    <cfcatch>
      <cfset local.return = StructNew()>
      <cfset local.return["status"] = "BAD">
      <cfset local.return["cfcatch"] = cfcatch>
      <cfreturn local.return>
    </cfcatch>

  </cftry>

</cffunction>

This function expects an IP address to be passed in which will be available to us as arguments.ip. Lines 14-20 are the bit that matters – we build the geoPlugin.net URL that we are going to call (in this case we want JSON formatted output returned to us as that is easier to manipulate than the alternatives), then we make a HTTP get request to geoPlugin.net.


<cfhttp result="local.ipRequest"
        url="#local.url#"
        method="get"
        timeout="5"
        throwOnError="yes"/>

The response from geoPlugin is then stored in the variable local.ipRequest. In most cases the response time is usually less than 100ms but I have occasionally seen a very slow response so I am allowing a timeout period of 5 seconds. What will happen if no IP address is passed to geoPlugin? The IP of the server making the request will be used. Although this won’t cause a problem, it won’t be much use either as you won’t be retrieving the geolocation details of your visitor but of your own ColdFusion server.

Assuming we call geoPlugin with the IP address 208.73.210.29, this is the response we would receive:

geoPlugin({
"geoplugin_request":"208.73.210.29",
"geoplugin_status":200,
"geoplugin_city":"Los Angeles",
"geoplugin_region":"CA",
"geoplugin_areaCode":213,
"geoplugin_dmaCode":803,
"geoplugin_countryCode":"US",
"geoplugin_countryName":"United States",
"geoplugin_continentCode":"NA",
"geoplugin_latitude":34.053298950195,
"geoplugin_longitude":-118.25489807129,
"geoplugin_regionCode":"CA",
"geoplugin_regionName":"California",
"geoplugin_currencyCode":"USD",
"geoplugin_currencySymbol":"$",
"geoplugin_currencyConverter":1
})

Unfortunately, although this looks like it is JSON, it isn’t quite. A quick check using JSONLint tells us that the geoPlugin() that wraps the response is not valid JSON. Simply stripping this off gives us something we can use, so that is exactly what we do in line 22

<cfset local.ipRequest =
       ReplaceList(local.ipRequest.filecontent,
                   "geoPlugin(,),geoplugin_",",,")>

before converting the JSON into a ColdFusion structure in line 32

<cfset local.return["geoplugin"] =
           DeserializeJSON(local.ipRequest,true)>

This should really be us finished – we have our response from geoPlugin and we’ve converted it into a format that ColdFusion can use. If we CFDUMP the structure DeserializeJSON has returned we can see what we have:

This looks exactly right. All of the fields are populated and are in the correct format. If all we want to do is return this structure as is to another ColdFusion template then we’re done. But what if we want to allow this function to be called as a web service or from Javascript using Ajax and to return JSON formatted output? This is easy enough – all we’d need to do is call the function using this URL:

http://myhost.com/geoplugin.cfc?method=iplocation&returnformat=json&ip=208.73.210.29

 

200 or 200.0?

Without the code at lines 35-40, this is what would be returned:

{
  "geoplugin":
  {
   "currencySymbol":"$",
   "continentCode":"NA",
   "request":"208.73.210.29",
   "status":200.0,
   "regionName":"California",
   "dmaCode":803.0,
   "latitude":34.053298950195,
   "countryCode":"US",
   "countryName":"United States",
   "regionCode":"CA",
   "currencyConverter":1.0,
   "city":"Los Angeles",
   "longitude":-118.25489807129,
   "region":"CA",
   "currencyCode":"USD",
   "areaCode":213.0
  },
  "status":"OK"
}

Look at the values returned for status,dmaCode,regionCode and areaCode. Instead of 213 the areaCode is being returned as 213.0. Worse still, depending on what version of ColdFusion you are using and what hotfixes you have applied it may actually be returned as the string “213.0”. I want to force these values to be returned as integers and this I do using Javacast:

<cfloop list="status,dmaCode,regionCode,areaCode" index="local.id">
  IsNumeric(local.return.geoplugin[local.id])>
    <cfset local.return.geoplugin[local.id] =
           Javacast("int",local.return.geoplugin[local.id])>
  </cfif>
</cfloop>

 

Dealing with null values

The final change I make to the geoPlugin data before returning it is to deal with null values. If we call geoLocation with IP set to 173.254.216.66 we get this response:

geoPlugin({
  "geoplugin_request":"173.254.216.66",
  "geoplugin_status":206,
  "geoplugin_city":"",
  "geoplugin_region":"",
  "geoplugin_areaCode":0,
  "geoplugin_dmaCode":0,
  "geoplugin_countryCode":"A1",
  "geoplugin_countryName":"Anonymous Proxy",
  "geoplugin_continentCode":"--",
  "geoplugin_latitude":0,
  "geoplugin_longitude":0,
  "geoplugin_regionCode":"",
  "geoplugin_regionName":null,
  "geoplugin_currencyCode":null,
  "geoplugin_currencySymbol":null,
  "geoplugin_currencyConverter":null
})

Because this is an anonymous proxy, many of the values are set to null. When this is deserialized to a CF structure and then serialized back into JSON the result is these values being returned as strings with the value “null” which is not the same thing at all.
By converting all null values  to empty strings (“”), lines 43-47 take care of that problem:

<cfloop collection="#local.return.geoplugin#" item="local.id">
  <cfif local.return.geoplugin[local.id] EQ "null">
    <cfset local.return.geoplugin[local.id] = "">
  </cfif>
</cfloop>

As you can see, retrieving location data for an IP is incredibly easy using the geoPlugin API and with a few extra lines of clean up code in ColdFusion we can ensure consistent and trouble free processing of the returned data.

 

Some sample IP addresses

These IP addresses may be useful for testing and illustrate the variety of output from geoPlugin:

  • 208.73.210.29 – Los Angeles, California, U.S.A., all available values returned.
  • 173.254.216.66 – An anonymous proxy – little location data returned.
  • 86.96.160.7 – Dubai, United Arab Emirates, all available values returned.
  • 127.0.0.1 – “Local host” – no values returned (response includes nulls).

 

The W3C geolocation specification

The W3C are currently working on a geolocation specification and we are already starting to see this functionality built into browsers such as Google Chrome. Unfortunately, it is likely it will be some time before this is universally available (if ever) and, even when fully implemented, it will be fairly limited in what it can do. It would seem that there will be a role for services such as geoPlugin for some time to come.

The source code for this ColdFusion function is available for download from Gist or Snipplr.

 

More: