Query of Queries error in Coldfusion when using LIKE

This very odd behaviour hit me the other day when querying an existing query which was in a scope (i.e request.query):

The query should look like this:

<cfquery dbtype="query" name="getReltrans">
SELECT * from request.translations WHERE ComposerID  LIKE '%#somevar#%';
</cfquery>

But it throws an error. The reason is, when querying a query object, you need to tell Coldfusion not to return NULL values.

This works:

<cfquery dbtype="query" name="getReltrans">
SELECT * from request.translations WHERE (ComposerID IS NOT NULL AND ComposerID LIKE '%#somevar#%');
</cfquery>

Thanks to Ben Nadel for the heads up!

Simple Coldfusion Error Catching

This should work on CF7: It’s quite generic, but essential. With the example below, add these two functions to your application.cfc and create a template to use for when the error is thrown. This example sends you an email with a dump of all the necessary vars used to troubleshoot.

You could of course use CFFILE to write a line to an html file instead of mailing. Also, dependent on your hosting, some shared hosts may restrict the dumpling of cgi vars ( you’ll get a sandbox.security error instead).

Add this to your application.cfc

</cffunction

Error occurred: #Now()#

Now create your Error Handling page: (I’ve used /extensions/error/ as the folder, but there’s no limit to where you can place it.

Error

An unexpected error has occured.

#arguments.exception.rootcause.Message#

p>The error has been logged and will be dealt with as soon as possible.

If this problem persists, please Contact Us.

Now test by going to a URL such as www.domain.com/?error=1
P.s you may want to add url.error as a cfparam.

Search Engine Optimization

The Problems:

Each search engine is different: Some read meta tags, some don’t bother, some create their own. Some will ignore variables after the filename, others will respect them. This means the indexing you may see is often inconsistent across Search engines. Some search engines will penalize you for duplicate meta descriptions, and assume they are the same page, even though the titles are different.

Most search engines look at dynamic urls as the same page: this is the major issue – often, a dynamic url will be used to specify a different row from a query to display, or include a certain template etc ( i.e www.domain.com/index.cfm?view=foo&id=1, compared to www.domain.com/index.cfm). This means what might be hundreds of pages are often represented as one, and sometimes a whole site is based on a url variable.

Potential solutions:

Using ISAPI (The Internet Server Application Programming Interface) rewrite (for IIS) or mod_rewrite (apache):

This can be one of the most powerful solutions to URL rewriting – and for apache, when used with .htaccess, is one of the most used techniques for blogs (see just about any wordpress install on a linux box..) “Rules” are written, and the true url masked in favour of the rewritten url. this means a rule such as:

RewriteCond Host: (www.)?domain.com
RewriteRule (.*) /somefolder$2

would serve files out of /somefolder, but retain the url www.domain.com – the major advantage is, that unlike frames, the page isn’t hidden. The problems start when you loose track of all the relative vs absolute urls, and if your rules aren’t comprehensive enough, or the syntax is flawed, you can get some unpredicatable problems. Also, ISAPI is triggered for every page request, which can potentially be a bottle neck. It does mean that you could write a rule to convert all variables after a template file to a directory: i.e


RewriteRule /events/ /events.cfm [I]
(Would rewrite /events.cfm as /events/)

RewriteRule /events/(d+)/ /events.cfm?id=$1 [I]
(Would rewrite /events.cfm?id=1 as /events/1/ )

RewriteRule /events/(w+)/ /events.cfm?var=$1 [I]
(Would rewrite /events.cfm?var=foo as /events/foo/)

RewriteRule /events/(w+)/(w+)/ /events.cfm?var1=$1&?var2=$2 [I,L]
(Would rewrite /events.cfm?var1=foo&var2=bar as /events/foo/bar/ )


Using Application.cfc

In Coldfusion, there’s nothing stopping you doing this as a very basic include framework:


and then using ?view=somepage as navigation. This had the advantage that you could change the physical location of the files easily without sacrificing the url. However, the dynamic variable means that the search engines could just see it as index.cfm. Moving this up to application.cfc means we can, in the onrequest method, do the same, with one important difference: As application.cfc is triggered before anything else, we can tell it to include the files irrespective of what code is in the template being called, and without any url variables. Below, “targetpage” is the requested page.


This is all very well, but with this include method, we’d have the same header for each page, irrespective of targetpage, and thus the same page title and meta data.

The next stage is to get the page title and meta data. For small sites, having a database may not be practical, so we need some way to get information out of a static file: perhaps we can use the actual page name..Below is the new onrequest method (The TitleCase function is one which just does automatic capitalization).

I could then include in my header.cfm the variable #request.pagetitle# – which would be created dependent of the included page’s name:


i.e discography_-_some_cd.cfm would yield "Discography - Some Cd".

The next stage would be for sites using a database: the concept is similar. Get your query beforehand using whatever info you need from the targetpage variable, look it up in the query, and then use that info passed into a custom tag: Here’s the whole thing minus the functions to give an idea:


It’s a very simple framework, but because of the dynamic includes, i.e the custom tags, you can get relevant, and above all, unique page descriptions into simply included pages.

It also means your content is in files which don’t necessarily need any CF tags in them (assuming the pages are quite simple) which means you could let others who may not know Coldfusion edit them without worrying too much about them killing the Code.


Blog.cfc Method

I’m only calling this the blog.cfc method as that’s where I’ve seen it most: The concept is simple enough: instead of using this type of syntax:


http://www.oxfordalto.co.uk/blog/index.cfm?y=2007&m=1&d=8&title=Sorting-Tables-using-Ajax

This syntax is used: Note index.cfm still appears in the url


http://www.oxfordalto.co.uk/blog/index.cfm/2007/1/8/Sorting-Tables-using-Ajax

For an explanation of this, it’s best to visit Ray Camdens site where he explains it in detail.

Sorting Tables using Ajax

This is by far the best solution I’ve come across:

http://www.kryogenix.org/code/browser/sorttable/

You don’t have to hard code anything, merely include the appropriate JS file, add a unique ID to the table, and give it a class of “sortable”.

Genius.

RIAForge.org

Link

RIA Forge released: This is a new venture from the likes of Ben Forta , Ray Camden et al: an open source repository with SVN etc for code made and for Adobe products.

Ray Camdens BlogCFC

This blog is running on Ray’s BlogCFC, a very useful blogging app for Coldfusion. Seems appropriate a first post should at least mention the good work that man is doing..

See Ray’s Blog

No idea what I’m actually going to post here yet, but seeing as everyone else is doing it, I may as well help to fill up cyberspace with yet more faff 🙂