How to UPDATE a SQL server database via a MID Server from a script

[bpolo]

We have a script where we insert an ECC queue record to UPDATE a SQL table. When we set the SQL query to be a SELECT statement it works. But when we change the SQL statement to be an UPDATE we get the following error: 

SQLException: Statement.executeQuery() cannot issue statements that do not produce result sets.

It seems that the statement that should be executeUpdate(). What field needs to be set up on the ECC queue record to make it say executeUpdate instead executeQuery() ?  Thanks in advance!

[Bert_c1]

See: what-all-possible-ways-to-push-data-from-servicenow-to-sql

Searching produces results.

[bpolo]

Thanks for the response! So the way we are doing this is via an onAfter script of a transform map. In the script we hard-code the payload and insert a record on the ECC queue. This will go via the MID Server to update the SQL database. The issue is that when the SQL statement is a "SELECT" it works, but when it is an "UPDATE" it does not work. Please advise.  

[Bert_c1]

@bpolo 

I put your thread title in the search on google.com. Try that, the AI response discusses what you seem to want.  also, post on the thread I provided a link to. Folks there may be able to help. I do not practice what you want to achieve. I suspect there are permission problems on the SQL server where a query works but not updating a record in the database works. There should be some error/logs there showing the problem.

On the google response, click "Dive deeper in AI mode" examples are shown.

[Ajay_Chavan]

ECC Queue Record Configuration:
Table: ecc_queue
- agent: [your_mid_server]
- topic: [your_topic] 
- name: "executeUpdate"
- payload: [your UPDATE SQL statement]
- source: [source_identifier]