greeting Lucee-ites. I am having a very strange issue in Lucee, with MSSQL server, and using either the (deprecated) JDBC driver, or the Microsoft JDBC extension. (These appear to be the only two options). When retrieving a country from our

Countries

table, we have an NCHAR(2) field for isoAlpha2 country codes (e.g.,

US

or

DZ

or

EN

, etc.). However, when I select a single country using ORM (e.g.

c = EntityLoadByPK("Country", 220);

) the isoAlpha2 value comes back TRUNCATED to just a single letter (

U

or

D

or

E

). As mentioned, I've tried the (deprecated) JDBC driver that comes with Lucee as well as the Microsoft JDBC extension. This same select / retrieval works fine in the Adobe CF version of the app that we are migrating from. I have checked all the possible datasource settings, to no avail. Any advice on troubleshooting from here would be welcome.

What do you have as the cfsqltype in the Country object?

I work with Marc so I can comment. The properties of the Country object are simply

property name="countryID" fieldtype="id" generator="identity";
property name="name";
property name="isoAlpha2";

Should assign it a sqlType=“nchar” or define the ormtype, for the column causing the issue.

What is the runlevel of your MSSQL server and language version as well as patch level. I ask as We have massive issues with SQL Server 2014 on a couple of Win 2016 installs. The issue, oddly enough went away by moving the very same code to SQL server 2019, same DB run level on Win 2019

With the full path it knows the exact

countries

object to use, with just the object name it must search for one which it could be finding a different one possibly? Thats one theory. But did you try to just change the

sqltype=

value to what you need it to be for that column?

if i modify the databse so that it's just a CHAR(2) field, both return the correct value.

property name="isoAlpha2" sqltype="char";

or string or whatever

yep. one moment...

but making that change in the object will require you to restart the app fyi

i have an ORMReload() at the top of my template.

Ahh yea that would clear it then

i'll publish my docker-compose later this morning that demonstrates it all for anyone to play with.

alright no prob; could be db related but with the minimal property config for the columns I was assuming it was that but it could relate to DB but I would confirm with those properties to define out your columns.

the issue is, while that may be the fix, it calls into question the entire codebase as converted from ColdFusion to Lucee, on something that should just work.

That is true, Lucee could have its issues in how it detects and sets what that column type is with that jdbc ext but I am not certain. Maybe check with @zackster who may have better insight on that.

ASL? 🙂 what versions of lucee, extensions are you using. that's always the first question I'm ever going to ask

i have a demo docker-compose app that will answer all your questions 😃

in your Application.CFC, change all your double quotes to single quotes or all your single quotes to double quotes. It's stupid, but it's one of those gotchas.

why on earth should that matter?

try scare quotes?

"maybe."

property name="isoAlpha2" ormtype="string";

loads it as expected and same as example #2 which casts it as a string also

interesting. this code is a direct port from ACF, which never had this problem -- and it seems the ORM is capable of determining the right value so long as the table name is fully qualified, so do you think this is a shortcoming in the Hibernate layer/extension?

Yea and Lucee vs ACF and what orm engines they are running are another story. But for best compatibility just define out your objects. This could be a hibernate shortcoming but I am not certain what ACF utilizes either.

ok. thanks for the clarification. not sure we can commit to modifying an entire working codebase adding the additional parameters to cfproperty tags (and possibly injecting more issues in the process) but we will definitely give it some good discussion. Truly appreciate your time and insight.

No problem, anytime!

For anyone interested, i have an updated docker compose that demonstrates the issue and shows that it's not an issue with MariaDB.

The issue with your DEMO is your insert value for IsoAlpha2 is the US, which should be a VCHAR value, not an NCHAR value as NCHAR values reference ASCII values. You are, in effect truncating your truncating storage and not using it correctly. US. US in effect is 2 ASCII Values of 85,83. https://learn.microsoft.com/en-us/sql/t-sql/functions/nchar-transact-sql?view=sql-server-ver16

you may be right but for the wrong reason (especially since ACF can handle this without issue - this is strictly a Lucee / ORM / Hibernate issue). The page you linked to mentions database collation and the returning of a single character, which is damned spot on with regards to the behavior we're seeing. The field holds two characters, and the N simply allows extended character set (Unicode). again, this has worked forever in our ACF implementation and only broke when porting to Lucee.

I think its the opposite. AFC does a lot of hand-holding under the hood which is why 100 percent of my AFC code always works. When moving said code onto lucee, it breaks not because its LUCEE per say, its LUCEE isnt correcting anything I am doing. Instead, Lucee has forced me to dig deeper and write better functioning SQL Statements and other bits of code.

If i eliminate Lucee from the equation, and simply

select * from countries

using Azure Studio or SSMS or any other DB client, I get the correct results. There's no hand-holding going on, and I don't think I am mis-using the datatype on the field (though one could successfully argue that we aren't going to need unicode on that particular piece of data). I would expect to see single character retrieval from the database itself if what you've postulated were in play.

Appears its a hibernate bug for the version Lucee is using. Here is the bug ticket: https://hibernate.atlassian.net/browse/HHH-2304

Open up SQL STUDIO, type in PRINT CHAR(85)+CHAR(83), that is what is being asked here. Database, Please SHOVE NCHAR (NUMBERIC VALUE CHARACTER) into DB table, Microsoft. OK, UM, Not a NUMBER, but WHO CARES Store "US" Now code, AFC bring me a result, AFC OK RESULT is US, LUCEE BRING ME THE SAME RESULT, LUCEE DB, How big are you 2 NUMBER CHARACTERS, LUCEE, OKAY, JAVA TELL ME WHAT IS 85 --"U" , LUCEE, HERE IS RESULT = "U"

@Evil Ware by that logic, i should be able to go into the database, change the datatype to NCHAR(4) and i should get 2 characters back, right?

If you can read Unicode characters, Cool, as for the bug, dbo.table name works. table name only works as a DSN defined.