This query runs in Splunk DB Connect 1, but why does it not work in Splunk DB Connect 2?

[cpatacsil]

The query below is running in Splunk DB Connect version 1 but not in version 2.

SELECT
(SELECT COUNT()
FROM Request_Table
WHERE Closed = 'Yes'
AND System = 'PlainOld_Source'
**AND TRUNC(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(60*60*24))) = TRUNC(sysdate-1)*
) AS PlainOld_Alias,
(SELECT COUNT()
FROM Request_Table
WHERE Closed = 'Yes'
**AND TRUNC(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(60*60*24))) = TRUNC(sysdate-1)*
AND Status ='Closed'
AND System = 'DSL'
) AS Connection_Source,
trunc(sysdate-1) Automatic_ClosedDate
FROM dual

I tried to dissect the query and I found out that on the condition in bold caused the error. Please let me know your thoughts on converting this to be compatible in DB Connect version 2 Splunk app. Thank you!

[Dmitri_P]

DB Connect v2 doesn't accept a slash in a SQL query because this sign is defined as reserved in urllib.py file. For resolution of the issue see my comment in a similar topic (https://answers.splunk.com/answers/341611/splunk-db-connect-2-slash-i... please:

To resolve the issue, you should edit
$SPLUNK_HOME/etc/apps/splunk_app_db_connect/bin/dbxquery.py:
change the line (usually its number is 123)
query = urllib.quote(query)
to
query = urllib.quote(query, safe='')

Don't forget to make a backup copy before editing. Take care to keep changes after upgrades.

[cpatacsil]

Thank you for your answer and it worked. But I have another question, this Select * FROM DUAL query is not working but the inside SELECT statements are now working

SELECT
(SELECT COUNT()
FROM Request_Table
WHERE Closed = 'Yes'
AND System = 'PlainOld_Source'
AND TRUNC(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(60*60*24))) = TRUNC(sysdate-1)
) AS PlainOld_Alias,
(SELECT COUNT()
FROM Request_Table
WHERE Closed = 'Yes'
AND TRUNC(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((ClosedDateTime-18000) /(60*60*24))) = TRUNC(sysdate-1)
AND Status ='Closed'
AND System = 'DSL'
) AS Connection_Source,
trunc(sysdate-1) Automatic_ClosedDate
FROM dual

[jcoates_splunk · Accepted solution]

this is probably due to changes in the JDBC driver required to run on Java 8?

[cpatacsil]

Thank you for your answer. I am still validating if this really caused the issue.