Issues with Like operator in ColdFusion Query of Query and solution

This post is regarding one of my recent findings on LIKE operator in ColdFusion query of query(QoQ). Please find the details below. Hope this helps.

1.       What is Query of Query(QoQ) in ColdFusion?

a.       When you run a query on another query, we call it QoQ. This base query could be obtained from Database or may be from cfdirectory or any other source.

2.       Issue with Like Operator and QoQ

a.      In SQL server, Like operator makes Case Insensitive search. So writing column_name Like ‘%abc%’ or ‘%ABC%’ returns the same result.

b.      This same principle does not work when you write Like operator, in QoQ. QoQ makes case sensitive search. This will result in wrong result set.

3.       Addressing issue with Like operator in QoQ

a.       To address this issue, we need to compare both sides of Like operator in same case.

b.      Ex. WHERE UPPER(item.item_name) LIKE ‘%#UCase(itemName)#%’

c.       WHERE LOWER(item.item_name) LIKE ‘%#LCase(itemName)#%’

HOW TO RUN FILES THAT TAKES VERY LONG TIME TO RUN?

You might have encountered some situations when your file runs for very long time and it results in timeout in browser. This happens when browsers don’t get response within some stipulated time frame. Few examples of such situations are,

1. generating report that needs lot many db operations,
2. reading or writing many files(I/O Operations)
3. long running data base operation through CF file etc.
4.  A page request that has lot many back end jobs to complete. There could be many examples as such.

Here is the suggested solution for this type of situations.

1. Set big requestTimeout value in the pages that are expected to run for very long time. Remember, here the value is in milliseconds.
2. In place of directly calling the files in browser, create a scheduled task in CF Admin and there you set the file URL(see highlights in second screenshot). Mention timeout value to a bigger amount to ensure successful completion of the task.
3. Here, to see what output you got from the page, you need to  check Publish checkbox(see highlights in second screenshot) and set a file path to write the output. Its recommended to set the output file extension to .html for better readability of output.
4. Once you are done with all these setting and done with the scheduled task creation, run the scheduled task, by clicking on green icon, to run the file immediately(screenshot three).

Hope, these details will be of help. Any suggestion or question is most welcome.