Not getting any response from the CF-ORM side, so trying here with Composite Keys and Rleationship mapping in ColdBox application

Oh I seriously need help on this and I am stuck, all I am getting is error after error.

First here are the 3 tables in the most minimum.

Advertiser.cfc

property name=“AdvertiserId” generator=“identity” fieldtype=“id”;

property name=“CompanyCode” ormtype=“string” length=“10” fieldtype=“id”;

property name=“AdvertiserTypeId” ormtype=“int”;

property name=“Company” ormtype=“string” length=“70”;

AdvertiserType.cfc

property name=“AdvertiserTypeId” generator=“identity” fieldtype=“id”;

property name=“CompanyCode” ormtype=“string” length=“10” fieldtype=“id”;

property name=“Name” ormtype=“string” length=“50”;

Galerry.cfc

property name=“GalleryId” generator=“identity” fieldtype=“id”;

property name=“CompanyCode” ormtype=“string” length=“10” fieldtype=“id”;

property name=“AdvertiserId” ormtype=“int”;

property name=“Name” ormtype=“string” length=“50”;

And the original query that I am transforming over to HQL

select distinct Advertiser.AdvertiserTypeId, AdvertiserType.Name

from AdvertiserType

inner join Advertiser ON AdvertiserType.AdvertiserTypeId = Advertiser.AdvertiserTypeId

right outer join Gallery ON Advertiser. AdvertiserId = Gallery.AdvertiserId

order by AdvertiserType.Name

So what I have tried to just get going is put a relationship inside the Advertiser.cfc like this

property name=“AdvertiserTypeId” fieldtype=“many-to-one” CFC=“AdvertiserType” fkcolumn=“AdvertiserTypeId,CompanyCode”;

When I right the first bit of HQL which I assume would be this

From AdvertiserType adt inner join adt.AdvertiserTypeId as ad

All I am getting is an exception thrown, which is listed in the previous post below. It seems that I am not understanding the relationships here, and as this is an existing DB with data I am just trying to map to it and get a result based on the same as a normal query.

Anyone have any ideas?

Glen,

Have you tried enabling SQL logging? I’ve had a similar error before and when I saw the SQL hibernate was generating it helped me make the changes needed.

-Aaron

It looks the the response you got from Brian on the cf-orm-dev list is spot on.

-A

No I haven’t tried the SQL Logging as of yet…

The response from Brian in that list is not really spot on, as there are links to the tables as you can see in Advertiser it has an AdvertiserTypeId, which is what I am trying to add to the entity via a mapping.

So unless I am not understanding that solution, but there is an association to link the tables, but would be interested to know why one would think this is not right.

It is not right because you are not doing any ORM relationships, you are just saying that the foreign key ids are properties so hibernate does not relate them.

HQL works on the basis of the related objects not like normal SQL where you do joins on the columns.

You need to define those properties using :http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WS5FFD2854-7F18-43ea-B383-161E007CE0D1.html

those foreign keys should be related objects using the available related fieldtypes: onetomany, etc.

You are missing one step.

Luis F. Majano
President
Ortus Solutions, Corp
www.ortussolutions.com

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

Social: twitter.com/lmajano facebook.com/lmajano

Hi Luis,

Actually that is not true, and I am indeed doing relationship if you read down to the bottom of the post, it is clearly there.

Well then it should work right?

You would think that, and why I made a bee line for the HQL and how with composite keys. Up till this point I have never had any problems with ORM and mapping to older DB’s.

But this time I started getting weird messages in the actual returned array, like permission denied and null pointers etc.

You would think that, and why I made a bee line for the HQL and how with composite keys. Up till this point I have never had any problems with ORM and mapping to older DB’s.

But this time I started getting weird messages in the actual returned array, like permission denied and null pointers etc.

I agree with Andrew.

Ben,

SQL Logging shows nothing, especially when the null pointer exception is thrown. In the other case I see the SQL call and the query, but nothing out of the ordinary when it comes to the data. But the error is not truly an exception, it is when you dump the entity out you get a permission denied right inside the actual object itself.

I guess no matter has actually taken the code and seen the error for yourselves then?

Do you mean Aaron?

Btw I have blogged the steps that Glen and I both took on this.

Regards,

Andrew Scott

http://www.andyscott.id.au/

Btw I have blogged the steps that Glen and I both took on this.

Thanks Ben,

I am also in the middle of finalising another blog post, that CFB would seriously benefit from.