The multi-part identifier "variable" could not be bound.

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]The multi-part identifier "my.variable.name" could not be bound.

This error means that you did not surround your variable(s) with pound signs (hash marks) in your query.

view plain print about
1<cfquery name="getData" >
2    SELECT     [ID],
3         [Value],
4         [Identifier],
5         [Identifier_id]
6    FROM     [MASTER_PLANNING]
7    WHERE    0 = 0
8        <cfif StructKeyExists(arguments, "ID")>
9            AND ID = arguments.ID
10        </cfif>
11        <cfif StructKeyExists(arguments, "Value")>
12            AND [Value] = arguments.Value
13        </cfif>
14        <cfif StructKeyExists(arguments, "Identifier")>
15            AND Identifier = arguments.Identifier
16        </cfif>
17        <cfif StructKeyExists(arguments, "Identifier_id")>
18            AND Identifier_id = arguments.Identifier_id
19        </cfif>
20</cfquery>

Should look like:

view plain print about
1<cfquery name="getData>
2    SELECT     [ID],
3         [Value],
4         [Identifier],
5         [Identifier_id]
6    FROM     [MASTER_PLANNING]
7    WHERE    0 = 0
8        <cfif StructKeyExists(arguments, "
ID")>
9            AND ID = #arguments.ID#
10        </cfif>
11        <cfif StructKeyExists(arguments, "
Value")>
12            AND [Value] = #arguments.Value#
13        </cfif>
14        <cfif StructKeyExists(arguments, "
Identifier")>
15            AND Identifier = #arguments.Identifier#
16        </cfif>
17        <cfif StructKeyExists(arguments, "
Identifier_id")>
18            AND Identifier_id = #arguments.Identifier_id#
19        </cfif>
20</cfquery>

CFMAIL Attachments Not Named Correctly and Have No File Extension

CFMAIL with attachments was changing the file name and dropping the file extension of the attachments. Email recipients were receiving attachments with no file extension of the same name but with a number appended.

For example, the form had 3 file input fields. The resultant emailed attachments were named, "uploads1", "uploads2", and "uploads3".

The directory intended to store my uploaded files was set:

view plain print about
1<cfset AttachmentDirectory = "uploads" />

The files were stored with CFFILE

view plain print about
1<cfif structKeyExists(form, "ad")>
2    <cfif structKeyExists(form, "FileOne") and form.FileOne is not "">
3        <cffile action="upload" filefield="form.FileOne" destination="#AttachmentDirectory#" nameconflict="makeunique">
4        <cfset FileOneName = CFFILE.ServerFile>    
5        <cfset FileOne = CFFILE.ServerDirectory & "\" & CFFILE.ServerFile>    
6    </cfif>
7    <cfif structKeyExists(form, "FileTwo") and form.FileTwo is not "">
8        <cffile action="upload" filefield="form.FileTwo" destination="#AttachmentDirectory#" nameconflict="makeunique">
9        <cfset FileTwoName = CFFILE.ServerFile>
10        <cfset FileTwo = CFFILE.ServerDirectory & "\" & CFFILE.ServerFile>
11    </cfif>
12    <cfif structKeyExists(form, "FileThree") and form.FileThree is not "">
13        <cffile action="upload" filefield="form.FileThree" destination="#AttachmentDirectory#" nameconflict="makeunique">
14        <cfset FileThreeName = CFFILE.ServerFile>
15        <cfset FileThree = CFFILE.ServerDirectory & "\" & CFFILE.ServerFile>
16    </cfif>
17</cfif>

The problem is with the the CFFILE destination. It is set with a relative directory. CF Documentation states:

"Pathname of directory in which to upload the file. If not an absolute path (starting with a drive letter and a colon, or a forward or backward slash), it is relative to the ColdFusion temporary directory, which is returned by the GetTempDirectory function."

"If the destination you specify does not exist, ColdFusion creates a file with the specified destination name. For example, if you specify the destination, C:\XYZ, ColdFusion creates a file XYZ in the C: drive."

The simple and correct solution is to set your CFFILE destination as an absolute path:

view plain print about
1<cfset AttachmentDirectory = "G:\www\mysite\uploads" />

Mach-ii 1.8 Upgrade Error

Upgrading Mach-ii to release 1.8 from 1.5 produced an exception error in our application on some saves.

Message
Event-handler for event 'index.cfm' in module '' is not defined.
Detail
Check that the event-handler exists and for misspellings in your links or XML configuration file.

The URL looks like this:
https://www.mysite.com/index.cfm/event/index.cfm?event=MyNextEvent&message=Your%20changes%20have%20been%20saved§ion=options

Notice the "index.cfm" appears twice in the URL. It seems that the URL is no longer being built correctly. We don't really have any SES URLs, so what needs to be adjusted for Mach-ii 1.8?

In mach-ii.xml there is a applicationRoot property which is set to "/".

view plain print about
1<!-- PROPERTIES -->
2<properties>
3        <property name="applicationRoot" value="/" />
4        <property name="defaultEvent" value="showHome" />
5        <property name="eventParameter" value="event" />
6        <property name="parameterPrecedence" value="form" />
7        <property name="maxEvents" value="200" />
8        <property name="exceptionEvent" value="exceptionEvent" />
9        <property name="exceptionMode"
10</properties>

I did a command search for "applicationRoot" to see how it impacts URLs. It shows up in the "Mach-ii/framework/viewLoaders" directory. My exception is being thrown on an event and not a view, so "applicationRoot" is not the key to this solution.

So looking through the Mach-ii directory, we see templates like UrlRoute.cfc. What does that do? Well it looks like it deals more with SES or SEO Urls, nothing as basic as what we are trying to accomplish.

http://trac.mach-ii.com/machii/wiki/MachII1.8SESImprovements has a buildRouteUrl() function with example syntax:

view plain print about
1buildRouteUrl("routeName", "urlParameters", "queryStringParameters", "urlBase")

So what's this "urlBase"? http://trac.mach-ii.com/machii/ticket/641 has a lengthy discussion and explains the difference between "applicationRoot" and "urlBase". The applicationRoot property has to do with your CFML engine finding files on disk (such as views) and not how URLs are built. URLs are driven off of urlBase which could have a full host in their as well.

So the bottom line here is to add the urlBase property to mach-ii.xml:

view plain print about
1<!-- PROPERTIES -->
2<properties>
3        <property name="urlBase" value="/" />
4        <property name="applicationRoot" value="/" />
5        <property name="defaultEvent" value="showHome" />
6        <property name="eventParameter" value="event" />
7        <property name="parameterPrecedence" value="form" />
8        <property name="maxEvents" value="200" />
9        <property name="exceptionEvent" value="exceptionEvent" />
10        <property name="exceptionMode"
11</properties>

Reinit the application and it works like a charm! No more URL goof ups.

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.

Previous Entries / More Entries

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