How to use database connector's command separator

Hello,

I am trying to use the command separator of the mysql database connector, but I couldn’t figure out how to structure the query. Right now, I’m using the script editor with Groovy interpreter, as follows:

return
"delete from TABLE1 where PERSISTENCEID = " + persistenceIdTbl1 + ";" +
"delete from TABLE2 where PERSISTENCEID IN (" + persistenceIdTbl2.join(",") + ");" +
"delete from TABLE3 where PERSISTENCEID = " + persistenceIdTbl3 + ";" +
"delete from TABLE4 where PERSISTENCEID IN (" + persistenceIdTbl4.join(",") + ");" +
"delete from TABLE5 where PERSISTENCEID = " + persistenceIdTbl5 + ";";

Description:

  • This script is done inside a human task’s Connectors Out

  • TABLE1 - TABLE5 exist in the same database, defined at the connector’s configuration (Driver, URL, username, password).

  • persistenceIdTbl1, 3, and 5 are of type integer, defined in the contract and will be returned through the form.

  • persistenceIdTbl2 and 4 are of type list integer (multiple column is checked in the contract)

I am using ; (semicolon) as the command separator. I am sure this code works separately as I’ve tried to create separate connectors with the exact same order of execution. Although, upon executing this string of code, I keep on running into an error saying “Script is not set”.

Any help will be very much appreciated. Thank you.

Hello

You may have an issue with the Groovy syntax you use, could you try merging line 1 & 2 in your script?

I think the first line returns null because the semicolon is optional in Groovy

Let us know

Hi, thanks for your answer. Could you elaborate more or give an example on what you mean by merging the 2 lines? I’m afraid I’m not so clear as to what you meant.

To make it clearer, I put the semicolon inside the brackets there to act as the separator of the sql queries, not exactly as a part of the groovy code. I might be doing it incorrectly though, please tell me if that is not how to do the separator.

Thanks!

Hi, please don’t mind my previous comment as I’ve figured out what you meant and have successfully executed the script now.

For those who wish to know further, what I did was merging the “return” and “delete from…” into 1 line.
In other words, it becomes like this:

return "delete from TABLE1 where PERSISTENCEID = " + persistenceIdTbl1 + ";" +
"delete from TABLE2 where PERSISTENCEID IN (" + persistenceIdTbl2.join(",") + ");" +
"delete from TABLE3 where PERSISTENCEID = " + persistenceIdTbl3 + ";" +
"delete from TABLE4 where PERSISTENCEID IN (" + persistenceIdTbl4.join(",") + ");" +
"delete from TABLE5 where PERSISTENCEID = " + persistenceIdTbl5 + ";";

Thank you Pierrick for your answer!