Search code examples
javaormcoldfusionintegercoldfusion-2016

How do I store an integer using ColdFusion ORM?

TL:DR;

What's the proper syntax/workaround/hack for CF ORM components which have foreign key constraints?

ColdFusion + Integers

Storing a variable as an integer should be an easy programming question, right? Find the appropriate function in the language's documentation (if you can't recall it off the dome) which will say returns: int (owtte) and use that.

Not with ColdFusion.

Some of you may already be aware of ColdFusion's various issues with integers but the one which is currently causing my rapid hair-loss is that ColdFusion will sometimes store your "integer" as either a string, double or long. Now, for most use cases this is not a functionality-breaking issue and is often overlooked/ignored/undiscovered. The exception, in my case, is when ORM comes into play. Of course, as any sensible developer would, I am recognising the possibility of this being down to user error somewhere.

The Problem

The problem occurs whenever CF attempts to interact with the DB. If there is a field which is a foreign key and type 'integer', CF throws a Hibernate Exception with the following message:

java.lang.Integer

That's it. Helpful, right? I found out that this "error message" is explicitly the variable type you're passing into that field, so if you pass a double into the field erroneously, the error message would read java.lang.Double.

Now, this seems to be less about the field itself and more to do with related components - this only happens on a field which has a foreign key constraint linking it to an integer field somewhere else.

Basic example of the kind of thing I'm going for:

Template.cfc

component persistent="true" table="template"{

    property name="id"              type="numeric"  sqltype="integer"   column="templateID" fieldtype="id"  generator="identity";
    property name="userID"          type="numeric"  sqltype="integer"   fkcolumn="userID"           fieldtype="many-to-one" cfc="User";
    property name="lastModified"    type="date"     sqltype="timestamp" column="lastModified";

}

User.cfc

component persistent="true" table="user"{

    property name="id"          type="numeric"  sqltype="integer"   column="userID"     fieldtype="id" generator="identity";
    property name="username"    type="string"   sqltype="nvarchar"  column="username";
    property name="password"    type="string"   sqltype="nvarchar"  column="password";

}

The problem occurs when flushing the ORM having updated template.userID.

Things I've tried

  • Contacting Adobe CF Support (we have an enterprise licence with platinum support, but in a month they haven't been able to give me a solution or even any information beyond "it's not working?")
  • Adding the ormtype attribute to identity fields (and then foreign key fields when that didn't work)
  • Changing the sqltype property (which I now believe is only for table creation which we never needed anyway since the database was already in place)
  • Converting the variable using various combinations of the following functions:
    • NumberFormat( var ) - documentation says it returns 'A formatted number value', actually returns a java.lang.String
    • LSParseNumber( var )- returns a double
    • Int( var ) - This one's great: documentation says it returns an integer, as a string. Actually returns a java.lang.Double
    • Val( var ) - returns a double
    • javaCast( 'int', var ) - returns an integer, which throws the same error
  • Setting the elementtype attribute on the property (both ends)
  • Relaxing the validate attribute on the property (both ends) to 'numeric'

I suspect this may be to do with a combination of Hibernate and CF, rather than just CF, and dispite it's javascript-reminiscent quirks, I still love it.

Environment

  • Windows Server 2012 R2
  • ColdFusion 2016 (Enterprise)
  • SQL Server
Solution

  • TL:DR;

    Pass the entity, not the ID. A foreign key property in CF ORM referencing persistent entity Foo is of type Foo and not int.

    More detail

    After all this time, Adobe passed me around a few people until I landed on an engineer who knew what he was talking about. It turns out the problem is not so much with the ORM engine (Hibernate) not recognising the integer as an integer, but if you have a foreign key which references another persistent entity, then you must pass the entity and not the ID.

    Example (in context of the question)

    First, I've removed the type and sqltype values from the question, since the former can be retrieved by CF inspecting the DB (which in my case is fine, but you may have to explicitly set it, especially if you've turned off useDBforMapping in Application.cfc).

    Second, I've renamed the userID property in the Template component to user, as it may help those that don't immediately understand what I said above about 'passing the entity' to visualise it better.

    Template.cfc

    component persistent="true" table="template"{

    property name="id"              column="templateID"      fieldtype="id"          generator="identity";
    property name="user"            fkcolumn="userID"        fieldtype="many-to-one" cfc="User";
    property name="lastModified"    column="lastModified";

}

    User.cfc

    component persistent="true" table="user"{

    property name="id"          column="userID"     fieldtype="id" generator="identity";
    property name="username"    column="username";
    property name="password"    column="password";

}

    So, lets say you know the ID of the user who is associated with this template (maybe they're the creator, for example). What you have to do is, not pass the ID to the user property, but rather create a User component, then pass that.

    index.cfm

    userID = 4; // Example user ID, which we got from somewhere earlier in the code

templateID = 20; // Example template ID, which we got from somewhere earlier in the code

// Create template entity (loading by primary key)
template = EntityLoadByPK( 'Template', templateID );

// To set the user in the Template entity, we must pass a User entity
user = EntityLoadByPK( 'User', userID );

// Only then can we successfully update the template's foreign key property
template.setUser( user );
EntitySave( template );

// Line below is not usually needed, but it forces DB interaction so it's 
// useful in the context of this example
ORMFlush();

    Hope this helps anyone else who happens across the same confusion.

    JC