Hi, I have created a process model which calls a WebService using Cal

vbhardwaj

Certified Senior Developer

over 8 years ago

Hi,

I have created a process model which calls a WebService using Call Webservice Smart Service to fetch the records and then inserts/updates the records in the mySQL database using Write to data store Smart Service. The Webservice returns 1192 records and 115 distinct records are inserted into the database, which is perfectly fine as one of the field in Webservice output schema is a PK in the table. Hence it would insert only distinct values into the table. But the problem is "Stored Values" on output tab of Write to datastore has 1192 records, it should have 115 records which were inserted into the database.
Please suggest, how will I get to know how many records were inserted into the database.

Thanks in advance,
Vikrant.

Discussion posts and replies are publicly visible

0 GouthamKrishna over 8 years ago

Hello Vikrant, ac!StoredValues seems to be returning one record multiple times. This might be because the "Write to DS entity" node tries to update a DB record multiple times each time when it recognizes a record with same PK is inputted, AND this node will return a record multiple times as the number of times it was altered(+1 of course for the newly added row) through ac!StoredValues o/p, but by yielding only latest values for a record and for a given PK. So I guess you can use a script task after this node and use fn!union(pv!StoredValues, pv!StoredValues) code to filter out any duplicate records.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Shyam Bommakanti
Certified Lead Developer
over 8 years ago

Extending Goutham's point. Write to DS entity doesn't filter for unique. Think of it as a plan insert/update statements, if you have duplicate insert/update statements they all get executed. And the output Stored Values will have the data that it inserted/updated.

Hope this clarifies.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Chris over 8 years ago

Not sure if this is the most elegant solution - but you could use a query rule to compare the current DB PKs to CDT PKs prior to the Write to DS node. The difference(rule!CDT.PKs,rule!QR_Pks) function would give you the new PKs - which you could use to identify the new rows. Or if the web service returns nulls for the new PKs, just check where in the CDT null values are located prior to the write.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Shyam Bommakanti
Certified Lead Developer
over 8 years ago

I wouldn't recommend querying for all PKs from DB, there can be potentially unlimited records.

Better approach is to query the DB with CDT PKs and then do the difference.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Jorge Sanchez
Appian Employee
over 8 years ago

Another option to to simply query for the data from the DB once the insert has completed.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Shyam Bommakanti
Certified Lead Developer
over 8 years ago

@Jorge Please clarify your solution? My confusion is we need before and after. Once inserted DB becomes "after".
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel