orm mysql issue with formula

[Jeremy R. DeYoung]

I'm running into an issue where I have a orm entity setup for the table `order` in my mysql database. This works as expected except when I add an orm formula to the properties that counts the number of records for example.

Hibernate/Lucee throws an error trying to replace `order` in the formula with order0.`order`

I suspect the problem has to do with the dialect parser not detecting the backslash part of `order` b/c order is a reserved word.

If i change the name of the table to anything else - it works perfectly. and if i remove the formula it works perfect. Only when both the table name is `order` and a formula exists does it throw an error.

Has anyone else experienced this?

[Nando Breiter]

Can you show some code? I don't understand what you mean by "orm formula".

I may have run into a similar issue several years ago. I remember I suspected that the issue was related to a reserved word, but I never was able to nail it down, so maybe it wasn't. The error message certainly didn't help. 

After years of experience using orm, when something doesn't work and I can't figure out why, I revert to SQL without hesitation. I've found the balance between convenience and loss of direct control tips toward the latter the more I use orm, especially as a project grows in size. 

Aria Media Sagl

+41 (0)76 303 4477 cell
skype: ariamedia

--
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html
---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/8e70f402-9fac-4c00-ac8b-3b49dff75547%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jeremy R DeYoung]

here is a snippet of my order.cfc

//Primary Key property name="orderID" fieldtype="id" generator="native" setter="false"; property name="cartID" default="0"; property name="orderNumber"; property name="accountID" default="0";

property name="hasChildren" formula="SELECT COUNT(*) FROM `order` o WHERE o.product = productID" setter="false" getter="true";

since the property hasChildren has a reserved word of order as a table name, it must be wrapped in ` like `order` in order for the mysql driver to properly execute the statement.  The above would result in the following SQL code 

           SELECT COUNT(*) FROM order0.`order` o WHERE o.product = productID

However if I were to change the name of the table to orders

if this hasChildren property had something like "SELECT COUNT(*) FROM orders o WHERE o.productID = productID

it would work just fine AND results as SELECT COUNT(*) FROM order0 o WHERE o.productID = productID

It appears to me that lucee is not understanding `order` is a proper table name for mysql and thus creating invalid SQL statements.

Jeremy R. DeYoung (615) 261-8201 | fax (615) 680-0870 What is Your Next Step.com?

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAGHrs%3D_wK%3Da9hQy%3Dacxq6kea%3DkL88E1WupaY0unmgG9kmxnK7A%40mail.gmail.com.

[Denard Springle]

Don't use reserved words as table or column names? I would think the problem would be solved more readily by not using reserved words than by trying to force Hibernate to not treat reserved words as, well... reserved. :)

My 2 cents. :)

-- Denny

[Jeremy R DeYoung]

agree

Jeremy R. DeYoung (615) 261-8201 | fax (615) 680-0870 What is Your Next Step.com?

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/7ab71e8a-6c54-4fc5-a960-ab924818fe2e%40googlegroups.com.