Database Insert using Write to Data Store Entity Smart Service

Hi,

Does your CDT is annotated with @Id @GeneratedValue annotations?
If yes, your CDT will look like below:
id(Primary key which is autogenerated)
department
type
currentStatus

As you mentioned pv!requestData.requestId - this field is not in your CDT. id and requestID fields are same ?

To insert new record in DB,
id[null]
department-pv!requestData.department
type-pv!requestData.type
currentStatus-pv!requestData.currentStatus

To update the record:
id-pv!requestData.id[this is the primary key for request table]
department-pv!requestData.department
type-pv!requestData.type
currentStatus-pv!requestData.currentStatus

Hi,
Have you tried using a!writeToDataStoreEntity() expression function which will easily be done at interface level.

Thanks.

Hi , please check whether you have specified id as primary key . While updating you have to pass all the parameters specified in CDT. If you are not passing them it will make it null. While updating you have to pass primary key. Where as on insert you need not to pass primary key

Write to datastore entity has a datastore entity input and u can create a CDT input which corresponds to the entity. Make sure that you have added an annotation @Id in your CDT which defines a column as a primary key in the table while any CRUD operations are done in Appian through CDT else Appian creates an identifier or primary key in your table which you would be able to check in DB but would not be able to retreive that in Appian and hence would not be able to either delete or update that record.

Hi Susane,

To specify Primary Key of the CDT:

Open "my CDT" you have created above, and in the fields, you can find columns like Name, Type, Length, Array, Key  and some others. Click on the Key column for the field for which you want to set primary key. Say "id" in the "my CDT".  In the dialog box that opens up, click on "Primary Key" as Key Constraint and check the check box below to make it Auto generated. Save your changes.

But then it should not be a problem, if you are creating a CDT, through create CDT from Database Table or View option, for Appian pre-selects the field which is marked as primary key in the table to be the primary key in Appian as well. Which version of Appian are you using?

"also I cannot see the parameter where I can specify which column in the database I want to update, and lastly the new value."

Unlike Database, you need not mention which field needs to be updated. You just pass the entire cdt with new values and Appian does the update for you.

For Ex,

So for the first time you write to database with values  like below,

pv!myCDT.id:null,

pv!myCDT.department:Finance,

pv!myCDT.type:LoanRequest,

pv!myCDT.currentStatus: Requested

and in the corresponding DB table, it is stored as below.

Id   Department    Type              Current Status

1     Finance      LoanRequest       Requested

Now when you want to update, you send in the id number, (unlike null when you insert), so that Appian knows which rows to update in the DB. Also, unlike an update query in Database, you need to send all values for the update of a particular row, both changed and unchanged, otherwise other fields will be nullified.

pv!myCDT.id:1,

pv!myCDT.department:Finance,

pv!myCDT.type:LoanRequest,

pv!myCDT.currentStatus: InitiatorRework

And in the DB

Id   Department    Type       Current Status

1    Finance       LoanRequest       InitiatorRework

Sending only id and the field to be updated, results in loss of data as below

Id   Department    Type       Current Status

1    null          null       Initiator Rework

And what is the type of pv!requestData? The myCDT isn't having a requestId field?

Hi susana197 ,
As per my understanding by your requirement, you need to update the "currentStatus", by passing only "id" value.
try to use "Query Database" smart service or else execute stored procedure.
if you want to update by using Write to datastore Entity, then you must pass all the other values like "type" and "department".

Thanks.

Hi susana,

As per my understanding from your description you just want to know how to update a record using write to DB smart service, if that is the case, try below steps.
1. Before your write to DB smart service take one script task and in output of task map your new value (make sure your primary key field also have value, the one row id which you want to update) in your cdt .
2. next to script task take Write to DB smart service, under input tab, Map your cdt type in "Data Store Entity" attribute (Value field), you can give any name for Name field than click on "New Input", here in value field map your cdt (one which contain values to update and value for Primary key).

In Write to DB smart service , when you specify Id (primary key) it work as Update DB, if your P.K is null, it create new entry.

Regards,
Simple Shah