ColdFusion Quickie: Using the null attribute in cfqueryparam

In a database with foreign key constraints a "null" value is typically used to store a value that doesn't have a link to the referenced table. Lets look at a simple example:

In this example we have a contact table with a foreign key reference to the address table.  This means that every value in the addressId column must have a matching value in the addressId table.  There may be situations where a contact's address is not known so in order to satisfy the foreign key constraint the value "null" must be stored in the addressId field.  

Now lets look at the typical CFC's for these objects: 

Contact.cfc

/** * @accessors true */component Contact{ /** *@getters true *@setters true */ property numeric contactId; /** *@getters true *@setters true */ property String firstName; /** *@getters true *@setters true */ property String lastName; /** *@getters true *@setters true */ property address Address; public void function init(numeric contactId = 0,                                   String firstName = "",                                  String lastName = "",                                   Address address = new Address()) output=false hint="default constructor"{  variables.contactId = arguments.contactId;  variables.firstName = arguments.firstName;  variables.lastName = arguments.lastName;  variables.address = arguments.address; }}

Address.cfc

/*** @accessors true*/component{ /** *@getters true *@setters true */ property numeric addressId; /** *@getters true *@setters true */ property String line1;                //rest of implementation hidden public void function init(numeric addressId = 0,                                   String line1 = "",                                   String line2 = "",                                   String city = "") output=false hint=""{  variables.addressId = arguments.addressId;                ... }}

Most of this code is quite uninteresting, however, whats important to note is that the addressId field is instantiated to a value of 0 in the default constructor.   If we were to write our own persistance layer inserting this value into the database would throw a foreign key constraint error, since there is likely to be no matching Address record with an addressId of 0.

We all know by know that we should use cfqueryparam for all of our params.  As it turns out this function has an attribute called "null".  When the "null" attribute is set to true cfqueryparam ignores the "value" attribute and inserts a value into the database.  In our above example we would write our insert/update statements like so (assuming that the contactId field is auto-generated):

<cfquery name="insertQuery" datasource="#variables.datasource#">INSERT INTO    Contact(firstName           ,lastName           ,addressId)VALUES(      <cfqueryparam cfsqltype="cf_sql_varchar"                        value="#Contact.getFirstName#" />     ,<cfqueryparam cfsqltype="cf_sql_varchar"                        value="#Contact.getLastName#" />     ,<cfqueryparam cfsqltype="cf_sql_varchar"                        value="#Contact.getLastName#"                        null="#Contact.getAddressId() eq 0#" />)</cfquery>

Now if we insert a record with the default addressId value of 0 our database wont throw an error because ColdFusion will insert the value into the database, this is assuming that the column does allow the value of of course.