12-17-2018 06:35 AM
I am working on Desktop workflows.
I have AES_256 asymmetric encrypted data in the table.
I have to use asymmetric key along with a select query to decrypt and encrypt the data in the table.
As M App enterprise workflow form field is mapped to each column in the table, how is it possible to run the decryption query to display the data?
Solved! Go to Solution.
12-17-2018 07:32 AM
in a similar scenario I would suggest you show a formfield to the user which is not the one from the DB (that one would be hidden). This dependent formfield will be set persisted="false" and you will calculate the defaultvalue using the private key and the selection.
Similarly to store it in the DB you will show the decrypted field (persisted="false") and you will use the defaultvalue of the original formfield to make the selection using the asymmetric key (if the content is already stored in the database in this case you will have to use the property override="true" to store the new value over the existing one).
12-18-2018 03:04 AM
Thank you very much Stefano.
Is there any format for writing sql in Default value - SQL Tab.
I have two lines of SQL query that is needed for decryption and M App enterprise giving messages like 'The default value attribute is invalid.
The value 'Query' is not valid according to any of the Member Types union.'
I tried adding semicolon to separate, but no luck with that.
What is going wrong here?
Is there any format to add multiple sql queries.
Query is something like below:
OPEN SYMMETRIC KEY skey
DECRYPTION BY ASYMMETRIC KEY akey WITH PASSWORD = 'Password'
SELECT CONVERT(VARCHAR,DECRYPTBYKEY(ColumnName)) FROM TableName
12-18-2018 05:53 AM
that's not a simple SQL query, looks like a script/PLSQL. You may want to create a DB function/procedure to incorporate all of the needed steps, defaultvalue supports only a single atomic SQL query.
12-18-2018 06:21 AM
Thank you Stefano
If I have a stored procedure sp_GetName which accepts the primary key of the record (recordno), how it should be stated in default value?
For instance I have tried with below statements which all give exceptitons
What would be the correct way to write procedure inside Default value-SQL
12-18-2018 06:49 AM
a stored procedure cannot be used in inline SQL. The exec is used for Transact-SQL, which should not be the purpose of a defaultvalue (it could be used in triggers with some limitations, in this case you may need to define a custom trigger based on the database type).
In your case I would suggest the usage of a function instead.
12-18-2018 07:51 AM
I am using a stored proc that will encrypt all the field and insert into the table.(Create workflow)
I intend to use this proc in a trigger and to call that trigger on submitting(Inside action).
All fields are made non persistant.
My problem is , it is not recognizing the stored procedure in the trigger.(I am using the SQL tab instead of static value for sp)
I have to pass all the fields in the forms to the stored procedure.
Is this something impossible in the trigger ?
I am able to pull a data to field using stored procedure in the default value.
12-18-2018 09:38 AM
Thank you very much for your suggestions.
I am able to insert a record using stored procedure.
I used a format that goes like this in the static value in the trigger and it worked.
SPtored procedure name
Name:field in the form