Enhanced query converting boolean value to string

[AS5278]

Hi All,

We are trying to update the 'EXCLUSIVE_ACCESS' column of the table 'APPLICATIONTYPE' using enhanced query. We are facing the issue:

'Data truncation error: Data too long for column 'EXCLUSIVE_ACCESS'.

Below are my observations and understanding:

1). The EXCLUSIVE_ACCESS columns expects a value of type 'Boolean' i.e  1 bit value. So, in our query we should be passing either 1 or true.

We have observed that irrespective of the value that we provide i.e  '1' or 1 or true, in the final query that is being executed, Saviynt is automatically putting the value 1 inside quotes like: '1' and trying to update this value in the column: 'EXCLUSIVE_ACCESS'. This is causing the error as this column accepts only boolean - 1 bit value and not a String value.

When we use any of the below 4 queries, the final Update query that Saviynt is constructing is the same:

Query 1).  SELECT 1 AS APPLICATIONTYPE__EXCLUSIVE_ACCESS, ap.APPTYPEKEY as APPLICATIONTYPE__PRIMARYKEY FROM APPLICATIONTYPE ap where ap.APPTYPEKEY=15;

Query 2). SELECT  '1' AS APPLICATIONTYPE__EXCLUSIVE_ACCESS, ap.APPTYPEKEY as APPLICATIONTYPE__PRIMARYKEY FROM APPLICATIONTYPE ap where ap.APPTYPEKEY=15;

Query 3). SELECT true AS APPLICATIONTYPE__EXCLUSIVE_ACCESS, ap.APPTYPEKEY as APPLICATIONTYPE__PRIMARYKEY FROM APPLICATIONTYPE ap where ap.APPTYPEKEY=15;

Query 4). SELECT TRUE AS APPLICATIONTYPE__EXCLUSIVE_ACCESS, ap.APPTYPEKEY as APPLICATIONTYPE__PRIMARYKEY FROM APPLICATIONTYPE ap where ap.APPTYPEKEY=15;

But Saviynt is automatically converting all of the above 4 queries into this:

[UPDATE APPLICATIONTYPE SET   EXCLUSIVE_ACCESS ='1'  WHERE APPTYPEKEY ='15' , table:APPLICATIONTYPE, primarykey:15]

See below:

So, the problem is not with the value we are passing the query, the problem is that Saviynt is automatically putting that value i.e 1 inside quotes like this: '1' and trying to update this. This of course will throw error as the column 'EXCLUSIVE_ACCESS' cannot have string value i.e '1', it only accepts boolean 1 bit value.

Am i understanding this correctly or am I writing the query wrong?. Please advice.

Thanks,

Atul Singh

[CR]

yes it is expected behavior only @AS5278 

[CR]

am not find any error in our system when we update above queries, may it is version problem.

[AS5278]

@CR Yes, it seems to be a version issue. I tried the same query in Saviynt Test environment where we are on a newer version v23.11 as compared to v23.8 in Test.

I could see that the final 'Update' query that is being constructed by Saviynt from the 'Select' query specified in the enhanced query job is different in Test and Prod.

In Saviynt Prod(v23.8) the final update query is: 

UPDATE APPLICATIONTYPE SET  EXCLUSIVE_ACCESS = '1'  WHERE APPTYPEKEY ='15';

Whereas, in Saviynt Test(v23.11), the final query is:

UPDATE APPLICATIONTYPE SET   EXCLUSIVE_ACCESS = 1  WHERE APPTYPEKEY ='15';

I think this is causing the issue...the value when enclosed withing quotes becomes a 'String'. But the column which we are trying to update accepts only boolean value. (boolean value 1 is a 1-bit value whereas '1' in String form has 16 bits.)

Datatype of column 'EXCLUSIVE_ACCESS' is 'Bit(1)'. Hence, it cannot accept a String value.

[rushikeshvartak]

Out of curiosity 

Whats the purpose of table / configuration?

[AS5278]

@rushikeshvartak 

This is related to the privileged access requests. We are trying to enable the slot selection for Privilege Access Requests and Application launcher as "SQLDeveloper". We were asked by the Saviynt team to update the value of the column 'EXCLUSIVE_ACCESS' to 1 for the 'DB' apptype. At present value is false for this column.

[rushikeshvartak]

Thanks

Below query worked in v24.2

UPDATE APPLICATIONTYPE SET   EXCLUSIVE_ACCESS = 1  WHERE APPTYPEKEY ='15';