DataFaucet ORM


This page will include some helpful hints for dealing with common "gotchas" that occur when developing an application with DataFaucet.

Column Not Found in [Table]

It wouldn't be entirely inaccurate to think of the DataFaucet services like the hot water heater in your home. In order to have hot water, it needs to be heated, which takes time. But when you want hot water, the last thing you want to do is sit around and wait for it to boil. So the water heater keeps a large tank of water pre-heated to probably hotter than you'll ever want, and it siphons off a small part of that tank any time you turn the tap in your sink to hot.

With the DataFaucet you really don't want your users waiting around for the system to analyze the database metadata on each request, or worse for each query. So the DataFaucet service caches the metadata information it uses in a DataManager object it keeps in the server scope. (Why the server scope? Because that's the same scope as your ColdFusion DSNs, so it's easier to keep them in sync if you have multiple applications sharing a database.)

If you don't reset the cache on each request, the faucet will use any available cache by default. So if you add a column to your database via a tool like Enterprise Manager or other software that might have been included with your database, there's no way for your ColdFusion application to know those changes have been made... unless you tell it. The good news is that it's easy to clear your database cache for an individual database by using the reset() method of the datasource object.

<cfset request.DataFaucet.getDatasource().reset() />

As an alternative, you can also use the faucet's Data Definition Language (DDL) features to add columns to your database or use the schema install/uninstall features in the active record object, which will automatically reset the cache for you.

No table defined for active record

This error is thrown when you have created an active record object and not used the setTable() method to declare which database table the object will use.

For more information, see the active record documentation.

Foreign Key Constraint Not Satisfied

If you're using foreign key constraints in your database (like you should) ;) then there's a good chance you'll see this error at some point. This is a helpful error message created by the faucet for the purpose of being captured in your code. Why? Because if the faucet didn't throw this error, then ColdFusion would throw a much more generic and likely platform-specific SQL error that would be more difficult to manage.

The problem stems from a race condition.

A foreign key constraint ensures that relationships between two tables in your database are properly maintained. Thus if you have a constraint that ensures that the "categoryid" in your tblProduct table matches an existing "categoryid" in your tblProductCategory table, then the database will throw an error if you attempt to insert a product into a category that doesn't exist. But the category should exist right? People can't insert the record otherwise right? Well that's true if you're using a foreign key constraint, otherwise users can create products in categories that don't exist and the reason is because of this race condition I mentioned before.

Here's how the race condition works:

  1. Tristan (user 1) enters the application and creates the product category "lingerie".
  2. Tristan proceeds to the product management section where he selects the "new product" option.
    • At this point the "lingerie" category still exists.
    • The "add new product" form displays a list of categories including "lingerie".
  3. Pat (user 2) enters the application and sees the "lingerie" category in the list of categories.
  4. Knowing that the company isn't planning to sell their lingerie products on the website, Pat deletes the "lingerie" category from the tblProductCategory table
  5. Tristan is still on the "add new product" form with the "lingerie" category selected.
  6. Tristan presses the "save" button, sending the form indicating that the silk teddy belongs to the now deleted "lingerie" product category.
  7. At this point one of two things happens:
    • With a Forein Key: the database raises an error indicating that the foreign key constraint was violated by the insert statement
    • Without a Foreign Key: the database dutifully inserts the "orphaned" product.

Without the foreign key, no error gets raised and the user is probably given a "thank you, your product has been saved" message. While technically correct, this isn't what Tristan (our user) wants to know because there's a good chance they won't be able to find or edit that product now that it's been entered. When they go back to the product management application a few days later to edit prices, they won't be able to find the product and will probably be unaware that it wasn't on the site in that time. Tristan would much rather know that someone deleted the lingerie category so they can either go back and re-enter it or find out who deleted it and ask them about that decision (which may alter their work schedule for the day).

The active record object throws an error of type "DataFaucet.ActiveRecord.ForeignKeyConstraint" so that you can catch this error and display a friendly message to the user indicating what happened. The extendedinfo for this error message also contains the name of the table and column of the foreign key so you can make the message to the user even friendlier by telling them what happened in human language (instead of techno-speak).

<cf_validate form="#myform#">
  <!--- Woohoo! The form data is valid, save the product --->
    <cfset productAR.update(form) />
    <cfcatch type="DataFaucet.ActiveRecord.ForeignKeyConstraint">
      <!--- We got an ActivRecord error for the race condition - let the user know what was missing. --->
      <cfswitch expression="#listlast(cfcatch.extendedInfo,'.')#">
        <cfcase value="categoryid">
          <cfset request.errMsg = "The selected Product Category could not be found. It may have been deleted." />
        <cfcase value="statusid">
          <cfset request.errMsg = "The selected Product Status could not be found. It may have been deleted." />
      <!--- Redisplay the form with the error message included --->
      <cfinclude template="productform.cfm" />

There are a few caveats to this.