Skip to main content
P
Phia Dev
Participating Frequently
April 26, 2021
Question

ColdFusion 2021 ORM

  • April 26, 2021
  • 3 replies
  • 2457 views

Q - My organization uses CF ORM and we are currently evaluating CF2021, contemplating an UG from CF2016, (which I understand includes an UG of Hibernate 4.2 to 5.2 in the process).


While I've worked with CF for quite some time, I have not had much history with CF ORM over the years.

 

We happen to have a legacy table named with a keyword in MS SQL Server and it apparently needs to be wrapped when CF2021 ORM performs TSQL operations. Understandable and confirmed and apparently Hibernate by default does not do so.


Changing the name of the table is not a viable option at this time.

 

How do we force Hibernate to wrap a keyword eg: [Case] ?

 

I imagine we need either
hibernate.globally_quoted_identifiers=true (where false is the default)
and/or
hibernate.auto_quote_keyword=true (where false is the default).

 

From what I have researched, I have not been able to clarify if we need to create a .hbmxml file whereas these settings are apparently not available within ormsettings...
and/or
we need to add a jvm argument in the CF 2021 admin, and if so, not sure of the syntax?

 

Perhaps someone else has encountered this?

 

Any words of wisdom would be appreciated.

    This topic has been closed for replies.

    3 replies

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    May 4, 2021

    Not sure these are words of wisdom, and I know you said you can't change the name of the table at this time, but have you considered building a set of views that you can use instead of directly addressing the tables?

     

    Dave Watts, Eidolon LLC

    Dave Watts, Eidolon LLC
    P
    Phia DevAuthor
    Participating Frequently
    May 4, 2021

    Another great idea. Thanks Dave. My gut tells me that may not work with our ORM puzzle for the specific problem we are experiencing whereas it's not just FW/1 and Hibernate but we also use a FW called Hibachi... I will keep it in the back of my mind. eg: When adding a property to an Entity hence adding a column to the corresponding db table for an object named with a reserved word the auto generated sql under the hood is a concern and I am not as intimately familiar with that as I wish I was. I have flipped this.ormsettings.logSQL to true per Charlie's earlier suggestion and I am so glad I did that. Helpful. Using a reserved word or special characters is a common issue with older apps but the ORM portion is candidly less familiar to me. I always aim to follow 5 self imposed rules - Consistent, Native, Specific, DRY & KISS. However, whenever employing 3rd party preboxed tools, somes rules may have to bend a little through osmosis. Thanks again to all that chimed in. Appreciated and actually sort of a fun challenge.

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    May 7, 2021

    Phia, it's not clear what you're saying did solve the problem. You mention the log setting I'd mentioned. Did that help point you to the right solution? Was it the other ormsettings value I first proposed? or the jvm arg? Or something else? Great if it is indeed solved now.

    /Charlie (troubleshooter, carehart. org)
    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 27, 2021

    Hi @Phia Dev,

    Note that the Hibernate upgrade to v5.2 occurred in the ColdFusion 2018 release. Of particular interest is the breaking change, "You must update an entity inside a transaction."

    P
    Phia DevAuthor
    Participating Frequently
    April 27, 2021

    Thank you. I'll look into that more. I was not familiar.

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    April 26, 2021

    Phia Dev, you have at least two options. You can wrap the tablename with backticks, in the cfcomponent line, or yes you can use the hibernate.globally_quoted_identifiers to do it globally. (Update since first commenting here: I had mistakenly written that "hibernate.globally_quoted_identifiers" here as singular rather than plural. I have corrected that here and in the two code samples below.) 

     

    I have tested this with a table named "table" (which would be a reserved word, and would only work in SQL for SQL Server if it was put in brackets, like you show).

     

    Here first is doing it in the cfcomponent (or you can do it in cfscript with component):

     
    <cfcomponent persistent="true" table="`table`">

     

    To be clear, when you make a change like that, you need to restart the app for that change to be picked up. The easiest way is to call the applicationstop() function in CFML.

     

    Or, here is how to do in the this.ormsettings in Application.cfc:

     

    <cfset this.ormsettings = {hibernate.globally_quoted_identifiers="true"}>

     

    Note that it can also be helpful to add also there the orm debug setting for ORM, logsql, as in:

    <cfset this.ormsettings = {hibernate.globally_quoted_identifiers="true",logsql="true"}>

     

    That causes output with error details to be written to the coldfusion-out.log (if running CF as a service) or to the console (if you started CF that way).

     

    Let us know if this gets you going. 

    /Charlie (troubleshooter, carehart. org)
    P
    Phia DevAuthor
    Participating Frequently
    April 27, 2021

    Hey Charlie:

     

    Thanks for taking the time to post a few options for us. Appreciated. I tried both and all is fine in CF2016 but unfortunately not in CF2021. Even both options together worked fine in CF2016. Interesting.

     

    Anyway, a snippet of our Case.cfc eg: table="`Case`" persistent="true"

     

    An excerpt of our ORM config specific to your example perhaps to confirm it is syntactically consisent with your suggestion...

    <cfset this.ormSettings.logSQL = true />
    <cfset structInsert( this.ormSettings,'hibernate',structNew(),true )>
    <cfset structInsert( this.ormSettings.hibernate,'globally_quoted_identifiers',true,true )>

     

    <!--- another thought I had and it seemed fine in CF2016 but not in CF2021 --->
    <cfset structInsert( this.ormSettings.hibernate,'auto_quote_keyword',true,true )>

     

    I suppose I should note that we flipped the preserve data type -Dcoldfusion.literal.preservetype=false and we leave the enable null support off for legacy code.

     

    In researching, I got a sense that we would need to create a .hbmxml file for vars that are not available within this.ormsettings although I am not familiar.

     

    We prefer your ormsettings option and perhaps if I can get it cooking we will go that route.

     

    I tested with your syntax too...

    <cfset this.ormsettings = {
    autogenmap = "true",
    automanageSession = "false",
    cacheprovider = "ehcache",
    cfclocation = "/xxxxxx/model/entity",
    datasource = "xxxxx",
    dbcreate = "update",
    dialect = "MicrosoftSQLServer",
    eventHandling = "true",
    flushatrequestend = "false",
    logSQL = "true",
    savemapping = "false",
    secondaryCacheEnabled = "true",
    skipCFCWithError = "false",
    useDBforMapping = "true",
    hibernate.globally_quoted_identifier = "true"
    }>

    ... but still no luck. I recycled the CF Service each time... but the error is...

    ErrorCode 156
    Message [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'Case'.
    ErrorCode 102
    Message [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'testcf2021'.
    SQLState HY000
    StackTrace java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near

     

    Let me know if I misinterpretted your suggestions.

     

    I suppose if we don't get either of these options cooking, we could consider renaming the db table Case to Cases and the model.entity.Case.cfc to Cases. I was trying not to have to go that route but it may be an option for us after all.

     

    Thanks again for taking the time.

     

    Long live CF 🙂

     

    Brad Lee

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    April 28, 2021

    Brad, my approach was confirmed to work on cf2021. I'm reading this on my phone so can't check out "cases" vs my "table" choice, let alone your various other variants. I hope I can try them tomorrow. 

     

    Conversely, you could try things as a simple demo yourself to see it works, and then add things of your sort until it breaks. 

    /Charlie (troubleshooter, carehart. org)
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices