http://coldfusion.com logo
Join Slack
Powered by
<@U070SQMD1> I posted this here since it is relate...
# sql
s
@zackster I posted this here since it is related to DB but knowing it is lucee specific. I’ve noticed there are certain data types from Postgres that instead of being returned as values, get returned as 
org.postgresql.util.PGobject
which then needs to have 
.toString()
called on them to actually print the value. (eg. 
json
jsonb
and recently i found 
citext
(case insensitive text) This seems like an additional step that could be handled before the query is returned. Is it possible to convert this somewhere before the query is returned?
b
@Scott Steinbeck Yes it's possible, but it's not currently done.
So far as I know, Adobe does the same thing Lucee does here so I'm curious what you mean when you say this is "Lucee specific".
The only difference I'm aware of is Adobe CF will auto-cast an instance of 
java.util.UUID
to a string when comparing it, whereas Lucee will not.
s
oh i didnt realize the did, i guess ive only encountered it on lucee
b
Lucee has a bunch of logic that will auto-cast JDBC return values to other things-- mostly around Oracle
Well, Lucee does it for ALL JDBC types, but some JDBC drivers like oracle or Postgres have their own custom types
I've tested Postgres on Adobe and when you use a custom type like JSON, you just get the instance of the java object back
And since some of these java classes have other method you may want to call other than 
toString()
it may be destructive to have Lucee just turn them into a string automatically.
s
interesting, yeah, that makes sense, ive just found myself only ever doing 2 things with it, keeping it as a string, or deserializing it, so it seemed like extra work to map over all the rows every time
b
If you look at PGObject, you'll see it's a base class used for a lot of custom types https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/util/PGobject.html
PGbox, PGcircle, PGInterval, PGline, PGlseg, PGmoney, PGpath, PGpoint, PGpolygon
I'm not sure if all of those can just be turned into a string in every case
heading to cook dinner...
s
ok cool, thanks for the deep dive
b
There's also a pull I sent for Lucee 6 that needs revisited. It started out because you couldn't do 
myQry.someGUID == 'xxxxxx-xxxx-xxxx-xxxx-xxxxxxxx'
in Lucee, but you could in Adobe CF.
After a LONG discussion in which Igal was adamant that no custom-JDBC type checks were necessary, I finally put in a pull to detect 
java.util.UUID
instances in a JDBC resultset coming back and convert them into a string while building the query. After submitting the pull, I did some additional testing on Adobe CF only to realize I had misinterpreted how Adobe does it-- they don't swap out the UUID instance in the result set for a string at the point where the query object is built-- instead the leave the raw UUID instance in the query but • make CFDump "smart" enough to always render it as a string when dumping it • Enhanced their auto-casting to automatically convert the UUID instance into a string when doing a string comparison on it or outputting it as a string. • This means you can still call methods from the underlying UUID class instance however on the column value because they don't convert it up front-- only at the point of use.
I actually preferred that approach once I understood it so I put this info on the pull's comment thread and requested assistance in finding the correct parts of Lucee to modify in order to do it instead.
But instead of getting any assistance, Micha swooped in a week later and just whacked the "merge" button on the pull without seeming to read a single bit of the comment thread and ignoring all my questions and desires to actually change the pull.
Even after repeated pings on the thread, he was 100% gone and never even came back to reply
it was a drive-by-merging 😕
Even @zackster went silent on it, which isn't like him.
s
Read through the PR, yeah that is weird, I mean i guess it got merged so thats a plus. But from the follow up comments it also seems like my best bet (since internally this is happening anyway for UUID) is to intercept the fields and convert them ahead of time since it appears either way there is looping involved. And I feel like modifying the driver to do my bidding would be a bit too fragile and cumbersome to maintain
b
Yeah, it's super annoying
The client app I was working on used transfer ORM so I was able to just modify Transfer itself to convert all the UUID's to strings and not touch all the application code
it got merged so thats a plus.
Not, if it's not the the fix we wanted/needed 😕
s
oh true, i saw you wanted to match Adobe’s functionality being convert on comparison or output which is different then just calling 
.toString()
and being done with it
r
if youre in control of the query, you can cast it yourself, e.g. 
column::text
👍 1
I do that with all uuid and jsonb fields today for instance
s
ill give that a shot, thanks ryan
2 Views
Open in Slack
PreviousNext
Powered by