I have worked with ColdFusion ORM for several years now, and one of the most frustrating things working with CF ORM is the lack of friendly error messages. It should be noted that this is not necessarily Adobe's fault- Hibernate, the technology used by ColdFusion provides most of these error messages. 

I have compiled a list of common error messages and will try to explain why the error is occurring along with potential resolutions. You may find this useful whether developing Java or ColdFusion applications.

500 error with no error message on the page, however, the following error shows up in the ColdFusion application log: coldfusion/orm/hibernate/ConfigurationManager.

This error may be caused as you have a reserved keyword in one of your persistent cfc's or in the cfclocation set in the application.cfc template.

Generally, this error will be raised before Hibernate is fully loaded and it is not due to any mapping issues. The first thing that I would suggest is to set the globally_quoted_identifiers argument to true: this.ormsettings.hibernate.globally_quoted_identifiers = true. This will escape any special reserved keywords that you may have.

If this does not work, you can also try turning off errors at the ORM level by turning cfc errors off with 'this.ormsettings.skipCFCWithError = true', however, I am not sure if this approach will work. I suspect that the error is raised even before loading any of the persistent cfc's that may cause an error.

In my case, I received this error after upgrading from CF2016 to CF2021 and the error was due to using the reserved keyword 'model' as the folder name that I used to store the persistent cfc's in. However, I did not get any error even with debugging turned on. I had to look into the ColdFusion logs and try to guess where the error came from. Changing the folder from mapping to 'galaxieDb' resolved my particular error thankfully.

A different object with the same identifier value was already associated with the session

This could be caused by several issues, namely:

1. This could be due to a unique identifier being duplicated when you save the entity. Check your primary key auto generators to see if the primary key value is duplicated somewhere.
2. A well-known Hibernate issue is when the memory is holding relationships between various objects. If you are using Java and Hibernate and your using cascade options, use 'merge' instead of using all.

For ColdFusion, you should eliminate cascade options with all pseudo columns that don't use a one-to-one relationship type. Cascade should not be used with many-to-one or many-to-many relationships according to Adobe. 

Attribute linkTable missing from the property

You need to add a link table to your existing many-to-many relationships. 

coldfusion.orm.PersistentTemplateProxy cannot be cast to java.util.Collection to an incompatible type

This may be due to incorrectly mapping a one-to-one relationship when the two entities actually have a many-to-x or x-to-many relationship. This error is reporting that it is expecting an array instead of a single string. Look at the entity's existing one-to-one relationships to find out if there is a missing x-to-many or many-to-x relationship.

Complex object types cannot be converted to simple values and Property true not present in the entity errors

You may be getting these errors when you are trying to make changes to an object that is returning multiple records.

Adobe provides documentation that you may set filters and arguments to load a single record, but I have not gotten this approach to work and had to rewrite my logic to ensure that a single record is always retrieved. 

Here is the relevant code to load a unique record from an array found on the Adobe site:

EntityLoad(entityName,[Filter="",unique="",options=""])

I have set the unique argument to true and have used an additional maxresults argument to attempt to load a single record, but this approach failed with the following error message- "Property true not present in the entity"

See below for a list of approaches that I have tried to unsuccessfully use.

<cfset CommenterRefDbObj = entityLoad("Commenter", { Email = arguments.email }, "true", {maxresults=1})> ('Property true not present in the entity.')
<cfset CommenterRefDbObj = entityLoad("Commenter", { Email = arguments.email }, true, {maxresults=1})> ('Property true not present in the entity.')
<cfset CommenterRefDbObj = entityLoad("Commenter", { Email = arguments.email }, unique="true", {maxresults=1})> ('Invalid CFML construct found on line x')
<cfset CommenterRefDbObj = entityLoad("Commenter", { Email = arguments.email }, {unique="true"}, {maxresults=1})> ('Complex object types cannot be converted to simple values.')
And others....

To address this problem, you will have to rewrite your logic to ensure that only a single record exists before loading the entity prior to using its set methods. I have not found a reliable way to pick out the record from an array when using ORM.

could not extract ResultSet

Hibernate error thrown when a many-to-many join table is not correct. Check your join tables for all relationships that have many-to-many relationships

Error casting an object of type to an incompatible type.

You will receive this error when you're using a link table and using the set methods of other entities when setting nonprimitive value types. This is due to the link table not having relationships mapped in the field type argument.

When you try to create a relationship in a link table that you are trying to create manually, you will receive a 'many-to-many requires a link table'. When you specify a link table in another entity that requires a many-to-many mapping, the link table is automatically assumed as having a many-to-many relationship and you need to leave the fieldtype relationship out. However, since the link table does not have a mapped relationship (ie fieldtype="many-to-many") you can't use objects to populate the columns since there is no mapped relationship. Instead, you must use primitive data types when setting the column value, such as integers.  

Error while executing the Hibernate query.

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node

I received this error when I was using a function to use HQL to query the database using a variety of filters such as WHERE Post LIKE 'x' AND Post.Remove = 0, etc. This is probably a bug with the ColdFusion implementation of HQL, however, you can generally fix this issue by limiting your filters to one or two WHERE clauses. If you are using Hibernate and not ColdFusion HQL, try setting the nativeQuery argument to true, i.e.: @Query(value = "SELECT Post.PostId FROM Post WHERE Post LIKE '%x'",
 nativeQuery = true)

Deleted object would be re-saved by cascade (remove deleted object from associations)

If you receive this error, you must remove all associations for this record belonging to other tables. For example, I have a many-to-one relationship between a blog category and a blog. Each blog may have multiple categories. However, when I try to delete the category without deleting the reference to the blog it throws this error.

To delete the category you must first set the category tables blog reference to null using the javacast method and then in a separate transaction delete the category like so:

<cftransaction>
    <!---  Delete the association to the blog table. --->
    <;!--- Load the comment entity. --->
    <cfset CategoryDbObj = entityLoad("Category", { CategoryId = arguments.categoryId }, "true" )>
    <!--- Remove the blog reference in order to delete this record --->
    <cfset CategoryDbObj.setBlogRef(javaCast("null",""))>
    <!--- Save it --->
    <cfset EntitySave(CategoryDbObj)>
</cftransaction>

<cftransaction>
    <!--- Now, in a different transaction, delete the record. --->
    <!--- Load the comment entity. --->
    <cfset CategoryDbObj = entityLoad("Category", { CategoryId = arguments.categoryId }, "true" )>
    <!--- Delete it --->
    <cfset EntityDelete(CategoryDbObj)>
</cftransaction>

Element x is undefined in a Java object of type class java.util.HashMap.

Check to see if you have a null in the database column. Replace the null with an empty string.

Error casting an object of type java.lang.Integer to an incompatible type

This could be due to a maddening error when you use 'WHERE 0=0 AND' in your SQL when you use a column that is a foreign key and you're using a cfqueryparam to pass the UserId. For example, the UserRef column in the IpAddress table is a foreign key to the Users.UserId column and the two entity keys have a relationship (many-to-one, one-to-one, etc). The following statement will cause this error:

SELECT new Map (
    IpAddress.IpAddress as IpAddress
)
FROM IpAddress as IpAddress 
WHERE 0=0
    AND UserRef = <cfqueryparam value="#getUserId#" cfsqltype="integer">

To resolve this, simply drop the WHERE 0=0 clause and use the following instead:

SELECT new Map (
    IpAddress.IpAddress as IpAddress,
)
FROM IpAddress as IpAddress 
WHERE UserRef = <cfqueryparam value="#getUserId#" cfsqltype="integer">

Error while resolving the relation between CFC common.cfc.db.model.ThemeSetting and CFC common.cfc.db.model.Theme because 'fkcolumn' is specified on both sides.

The fkcolumn must be specified on the side whose table has the fkcolumn. On the other side, you must specify mappedby.

expecting CLOSE, found 'ThemeSettingRef' near line x, column x

You may be missing a comma after or before a column, ie SELECT FirstName LastName FROM User. Note the missing comma between the first and last name columns.

expecting CLOSE, found 'x'

You may have a missing comma after a column in your HQL query.

illegal attempt to dereference collection [{synthetic-alias}

You may be trying to use a collection, or an array to join two classes without an alias name.

java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Ambiguous column name 'x'

Properly reference any database columns that may be in two or more tables.

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: )

There is an extra comma at the end of the select statements after the last column. The ')' signifies that you are using a SELECT New Map (col1, col2,) for example.

org.hibernate.MappingException: TableX refers to an unmapped class

There may be some class name issue that is causing a hiccup with hibernate when you have multiple ORM databases.

Generally, the error does not provide which relationship is at fault, so you may have to comment out each relationship (one-to-many and many-to-one) to find out the relationship is causing the error. Don't get locked in and assume the broken relationship, there seems to be a missing class name in the XML that Hibernate generates behind the scenes.

I have only had this error twice during my ORM experience and I am not sure what the cause was. With ColdFusion, I don't know of any way to fix the problem when creating the initial database schema, but I have found that I can remove the relationship during the database creation and successfully add the offending relationship back into the table after the database has been created. 

ORM is not configured for the current application

1. You may have duplicate ORM settings in different parts of your Application.cfc. Generally, you should have the ORM Settings, along with ORMReload(), in the OnApplicationStart in your application.cfc and remove OrmReload in the OnRequestStart (see http://nm1m.blogspot.com/2010/09/working-on-little-app-in-coldfusion-9.html).
2. Make sure that ORMReload and InitOrm are not conflicting with each other. You can't have both statements in the same logical block.

If you get a "The passed value does not evaluate to a valid array object" error, it is due to the one-to-many relationship set to the type of an array in the entity declaration.

Path expected for join!

A SQL join was not set correctly.

Root cause :org.hibernate.HibernateException: Property : x - Object of type class java.lang.String cannot be used as an array

The following error is also related to having an incorrect one-to-one relationship when it expects an x-to-many or many-to-x relationship.

Session is closed' or 'Session Closed!

You can't process two or more different database sessions at the same time if you leave the default ORM settings as they are. If you are getting session errors with ORM, change the ORM settings to flushAtRequestEnd=false (<cfset this.ormsettings.flushAtRequestEnd = false>).

After setting this, you must change your code to use cftransactions around all of your ORM operations in order to flush the results unless you choose to manually use flush to maintain your ORM sessions programmatically. 

Another issue may be that there are errors in your CRUD operations code, especially if your crud operations are within a loop. What may be going on is that the error is trying to be reported, however, the ORM session that caused the code can't report the error internally as a new ORM session is being created and the original session is closed before it can report an error.

When this error happens you may also get a cryptic 'Unknown service requested [org.hibernate.stat.spi.StatisticsImplementor]; nested exception is org.hibernate.service.UnknownServiceException: Unknown service requested [org.hibernate.stat.spi.StatisticsImplementor]' error.

To fix this error, check to see if your code works with one record first. If it works, then try to code within the looping structure again.

The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.

There are two issues that I am documenting here:

You may be using a cfquery param tag in HQL on a primary key or relationship that has a relationship. Remove the cfquery param.

Another issue is that you may have a table name and a column name that is identical. For example, when I received this error I had a Role table and a Role column. Hibernate was confused between the object and the column when the column names are identical.

The fix here is to name the column something different than the table- such as Role for the table name, and RoleName for the column name. This issue caught me several times as I typically name the column holding the entity value the same as the table name (ie table: Role, column: Role). This is not a good idea when using ORM.

Note: Null Pointers are another name for undefined values.

This could also be the result of a ColdFusion or Hibernate bug. When you try to use an HQL query that specifies another join to a different nested object and you don't specify which item to pull from the nested array, any column listed after that will raise this error message. See the Theme and Theme Setting HQL query for more information.

You have attempted to dereference a scalar variable of type class java.util.ArrayList as a structure with members.

You may have forgotten to add the index of the array like so:

<cfset BlogDbObj = entityLoadByPk("Comment", 1)>

Populating the new tables

We will be using simple 'SELECT *' database queries on the original BlogCfc tables and populate the new tables that CF-ORM created for us. Populating the database is where the rubber hits the road, if you made any errors in your ORM mappings, all of the flaws will be exposed. I'll try to show you some of the major issues that I faced, and how to resolve them. Before we go any further on this topic, we must go over a few key concepts

1. We can use explicit values to populate the database when no relationship exists.
2. When are are dealing with a relationship, i.e. a property with a fieldtype (one-to-many, etc), a cfc, and a fkcolumn; we must use CF-ORM objects to populate the database.

In order to populate the new tables that were created with our persistent CFC mappings, we are going to use the EntityNewand EntityLoadobjects along with these objects getand setmethods.

1. EntityNew instantiates a persistent CFC and allows us to insert records into the table columns using its set methods.
2. EntityLoad allows us to load the persistent CFC in order to get the current values from the database, and then pass the CF-ORM object back to the set method of the object instantiated using EntityNew

In other words, we will use EntityNew to insert known or static values into the database, or use EntityLoadto get the values already in the database, and pass the loaded objectback to the object that we created using EntityNewWe will cover each example in depth below.

1) Let's begin by inserting records into a table with no relationships

The code below is an example of populating the data from one table to another. The steps that we will use are:

• Wrap the entire block with a transaction tag
• Create a query that gets the current data found in the BlogCfc's original tblBlogRoles table
• Loop through the tblBlogRoles query object using a cfoutput query tag and:
  • Create a new entity of the Role cfc object using EntityNew. The prefix before EntityNew can be anything you would like- I used RoleDbObj.
  • Use the set methods in the Role entity object to insert the records into all of the columns
  • Once the columns are set, save the Role entity with EntitySave

<!--- Get the Roles from BlogCfc --->
<cfquery name="getTblBlogRoles" datasource="#dsn#">
	SELECT  
	id
 	,role
	,description
  	FROM tblblogroles
</cfquery>

<!--- Use a transaction --->
<cftransaction>
	<cfoutput query="getTblBlogRoles">
		<!--- Load the entity. --->
		<cfset RoleDbObj="entityNew("Role")">
		<!--- Use the entity objects to set the data. --->
		<cfset RoleDbObj.setBlogRef(blogRef)="">	
		<cfset RoleDbObj.setRole(role)=""> 
		<cfset RoleDbObj.setDescription(description)=""> 
		<cfset RoleDbObj.setDate(now())=""> 
		<!--- Save it. Note: updates will automatically occur on persisted objects if the object notices any change. We don't have to use entity save after the Entity has been loaded and saved. ---> 
       		<cfset EntitySave(RoleDbObj)=""> 
       </cfoutput>
</cftransaction>

2) Inserting records into a table that contains a relationship

We can load an object using EntityLoad several different ways:

A) Load a CF-ORM object using the primary key

BlogRefObj is the variable that we are storing the object in. We are loading the Blog table. The numeric value, which is 1, is the value of its primary key. You can also use dynamic values, such as [currentRow] when looping through a recordset.

<cfset BlogRefObj="entityLoadByPK("Blog"," 1)="">

B) Load a CF-ORM object using filters, similar to the WHERE clause in a query

Here, we are loading the Users table. UserName is the name of the column that we are querying, and "gregory" is the value that we are searching for. Notice the "true" argument at the tail end of this code. This argument allows us to load a single record, which is necessary when we pass back the object to the EntityNew's set method.

<cfset UserRefObj="entityLoad("Users"," {="" UserName="gregory" },="" "true"="" )="">

2A) Code Example using LoadByPk

• Get all of the search statistics in the BlogCfc table
• Wrap the code block with transaction tags
• Loop through the getTblSearchStats query object using a cfoutput query tag and:
  • Load the new SearchQuery object using the loadByPk method where the primary key is equal to 1 (there is only one blog record)
  • Create a new SearchQueryObj entity (SearchQuery is the name of the persistent CFC)
  • Pass the BlogRefObj object to populate the BlogRef column using the set method.
  • Use the set methods to explicity insert values in the columns that don't contain references
  • Finally, save the SearchQueryObj entity with EntitySave

<!--- Get the Post Categories from BlogCfc --->
<cfquery name="getTblSearchStats" datasource="#dsn#">
	SELECT 
	searchterm
	,searched
	,blog
	FROM tblblogsearchstats
</cfquery>

<!--- Use a transaction --->
<cftransaction>
	<cfoutput query="getTblSearchStats">
		<!--- Load the blog table and get the first record (there only should be one record at this time). This will pass back an object with the value of the blogId. This is needed as the setBlogRef is a foreign key and for some odd reason, ColdFusion or Hibernate must have an object passed as a reference instead of a hardcoded value. --->
		<cfset BlogRefObj="entityLoadByPK("Blog"," 1)="">
		<!--- Load the entity. --->
		<cfset SearchQueryObj="entityNew("SearchQuery")">
		<!--- Use the entity objects to set the data. --->
		<cfset SearchQueryObj.setBlogRef(BlogRefObj)=""> 
		<cfset SearchQueryObj.setSearchQuery(searchterm)=""> 
		<cfset SearchQueryObj.setDate(searched)=""> 
		<!-- Save it. Note: updates will automatically occur on persisted objects if the object notices any change. We don't have to use entity save after the Entity has been loaded and saved. --> 
		<cfset EntitySave(SearchQueryObj)=""> 
 	</cfoutput>
</cftransaction>

2B) Code Example using EntityLoad filter methods

• Get all of the roles from the tblBlogRoles table
• Wrap the code block with transaction tags
• Loop through the tblBlogRoles query object using a cfoutput query tag and:
  • Load one record from the users object where the user name is equal to the user name in the tblUserRoles query
  • Create a new UserRole entity (UserRole is the name of the persistent CFC)
  • Pass the UserRoleRef object to populate the UserRef column using the set method.
  • Use the set methods to explicity insert values in the columns that don't contain references
  • Finally, save the UserRole entity with EntitySave

<!-- Get the Users from BlogCfc -->
<cfquery name="getTblUserRoles" datasource="#dsn#">
	SELECT 
	username
,roleidfk
	,role
	FROM tbluserroles
</cfquery>

<!-- Use a transaction -->
<cftransaction>
 	<cfoutput query="getTblUserRoles">
		<!-- Get the user by the username in the Users Obj. -->		
		<cfset UserRefObj="entityLoad("Users"," {="" UserName="username" },="" "true"="" )="">
		<!-- Load the entity. -->
		<cfset UserRoleDbObj="entityNew("UserRole")">
		<!-- Use the entity objects to set the data. -->
		<cfset UserRoleDbObj.setUserRef(UserRefObj)=""> 
		<cfset UserRoleDbObj.setDate(now())=""> 
		<!-- Save it. Note: updates will automatically occur on persisted objects if the object notices any change. We don't have to use entity save after the Entity has been loaded and saved. --> 
      	<cfset EntitySave(UserRoleDbObj)=""> 
	</cfoutput>
</cftransaction>

Dealing with problems

Unless you're perfect, you will likely encounter problems once you try to populate your new tables. I will share a few solutions to common problems.

Inserting data that may have null values in columns that have relationships

If you have columns that may have null values that contain relationships, you need to use the missingrowignored="true"argument For example, in my Comment table, I need to have either a known blog user or a commenter, attached to a comment record. A blog user is typically the administrator or super-user of the blog, and a commenter is a user that is a general user making a comment. The Blog user is able to have more functionality, and can perform actions on a comment; whereas the blog commenter has limited functionality and can just make a comment. My Comment table needs one of these references to be defined but does not require both- unless of course, the blog user is also the commenter. These two columns need to accept a null value. To allow this, I am using the missingrowignored="true" argument.

<cfcomponent displayName="Comment" persistent="true" table="Comment" output="no" hint="ORM logic for the new Comment table">
	<cfproperty name="CommentId" fieldtype="id" generator="native" setter="false">
	<!-- Many comments for one post -->
	<cfproperty name="PostRef" ormtype="int" fieldtype="many-to-one" cfc="Post" fkcolumn="PostRef" cascade="all" lazy="false">
	<!-- Many comments for one User -->
	<cfproperty name="UserRef" ormtype="int" fieldtype="many-to-one" cfc="Users" fkcolumn="UserRef" cascade="all" lazy="false" missingrowignored="true">
	<!-- Many comments for one commenter -->
	<cfproperty name="CommenterRef" ormtype="int" fieldtype="many-to-one" cfc="Commenter" fkcolumn="CommenterRef" cascade="all" lazy="false" missingrowignored="true">
      ... 
</cfcomponent>

The cryptic coldfusion orm java.lang.String error

This error message can manifest itself in many ways. The last part of the message Stringwill be whatever datatype you passed to the set method. For example, it can be coldfusion orm java.lang.Intif you passed in an int, etc. If you're getting this error- you likely forgot to pass in an object to the set method. Objects mustbe passed to columns that contain relationships! Use the EntityLoad method to load a CF-ORM object, and pass that object to the set method instead of setting an explicit value.

Issues with constraints

Cannot insert duplicate key in object xxx. The duplicate key value is (xxx).' error

Check your relationships and remember that any time that you encounter a onein your relationships (many-to- onefor example), a unique constraint is placed on that key.

While CF-ORM will create relationships and constraints for you, CF-ORM will not remove existing constraints- even if CF-ORM created the constraint in the first place.

While creating tables and populating records, you'll probably have to change the existing relationships after encountering errors. When you change the relationships, make sure that you delete the existing relationships and constraints. I'll provide some helpful scripts that I used with SQL Server. Script to delete an existing constraint (replace TableName with the table that your working on)

ALTER TABLE TableName DROP ConstraintName

Script to delete records and set the primary key back to a 1:

DELETE FROM TableName;
DBCC CHECKIDENT ('[TableName]', RESEED, 0);

Script to determine existing relationships

select name 'ForeignKeyName', 
    OBJECT_NAME(referenced_object_id) 'RefrencedTable',
    OBJECT_NAME(parent_object_id) 'ParentTable'
from sys.foreign_keys
where referenced_object_id = OBJECT_ID('TableName') or 
    parent_object_id = OBJECT_ID('TableName')

Remove all relationships from the database. This is helpful if you just want to delete everything and start over again.

SELECT 'ALTER TABLE ' + Table_Name  +' DROP CONSTRAINT ' + Constraint_Name
FROM Information_Schema.CONSTRAINT_TABLE_USAGE

The 'The ALTER TABLE statement conflicted with the FOREIGN KEY constraint' errors and ghost relationships

This particular error drove me crazy. I was receiving this constraint error for hours, and no matter what I did- I could not make it go away. I could not find the problematic relationship and deleted all of the constraints in the database, yet this error would not go away. However, I did not delete the records that were in the database. What happened here was that CF-ORM was trying to create a new constraint based upon the recent changes that I applied to the persistent CFCs. Since there were records in the database that did not meet the new referential integrity, an error was raised. The database was enforcing referential integrity and could not create the constraint as it would result in orphaned records. CF-ORM could not create the new constraint and reported the error. The solution to this problem is to delete the existing recordsfrom the table and to start over.

Other Resources

I highly recommend getting the ColdFusion ORM bookwritten by John Whish. Although it was written for ColdFusion 9, the materials are still relevant and it is the most in-depth CF-ORM resource that I have found. The cfml.slack.com pagealso has an active ORM channel with a lot of ORM experts. If you need a solution that we have not covered, try to pose the question there. Of course, you're always welcome to make a comment here if you're in need of help!

Configuring ColdFusion ORM

The first thing we need to do in order to use CF-ORM is to place the following code in the Application.cfc template.

• The first line of code below directs ColdFusion to reload CF-ORM every time the page is refreshed.
  • While initially setting up CF-ORM, you will want to include this argument in your code
  • Once everything is ready for production, you should remove this line of code as consumes more resources
• The ormenabled setting enables CF-ORM.
• The dbcreate = "update" argument is used to have CF-ORM create the database tables and relationships for you.
• The cfclocation argument is optional and is used when you keep all of your persistent cfc's in a particular location. Setting the cfclocation argument should also marginally improve performance as without it ColdFusion will search the entire folder structure to find any persistent cfc's.

<cfset ORMReload()="">
<cfset this.ormenabled="true">
<cfset this.datasource="GregorysBlog">
<!-- Allow ColdFusion to update and create the tables when they do not already exist. --->
<cfset this.ormSettings.dbcreate="update">
<cfset this.ormSettings.cfclocation="expandPath("/common/cfc/db/model/")"> 

Creating the tables and the relationships using persistent CFC's

The code below is a persistent CFC that will be used to create the table and its relationships. Like other CFC's, this CFC has the initial component declaration with the addition of the persistent argument which is set to true, and the properties map the column names. We'll go over the relationships in-depth later in the article.

• Examining the PostId
  • The PostId is our primary key, it is annotated with the fieldtype="id"
  • generator determines how to increment the primary key
  • See Map the properties for more information
• The ormtype is the datatype. These are generic values since CF-ORM is database agnostic. If you want finer control, you can substitute the ormtype with sqltype.
• length specifies the column length. The default length for string datatypes is 255 characters.
• I will not cover the details of the other properties here, refer to Map the properties for more information.

<cfcomponent displayName="Post" persistent="true" table="Post" output="no" hint="ORM logic for the new Post table">

<cfproperty name="PostId" fieldtype="id" generator="native" setter="false">
<!-- Many posts for one blog. --->
<cfproperty name="BlogRef" ormtype="int" fieldtype="many-to-one" cfc="Blog" fkcolumn="BlogRef" cascade="all">
<cfproperty name="UserRef" ormtype="int" fieldtype="many-to-one" cfc="Users" fkcolumn="UserRef" cascade="all">
<!-- The ThemeRef is optional. I am not going to make a relationship here as it will make a required constraint. --->
<cfproperty name="ThemeRef" ormtype="int"> 
<cfproperty name="PostUuid" ormtype="string" length="35" default=""> 
<cfproperty name="PostAlias" ormtype="string" length="100" default=""> 
<cfproperty name="Title" ormtype="string" length="125" default=""> 
<cfproperty name="Headline" ormtype="string" length="110" default=""> 
<cfproperty name="Body" ormtype="string" sqltype="varchar(max)" default=""> 
<cfproperty name="MoreBody" ormtype="string" sqltype="varchar(max)" default=""> 
<cfproperty name="AllowComment" ormtype="boolean" default="true"> 
<cfproperty name="NumViews" ormtype="int" default=""> 
<cfproperty name="Mailed" ormtype="boolean" default="false"> 
<cfproperty name="Released" ormtype="boolean" default="false"> 
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

The cfproperty name argument

The cfproperty name argument can either be the name of the physical column in the database, the name of a CFC, or a reference to a new object that will not be placed into the database. We'll cover this important aspect later in the post when we discuss the fkcolumn argument.

The field type argument maps the relationships

CF-ORM has the following relationships: one-to-one: one-to-many: many-to-one: and many-to-many.

The order of the relationship keywords

When you map a relationship, the keyword (one or many) to the left is applied to the cfc that you're working on- and the keyword to the right is applied to the table that you're mapping to. For example, in the Post.cfc, the many-to-one relationship created below signifies that there are many posts (the CFC that is being worked on) for one blog (the table that I am making a reference to).

<cfproperty name="BlogRef" ormtype="int" fieldtype="many-to-one" cfc="Blog" fkcolumn="BlogRef" cascade="all">

Understanding the one and the many

The one keyword signifies that this must be unique. When a one-to-many relationship is found, CF-ORM will create a unique key in the database for the table that is being mapped by the CFC. So, in the case of the UserRef column (which maps to a user), there can be many users for one post. If the mapping for UserRef was one-to-one, a duplicate error will be raised when you try to insert the same user. Since the same author can make many posts, the many-to-one relationship must be used here. If you are receiving a duplicate error from the database when inserting new records, be sure to check your mapping a change any erroneous one-to-one mapping relationships.

one-to-one relationship

A one-to-one relationship is often applied to two tables when there is a set of optional data that is not required. For example, the PostRef, found in the code below, is a one-to-one relationship. I have a Post table that may have an optional image or video. The image or video is not required to be in the post table as there are posts that do not have any images or video. In order to consolidate the post table, I wanted to store images or video into a generic Mediatable, which is a different type of a 'thing'. Having the one-to-one relationship here helps me reduce the length of the column in the Post table, and allows me to organize the concept of two different 'things', i.e. a post and its associated media. Additionally, this relationship is quite useful for dropdowns; I can query the entire media table to make a dropdown list to allow the user to change the image or video. Often, I am surprised that a lot of folks have a negative impression of the one-to-one relationship. This relationship is often misunderstood. I find the one to one relationship to be quite useful. Since this is not relevant to this article, I'll reserve further elaboration for another article.

The many-to-one relationship

The MimeTypeRef in the code below has a many-to-one relationship. This could be a little confusing, you might rationally conclude that one image or video (the CFC that we are working on) would have one mime-type, but remember that whenever the one keyword is found, it signifies that it must be unique record. If we did put a one-to-one relationship here, we would receive a duplicate error message whenever we tried to put in the same mime type for a new record. With the many-to-one mapping, we can have many videos and images for one mime type.

<cfcomponent displayName="Media" persistent="true" table="Media" output="no" hint="ORM logic for the new Media table, can be an image or a video.">
	
 <cfproperty name="MediaId" fieldtype="id" generator="native" setter="false">
<!-- There can be many images and videos for a post --->
<cfproperty name="PostRef" ormtype="int" fieldtype="one-to-one" cfc="Post" fkcolumn="PostRef" cascade="all" missingrowignored="true">
<!-- Many images can have one mime type (if you have many-to-one you'll recive a 'Cannot insert duplicate key in object 'dbo.Media'. The duplicate key value is (11).' error ')--->
<cfproperty name="MimeTypeRef" ormtype="int" fieldtype="many-to-one" cfc="MimeType" fkcolumn="MimeTypeRef" cascade="all" missingrowignored="true">
<cfproperty name="FeaturedMedia" ormtype="boolean" default="false" hint="Is this an image or video that should be at the top of a blog post?"> 
<cfproperty name="MediaPath" ormtype="string" length="255" default=""> 
<cfproperty name="MediaUrl" ormtype="string" length="255" default=""> 
<cfproperty name="MediaTitle" ormtype="string" length="255" default="" hint="Also used for the alt tag."> 
<cfproperty name="MediaWidth" ormtype="string" length="25" default=""> 
<cfproperty name="MediaHeight" ormtype="string" length="25" default=""> 
<cfproperty name="MediaSize" ormtype="string" length="25" default=""> 
<cfproperty name="MediaVideoDuration" ormtype="string" default="" length="25" hint="Used for video types"> 
<cfproperty name="MediaVideoCoverUrl" ormtype="string" default="" length="255" hint="The image URL to cover the video. Used for video types"> 
<cfproperty name="MediaVideoSubTitleUrl" ormtype="string" default="" length="255" hint="The URL to the subtitle file. Used for video types"> 
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

The one-to-many relationship

Simply put, the one-to-many relationship is the inverse of the many-to-one relationship. That is, there is one thing in the CFC that we are working on, to many things that we are mapping to. For example, in a blog, one Author can have many Posts. I don't often use this relationship as I tend to make the relationships from the other side (many-to-one).

The many-to-many relationship

A many-to-manyrelationship couldbe used to map a blog categoryto a blog post like so:

<cfcomponent displayName="Category" persistent="true" table="Category" output="no" hint="ORM logic for the new Category table">
<cfproperty name="CategoryId" fieldtype="id" generator="native" setter="false">
<!-- Many categories for one blog --->
<cfproperty name="PostRef" ormtype="int" fieldtype="many-to-many" cfc="Post" fkcolumn="PostRef" cascade="all">
<cfproperty name="CategoryUuid" ormtype="string" length="75" default="">
<cfproperty name="CategoryAlias" ormtype="string" length="75" default=""> 
<cfproperty name="Category" ormtype="string" length="125" default=""> 
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

However, I replaced this many-to-many relationship with several many-to-one relationships placed into a junction table. A Junction table is also commonly defined as a:

• cross-reference table
• bridge table
• join table
• map table
• intersection table
• link table

While not technically correct, I personally refer to this as a lookup table as it closely matches the lookup table definition used in computer science (a table that contains a simple array). Whatever you may call it, a many-to-many, or a set of many-to-one relationships used in a junction table are bi-directional. Instead of using a many-to-many relationship, I used a many-to-one relationship to map a Post with a Category with a PostCategoryLookup junction table like so:

<cfcomponent displayName="PostCategoryLookup" persistent="true" table="PostCategoryLookup" output="no" hint="ORM logic for the new PostCategoryLookup table">
<cfproperty name="PostCategoryLookupId" fieldtype="id" generator="native" setter="false">
<!-- There can be many posts and categories --->
<cfproperty name="PostRef" ormtype="int" fieldtype="many-to-one" cfc="Post" fkcolumn="PostRef" singularname="Post" lazy="false" cascade="all">
<cfproperty name="CategoryRef" ormtype="int" fieldtype="many-to-one" cfc="Category" fkcolumn="CategoryRef" cascade="all">
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

Mapping with the cfc and fkcolumn arguments

The cfc and fkcolumn argument determines the table and column that you're mapping to. The cfc argument is simple and needs no further explanation. It is the table that you want to create a foreign key too. The fkcolumn is not so simple, rather I find it to be an unnecessarily complex beast.

The fkcolumn argument

The fkcolumn argument takes on different characteristics depending on how its name was set.

If the cfproperty name is an ORM object reference

If we used the name as an object reference, such as Posts, we must use a name that is not already in use in the database, or the name of an existing CFC. A lot of documentation on the web uses the plural name of the table, such as the name that I used- i.e. Posts. However, John Whish, the author of the excellent book ColdFusion ORM, suggests using 'fk_TableName' to signify that the reference is a foreign key. If he applied his naming convention with my RelatedPost table, for example, he would use fk_RelatedPost. No matter what approach you use, what's important to recognize is that this 'Posts' or 'fk_RelatedPost' name is just a reference, and it won't actually exist in the database. If the name is a reference, the fkcolumn argument should be set to the name of the primary key of the cfc that you're mapping to. Here the cfc value is Post and the fkcolumn is PostId. Even though the cfproperty name is Posts, the column that will be created into the RelatedPost table will be the value of the fkcolumn, i.e. BlogId. However, Posts will be the column when I dump out the CFC object. Posts as an object reference in the persistent CFC:

<cfcomponent displayName="RelatedPost" persistent="true" table="RelatedPost" output="no" hint="ORM logic for the new RelatedPost table. This is used to indicate the related posts within the forum.">	
<cfproperty name="RelatedPostId" fieldtype="id" generator="native" setter="false">
<!-- There are many related posts for one post --->
<cfproperty name="Posts" ormtype="int" fieldtype="many-to-one" cfc="Post" fkcolumn="PostId" singularname="Post" cascade="all" lazy="false">
<!-- There is one related post that we are pointing to --->
<cfproperty name="Date" ormtype="timestamp">		
</cfcomponent>

PostId is stored as a column in the database:

Posts is the item in the CFC database object:

This works fine when working with a single foreign key reference to the Post.PostId column, but what if we wanted more than one reference to Post.PostId? The RelatedPost table maps a relationship to a single post to all other posts that relate to it. At the bottom of many of my own posts, you will see a Related Resources label that has links to other posts. The RelatedPost database table was designed to handle this. So here, we need two references to the Post table's primary key- PostId

However, when we use more than one PostIdin the fkcolumn, yet use two objects as the cfproperty name (Posts and RelatedPosts), we will get an error.

<cfcomponent displayName="RelatedPost" persistent="true" table="RelatedPost" output="no" hint="ORM logic for the new RelatedPost table. This is used to indicate the related posts within the forum.">
<cfproperty name="RelatedPostId" fieldtype="id" generator="native" setter="false">
<!-- There are many related posts for one post --->
<cfproperty name="Posts" ormtype="int" fieldtype="many-to-one" cfc="Post" fkcolumn="PostId" singularname="Post" cascade="all" lazy="false">
<!-- There is one related post that we are pointing to --->
<cfproperty name="RelatedPosts" ormtype="int" fieldtype="one-to-one" cfc="Post" fkcolumn="PostId" singularname="Post" cascade="all" lazy="false">
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

This is the error message raised when there is more than one reference to the Post.PostId column in the database: Repeated column in mapping for entity: RelatedPost column: PostId (should be mapped with insert="false" update="false")This error signifies that this column needs to be a read-only post. However, as with many other ORM-related errors, this is incorrect. The problem here is that CF-ORM is trying to create two BlogId references into the RelatedPost table. There is several ways to fix this, but the best way that I found is to use the fkcolumn to point the foreign key back to itself.

Using the fkcolumn to point back to its self

Another way to use the cfproperty name and the fkcolumn is to create a new column into the database using the name attribute, and then use the same column name in fkcolumn to point back to itself. Let's look at the code:

<cfcomponent displayName="RelatedPost" persistent="true" table="RelatedPost" output="no" hint="ORM logic for the new RelatedPost table. This is used to indicate the related posts within the forum.">
<cfproperty name="RelatedPostId" fieldtype="id" generator="native" setter="false">
<!-- There are many related posts for one post --->
<cfproperty name="PostRef" ormtype="int" fieldtype="many-to-one" cfc="Post" fkcolumn="PostRef" cascade="all" lazy="false">
<!-- There is one related post that we are pointing to --->
<cfproperty name="RelatedPostRef" ormtype="int" fieldtype="one-to-one" cfc="Post" fkcolumn="RelatedPostRef" cascade="all" lazy="false">  
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

Instead of creating an object reference using the name attribute, we are using PostRef and RelatedPostRef to create two new database columns. The cfc that we are pointing to remains the same, we want to use the primary key found in the Post table, and we are also using the same that we used in the fkcolumn to point back to itself. This creates two new columns, the PostRef and the RelatedPostRef into the database, and creates the relationship to the PostId using these new columns. Viola! the error is gone, and now the database model is an exact replica of the ORM object model! The PostRef relationship in the database

The RelatedPostRef relationship in the database

And the ORM Object that is now identical to the database model

I must admit that I am not a CF-ORM expert and stumbled upon this approach while discovering different ways to solve some issues. However, it is now my preferred approach. It is my personal preference to use TableName Ref for all of my foreign keys, and use TableName Id for my primary keys. I personally think that it is easier to read, and it allows me to quickly identify what I am working with. I have been using the same name database naming conventions for many many years. I also like the fact that both my object and database models are now uniform.

Final Relationship Notes

You will notice that anytime we create a new relationship with CF-ORM, the columns that hold the relationships will be placed at the end of the table. The order of the columns indicated with the persistent CFC will not be enforced. However, you can change the order of the columns after the tables are created, and CF-ORM won't modify the new order. There is a lot of flexibility with CF-ORM. However, I personally think that the implementation is confusing and would prefer that CF-ORM be more opinionated. One of the reasons why I don't like PHP is that there are far too many ways in PHP to perform a task. This results in added complexity. There is a lot of room for improvement with CF-ORM. I wish that there was an argument in allowed in the cfproperty to be used as a foreign key, such as a foreignKey="true", or something like fkeycolumn="this" or "self".

ColdFusion ORM Supported Databases with a Custom Dialect

The following databases should be supported as long as you provide the custom dialect using the fully qualified class name.

However, ColdFusion ORM is not without its challenges

While having CF-ORM support all database platforms is terrific; using CF-ORM may lock you into using a particular ColdFusion version. CF-ORM functionality changes depending upon the version of ColdFusion. CF-ORM on ColdFusion 9 is quite a bit different than CF-ORM on ColdFusion 2016. So, while you may be supporting a wide variety of database platforms, you may be stuck on a particular ColdFusion version. There are also differences between CF-ORM between ColdFusion, and its open-source sister, Lucee.

Also, CF-ORM has a slew of other challenges. Error reporting is confusing. Since CF-ORM is built upon Hibernate, it does not have the error messages that we typically expect out of a ColdFusion product. Error messages are not always available and are often confusing. The documentation is sparse, and at times misleading. Take for example the cfproperty documentation on the Adobe site. Most of the properties are not documented at all. Two-thirds of the description columns are blank.

Additionally, if you dig around and find documentation for a certain feature, it may not work at all! For example, I wanted to eliminate the database constraint for some of the database columns using the constrained="false" argument. Adobe's own cfproperty documentation mentioned the constrained property, but gave no description. Finally, I was able to find some documentation on the constrained property on another site, however, no matter what I did, I could not get the argument to work. I also went to the ORM channel on cfslack to ask for help, and no one knew how to get it to work.

Finally, another issue is that just like ColdFusion UI, CF-ORM is tied to Adobe ColdFusion and you're limited in what changes you can make. You'll have to wait until a new ColdFusion version to come out before you can upgrade to a new version of Hibernate.

ColdFusion ORM alternatives

While I have not used them yet myself, I have heard good things about Quick and cbOrm. Quick is written in ColdFusion and it handles errors much better than CF-ORM. cbOrm is an extension of CF-ORM, and Ortus has added a lot of functionality and flexibility to the underlying Hibernate engine that CF-ORM uses. I won't cover either Quick or cbOrm here, but they are both worth recommending if you want to avoid CF-ORM, especially if you're already using the ColdBox framework.

Since my project requires that I support as many database platforms possible, and I don't want to include another library, such as ColdBox, I am using CF-ORM and will show you some of the solutions that I used to overcome common CF-ORM pitfalls.

In the next article, we'll jump in and look at the code....

Coldfusion orm java.lang.Int error

Background I ran into an interesting error when working on converting Galaxie Blog's database to use ORM. When importing data from the original database, I received a very cryptic ColdFusion ORM error coldfusion orm java.lang.String errorerror when trying to set the value of a foreign key. It was trying to set an int, and assumed that ColdFusion somehow was casting the int to a string. I manually set the value to an int, and still received the error, but this time received Coldfusion orm java.lang.Int error. This was perplexing. This should have worked as the foreign key expected an int.

Relevant property of BlogRef

<cfcomponent displayName="Users" persistent="true" table="Users" output="no" hint="ORM logic for the new Users table">
<cfproperty name="UserId" fieldtype="id" generator="native" setter="false">
<!-- Many users per blog. --->
<cfproperty name="BlogRef" ormtype="int" fieldtype="many-to-one" cfc="Blog" fkcolumn="BlogRef">
</cfcomponent>

To try to understand what was going on- I kept on trying to change the datatype, but no matter what I set the datatype to, I would receive the same cryptic error. The only difference in the error is that the datatype java.lang.thisDataType errorwould change.

Even hardcoding the value to an int causes an error:

<!-- Load the entity. --->
<cfset UserDbObj="entityNew("Users")">
<!-- Use the entity objects to set the data. --->
<cfset UserDbObj.setBlogRef(1)="">

Results in: Coldfusion orm java.lang.Int error What was even more perplexing is that I had successfully used the same code in previous blocks that had worked! I have just started down the ColdFusion ORM path and wondered what the hell have I gotten myself into. After much research, it turns out that either ColdFusion ORM or Hibernate wants an object passed to a foreign key. Oftentimes, ColdFusion may always raise this cryptic error if the value is set in any other way! The following code finally solved this perplexing error:

<!-- Load the blog table and get the first record (at this time there only should be one record). This will pass back an object with the value of the blogId. This is needed as the setBlogRef is a foreign key and for some odd reason ColdFusion or Hibernate must have an object passed as a reference instead of a hardcoded value. --->
<cfset BlogRefObj="entityLoadByPK("Blog"," 1)="">
<!-- Load the entity. --->
<cfset UserDbObj="entityNew("Users")">
<!-- Use the BlogRefObj entity object to set the data. --->
<cfset UserDbObj.setBlogRef(="" BlogRefObj="" )="">

Further Reading How do I store an integer using ColdFusion Orm? - by James Cushing