New post
Question
· Jan 30, 2023

SQLComputed Code issue

#SQL #Caché

Hi All,

I have a below query for which I need help.

I have a persistent class User.Cars.cls. The properties of this class is mapped through Global mapping with global (^CAR(Date,CarSerialno,Seq)).

Property CarNumber as %String (TRUNCATE=1) [SqlFieldName= CAR_Number];

Mapping of 5th Piece of ^CAR global to CarNumber property.

For e.g.^CAR(Date,CarSerialno,Seq)=1^2^3^4^BMW 4567^6^7

To increase the security we have encrypted the database. 5th Piece is also encrypted in global (New value after encryption: 'ASAD=4367' through some tool technique).

Now I want to decrypt this value in below section of property CarNumber (SqlComputed code I need to use). But when I put the below code, I am not getting the  any value. Does this not work when we use a SQL storage mapping ? or what can be issue. I also tried "Transient" keyword as well but still not working

Property CarNumber as %String (TRUNCATE=1) [ SqlComputeCode= {

Set CARNUM={CAR_Number}

If Clear Set {CAR_Number}=$$Decrypt(CARNUM)

Else  Set {CAR_Number}=CARNUM

},SqlComputed,SqlFieldName= CAR_Number];

Product version: Caché 2014.1
Discussion (1)1
Log in or sign up to continue
Vitaliy Serdtsev · Jan 31, 2023

there are a few comments:

  • you can write shorter:
    Property CarNumber As %String(TRUNCATE = 1) [ SqlComputeCode = {s {*}=$s(Clear:$$Decrypt({CAR_Number}),1:{CAR_Number})}, SqlComputed, SqlFieldName = CAR_Number ];
  • you can make a separate calculated field for the decrypted value, for example:
    Property CarNumber As %String(TRUNCATE = 1) [ SqlFieldName = CAR_Number ];

Property CarNumberDecr As %String(TRUNCATE = 1) [ Calculated, SqlComputeCode = {s {*}=$s(Clear:$$Decrypt({CAR_Number}),1:{CAR_Number})}, SqlComputed, SqlFieldName = CAR_NumberDecr ];
  • where do you get the value of the Clear variable (see above)?
  • you can create your own data type for encrypted strings and override the corresponding methods there: How Data Type Classes Work

    In this case, you will not have to clutter up your queries with unnecessary encryption/decryption operations.

1 0