REST ImportUserJSON: Data too long for column error when using #CONST#${ ... }.

[ChrisBellobuono]

As the subject states, we are unable to get #CONST#${ <javascript> } mapping values to work as it always results in the job failing with:

Error in Users Import - Error while processing data: Data truncation: Data too long for column 'CUSTOMPROPERTY1' at row 1

When we run those same scripts through Postman all the values returned are less than 25 characters in length.  The script in this particular case is 360 characters long which had me suspecting it is being mapped as-is and not actually being executed.  The full ImportUserJSON is attached.  

We also tried removing all but 3 of the #CONST# entries:

"EMPLOYEEID": "#CONST#${return response.userId.substr(2).padStart(6,'0')}~#~char",
"EMPLOYEETYPE": "#CONST#Associate~#~char",
"CUSTOMPROPERTY20": "#CONST#NA Success Factors~#~char",

Both of the static text ones worked but the simple script resulted the script text being written to the attribute:

So instead of executing the script it is treating anything after #CONST# as static text, confirming my earlier suspicion about the CUSTOMPROPERTY1 mapping.

I saw another post where REST pagination was causing problems with scripting, but in this case we are only returning 4 users but I also tried it without the pagination section of the configuration.

Any ideas of how we can get scripting to work?

[naveenss]

Hi @ChrisBellobuono  the error says about issue with a mapping for customproperty1. 

Error in Users Import - Error while processing data: Data truncation: Data too long for column 'CUSTOMPROPERTY1' at row 1

Can you please share the mapping for customproperty1 and make sure the data is less than 255 characters?

also, can you share the full JSON?

[ChrisBellobuono]

Whoops, I forgot to attach the file.  I have update the post to include it.

As I mentioned, the script in the CUSTOMPROPERTY1 mapping returns a result which is < 25 characters long when it is actually executed, but from what I am seeing with the EMPLOYEEID mapping, Saviynt is trying to save the script text and not the calculated value.

[ChrisBellobuono]

After doing more testing we found that Saviynt always treats the script as static text if it is followed by the ~#~char tag.  If we leave the tag off, a blank value is written to the field.  There is no indication in the logs if the script is being executed in either case.

We also tried this based on other posts we saw to take the JSON response out of the equation:

"EMPLOYEEID":"#CONST#${String strUID='V0103697'; strUID=strUID.substr(2); return strUID}",

But it also leaves the field blank.

[rushikeshvartak]

"employeeid": "#c#${return response.userId.substr(2).padStart(6,'0')}~#~char",

[ChrisBellobuono]

Thanks for the suggestion, but that did not work as the field was left blank.

What I did figure out is that the Scripting engine used by Saviynt does not support JavaScript's substr or padStart functions.  It does support substring.  As an alternative to padStart I used StringUtils.leftPad.  This is the new mapping:

"EMPLOYEEID": "#CONST#${return org.apache.commons.lang.StringUtils.leftPad(response.userId.substring(2), 6, '0')}~#~char",

What is weird is both substr and subtring were available in JavaScript since ECMAScript1 (1997).  On the other hand, padStart was added in ECMAScript7 (2016).  The Nashorn scripting engine has been part of java from JDK 8 to JDK14 and is ECMAScript5 compliant.  As of JDK15+ it was removed from the delivered JDK leaving developers to switch to either Nashorn open source or GraalVM JavaScript which support ECMAScript 6 and 13 respectively.  Granted, I am making a big assumption about what the Saviynt back end is built on.

Regardless, it is unfortunate that Saviynt chose to suppress the scripting engine errors from the logs and just records the DB error as that would have significantly sped up troubleshooting this since the messages would have been very clear as to where and what the issue was.

That all said, I now need to diagnose each script individually because even if I dumb down the one for CUSTOMPROPERTY1 to just its conditional (ternary C?T:F) operator it fails.  Again, if I had the actual errors from the scripting engine it would be easy to figure out but I am only left with trial and error.

[RobinAtIS4U]

Hi,

I've been following this thread looking for the same answers as OP.

We gave up on getting scripting to work in the colsToPropsMap and reverted to the User Import Preprocessor (https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter03-User-Management/User-Impo...). I think it's an elegant solution which doesn't clutter the colsToPropMap and I'm discovering all sorts of new things you can do with SQL every day (look in the MySQL docs for help, although not everything works, e.g. REGEXP_SUBSTR()).

If SQL can't fix our use case, we implement it with a Java class and refer to it in the CUSTOMFUNCTIONS. This has the advantage that we can test the Java class on sample import on our local machines and be fairly confident that it will work once we upload the JAR to Saviynt.

Not an answer to your question, but still hope this helps fellow travelers on their path.

[ChrisBellobuono]

The latest things I found are:

• Saviynt does not like the JavaScript var for variable declarations.  Instead, I had to use actual data types (Object, String, int, boolean, etc) when defining them.
• When comparing a numeric value from the response, it needs to be converted to a number first (e.g. Integer.parseInt(...)).

Given what I have found so far, I am beginning to question if the scripting language is actually JavaScript.

10 out of my 12 scripts are currently working.  The last two are almost identical, so when I solve one the other should start working as well.  I think it has something to do with the regular expression I am using.

I had thought about moving them to MODIFYUSERDATAJSON as well, but the scripts are unfortunately a little too complicated to implement as SQL.  Moving to CUSTOMFUNCTIONS was going to be our fallback if we started getting too close to our deadlines.

[ChrisBellobuono]

So my current hurdle appears to be with looping through a node array.  The following works and correctly lists the size as 2:

{

String returnPhoneType = 'C';

boolean includePhoneNumber = true;

boolean includeExt = false;

boolean includeIntCode = false;

List phones = response.employmentNav.personNav.phoneNav.results;

String retPhoneNumber = null;

retPhoneNumber = "123-456-7890: size " + phones.size();

return retPhoneNumber;

}

But when I try to introduce a loop around setting retPhoneNumber it fails.  I have tried all of the following:

for (Object phone: phones)

{

for (int i = 0; i < phones.size(); i++)

{

int i = 0;

int phonesLen = phones.size();

while (i < phonesLen) 

{

for (Iterator phonesIter = phones.iterator(); phonesIter.hasNext(); )

{

Iterator phonesIter = phones.iterator();

while (phonesIter.hasNext())

{

I am not sure how else I can write this to make it work.

[ChrisBellobuono]

So, it is not only loops that have a problem, but IF statements as well.  To test the rest of the script, I removed the loop and just set a variable to the first element in the array since all my test cases have at least one phone number.  When I added in the IF statements for how to build the phone number it failed.

Attached is the last version of the mappings that I am trying to get to work.  It is the two phone number fields which are not working.

[puneetkhullar]

Hi

I have an example where we implemented for SuccessFactor and it worked fine. Please review this JSON example which has "For loop and also IF else logic". You can write your logic accordingly.

"jobdescription": "#CONST#${List responseList = response?.empInfo?.jobInfoNav?.results;if(responseList.size() == 1){return responseList.get(0).jobCodeNav?.name;};else if(responseList.size() > 1){int refIndex = 0;for(int i=1; i<responseList.size(); i++){if(responseList.get(i).emplStatusNav?.externalCode=='A'){refIndex = i; break;};};return responseList.get(refIndex).jobCodeNav?.name;};else {return null;};}~#~char",

[ChrisBellobuono]

Thanks for the suggestion, but that looks similar to some of what I tried.  I did notice that you have semi-colons after each of your nested control blocks (e.g. if (...) { ... }; else { ... }; ).  Is that a syntax Saviynt is expecting?  I have never seen that before in Java or JavaScript.

Also, I believe this is not working the way you think:

List responseList = response?.empInfo?.jobInfoNav?.results;
if(responseList.size() == 1)
...

If any of response, empInfo, jobInfoNav, or results is null, responseList is going to be null.  That is what the elvis operator / conditional chaining (?.) brings to the table; you avoid having to null check each item in the chain individually.  If that happens, your if statement is going to fail with a NullPointerException / null reference.  You should have this as the first if instead of as the trailing else:

if (responseList == null)
return null;
else if (responseList.size() == 1)
...

[puneetkhullar]

@ChrisBellobuono Like I mentioned earlier, it is a working Project code implementation for SuccessFactor for one of our customer and we are not facing any issues with nulls there. As a good practice absolutely you can check that with your suggestion.

But Again coming back to real question asked  that "So, it is not only loops that have a problem, but IF statements as well"

Please develop your code according to the sample shared and see if worked. If not then you can Open Support ticket for Ops agent. 

[ChrisBellobuono]

We have a ticket opened with support about this and have been working with them for a few weeks on it.  All of our code works as expected in the jdoodle groovy tester.  Support is looking into why it works there and not in Saviynt.  Once we have a solution I will update this post with those details.