Is there a way to make Splunk DB Connect support truncate table statements? When I try to run one it says "A ResultSet was expected but not generated from query "x". Query not executed.
As @DalJeanis stated, this is probably only going to work as a stored procedure or a function. DBX isn't tested against Vertica for stored procedure calls, but it might work. Take a look at this link for an example of creating a function in Vertica and calling it from a SQL statement:
https://my.vertica.com/docs/7.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIO...
It's complaining that you need to return a result set. There are ways to do that.
For instance, you could put your truncate
into a stored procedure which then returns a return code.
Other than that, I'm not sure to what degree dbxquery supports standalone DDL as opposed to pure SQL statements. I do know that it only supports stored procedures that return a single result set, as per this page....
http://docs.splunk.com/Documentation/DBX/3.1.1/DeployDBX/Commands
"Important notes on stored procedure support in DB Connect: dbxquery only
supports stored procedures which return a single result set."
It is also possible that there is some option in dbxoutput
that would allow this, but I don't find it in the docs.
@somesoni2? @woodcock? @niketnilay?
Similar question asked here was never answereed... https://answers.splunk.com/answers/510857/truncate-table-with-db-connect-2.html
Can dbconnect call functions instead? I have vertica and it does not do stored procedures in the traditional sense.
I would try creating a stored procedure that calls truncate
. That should work.