Database Table Connector appending extra spaces while creating record on target

Troubleshooting
,
1

Hello Experts,

I am trying to use Database table connector to insert/update records on a table in MS SQL Server database. I am able to successfully insert records on the target but it appending extra spaces on the target fields causing extra spaces in the database columns after insert.

Any suggestion to fix this?

Here is the sync payload for reference. You can look any field on target. For example - ATNPhone, NAME etc…

{
    "payload": {
        "_id": "60f8a053-6ee0-4ea7-b897-dfd3d209e90c-2245525",
        "action": "CREATE",
        "eventName": "sync",
        "exception": null,
        "level": "INFO",
        "linkQualifier": "default",
        "mapping": "managedAlpha_user_systemMetadirectory__account__",
        "message": null,
        "situation": "ABSENT",
        "source": "audit",
        "sourceObject": {
            "_id": "47ff4cdd-b612-4e6f-b50a-94caf74244ef",
            "_rev": "b0ea7e02-0055-489f-bd70-9456cec5bdca-235999",
            "accountStatus": "active",
            "aliasList": [],
            "assignments": [],
            "city": "Klundert",
            "cn": "Grame Anusha",
            "consentedMappings": [],
            "country": "US",
            "custom_contractorCompany": "EVT Corp",
            "custom_contractorEmail": "grame.anusha@evtcorp.com",
            "custom_costCenter": "000100976",
            "custom_department": "Developer",
            "custom_encryptedPassword": null,
            "custom_initialPassword": "Ou==O;;8OCG;=;O",
            "custom_jobTitle": "INDIVIDUAL CONTRIBUTOR USIT16",
            "custom_middleInitials": "k",
            "custom_preferredName": "Grame",
            "custom_terminationDate": "2025-01-30",
            "description": "new hire",
            "displayName": null,
            "effectiveApplications": [
                {
                    "_id": "1e726d6e-93e9-4784-a6e3-25134c1e881e",
                    "_ref": "managed/alpha_application/1e726d6e-93e9-4784-a6e3-25134c1e881e",
                    "_refResourceCollection": "managed/alpha_application",
                    "_refResourceId": "1e726d6e-93e9-4784-a6e3-25134c1e881e",
                    "_rev": "ce44ba8c-0a24-4138-948f-82b69c41a260-196323",
                    "name": "IBMTivoliLDAP"
                },
                {
                    "_id": "8de1bdb9-2470-4442-b400-1f4774f85b1a",
                    "_ref": "managed/alpha_application/8de1bdb9-2470-4442-b400-1f4774f85b1a",
                    "_refResourceCollection": "managed/alpha_application",
                    "_refResourceId": "8de1bdb9-2470-4442-b400-1f4774f85b1a",
                    "_rev": "8a22f14e-575e-46c7-8cc7-bcdf4b9dfb68-15142",
                    "name": "MetaDirectory"
                }
            ],
            "effectiveAssignments": [
                {
                    "_id": "2492d52f-b53d-4cda-aaef-8e451647ab64",
                    "_ref": "managed/alpha_assignment/2492d52f-b53d-4cda-aaef-8e451647ab64",
                    "_refResourceCollection": "managed/alpha_assignment",
                    "_refResourceId": "2492d52f-b53d-4cda-aaef-8e451647ab64",
                    "_rev": "b0ea7e02-0055-489f-bd70-9456cec5bdca-235995",
                    "attributes": [
                        {
                            "assignmentOperation": "mergeWithTarget",
                            "name": "__NAME__",
                            "unassignmentOperation": "removeFromTarget",
                            "value": "C200141"
                        }
                    ],
                    "condition": null,
                    "description": "role-54851889-add3-4c55-87dc-14a086f094a7-overrideAssignment",
                    "mapping": "managedAlpha_user_systemMetadirectory__account__",
                    "name": "role-54851889-add3-4c55-87dc-14a086f094a7-overrideAssignment",
                    "type": "__OVERRIDE__"
                }
            ],
            "effectiveGroups": [],
            "effectiveRoles": [
                {
                    "_ref": "managed/alpha_role/ce5a02a9-6654-4664-9f83-ca86741a43c7",
                    "_refResourceCollection": "managed/alpha_role",
                    "_refResourceId": "ce5a02a9-6654-4664-9f83-ca86741a43c7"
                },
                {
                    "_ref": "managed/alpha_role/9b839ac1-0ac6-4b15-a3db-24c863c94f67",
                    "_refResourceCollection": "managed/alpha_role",
                    "_refResourceId": "9b839ac1-0ac6-4b15-a3db-24c863c94f67"
                },
                {
                    "_ref": "managed/alpha_role/54851889-add3-4c55-87dc-14a086f094a7",
                    "_refResourceCollection": "managed/alpha_role",
                    "_refResourceId": "54851889-add3-4c55-87dc-14a086f094a7"
                }
            ],
            "frIndexedDate1": "2024-09-27T04:53:58Z",
            "frIndexedDate2": null,
            "frIndexedDate3": "2024-09-27T04:53:58Z",
            "frIndexedDate4": null,
            "frIndexedDate5": null,
            "frIndexedInteger1": null,
            "frIndexedInteger2": null,
            "frIndexedInteger3": null,
            "frIndexedInteger4": null,
            "frIndexedInteger5": null,
            "frIndexedMultivalued1": [],
            "frIndexedMultivalued2": [],
            "frIndexedMultivalued3": [],
            "frIndexedMultivalued4": [],
            "frIndexedMultivalued5": [],
            "frIndexedString1": null,
            "frIndexedString2": "contractor",
            "frIndexedString3": null,
            "frIndexedString4": null,
            "frIndexedString5": null,
            "frUnindexedDate1": null,
            "frUnindexedDate2": null,
            "frUnindexedDate3": null,
            "frUnindexedDate4": null,
            "frUnindexedDate5": null,
            "frUnindexedInteger1": null,
            "frUnindexedInteger2": null,
            "frUnindexedInteger3": null,
            "frUnindexedInteger4": null,
            "frUnindexedInteger5": null,
            "frUnindexedMultivalued1": [],
            "frUnindexedMultivalued2": [],
            "frUnindexedMultivalued3": [],
            "frUnindexedMultivalued4": [],
            "frUnindexedMultivalued5": [],
            "frUnindexedString1": "2024-09-26",
            "frUnindexedString2": null,
            "frUnindexedString3": null,
            "frUnindexedString4": null,
            "frUnindexedString5": null,
            "givenName": "Grame",
            "kbaInfo": [],
            "lastSync": {
                "managedAlpha_user_systemIbmtivolildapUser": {
                    "effectiveAssignments": [],
                    "timestamp": "2024-09-27T05:39:54.490808623"
                }
            },
            "mail": "grame.anusha@valvolineglobal.com",
            "memberOfOrgIDs": [],
            "password": "{PBKDF2-HMAC-SHA256}1000:UNxaUGqaoyhjEtu3fD+WlxGHDrcrNhk5l6/YQnAYijF2bbYbZOUWuMqxbfHFZIee",
            "postalAddress": "1235 High Street",
            "postalCode": "11000",
            "preferences": null,
            "profileImage": null,
            "sn": "Anusha",
            "stateProvince": "CA",
            "telephoneNumber": "765 234 5678",
            "userName": "C200140"
        },
        "sourceObjectId": "managed/alpha_user/47ff4cdd-b612-4e6f-b50a-94caf74244ef",
        "status": "SUCCESS",
        "targetObject": {
            "ATNPhone": "765 234 5678                    ",
            "ContractorCompany": "EVT Corp                                                                                                                        ",
            "CostCenter": "100976    ",
            "CountryAbrv": "US ",
            "DirectReport": "A312152 ",
            "Email": "grame.anusha@valvolineglobal.com                                ",
            "FirstName": "Grame                              ",
            "LastName": "Anusha                                       ",
            "LogonType": "Contractor               ",
            "WorkCity": "Klundert                           ",
            "WorkState": "CA",
            "__NAME__": "C200141                                                                                                                         ",
            "_id": "C200141                                                                                                                         "
        },
        "targetObjectId": "C200141                                                                                                                         ",
        "timestamp": "2024-09-29T08:10:13.779Z",
        "topic": "sync",
        "transactionId": "bf9cefcb-28e4-4f9d-a84e-6df9577114be/0",
        "userId": "0bb6e401-358f-41b3-b87d-13d1950c1497"
    },
    "timestamp": "2024-09-29T08:10:13.779690243Z",
    "type": "application/json",
    "source": "idm-sync"
}
2

Was this solved? I’ll bite…
Is this an affect of using the “All Native” configuration in the Database Table adapter?
Is this the affect of using the “mandatory field lengths” in a scripted sql adapter?
Or, have field lengths been set on the IdM managed object, or sync mapping, itself?

Apologies for the questions, but I just don’t have enough to go on with the short set of data provided.

Cheers!

3

Thanks for your response @grpensa. Unfortunately, we still have the problem.

We are using database table connector with “All Native” configuration unchecked. We don’t have any field length set in the IDM Managed object or sync mapping.

Although I found in the MS SQL database table, columns are defined as char() datatype and depending on the length of char field it is adding trailing space if value is less than total length. For example, FirstName column is defined as char(16) and if value inserted is 10 character length, then it appends 6 trailing spaces.

4

In this case and without additional evidence, all fingers point to your jdbc driver as the culprit as this is not standard behavior of IdM.

So back to my original post, is there is no conflicting setting in the provisioner file, or is there a conflicting setting in the jdbc driver?

Therefore, I’d be raising an SR with PingIdentity as some root cause analysis with stack dumps and logs is required.

5

The field type of CHAR is your issue - CHAR is a fixed length data type, meaning any data stored in that field must have the number of characters allocated to the field. Great for cases where the data is of a fixed-length, not as great if you need variable lengths. In this case, the either the connector or driver is aware of the need to fill the available space and does so with blank characters.

2 Likes
6

I can’t believe I completely missed that. As I always use varchar. Thanks @mwtech !

1 Like
7

I’m typically the same way with data types. CHAR can get you some performance improvements over VARCHAR, but the rigidness of the datatype often leads to rework.