MSSQL Replacing a Single Table with a Back Up Copy

It happens. It happened to me. Twice. Testing code, your table gets screwed up. Heavy into CF9 ORM development, I managed to delete every row from one table. One week later I blanked out half of another table, simply by changing ORM relationships without adjusting ORM update code.

Whether you are using ORM or not, it's bound to happen to you.

So you wish to recover the contents of a ravaged table. First, you'll need a back up MS SQL database that contains the exact contents of the table, before you jacked it up.

Run this query

view plain print about
1--set the identity field increment back to 1
2truncate table [my-oops-db].[dbo].[myjackedtable]
3
4INSERT INTO [my-oops-db].[dbo].[myjackedtable](
5 [myJackedTableId]
6 ,[Text]
7 ,[Type]
8 ,[Section]
9 ,[Required]
10 ,[Seperator]
11 ,[Ordinal]
12 ,[Table]
13 ,[Column]
14 ,[FieldName]
15 ,[Validate]
16 ,[MinLength]
17 ,[maxLength]
18 ,[DisplayText]
19)
20
21SELECT
22 [myJackedTableId]
23 ,[Text]
24 ,[Type]
25 ,[Section]
26 ,[Required]
27 ,[Seperator]
28 ,[Ordinal]
29 ,[Table]
30 ,[Column]
31 ,[FieldName]
32 ,[Validate]
33 ,[MinLength]
34 ,[maxLength]
35 ,[DisplayText]
36
37FROM [my-clean-db].[dbo].[myjackedtable]

The 'truncate table' command resets the Id counters back to zero, so we can add back the same primary keys as in the back up table.

Checking or Testing ORM Objects

Try to stay away from using logic that will only evaluate IF the UserAccount Object, or any other ORM object, was set to "false" otherwise it will throw an error stating that the complex object cannot be evaluated as a simple type.

Avoid:

view plain print about
1<cfif MyORMObject NEQ false>

Instead, try the following to get the same result or end for which we are looking.

Check for the object instead of checking for the absence of one.

view plain print about
1<cfif isObject(MyORMObject)>

Check for an array of objects if you are grabbing more than one possibility.

view plain print about
1<cfif isArray(MyORMObject) AND ArrayLen(MyORMObject)>

Checks for the object and then makes sure there is a value in its primary key, this is good when you have already created the entity "createEntity()" but may have not loaded it properly so you want to check that it exists AND has its primary key value.

view plain print about
1<cfif isObject(MyORMObject) AND Len(MyORMObject.getMyORMObjectID())>

ColdFusion ORM Setting Empty String Value as Null

Setting a numeric field as Null with an Empty String value passed in.

A form submission of several numeric values cannot accept an Empty String. It will not convert it to a Null value.

To pass in a Null value, it must first be converted to Null using javacast("null","").

Here's the background.

The initial error:

Message
The value cannot be converted to a numeric because it is not a simple value.
Detail
Simple values are booleans, numbers, strings, and date-time values

The form field structure of variables and values to be set.

Let's take a look at LoanTerm1Value which is [empty string]. The database column allows Nulls. The form field variable will be passed into ColdFusion ORM through this property in the tables's ORM CFC:

view plain print about
1property name="LoanTerm1" column="LOAN_TERM1" type="numeric" ormtype="double" ;

using this line in the form processing or setter function:

view plain print about
1LoanTermOption.setLoanTerm1(arguments.EventArgs.LoanTerm1Value);

This is where we get the error shown above. Unfortunately, to fix this, we have to check for an empty string first, then set to Null using javacast("null",""), otherwise set to our passed in value.

view plain print about
1if (arguments.EventArgs.LoanTerm1Value EQ ""){ LoanTermOption.setLoanTerm1(javacast("null",""));    }
2else { LoanTermOption.setLoanTerm1(arguments.EventArgs.LoanTerm1Value); }

Research credit on this subject goes to:

Ray Camden's Blog
www.coldfusionjedi.com/index.cfm/2011/4/25/Null-Entities-and-CFDUMP
www.coldfusionjedi.com/index.cfm/2011/2/21/ColdFusion-9-ORM-error-String-index-out-of-range-0
He was discussing mostly getting and not setting entities.

StackOverflow
stackoverflow.com/questions/3505937/set-null-values-in-cf9-orm
Where the question was asked, "Is there a way to get CF9 ORM to insert NULL Values into the database rather than an empty string? I've got a numeric field which can be null, but throws an error because it's trying to enter '' " Which is exactly how I would have phrased the question for this issue.

ids for this class must be manually assigned before calling EntitySave()

ColdFusion 9 ORM error

Message
ids for this class must be manually assigned before calling EntitySave():

Detail
Root cause :org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling EntitySave():

This error occurs when creating a new record using ORM. The ORM bean for the table is not configured to let the database create the new ID, identifier, PK, or Primary Key.

Add this to the id property of the table's bean CFC:

view plain print about
1generator="identity" unique="true"

So the entire CFC would look something like this:

view plain print about
1component persistent="true" table="MyTable" alias="MyTableAlias" schema="dbo" output="false"
2{
3    /* properties */
4    
5    property name="Id" column="Id" type="numeric" ormtype="int" fieldtype="id" generator="identity" unique="true" ;
6    property name="Whatever" column="Whatever" type="string" ormtype="string" ;
7    property name="Description" column="Description" type="string" ormtype="string" ;
8}

This component is written in CFScript, in case it doesn't look familiar without tags.

org.hibernate. NonUniqueObjectException: a different object with the same identifier value Part 2

Let's have another look at this and see if we can change our logic up to eliminate the use of EntityLoadByExample. This wasn't working consistently across all similar functions in the application. This method will allow us to eliminate the need for ORMGetSession().merge() methods, or ORMEntityMerge() methods.

We will check to see if an object is returned from EntityLoad using our primary keys.

Here's the code walk through:

First we set all our variables from arguments or ORM defined objects.

view plain print about
1var ReferralInfoArray = getCompany().getCompanyInfo().getReferralInfo();
2var CompanyCode = getCompany().getCompanyCode();
3var ReferralId = arguments.eventArgs.ReferralId;
4var ReferralSource = arguments.eventArgs.ReferralSource;
5var ReferralEmail = arguments.eventArgs.ReferralEmail;

Next we create or ORM Entity to utilize the methods and attempt to create the object that contains the primary keys for which we are looking.

view plain print about
1var ReferralSourceArray = application.orm.createEntity("COMPANY_REFERRAL_INFO");
2var objReferralSource = ReferralSourceArray.EntityLoad(CompanyCode=CompanyCode,ReferralId=ReferralId);

If an object is returned with our primary keys, then we know it already exists in the ORM session. So we test to see if it is an object, and if so, set the other variables.

view plain print about
1if (isObject(objReferralSource)){
2        objReferralSource.setEmailAddress(ReferralEmail);
3        objReferralSource.setSource(ReferralSource);
4    }

If an object is not returned, this means that this object does not exist in the ORM session. So we set the variables using the original ORM Entity methods and save. We have to save because it's new and not in the ORM session.

view plain print about
1else {
2        ReferralSourceArray.setCompanyCode(CompanyCode);
3        ReferralSourceArray.setEmailAddress(ReferralEmail);
4        ReferralSourceArray.setSource(ReferralSource);
5        ReferralSourceArray.setReferralId(ReferralId);
6        ReferralSourceArray.save(ReferralSourceArray);
7    }

And there we go.....

Here's the whole function for reference:

view plain print about
1<cffunction name="submitReferralSource" access="public" output="false" hint="uses the cust admin service to add/edit referral source info">
2 <cfargument name="eventArgs" required="true" />
3 <cfscript>
4var ReferralInfoArray = getCompany().getCompanyInfo().getReferralInfo();
5var CompanyCode = getCompany().getCompanyCode();
6var ReferralId = arguments.eventArgs.ReferralId;
7var ReferralSource = arguments.eventArgs.ReferralSource;
8var ReferralEmail = arguments.eventArgs.ReferralEmail;
9var ReferralSourceArray = application.orm.createEntity("COMPANY_REFERRAL_INFO");
10var objReferralSource = ReferralSourceArray.EntityLoad(CompanyCode=CompanyCode,ReferralId=ReferralId);
11    
12    if isObjectt(objReferralSource)){
13        objReferralSource.setEmailAddress(ReferralEmail);
14        objReferralSource.setSource(ReferralSource);
15    }
16 else {
17        ReferralSourceArray.setCompanyCode(CompanyCode);
18        ReferralSourceArray.setEmailAddress(ReferralEmail);
19        ReferralSourceArray.setSource(ReferralSource);
20        ReferralSourceArray.setReferralId(ReferralId);
21        ReferralSourceArray.save(ReferralSourceArray);
22    }
23
</cfscript>
24</cffunction>

org.hibernate. NonUniqueObjectException: a different object with the same identifier value

Message
a different object with the same identifier value was already associated with the session: [COMPANY_REFERRAL_INFO#{CompanyCode=1002, ReferralId=10}]

Detail
Root cause :org.hibernate.NonUniqueObjectException: a different object with the same identifier value was already associated with the session: [COMPANY_REFERRAL_INFO#{CompanyCode=1002, ReferralId=10}]

ColdFusion 9 ORM. This error occurs when using the same function to update and create records. The save works fine for new records but update an existing record throws the error above.

Here's what works for saving the new record:

view plain print about
1private.ReferralSourceArray.save(private.ReferralSourceArray);

ORM will not allow us to update an object with the same ID already defined in the session. Doing a little research gave us the merge method. This works for saving updated records, however, not for saving newly created records.

Here's what works for saving an updated record:

view plain print about
1obj = ORMGetSession().merge(private.ReferralSourceArray);
2private.ReferralSourceArray.save(obj);

So how do we use both? Creating an if statement, but how to differentiate? EntityLoadByExample!

view plain print about
1if (ArrayLen(EntityLoadByExample(private.ReferralSourceArray))){
2    obj = ORMGetSession().merge(private.ReferralSourceArray);
3    private.ReferralSourceArray.save(obj);
4}
5else {
6    private.ReferralSourceArray.save(private.ReferralSourceArray);
7}

EntityLoadByExample loads and returns an array of objects that match the sample entity. If none exists, it returns an empty array. This is how we know if the record to be changed is new or an update. It checks the record to be changed (it's passed in) against the existing ORM records.

Here's the whole function for reference:

view plain print about
1<cffunction name="submitReferralSource" access="public" output="false" hint="uses the cust admin service to add/edit referral source info">
2    <cfargument name="eventArgs" required="true" />
3    <cfscript>
4    var ReferralInfoArray = getCompany().getCompanyInfo().getReferralInfo();
5    var CompanyCode = getCompany().getCompanyCode();
6    var private = structNew();
7    private.ReferralSourceArray = application.orm.createEntity("COMPANY_REFERRAL_INFO");
8private.ReferralSourceArray.setCompanyCode(CompanyCode);
9private.ReferralSourceArray.setEmailAddress(arguments.eventArgs.ReferralEmail);
10private.ReferralSourceArray.setSource(arguments.eventArgs.ReferralSource);
11private.ReferralSourceArray.setReferralId(arguments.eventArgs.ReferralId);
12    if ArrayLenn(EntityLoadByExample(private.ReferralSourceArray))){
13        obj = ORMGetSession().merge(private.ReferralSourceArray);
14        private.ReferralSourceArray.save(obj);
15    }
16    else {
17private.ReferralSourceArray.save(private.ReferralSourceArray);
18    }
19    
</cfscript>
20</cffunction>

ids for this class must be manually assigned before calling save()

Message
ids for this class must be manually assigned before calling save(): Fee

Detail
Root cause :org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save(): Fee

Using CF9 and ORM, I ran into this error. This table is set up as the "many" end of a "one to many" relationship in the ORM relationship mapping.

This is the ORM bean component for this table:

view plain print about
1component persistent="true" table="Fee" extends="/.com/ORM" schema="dbo" output="false"
2{
3    /* properties */
4    
5    property name="FeeId" column="FeeId" type="numeric" ormtype="int" fieldtype="id" ;
6    property name="CompanyCode" column="CompanyCode" type="string" ormtype="string" ;
7    property name="Description" column="Description" type="string" ormtype="string" ;
8    property name="Amount" column="Amount" type="numeric" ormtype="double" ;
9    property name="Ordinal" column="Ordinal" type="numeric" ormtype="double" ;     
10}

The primary key of this table is not referenced as the foreign key on the other table. It uses another column, in this case CompanyCode.

As the Primary Key of the Fee table is not generated or reliant on the related table, we have to set the "generator" attribute. The default is "assigned" in CF9. This means that the application assigns the id or primary key for the table. This won't do in this instance because, as stated above, there is no relationship with the other table on this column.

Adding the generator attribute to the primary key and setting it to "identity" resolved the situation.

view plain print about
1component persistent="true" table="Fee" extends="/.com/ORM" schema="dbo" output="false"
2{
3    /* properties */
4    
5    property name="FeeId" column="FeeId" type="numeric" ormtype="int" fieldtype="id" generator="identity" ;
6    property name="CompanyCode" column="CompanyCode" type="string" ormtype="string" ;
7    property name="Description" column="Description" type="string" ormtype="string" ;
8    property name="Amount" column="Amount" type="numeric" ormtype="double" ;
9    property name="Ordinal" column="Ordinal" type="numeric" ormtype="double" ;     
10}

The identity value ensures the key is automatically generated by the database and populates the object with the generated key. But, this strategy requires ORM to execute two SQL queries to insert a new object.

Context validation error for tag cffunction

Either the end tag </cffunction> encountered on line 12 at column 11 requires a matching start tag or tag cffunction does not support end tag.

Working with ColdFusion 9 and ORM, checking out and loading an application from a SVN repository resulted in the Context validation error for tag cffunction error.

It turns out that ORM cannot process the functions in MXUnit folders. It is necessary to remove the MXUnit folder from the web root path.

Element APPLICATION.ORM is undefined in a Java object of type class ERROR

Converting a huge application from Reactor to CF ORM, this error turned up during a conversion of a cffunction.

Element APPLICATION.ORM is undefined in a Java object of type class [Ljava.lang.String; referenced as ''

Here's the original function (notice the preceding "q" denoting a query):

view plain print about
1<cffunction name="getqAuthenticationPackages" access="public" hint="return query recordset of authentication packages">
2    <cfscript>
3    return variables.Reactor.createGateway("AuthenticationPackage").getAll();
4    
</cfscript>    
5</cffunction>

Here is the new function which produced the error:

view plain print about
1<cffunction name="getaAuthenticationPackages" access="public" hint="return query recordset of authentication packages">
2    <cfscript>
3    //return variables.Reactor.createGateway("AuthenticationPackage").getAll();
4
    return variables.application.ORM.createEntity("AuthenticationPackage").loadAll("AuthenticationPackage");
5    
</cfscript>    
6</cffunction>

Two things to notice here are:

  1. The preceding "q" has been replaced by an "a". ORM returns everything as an array, so we have to convert all our associated functions to work with the returned data as an array.
  2. The scope of the ORM.creatEntity() call. This is what produced the error. Remove the "variables" scope reference, so the call is made from the actual "application" scope.

Final working function:

view plain print about
1<cffunction name="getaAuthenticationPackages" access="public" hint="return query recordset of authentication packages">
2    <cfscript>
3    return application.ORM.createEntity("AuthenticationPackage").loadAll("AuthenticationPackage");
4    
</cfscript>    
5</cffunction>

Hibernate Dialect must be explicitly set

Using ORM and ColdFusion 9 Developer's Edition, I received the following error:

Hibernate Dialect must be explicitly set

Checking my database, I found it was not running! Get that database back online and try again!

But my first error was:

The request has exceeded the allowable time limit Tag: cfoutput

Changing my locally hosted CF Admin timeout request up to 120 seconds from the default 60 seconds, saving and retrying got me the Hibernate error.

BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Blog Owner