M.App Enterprise Discussions

Discuss topics with other M.App Enterprise Product pioneers and experts to get the most out of it.
Showing results for 
Search instead for 
Do you mean 
Reply
Highlighted
Frequent Contributor
Posts: 123
Registered: ‎07-25-2018
Accepted Solution

Decrypt and display each field in Workflow form

Hello Team,

 

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?

 

 

Regards,

Sreedevi

 

 

Highlighted
Staff
Posts: 1,065
Registered: ‎10-18-2015

Re: Decrypt and display each field in Workflow form

Hi Sreedevi,

 

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).

 

HTH,

Stefano 

Stefano Turcato
Presale Engineer
Hexagon Geospatial
Highlighted
Frequent Contributor
Posts: 123
Registered: ‎07-25-2018

Re: Decrypt and display each field in Workflow form

 

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

 

 

Regards,

Sreedevi

 

 

 

 

Highlighted
Staff
Posts: 1,065
Registered: ‎10-18-2015

Re: Decrypt and display each field in Workflow form

Hi Sreedevi,

 

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.

 

Stefano 

Stefano Turcato
Presale Engineer
Hexagon Geospatial
Highlighted
Frequent Contributor
Posts: 123
Registered: ‎07-25-2018

Re: Decrypt and display each field in Workflow form

 

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

 

  1. sp_GetName {Form.recordno}
  2. Exec ('sp_GetName ' '{Form.recordno'})

What would be the  correct way to write procedure inside Default value-SQL

 

 

Regards,

Sreedevi

Highlighted
Staff
Posts: 1,065
Registered: ‎10-18-2015

Re: Decrypt and display each field in Workflow form

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.

 

Stefano

Stefano Turcato
Presale Engineer
Hexagon Geospatial
Highlighted
Frequent Contributor
Posts: 123
Registered: ‎07-25-2018

Re: Decrypt and display each field in Workflow form

Hi Stefano,

 

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.

 

 

Regards,

Sreeedevi

 

 

 

Highlighted
Frequent Contributor
Posts: 123
Registered: ‎07-25-2018

Re: Decrypt and display each field in Workflow form

Hi Stefano,

 

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.

 

EXEC('SP''+ {FORM.Name}+'''')

 

SPSmiley Frustratedtored procedure name

Name:field in the form

 

 

Regards,

Sreedevi