Aurelius and computed columns

BIZ TMS Aurelius
1

I have an encrypted column in my mssql database. It can be decrypted using a mssql DECRYPTBYKEY function but symmetric key needs to be open previously within same connection using ‘open symmetric key ...’ statement. I created a computed column in my table and use the DECRYPTBYKEY in the formula to decrypt my encrypted field. It works ok in vcl application when I can run first the ‘open key ‘ statement and then run my select statement which returns the decrypted data in my computed column. But I cannot make it work with Aurelius. I cannot find the right place in my xdata server where I can run the ‘open key’ statement before Aurelius returns me the data. I tried different events with no luck. It must be within same connection.
No issues with regular computed columns. Can you advice please.

2

Hi. The way I go when need so set/execute some connection stuff is to define/inhrrit a custom XdataConnrctionPool and override de GetConnection function.

Regards

3

Thank you. I will try.

4

I did it differently but it did not help. I tried OnSQLExecuting event of TAureliusModelEvents. I am running the 'open symmetrickey' statement using my firedac connection. It looks like it is working in terms of opening the key and making the decrypted data availabel for the current connection because I am checking it after by running a select statements
1.the status of my SYMMETRIC KEY is 1 ('open').
2.the encrypted data from the first record is available.

But why the it is not coming to my xdatawebdataset in my client application ?
Maybe something is wrong with the declarations in my Aurelius schema.
I tried the default declaration of that fields:

[Column('FIRSTNAME_DCR', [TColumnProp.NoInsert, TColumnProp.NoUpdate])]
FFIRSTNAME_DCR: Nullable;
[Column('LASTNAME_DCR', [TColumnProp.NoInsert, TColumnProp.NoUpdate])]
FLASTNAME_DCR: Nullable;

then i changed it to

[Column('FIRSTNAME_DCR', [], 128)]
FFIRSTNAME_DCR: Nullable<string>;
[Column('LASTNAME_DCR', [], 128)]
FLASTNAME_DCR: Nullable<string>;

The result is the same. data is empty.

5

You can simply use the events of the TFDConnection like AfterConnect to execute the SQL statements for the specific connection.

6

Yes. That was what i tried first. But i am not getting any data. Is there something to do with the schema aurelius generates? Why am i getting data running the select statement right after my 'open key' command in the after connect event in xdataserver but not getting it to my client application?

7

I don't know, to be honest. I'm not sure about your application structure, and I never used such DECRYPTBYKEY mechanism.
What I can say is that the main difference between a traditional client-server Aurelius application and XData one, is that the latter uses a connection pool, thus you have multiple database connections open.
When a new connection is needed, XData creates a new one, so there are multiple TFDConnection instances created and connected. But if you use AfterConnect, you should have all of them set up.

8

Thank you.
But what about the schema aurelius generates. At first it creates the definition without mentioning the size of the field:
[Column('FIRSTNAME_DCR', [TColumnProp.NoInsert, TColumnProp.NoUpdate])]
FFIRSTNAME_DCR: Nullable;

[Column('LASTNAME_DCR', [TColumnProp.NoInsert, TColumnProp.NoUpdate])]
FLASTNAME_DCR: Nullable<Variant>;

Is this how it should be?

I thought that because of this definition i was not getting the data.
and I changed it to:
[Column('FIRSTNAME_DCR', [], 128)]
FFIRSTNAME_DCR: Nullable;
[Column('LASTNAME_DCR', [], 128)]
FLASTNAME_DCR: Nullable;

9

Variant type is not supported at all. You should change it to a type supported by Aurelius.
If the field is a string field, Aurelius will take into account the value you put in the Column attribute. For most types, it's ignored (like integers, dates, boolean, etc.).