All Apps and Add-ons

Splunk DB Connect: Can I run a truncate table statement?

jedatt01
Builder

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.

0 Karma

tmuth_splunk
Splunk Employee
Splunk Employee

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...

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

jedatt01
Builder

Can dbconnect call functions instead? I have vertica and it does not do stored procedures in the traditional sense.

0 Karma

woodcock
Esteemed Legend

I would try creating a stored procedure that calls truncate. That should work.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...