Power sil script does not work

Question

Dear all,

I am trying to read a value from a table, in order to find out which user to apply as watcher.

If I run the sql statements directly in database, the results are correct.

Although, when I am trying to do it though jira sil it does nothing. 
I am sending you the script below and please let me know if you find something that I should change.

Below the script:

include "C:\Program Files\Atlassian\Application Data\JIRA\silprograms\postfunctions_functions.incl";

logPrint("DEBUG","stp_assign_to_maker.sil:Started");
string cif=cf_cif_value;
string account=cf_account_value;
string fcubsUsername;
string ROwatcher;
string watcher1;

if (cif=="") {
cif="000000";
}

if (account=="") {
account="0000000000000";
}

string selectSQL="SELECT a.RM_ID FROM ancoria.flx_sttm_customer a, ancoria.flx_sttm_cust_account b WHERE a.customer_no = b.cust_no and b.cust_ac_no = ?";
string[] relationShipOfficers = sql(silEnv("kposJDNI"), selectSQL, account);
string[] usernames;

//no relationship officer assigned to customer so assign to maker
if (size(relationShipOfficers) > 0) {
fcubsUsername=relationShipOfficers[0];
}
selectSQL="select user_name from ancoria.smn_users b where fcubs_user_name=?";
usernames = sql(silEnv("kposJDNI"), selectSQL, toUpper(fcubsUsername));

if (size(usernames) >0) {
watcher1=usernames[0];
ROwatcher=watcher1;
watchers = addElement(watchers, ROwatcher);
}

Gor Greyan · Answer

Hi @NICOLETTA HADJILAMBI

Sure, could you run the script with these log statements and share the output? The logs will help identify exactly where the execution stops and whether the issue is with the SQL query, the returned values, or the watcher assignment.

include "C:\Program Files\Atlassian\Application Data\JIRA\silprograms\postfunctions_functions.incl";

logPrint("DEBUG","stp_assign_to_maker.sil: Started");

string cif = cf_cif_value;
string account = cf_account_value;
string fcubsUsername;
string ROwatcher;
string watcher1;

logPrint("DEBUG", "CIF: " + cif);
logPrint("DEBUG", "Account: " + account);

if (cif == "") {
cif = "000000";
}

if (account == "") {
account = "0000000000000";
}

logPrint("DEBUG", "Account after validation: " + account);

string selectSQL = "SELECT a.RM_ID FROM ancoria.flx_sttm_customer a, ancoria.flx_sttm_cust_account b WHERE a.customer_no = b.cust_no and b.cust_ac_no = ?";

logPrint("DEBUG", "Executing SQL 1");
logPrint("DEBUG", "SQL: " + selectSQL);

string[] relationShipOfficers = sql(silEnv("kposJDNI"), selectSQL, account);

logPrint("DEBUG", "SQL 1 returned " + size(relationShipOfficers) + " row(s)");

if (size(relationShipOfficers) > 0) {
fcubsUsername = relationShipOfficers[0];
logPrint("DEBUG", "FCUBS Username: " + fcubsUsername);
} else {
logPrint("DEBUG", "No Relationship Officer found.");
}

selectSQL = "select user_name from ancoria.smn_users b where fcubs_user_name=?";

logPrint("DEBUG", "Executing SQL 2");
logPrint("DEBUG", "SQL: " + selectSQL);

usernames = sql(silEnv("kposJDNI"), selectSQL, toUpper(fcubsUsername));

logPrint("DEBUG", "SQL 2 returned " + size(usernames) + " row(s)");

if (size(usernames) > 0) {
watcher1 = usernames[0];
ROwatcher = watcher1;

logPrint("DEBUG", "Watcher to add: " + ROwatcher);

watchers = addElement(watchers, ROwatcher);

logPrint("DEBUG", "Watcher added successfully.");
} else {
logPrint("DEBUG", "No Jira username found.");
}

logPrint("DEBUG", "Script finished.");

NICOLETTA HADJILAMBI · Answer

Hi @Gor Greyan ,

Thank you for your answer.

I am not very familiar in sil script writing, so I would like to ask you help me on how to add log messages before and after the sql().

This will be very helpful for me.

Thanks

Gor Greyan · Answer

Hi @NICOLETTA HADJILAMBI

Welcome to the Atlassian Community!

If the same SQL statement works directly in the database but does nothing in SIL, I would first check whether the script is actually reaching the SQL part and what values are being passed into it.
I’d suggest adding log messages before and after sql() and also logging the values of cf, account, and selectSQL. Very often the query works manually, but in SIL one of the variables is empty or formatted differently. 
Also, please check what sql() returns in your case. If it returns an empty result, then the issue is likely with the values passed to the query. If it returns the expected username, then the next thing to check is whether addElement(watchers, ROwatcher) expects a username, user key, or account ID in your Jira version. 
Sharing the full execution log would help identify exactly where the script stops.