We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Data Truncation Error rather than Proper Value

RyanDove
New Contributor
New Contributor

I think I have run into a system bug, but want to confirm with the forums.  When I execute the following, I get the proper value parsed:

{
"accountParams": {
"processingType":"SequentialAndIterative",
"connection":"acctAuth",
"call": {
"call1": {
"callOrder": 0,
"stageNumber": 0,
"http": {
"url":"https://xxxxxx.xxx.xxxxxxxxxx.com/profile/spend/v4/Users/ee1e890",
"httpMethod": "GET",
"httpContentType":"application/json",
"httpHeaders": {
"accept":"application/json",
"Authorization":"${access_token}"
}
},
"listField":"",
"keyField":"accountID",
"colsToPropsMap": {
"accountID":"id~#~char",
"name":"id~#~char",
"customproperty3":"#CONST#${return response.id}~#~char"
}
}
}
}
}


But, when updating to get the customData value with id = orgunit1, I receive a data truncation error due to lack of string replacement:

Response with error:
{
"accountParams": {
"processingType":"SequentialAndIterative",
"connection":"acctAuth",
"call": {
"call1": {
"callOrder": 0,
"stageNumber": 0,
"http": {
"url":"https://xxxx.xxxxxxxxxxxxxxxxx.com/profile/spend/v4/Users/ee1e890",
"httpMethod": "GET",
"httpContentType":"application/json",
"httpHeaders": {
"accept":"application/json",
"Authorization":"${access_token}"
}
},
"listField":"",
"keyField":"accountID",
"colsToPropsMap": {
"accountID":"id~#~char",
"name":"id~#~char",
"customproperty3":"#CONST#${List responseList = response.urn:ietf:params:scim:schemas:extension:spend:2~dot#0:User:customData; int count = 0; int size = responseList.size(); Iterator iterator = responseList.iterator(); while (iterator.hasNext()){count++; Map dataMap = iterator.next(); if('orgunit1'.equals(dataMap.id)){return dataMap.value} else if(count == size){return 'NOT FOUND'}}}~#~char"
}
}
}
}
}

Error received:

2023-07-15/23:17:52.663 [{}] [quartzScheduler_Worker-4] DEBUG rest.RestProvisioningService - INFO ErEx: importAccountsFull:persistAccounts : Data truncation: Data too long for column 'CUSTOMPROPERTY3' at row 1
2023-07-15/23:17:52.663 [{}] [quartzScheduler_Worker-4] DEBUG rest.RestProvisioningService - failedBatchDetailsMap : [1:Update accounts set JOBID = 420517, status = '1', name = 'e468bc13-07a7-4dcf-8282-3392eee1e890', customproperty3 = '${List responseList = response.urn:ietf:params:scim:schemas:extension:spend:2~dot#0:User.customData; int count = 0; int size = responseList.size(); Iterator iterator = responseList.iterator(); while (iterator.hasNext()){count++; Map dataMap = iterator.next(); if(\'orgunit1\'.equals(dataMap.id)){return dataMap.value}else if(count == size){return null}}}' where ACCOUNTKEY = 1565670 and ENDPOINTKEY = 1134]


Data received from API call:
{
"schemas": [
"urn:ietf:params:scim:schemas:extension:spend:2.0:Role",
"urn:ietf:params:scim:schemas:extension:spend:2.0:WorkflowPreference",
"urn:ietf:params:scim:schemas:extension:spend:2.0:User",
"urn:ietf:params:scim:schemas:extension:enterprise:2.0:Payroll",
"urn:ietf:params:scim:schemas:extension:spend:2.0:UserPreference",
"urn:ietf:params:scim:schemas:extension:spend:2.0:Approver",
"urn:ietf:params:scim:schemas:extension:spend:2.0:Delegate",
"urn:ietf:params:scim:schemas:ScimResource"
],
"id": "e468bc13-07a7-4dcf-8282-3392eee1e890",
"meta": {
"resourceType": "User",
"created": null,
"lastModified": "2023-06-13T19:49:51.517Z",
"location": "",
"version": null
},
"urn:ietf:params:scim:schemas:extension:spend:2.0:User": {
"reimbursementCurrency": "USD",
"reimbursementType": null,
"ledgerCode": "SUN",
"country": "US",
"budgetCountryCode": null,
"stateProvince": "WI",
"locale": "en-US",
"cashAdvanceAccountCode": "",
"testEmployee": false,
"nonEmployee": false,
"biManager": null,
"customData": [
{
"id": "custom22",
"value": "",
"syncGuid": "03c0d782-4e1e-5840-b5a0-d806c3ecdd94",
"href": ""
},
{
"id": "custom21",
"value": "",
"syncGuid": "9e7286bf-749e-bf4e-b64a-187f70d84de9",
"href": ""
},
{
"id": "custom15",
"value": "N"
},
{
"id": "custom17",
"value": ""
},
{
"id": "custom16",
"value": "N"
},
{
"id": "custom3",
"value": "Y"
},
{
"id": "orgunit1",
"value": "IT",
"syncGuid": "2b18114c-f8b8-6c46-b0e5-ffa4ad1e5ed8",
"href": ""
},
{
"id": "orgunit2",
"value": "8300",
"syncGuid": "0272e15c-03c3-fb40-8f7f-77dfcc292a08",
"href": ""
},
{
"id": "custom1",
"value": "N"
},
{
"id": "custom2",
"value": "N"
}
]
}
}

To confirm, I'm also able to parse the proper email just fine:
"customproperty1":"#CONST#${List responseList = response.emails; int count = 0; int size = responseList.size(); Iterator iterator = responseList.iterator(); while (iterator.hasNext()){count++; Map dataMap = iterator.next(); if('work'.equals(dataMap.type)){return dataMap.value} else if(count == size){return null}}}~#~char"

The issue seems to be related to SCIM headers:  

urn:ietf:params:scim:schemas:extension:spend:2.0:User and the dot replacement ~dot#
 
[This post has been edited by a Moderator to merge two posts.]
9 REPLIES 9

armaanzahir
Valued Contributor
Valued Contributor

Hi @RyanDove ,

It seems that the data is too long for cp3 which is of varchar type.

Can you try mapping cp31 instead of cp3. 

cp31 of the accounts profile is of type longtext.

cp57-60 is also of mediumtext type.

armaanzahir_1-1689582382203.png

Database Schema Reference (saviyntcloud.com)

Thanks,

Armaan

 

Regards,
Md Armaan Zahir

Thanks for the idea!  Unfortunately the data truncation error is just a symptom of the actual problem. Saviynt is not properly replacing the text inside ${} and instead trying to write that entire formula into the database. I believe this is because of the 2.0 in the SCIM header. You can see the error above including the formula it is trying to instert. 

armaanzahir
Valued Contributor
Valued Contributor

Hi @RyanDove ,

Since this is an enclosed code string, can you try using the dot "." instead of ~dot#0 in your code. The error mostly seems to be related to the dot.

If that does not work, since it is a code, can you also try and use an escape character \ before the dot. 

Thanks,

Armaan

Regards,
Md Armaan Zahir

I again appreciate the ideas.  No luck!  None of the following work.  The period in the beginning after response is not an issue, as I have other enclosed code strings that are working fine.  Saviynt cannot seem to handle the path when SCIM headers are needed.

response.urn:ietf:params:scim:schemas:extension:spend:2.0
response.urn:ietf:params:scim:schemas:extension:spend:2~dot#0

response.urn:ietf:params:scim:schemas:extension:spend:2\\.0

flegare
Regular Contributor II
Regular Contributor II

Running into the exact same issue...  has anyone been able to identify a workaround

vivekmohanty_pm
Saviynt Employee
Saviynt Employee

@RyanDove @flegare 
Can you try enclosing the key within single quotes - 

List responseList = response.'urn:ietf:params:scim:schemas:extension:spend:2.0:User:customData';

or 

List responseList = response.'urn:ietf:params:scim:schemas:extension:spend:2~dot#0:User:customData';

 

Regards,
Vivek Mohanty

If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem

flegare
Regular Contributor II
Regular Contributor II

Hi @vivekmohanty_pm ,

Thanks for this...  however, I gave this a try and I am running into the same exception.

Do you know if SCIM integration is on an eventual roadmap?

Thanks again!

 

@flegare can you share the exception?

flegare
Regular Contributor II
Regular Contributor II

Of course.  Here is the faulty statement from ImportAccountEntJSON: 

"customproperty6":"#CONST#${List responseList = response.'urn:ietf:params:scim:schemas:extension:spend:2~dot#0:User:customData'; int count = 0; int size = responseList.size(); Iterator iterator = responseList.iterator(); while (iterator.hasNext()){count++; Map dataMap = iterator.next(); if('orgunit1'.equals(dataMap.id)){return dataMap.value} else if(count == size){return 'NOT FOUND'}}}~#~char"

And here is the exception returned at processing time

Exception in retryFailedBatch with retry# 1 : java.sql.BatchUpdateException: Data truncation: Data too long for column 'CUSTOMPROPERTY6' at row 1"