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!