All Apps and Add-ons

Splunk DB Connect: Error message -- Cannot INSERT to MySql Database

Engager

Hi splunkers,

I would like to insert in my table on MySql database using DB Connect, but i get an error :
java.sql.SQLException: Can not issue data manipulation statements with executeQuery().

the insert query is:
INSERT INTO sms_dump (remote_number,content) VALUES ('08178989988','test insert value');

Spesification :
Splunk DB Connect :3.1.1
Driver : mysql-connector-java-5.1.6-bin.jar
GRANTS : GRANT ALL PRIVILEGES ON . TO 'merapi'@'192.168.3.xxx' IDENTIFIED BY PASSWORD '*E9DDA647E40EF883E5CEE2112CD514DF4xxxxxxx'

below is capture:
alt text

Explorer

I did a insert in postgres and in a mysql 5.1 version, but in 5.7 version I had this problem...

0 Karma

Champion

Hi,

I think you cant use dbxquery for inserting rows in table. Because dbxquery use "executeQuery()" command whereas to manipulate data you actually need executeUpdate().

You can use dbxoutput command. Refer below doc:
http://docs.splunk.com/Documentation/DBX/3.1.2/DeployDBX/Createandmanagedatabaseoutputs

Also did you try creating output directly?

0 Karma

Explorer

p_gurav, please help me here.

This is my query:

insert into schema.table_name (id, web_id, flg_active)
select * from (select ? as id, 4 as web_id, 1 as flg_active) as tmp where not exists (

select 1 from table_name where id = ? and web_id = 4 and flg_active = 1 )
and exists ( select 1 from table_name where id = ? and flg_active = 1) limit 1;

How I input this on the DBOutput if it's need the run a search on step 2?

0 Karma

Explorer

I have the same problem... Any log error to undestand...

0 Karma