• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

ColdFusion 2021 ORM

New Here ,
Apr 26, 2021 Apr 26, 2021

Copy link to clipboard

Copied

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.

Views

1.4K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 26, 2021 Apr 26, 2021

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 27, 2021 Apr 27, 2021

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 27, 2021 Apr 27, 2021

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 28, 2021 Apr 28, 2021

Copy link to clipboard

Copied

No problem Charlie. I trust you immensely. It's probably something on my end. Will revisit it again today 🙂

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 03, 2021 May 03, 2021

Copy link to clipboard

Copied

This ormsettings solution unfortunately is not working for us. Our this.ormsettings values are displayed in the attached image. We are evaluating CF2021 to upgrade but have to get beyond this hurdle to do so. Hoping that someone out there has been down this path with the challenge of a legacy db table named as a reserved word such as 'case' or 'group' and short of going through a large painful refactor - is there a way in CF 2021/Hibernate 5.2 to force Hibernate/ORM to simply wrap eg: [] in MS SQL Server? This reserved word issue does not surface in CF2016.20210503_this_ormsettings_public.png

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 04, 2021 May 04, 2021

Copy link to clipboard

Copied

quote

This ormsettings solution unfortunately is not working for us. Our this.ormsettings values are displayed in the attached image. We are evaluating CF2021 to upgrade but have to get beyond this hurdle to do so. Hoping that someone out there has been down this path with the challenge of a legacy db table named as a reserved word such as 'case' or 'group' and short of going through a large painful refactor - is there a way in CF 2021/Hibernate 5.2 to force Hibernate/ORM to simply wrap eg: [] in MS SQL Server? This reserved word issue does not surface in CF2016.


By @Phia Dev

 

I know of 4 ways to enable Hibernate to use reserved words. It is doubtful whether they will work in ColdFusion. However, you'll never know till you try.

 

  1. Use backticks (for individual reserved words). For example, replace case, group, etc. with `case`, `group`, etc.
  2. Set hibernate.globally_quoted_identifiers as a Java property (global, for every reserved word).
  3. Create a file named hibernate.properties and include its full path in the classpath (global, for every reserved word).
  4. Create a Hibernate configuration file, hibernate.cfg.xml and include its full path as the value of this.ormSettings.ormconfig (global, for every reserved word).

 

 

 

Option 1:

straightforward.

 

Option 2:

Add the following flag in ColdFusion's jvm.config file.

 

-DHibernate.globally_quoted_identifiers=true

 

Then restart ColdFusion.

 

Option3:

Contents of the file hibernate.properties

hibernate.globally_quoted_identifiers=true

Include the full path of the file in the classpath setting in jvm.config. 

Restart ColdFusion.

 

Option 4:

My guess for implementing this option:

 

<cfset this.ormsettings = {

...
ormconfig="absolute_path_to_Hibernate_configuration_file"

...

}>

 

 

There are 2 possible versions of  the file hibernate.cfg.xml, depending on whether a public or system DTD is ised.

 

Public DTD

 

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
	<session-factory  name="java:hibernate/SessionFactory">
		<property name="hibernate.globally_quoted_identifiers" >true</property> 
	</session-factory>
</hibernate-configuration>

 

 

 

System DTD

 

<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM 
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
	<session-factory>   
		<property name = "hibernate.globally_quoted_identifiers">true</property>         
	</session-factory>
</hibernate-configuration>

 

 

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 04, 2021 May 04, 2021

Copy link to clipboard

Copied

Much appreciated. Option 1 did not work for me either. Straight forward but perhaps in our environment something is not playing well with something. All of your suggestions are pretty familiar due to my recent research, but your Option 2 is what I have been looking for all along... so I'll give that awhirl next. Thanks for taking the time to post this. Long live CF.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 04, 2021 May 04, 2021

Copy link to clipboard

Copied

Phia, while the jvm arg may well prove to work best for you, I see now that both you and I were loose earlier with our spalling of that arg when set in the application.cfc this.ormsettings.

 

We sometimes made it plural and sometimes did not. It is indeed hibernate.globally_quoted_identifiers, not hibernate.globally_quoted_identifier. I will correct my first note, as others may come upon this in the future. You showed using it also, and said things didn't work. This may be why.

 

But again I appreciate the value of a jvm setting vs changing many application.cfc/cfm files.

 

That said, this is indeed a valid 5th to BKBKs. "4 ways". (He seems not to have noticed I'd already proposed the backtrick approach, and you'd said it didn't work. So he apparently didn't see the proposal of this application-level setting, as a 5th option for his list.)

 

It's certainly good to have options at so many levels, as each may appeal to different needs, from code-level, to app-level, to orm config file level, to jvm-level.

 

Looking forward to hearing if any may finally work for you, like the first had worked for me, in cf2021 and earlier. 


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 04, 2021 May 04, 2021

Copy link to clipboard

Copied

Good get Charlie. I'm smiling. Glad you caught that. I strive to not ever do that type of thing but perhaps I may have had a senior moment 🙂 I'm thankful there are multiple options and just because one did not work for me in the environment I am currently working in, doesn't mean it won't work for others. Yes, you both suggested the backtick. As I tested, I tried to make sure I was always introducing only one solution at a time of course, to avoid conflicts. I have candidly not had a lot of experience with ORM over the years. I'm still testing but the jvm arg is promising so far. In addition to '-DHibernate.globally_quoted_identifiers=true' we will also employ '-Dcoldfusion.literal.preservetype=false' in CF2021 and we leave the enable null support off for legacy code. Hopefully those are not issues. There very well may be other issues coming into play whereas we employ FW/1 and Hibachi and of course ORM vis a vis Hibernate 4.2 with CF2016 and 5.2 with CF2021. Another curiosity I've had is the difference between 'auto_quote_keyword' and 'globally_quoted_identifiers' (and not sure if that is also supposed to be plural), but no worries. I will post what I have learned here when done... soon. Thanks to all.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 04, 2021 May 04, 2021

Copy link to clipboard

Copied

To paraphrase the 80's song from Human League, we're only human. 🙂 And yes, that second arg is indeed singular, and these are documented here. And yep, that one may also be a solution in addition to or instead of the other, and it, too, could be set either at the jvm or app or orm config file level. So again, lots of options, and I'll be curious to hear which (or if both) may solve it for you. 


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 04, 2021 May 04, 2021

Copy link to clipboard

Copied

Thanks Charlie. I appreciate your specific comments. Great to know. I also like the music analogy being a Berklee grad and Billboard alum. Now I just have to kick someone out of that server... I'll post back here with results. Take care.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 08, 2021 May 08, 2021

Copy link to clipboard

Copied

@Phia Dev , An update. I have looked into your original question some more. My findings are as follows:

 

  • In ColdFusion 2021, you have to do nothing to "force Hibernate to wrap a keyword". That is because ColdFusion's Hibernate implementation is configured, by default, to "wrap" keywords. In other words, in ColdFusion's Hibernate implementation, the default value of the setting hibernate.globally_quoted_identifiers is True. However, a discrepancy arises.
  • The underlying ORM engine in ColdFusion 2021 is Hibernate v5.2. In this Hibernate version, the default value of the setting hibernate.globally_quoted_identifiers is False.
  • Notwithstanding the discrepancy, there are apparently some keywords that ColdFusion's Hibernate implementation is unable to wrap. For example, Where. 
  • See https://tracker.adobe.com/#/view/CF-4211713 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 11, 2021 May 11, 2021

Copy link to clipboard

Copied

Thank you. I reviewed https://tracker.adobe.com/#/view/CF-4211713 Short answer still wip.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 27, 2021 Apr 27, 2021

Copy link to clipboard

Copied

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."

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 27, 2021 Apr 27, 2021

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 04, 2021 May 04, 2021

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 04, 2021 May 04, 2021

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 06, 2021 May 06, 2021

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 07, 2021 May 07, 2021

Copy link to clipboard

Copied

Charlie: Setting this.ormSettings.logSQL=true even if temporarily is helpful in my opinion for trouble shooting ORM. Thank you for that suggestion. Otherwise, this is still a wip for the team...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 07, 2021 May 07, 2021

Copy link to clipboard

Copied

Oh, so by "still a wip" do you mean the problem remains unresolved? Even with the corrected property name? If so, bummer indeed. 


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 11, 2021 May 11, 2021

Copy link to clipboard

Copied

@Phia Dev , any news on this?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 11, 2021 May 11, 2021

Copy link to clipboard

Copied

I posted above but still a wip. I was out yesterday and someone else was on it Friday.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 12, 2021 May 12, 2021

Copy link to clipboard

Copied

LATEST

Thanks for the update.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation