Quite often I find it useful to have a ColdFusion object which reflects the data in the row of a query or database table.

I have a written a generator to accomplish this. Please note that this is CF 9 specific. But wait, you say, CF 9 has ORM built in why not just use that. There are two limitations of Hibernate as implemented in CF 9 which rule it out for my use. 1)It is limited to a single datasource and 2) it will not work on an arbitrary query.

We have an application for which different clients have different datasources. The database schema is the same for each client but the database instance, and hence datasource is different.

Also, we often move data returned by a query around the application and to the client side to be used by jQuery. It is much easier to use an object for this than using queries directly.

The Generator

The generator retrieves information about the table or query, and creates a cfc reflecting that structure and writes it to the appropriate location in the file system. The generator is designed to be run once and need not be run again unless the schema changes. If it is rerun it respects changes made to the cfc by the developer and does not overwrite them.

I will discuss the generator code a bit later but lets look at a typical cfc that is generated first. After all if you don't find the generated cfc useful why would you care about how it is generated?

Sample cfc of the artists table in cfartgallery

Notice the metadata attached to the component: datasource, primary key, pkIsIdentity. These were collected by the generator when it was run and are used when interacting with the database. The datasource is the datasource used when the component was generated. It is used by default but you may set a new datasource at runtime if necessary.

Also notice that accessors is set to true. When CF sees this it generates getters and setters automatically. If the developer overrides one of the accessors in the "usercode" section the code "getter=false or setter=false" is added to the appropriate property when the generator is rerun.

The first thing you may notice is that the code contains no useful methods. It is basically a list of properties reflecting the structure of the table. Notice that these properties incorporate the database type from the database. The component extends com.vawter.utilities.baseTableClass which contains all the methods and does the heavy lifting.

Methods inherited from the superclass:

1. setDatsource(string ds)
2. getDatasource()
3. add()
4. update()
5. delete()
6. read()
7. save()
8. fillFromQuery(query q)
9. fillObjectFromQueryRow(query q,numeric row)
10. fillObjectFromStruct(struct inStruct)
11. getJson()
12. getJsonFromArray(array aObj)

The purpose of the first two methods is to change the datasource. The next five should be obvious. They are just the standard CRUD operations.

The next three are methods for populating the object. fillFromQuery is a little different. It creates an array of objects, one for each row of the supplied query. Each instance is of the same type as the calling object.

getJson returns a string of the serialized data in the object and getJsonFromArray returns a string of the serialized data from an array of objects (usually from fillFromQuery).

All the methods except 1,2,11,12 return a struct with elements result and errormessage. Result is true or false and if false errormessage is the reason. fillFromQuery returns an additional element "records" which is an array of objects populated from the query.

To use the read method you first populate the object with the pk you want to fetch and then call read. The method queries the database for that record and updates the object. Values other than the pk which were in the original object are ignored.

The CRUD methods are only useful for tables and views. It makes no sense to "add" to the database if the object reflects the data from an arbitrary query. Also the table must have a primary key or the CRUD operations will fail.

Sample usage of the generated code

The Generator Code

The generator code is straightforward. After collecting necessary file locations and the desired component path, it collects the metadata for the table or query and information about the defaults, generates the code and writes it to the file system.

Collecting metadata for tables and views

The tag provides information about the table structure and default values.

Unfortunately cfscript does not have an equivalent for cfdbinfo. To use it and retain script notation we use an include statement: include "incdbinfo.cfm". incdbinfo.cfm is very simple:

If one had an action other than "columns" one would not have a table name, hence the if statement.

Collecting metadata for a supplied query

For a query, dbinfo is not available. We can, however collect information on the columns and data types by executing the query and then using the metadata.

For queries qSQL is the developer supplied sql. Since this generator should not live in a production environment we are not overly concerned about sql injection.

Notice the "where 1 = 0" statement. We need no records in the result set to get the metadata so we insure that none are returned.

The metadata returned is an array of structs, one for each column. The struct contains the column name, the column data type, and whether the data is case sensitive. The column names are the same case as the database. For the examples in this post they are all upper case but in the general case they are not. We preserve the case when generating code so our properties reflect the underlying database case. This makes reading the code much easier and means that when we pass the data to javascript we are certain of the case of the variable names. Of course within CF the case of the property is not important.

Sample Usage of Generator

local.res will return a struct with 3 elements a)result(true|false), b)errormessage c) the text of the cfc generated The text of the cfc generated is just for informational purposes. The real code is written to the file system You will probably want to import the generated code into your version control system.

Summary

It is easy to create objects representing tables or queries. The components generated are simple with all the methods containe in a common superclass.

You may download the code for the generator here.

In a future post I will discuss moving objects to and from the client using jQuery.

Comments and suggestions welcomed,

I am working on a redesign of a cryptogram site. (http://www.dailycryptogram.com). The old version works but has a lot of cumbersome javascript written long before jQuery was available. The handlers for keypress are complex and refreshing the display involves looping over the arrays of the encrypted, unencrypted, used, and available letters. There has to be an easier way.

What have I chosen is to add attributes to the DOM elements where the letters are displayed so the event handlers have all the information necessary without looking it up:

 $(".enc_"+el).data("encLetter",el);
 $(".enc_"+el).data("solLetter",sl);
 $(".enc_"+el).data("dspLetter","");

The event handlers become much simpler:

   $(".chosen").live("click",function(){
     try{
	if($(this).data("dspLetter")!=""){    //we are replacing letter so show original as available
	   $(".choice."+$(this).data("dspLetter")).show();
	}
	if($.chosenLetter==""){ //if no letter is chosen make sure textbox is empty
	    $(".enc_"+$(this).data("encLetter")+">.letterbox").attr("value","");
	}
	else{    //fill the textbox and remove the letter from the available pool
	    $(".enc_"+$(this).data("encLetter")+">.letterbox").attr("value",$.chosenLetter);
	    $("."+$.chosenLetter).hide();
	}
        $(".enc_"+$(this).data("encLetter")).data("dspLetter",$.chosenLetter);//update data on all occurences of the letter			
	$.isSolved();
	$.chosenLetter="";
	$.destinationLetter="";
    }catch(except){alert(except)}
});

Also checking for a correct solution is quite easy:

$.isSolved=function(){
   var solved=true;
   $(".bb>.chosen").each(function(i){ // elements of the bb class holds the data infomation
	solved=solved && ($(this).data("solLetter")==$(this).data("dspLetter"));
   });
   if(solved){
	$(".letterbox").addClass("greenboxes").removeClass("letterbox");
   }
}

Demo

The demo source gives more detais. To solve the cryptogram you can either drag an available letter(red) into the textbox or just type in the textbox. To remove a letter just click on it. You will not be able to enter a letter twice.

Attaching data to a DOM element is quite useful and simple to do. The old way of attaching a primary key to the id of a table row no longer is necessary. Attaching the pk to the data of the row is just as easy and avoids the worry of making sure the id is unique on the page.

I was having an issue with an ajax call failing. Of course I thought there was something wrong in the cfc so I added a few more trace statements which didn't help. I finally decided to examine the stacktrace and it pointed (eventually) to the line where the trace occured. I removed that trace and the exception moved to the next trace line. I know I may be in the slow group but I see a pattern here. After removing all the trace statements the code worked fine.

It is a bit ironic that one's first impulse is to add some trace statements to troubleshoot which only exacerbates the problem!

Changing the inline attribute to false had no effect.

If you uncheck the debug setting: "Enable request debugging output" in CFAdministrator the ajax call works fine but the output from the trace is not written to the trace.log file

If you surround the trace with a try catch you will see the exception message is: Variable DEBUGGER is undefined.

component {
	remote string function doesitfail( String input)
		returnformat="JSON" {
		try{
			trace( text="#arguments.input#" inline="false" );
			writelog( file="mylog" text="#arguments.input# was entered");
		} 
		catch( any ex){
			writelog(file="mylog" text="#ex.message#");
		}
		return ucase(arguments.input);

	}
}

<cfparam name="url.inString" default="No input">
<cfajaxproxy cfc="com.vawter.testtrace_cfc"  jsclassname="testTrace">
	<script language="JavaScript" type="text/javascript">
	     oT=new testTrace();
	     oT.setCallbackHandler(successh);
	     oT.setErrorHandler(errorh);
	     
             function successh(data){
		alert(data);
	     }
	     function errorh(data){
	       alert("failed "+data);
	     }
	</script>
	
	<cfoutput><a href="##" onclick='oT.doesitfail("#url.instring#");return false;'>click me</a></cfoutput>

For the above code the ajax call succeeds because the exception is caught and the return statement is executed despite the error.

Workaround:

Use writelog instead of trace

The problem also occurs in CF8.

I have an application that uses value objects. In order to avoid public instance variables we use cfproperty tags to determine instance variables. When you extend the value object the property tags are not inherited.

To circumvent this what I have done is create an array in the variables scope:

variables.objprops

In the value objects init method I grab the properties using

props=getMetaData(this).properties

I then check for the existence of variables['objprops'] and if it doesn't exist create it as an empty array.

I loop through the array (props) and append them to variables['objprops'].

Since a subclass may override a property in the parent class I replace rather than append if a property of the same name exists in the array. Since the init method of the parent class is called first this effectively overrides property in the parent class.

We use the onMissingMethod function to create implicit setters and getters but that is a topic for another post.