4

I have a code snippet where I am fetching rows from a database and finding dateDiff from one of the columns in the query.

<cfquery name="querySearchUUID" maxrows="1">
   SELECT [DateInvited] 
   FROM [INVITE_PERSON] 
   WHERE [UUID] = 
  <cfqueryparam value="#arguments.userUUID#"cfsqltype="cf_sql_varchar">
</cfquery>

<cfif querySearchUUID.RecordCount EQ 1>
   <cfif dateDiff('h', querySearchUUID.DateInvited, now()) LTE 24>
       <cfreturn true>
   <cfelse>
       <cfreturn false>
   </cfif>
<cfelse>
  <cfreturn false>
</cfif>

However when I change the code querySearchUUID.DateInvited to querySearchUUID['DateInvited'] in the dateDiff() It fails I dont know why.

Here was the error it produced.

The value class coldfusion.sql.QueryColumn cannot be converted to a date.

Can anyone please explain me what exactly is the difference. Thank you.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Bikash Das
  • 147
  • 1
  • 12
  • 5
    The notation with the square brackets returns the whole column. If you want to access a single cell you have to write `querySearchUUID['DateInvited'][ 1 ]` for the value in that column in the first row. – Bernhard Döbler Apr 16 '19 at 08:57
  • Exactly as @BernhardDöbler said I came up with that too, if it returns complete column then why it does not print anything when I did a `` – Bikash Das Apr 16 '19 at 09:00
  • I believe this has to do with dynamic typing. If you have the code `` and nothing is printed that means the column in your database is empty. I guess the cfdump tag has some internal functionality to find the value in the first row of that column and prints it. When this variable is passed to a function it is passed as said Java object `coldfusion.sql.QueryColumn`. Look at `` – Bernhard Döbler Apr 16 '19 at 10:01
  • So the Object `coldfusion.sql.QueryColumn` is iterable which contains the whole column and returned when we use '[]' whereas '.' gives the first value inside the column of the query. Nice. I did not find any resource saying it. – Bikash Das Apr 16 '19 at 10:13
  • 2
    Typically, yes. Though [there are a few exceptions](https://stackoverflow.com/questions/49723400/generating-a-total-within-a-query-loop/49723548#49723548). Traditionally, when dot notation is used on a query object - *without a query loop* - ColdFusion applies some syntactic sugar and implicitly translates `#queryName.theColumnName#` to "return the value in the first row". That behavior may be a carry over from the early days of CF when complex objects weren't as accessible and it relied more heavily on dot notation. – SOS Apr 16 '19 at 13:51
  • What version of CF? – Shawn Apr 16 '19 at 15:21
  • The version of Coldfusion I am using while writing this code is CF 2018, though I have already tested the same code with CF 10 the result is same. – Bikash Das Apr 16 '19 at 15:51
  • 1
    OT: Since Bernhard & Ageax answered the question: First, if you're using CF2018, you should start refactoring to script-based code instead of tag-based, especially if you are working with cf functions. It's easier and a lot less code. Second, you can simplify your return val a lot. Get rid of the `cfif` and change it to just ``. Or https://cffiddle.org/app/file?filepath=da7bd277-2df9-4001-836d-8f8d8fc9ad5d/b4cfb652-b43c-4e34-9ac0-6ce4df4d89e8/80fc07b6-38ca-4af8-b79a-f8cced8d1102.cfm – Shawn Apr 16 '19 at 16:25
  • And coming back to this one, I'll go one further. It appears that you're simply checking if someone has a `DateInvited` within the past 24 hours. You can get that answer pretty easily through your query itself by using your SQL server `dateDiff()` function. No need for the overhead of processing that on the CF server. The only difference is that the CF method will interpret the difference from the CF Server whereas a query method will interpret from the SQL server. If both servers are in the same time zone, there'd be no difference. Otherwise, it may require more processing. – Shawn Apr 16 '19 at 17:45
  • 1
    @Shawn There's absolutely no reason to refactor code from tag to script when its working perfectly fine as-is. Any refactor costs money in development time, regression testing, deployment and post-production support. There's zero ROI just to satisfy someone's coding preference. However, it's perfectly fine to set those kind of coding standards for new development. – Adrian J. Moreno Apr 17 '19 at 21:24
  • @AdrianJ.Moreno In general, I'd agree. But I'd also disagree that tag-to-scripting code that you're already working on is simply to satisfy coding preference. Or that it provides zero ROI. Many times, just because it currently works, doesn't mean it necessarily works like it should. I've worked with quite a bit of VB code that worked very well for a decade, but could do bad things if a user accidentally (or intentionally) injected something into it that wasn't even possible when it was written. Plus, a lot of things (like looping) are significantly easier and cleaner in script, and ... – Shawn Apr 17 '19 at 21:56
  • ... do a better job of preventing issues downstream (like variable leakage). But mostly, in my experience, when someone sees `...`, they're much more likely to come to the mistaken belief that _"We should replace our CF site with xxxxx"_ than they are when they see `foo = queryExecute(...)`. From the perspective of a developer who has worked with CF through 20 years, keeping that CF site around is a good personal ROI. – Shawn Apr 17 '19 at 21:56
  • 1
    And after jumping down from that soapbox, I realize my original statement wasn't quite what I intended. Rather than "...refactoring...", I should have said that if you're using CF2018, you probably shouldn't be using cf tags in the first place. And if your system is on CF2018 and there are still a lot of cf tags, it might be time to look at whether or not your application is as secure as you think it is. It truly scares me to see questions about CF9 or lower that are clearly medical or financial systems. – Shawn Apr 17 '19 at 21:58
  • @Shawn I'd imagine you and I are among the ever shrinking group of CF devs going on 20+ years. :) – Adrian J. Moreno Apr 17 '19 at 22:19
  • 1
    @AdrianJ.Moreno I know we're probably part of the few who are still willing to admit to such. :-) – Shawn Apr 18 '19 at 00:45
  • @BernhardDöbler - You should write up an answer. – SOS Apr 21 '19 at 20:56

1 Answers1

1

According to my experience, it depends on the way the variable is used. When you use it in a cfdump tag some ColdFusion magic prints the value from the cell in the first row. When you pass it into a function, the original object of type coldfusion.sql.QueryColumn is passed. You can always find ot the type calling getMetadata like <cfdump var="#getMetadata(querySearchUUID['DateInvited'])#">

When you need to pass a value you have to use the line number querySearchUUID['DateInvited'][ 1 ] starting with one for the first row.

Bernhard Döbler
  • 1,960
  • 2
  • 25
  • 39
  • 1
    Very true. @bikashdas - One thing to watch out for with `cfdump` is that it doesn't always display underlying values exactly. It may format them to be more "readable". So when in doubt, use an alternate method as a sanity check. – SOS Apr 23 '19 at 10:39