How to use database connector's command separator

Q&A
1

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.

2

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

3

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!

4

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!