topic Re: Splunk DB Connect 2: How to change SQL to URL-encoded SQL query for dbxquery? in All Apps and Add-ons

Splunk DB Connect 2: How to change SQL to URL-encoded SQL query for dbxquery?

pop1989 — Wed, 15 Jul 2015 06:23:08 GMT

I have used DB connect 2 to estabilish a connection to an Oracle database. Now I want to use dbxquery to search the oracle table, but to run the dbxquery command, I must first URL encode the SQL query and I have no idea how to do this. For example, I search a table using SELECT * FROM "USER_ADDR" but how do I change this to a URL encoded query and where I can learn how to do it?

Re: Splunk DB Connect 2: How to change SQL to URL-encoded SQL query for dbxquery?

hmozaffari — Thu, 16 Jul 2015 20:40:45 GMT

Most of the time you need to replace space with %20. For other characters check http://www.w3schools.com/tags/ref_urlencode.asp

SELECT%20*%20FROM%20%22USER_ADDR%22

Re: Splunk DB Connect 2: How to change SQL to URL-encoded SQL query for dbxquery?

pop1989 — Tue, 29 Sep 2020 06:45:11 GMT

hi ,hmozaffari

the url -encoded is HTML URL? the url http://www.w3schools.com/tags/ref_urlencode.asp couldn't be open ,and then ,I open http://www.w3school.com.cn/tags/html_ref_urlencode.html ,but I don't know if it right . could you help check the follow error

select C.CHAIN_ID,C.LOG_ID,C.DATUM,C.ZEIT,C.ANALYZED_STATUS,R.TYPE,R.EVENTP_START from RSPCLOGCHAIN C,RSPCPROCESSLOG R where C.LOG_ID=R.LOG_ID and (C.ANALYZED_STATUS='X' OR C.ANALYZED_STATUS='R') ORDER BY C.DATUM DESC

|dbxquery query="select%20C.CHAIN_ID%2CC.LOG_ID%2CC.DATUM%2CC.ZEIT%2CC.ANALYZED_STATUS%2CR.TYPE%2CR.EVENTP_START%20from%20RSPCLOGCHAIN%20C%2CRSPCPROCESSLOG%20R%20where%20C.LOG_ID%3DR.LOG_ID%20and%20%28C.ANALYZED_STATUS%3D%27X%27%20OR%20C.ANALYZED_STATUS%3D%27R%27%29%20%20ORDER%20BY%20C.DATUM%20DESC

" connection="p11_connection" maxrows=100

************ command="dbxquery", No JSON object could be decoded*************

Re: Splunk DB Connect 2: How to change SQL to URL-encoded SQL query for dbxquery?

suryaavinash — Tue, 04 Aug 2015 07:37:02 GMT

Any answers for this one , we were able to work with the SQL queries using dbquery in DB connect 1 but are facing issues with DB COnnect 2 (dbxquery). Please help on this.

Re: Splunk DB Connect 2: How to change SQL to URL-encoded SQL query for dbxquery?

koshyk — Tue, 04 Aug 2015 07:56:22 GMT

You could do convert it using online tools
http://meyerweb.com/eric/tools/dencoder/

Re: Splunk DB Connect 2: How to change SQL to URL-encoded SQL query for dbxquery?

hmozaffari — Tue, 04 Aug 2015 12:09:08 GMT

I suspect the JSON error you receive is not because of query encoding. After query is executed and database returns the results, the content has a special characters which are not JSON friendly, since results is returned through REST API and in JSON format. To verify it simplify your query and instead of asking for all columns change it to:

select%20C.CHAIN_ID%20from...

Also you don't need to encode comma to %2C.