PocketQuery and Oracle Date format - colon is misinterpreted as a parameter

[eminkevich_scg]

Hi,

Apparently PocketQuery interprets the colon in the date format descriptor as a parameter designator:

TO_CHAR(SAMPLE_TIME,'Mon dd, HH24:00') as SAMPLE_DTTM

Is there a way how would I escape the colon to use it as a literal?

[Felix Grund (Scandio) · Accepted solution]

Yea, now I see. This is actually kind of a problem. I'd say it'll be fixed in PocketQuery 1.13.1 which I plan to release sometime in September. Is this OK?

[eminkevich_scg]

Thank you, that would be great!

[Felix Grund (Scandio)]

Hi Evgeny, can you please check if this snapshot version of PocketQuery 1.13.1 solves this problem? Because in my tests it did ;). Let me know!

[eminkevich_scg]

Yes it has solved the issue.

Should I just deploy it to PROD (how much has it changed from 1.13) or wait until the official release?

[Felix Grund (Scandio)]

Nice! I'll prepare the official release and upload it to the marketplace later today! The new version contains your bugfix and another minor JavaScript issue that won't affect you ;). By the way: if you like PocketQuery, could you consider leaving a review at the Atlassian Marketplace?

[Felix Grund (Scandio)]

Please find version 1.13.1 at the marketplace.

[Felix Grund (Scandio)]

Hi Evgeny,

I tried to reproduce the problem with a sample MySQL database (this shouldn't be Oracle-specific) but I couldn't. I used this statement:

SELECT Name, DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') AS MyDate
FROM Country;

Can you provide your full statement?

Regards, Felix

[eminkevich_scg]

Thank you for responding to this.

Here it is

select 
TO_CHAR(SAMPLE_TIME,'Mon dd HH24:mm') as SAMPLE_DTTM,
NVL((1-m.AVG_IDLE_TIME) , 0) as "Hourly Load Average" 
from JON_DASH.JON_HOST_METRICS M
where HOSTNAME=:hostname
and SAMPLE_TIME> SYSDATE-28
order by SAMPLE_TIME asc

And that's how it is presented with the macro config (the 'mm' parameter, which it should not be):