[Coldbox 5.5] Saving with FKs and full Hibernate log

Communities ColdBox HMVC
1

Hi all,

I am attempting my first “Save” of an entity using the virtual service method, which contains a number of FK’s.
Unfortunately, I get the error message:

org.hibernate.exception.ConstraintViolationException

Although the query is outputted in
lucee-5.2.9.31\logs\server.out
…to work out where I’m going wrong, I need to see the full non-parametised INSERT statement. Is this possible?

I suspect I am going wrong with the populateModel method, specifically with the many-to-one relationships. Does anyone have an example of the create handler and view which involve many-to-one relationships?
E.g. On a new user form, select the user role from a drop down. Therefore:
User -> Role = many-to-one

John

2

You will need to post your entities to debug it. Most likely a bi-directional relationship and hibernate is trying to insert it twice and the constraint fails or you have a unique constraint

3

Hi Luis,

Thanks for the reply. I’ve attached
WorkOrder.cfc
BaseIdentity.cfc
WorkOrderJobType.cfc

There are other relationships, but if I can get one sorted, I should be able to replicate the process to the others.

This is my hibernate log:

insert

into tbl_WorkOrder (CreatedBy, Active, WorkOrderName, WorkOrderNumber, EnquiryDate, AssessmentBy, LastUpdatedBy, Priority, Notes, AssessmentComplete, TotalEx, AssessmentDate, WorkOrderReference, DueDate, StatusID, CompanyID, WorkOrderJobTypeID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

And my populate:

prc.WorkOrder = populateModel(
model = WorkOrderService.get( rc.WorkOrderID ),
exclude = “WorkOrderID”,
composeRelationships = true
);

I would like to check a full Hibernate log with all the parameters, to see straight away the incorrect value(s).

WorkOrder.cfc (3.84 KB)

WorkOrderJobType.cfc (1.25 KB)

BaseEntity.cfc (1.79 KB)

4

Without seeing the referenced CFCs, I suspect that one of your columns in the save is being saved with a null value. Your populateModel call below does not have a memento argument. As such, you are populating it with the entire request collection ( which you will want to check for every property name that is an FK relationship ).

My suggestion would be to use cbValidation ( which is part of CBORM ) and check for the model’s validity before firing the save.

Also, from experience, unless a column has a notnull constraint on it, I would avoid using fetch=”join”. I’m not sure if that’s the case or not here but, if you have a null key in anything defined with that fetch, it will be missing from any collection results you bring back, because of the join statement.

HTH,

Jon

5

Clarification: without seeing the unincluded referenced CFCs : WorkOrderStatus, Company, WorkOrderJobType, etc. I did download and look at the attached files. :slight_smile:

6

Thanks Jon,

I’ve attached 2 more files which complete the relationships to WorkOrder. I have also attached my handler WorkOrders.cfc.

In this instance the 3 x “many-to-one” within WorkOrder are all notnull=“true” - so I have added this. However I have found my first bug - WorkOrderNumber was not being passed in and didn’t have a default value. I’ve updated WorkOrder accordingly. The error message is still the same…

Is it therefore case that there’s no way to output the full SQL query? [In the old days (i.e. last month)) this was always how I debugged my queries.]

WorkOrderStatus.cfc (566 Bytes)

company.cfc (994 Bytes)

WorkOrder.cfc (3.9 KB)

WorkOrderJobType.cfc (1.25 KB)

WorkOrders.cfc (11 KB)

7

Found it / them!
The final variable that did not have a default value was EnquiryDate.

prc.WorkOrder.setEnquiryDate(now());

Thanks for the help.

However, my original question regarding getting the SQL statement still stands. If I had this, then at a glance, all the problems would be obvious.