This page will include some helpful hints for dealing with common "gotchas" that occur when developing an application with DataFaucet.
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.
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.
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:
- Tristan (user 1) enters the application and creates the product category "lingerie".
- 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".
- 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 ---> <cftry> <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> <cfcase value="statusid"> <cfset request.errMsg = "The selected Product Status could not be found. It may have been deleted." /> </cfcase> </cfswitch> <!--- Redisplay the form with the error message included ---> <cfinclude template="productform.cfm" /> </cfcatch> </cftry> </cf_validate>
There are a few caveats to this.
- In order for the race condition to be properly handled, both tables must be managed using active record objects. So in this case, both the product table and the product category table must each be managed with an active record object for the faucet to handle the race condition and raise this error type.
- This error will not be thrown (by default) if the column in the related table can be null, so for example if the categoryid column in the product table is nullable, deleting the category will simply reset the value of the categoryid in products associated with the deleted category to null. Meaning that the products will stay in the table and be dissociated from the deleted category.
- The race condition will also still exist (although it will be much less likely to occur) if the application contains code that deletes multiple records from either table with a single query, for example deleting all records from the category table where the category name is like '%electronics%' would reintroduce the race condition on a more limited basis. The active record object itself by default will perform multi-record deletes of this nature based on its foreign keys when it's deleted, which means some race conditions still exist by default. This remaining race condition is much less likely because it will only occur if the queries for the record being saved and the records being deleted occur simultaneously, instead of the above example wherein the queries are executed at different times.