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

[nugrohoanung]

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:

[jmorais]

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

[p_gurav]

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?

[jmorais]

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?

[jmorais]

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