Weird issues with ColdFusion ORM Relationships and some unexpected results

Tags: ColdFusion 9, CFORM

The last week I had been working with another friend of mine trying to map some ORM to an existing database, after awhile we started discovering something very weird. Understanding HQL at the best of times is not easy as it really doesn't work how you think it does, especially when you are trying to come to grips with how it works.

Anyway the problem that manifested itself was with an older database, that had composite keys in the actual tables. Never having used it like this before I thought to myself that it should be that hard, and boy was I wrong with that assumption. I thought had relationships down pat, and this has certainly shown me that the is sometimes a curve ball thrown our way that will really make us think

When I sat down and starting writing the entity code I kept it to a minimum to begin with, and then slowly moved into adding the next piece so that we could get a feel of how it was progressing as we went along.  There are 3 tables in question to this problem and I only got as far as mapping two of them, before I started seeing some strange behaviour.

Lets look at the first code.

Advertiser.cfc
component persistent="true" {
   property name="AdvertiserId" generator="identity" fieldtype="id";
   property name="AdvertiserTypeId" ormtype="int";
   property name="Company" ormtype="string" length="70";
   property name="Address" ormtype="string" length="500";
   property name="suburb" ormtype="string" length="500";
   property name="State" ormtype="string" length="20";
   property name="Postcode" ormtype="string" length="12";
}

Now the only thing to note here is that there is an FK in this table, which is added above but as we began to do this with small steps we knew that this should still map fine, which it did. We had been able to  create a new Advertiser and populate the data, as well as being able to load up a record.

So everything was going to plan. The next step was to then write the entity for the AdvertiserType, which again we did without any problems, and was again able to create a new record, and load up any existing data that we asked it too. This code at this point in the process is listed below.

component persistent="true" {
   property name="AdvertiserTypeId" generator="identity" fieldtype="id";
   property name="Name" ormtype="string" length="50";
}

Again we just kept it very simple and progressed with small steps, so the next stage of the equation was to add the relationship to between the two, and this is when we began to run into trouble. You will also notice that I did speak of composite keys, and we did not map these into the object to begin with, because we weren't to sure on how it would go. Again we just took small steps.

So to get them related to one another we began with the Advertiser.cfc and added the following line of code, and removed the line with the same.

property name="AdvertiserType" column="AdvertiserType" fieldtype="many-to-one" CFC="AdvertiserType" fkcolumn="AdvertiserTypeId";

As you can see that it is very straight forward, and nothing out of the ordinary here. And when we ran the following HQL it retrieved the objects that we expected it to retrieve, which was no surprise to the both of us.

savecontent variable="local.hqlQuery" {
   writeOutput("From AdvertiserType as adt");
}

But as this was not the final query we had been wanting, we added the next step of the HQL, so it was rewritten like the following.

savecontent variable="local.hqlQuery" {
   writeOutput("From abAdvertiserType as adt inner join adt.AdvertiserTypeId as ad");
}

What we got back was anything that we expected to see, all the above line of code is suppose to do is map the data together. But the error that we received was nothing descriptive, as you can tell.

Application Execution Exception
Error Type: java.lang.NullPointerException : [N/A]
Error Messages:

As we had SQL logging on for our database, we decided to stop ColdFusion and delete the log file and restart ColdFusion. And when we ran the template again, we saw nothing in the log files, absolutely nothing had been logged. Ok we thought that this must be because we have the hibernate relationship on the wrong side, so we modified the HQL query to the following to see if what we suspected was the case.

savecontent variable="local.hqlQuery" {
   writeOutput("From Advertiser as adt inner join ad.AdvertiserTypeId as ad");
}

 

We got the exact same result, and it just wasn't making sense. The next step was to just add a relationship on the AdvertiserType and see if it actually needed it there, we knew that it didn't but we wanted to satisfy our curiosity on this, and we had been right it made no difference at all. We spent a good 4-6 hours on this problem, and couldn't figure out what we had been doing wrong. Then eventually we started getting some data back, but what we noticed was that some of the records actually contained a very strange message.

Rather than go on about how we tried various things, the ultimate problem was that we would get one of two errors no matter what we tried.

So far no one has had an answer to this and Glen has posted in the appropriate areas with no answers either. The error message that we began to see in our array, and that's right this was inside of our array..

[undefined array element] access denied (coldfusion.sql.DataSourcePermission testing123)

So after the amount of time we tried to get this to work, we gave up and decided to switch straight back to normal SQL for this part of the code only, until we can revisit this at another time.


TweetBacks

Weird issues with ColdFusion ORM Relationships and some unexpected results http://fb.me/17wUeFnlC Aug 8, 2011
Weird issues with ColdFusion ORM Relationships and some unexpected results - http://goo.gl/clSkw Aug 8, 2011

Comments (Moderated) | Add Comment

  • Jaime Metcher's Gravatar Maybe your problem is that property AdvertiserTypeID doesn't exist on Advertiser. It looks like you replaced it with AdvertiserType when you set up the many-to-one association. So from that direction you'd need to join on Advertiser.AdvertiserType.

    From the other direction - well, this is a unidirectional assocation as you've presented it, so there is no other direction. You'd need to add a one-to-many to AdvertiserType if you wanted to go the other way.

    One way to think of HQL is that it is querying the object graph, NOT the database schema. AdvertiserType does not actually have a reference (as in a pointer to an object) to Advertiser, therefore HQL can't see any association from that direction. Quite different from SQL, which is always bidirectional, but entirely consistent with how you'd navigate the object graph in code.
    # Posted By Jaime Metcher | 8/9/11 11:44 PM
  • Andrew Scott's Gravatar Thanks James, I was aware that it needed to be a bi-directional relationship. The question was, no the problem was that I had trouble getting that relationship to work.
    # Posted By Andrew Scott | 8/10/11 7:05 AM

Comments (Moderated) | Add Comment