Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Can you use sessionMethodsSoap Write and/or WriteCollection to delete table rows using a field other than the primary key?

Avatar

Level 2

I have a large custom table that I want to remove targeted rows from on a regular basis, in order to keep the row count at a reasonable number for performance reasons.

The primary key (PK) is on an integer field, but I was to delete based on a date field.  However, when I try to use the Write API to remove based on the date, it returns with an error after 300 seconds.  Consistently.  And the rows are not removed.  When I try to use the WriteCollection API to remove rows based on dates, it returns almost immediately with no error, but the deletes never happen.

Here is a sample of the Write:

<Write xmlns="urn:xtk:session">
<sessiontoken>... my token ...</sessiontoken>
<domDoc>
<myTable xtkschema="mySchema:myTable" _key="@effDate" _operation="delete" 
effDate="2016-01-31" />
</domDoc></Write>

And of the WriteCollection:

<urn:WriteCollection>
         <urn:sessiontoken>... my token...</urn:sessiontoken>
         <urn:domDoc>
<myTable-collection xtkschema="mySchema:myTable">
  <myTable_operation="delete" _key="@effDate" effDate="2015-10-31" />
  <myTable _operation="delete" _key="@effDate" effDate="2015-11-30" />
  <myTable_operation="delete" _key="@effDate" effDate="2016-01-31" />
</myTable-collection>
         </urn:domDoc>
      </urn:WriteCollection>

 

Similar Write calls and WriteCollection calls do work if I specify the PK as the key - but this is very painful as there are millions of records in the table.

I've tried this with and without an index on effDate - it doesn't change the behavior in either case.

This leads me to wonder the following:

* Can you only delete records based on the primary key field?

* Can you adjust the timeout (apparently set to 300 seconds) in order to give a long running delete a chance to finish?

Thanks for any feedback!

0 Replies

Avatar

Level 2

I found an interesting possible alternative in the JS API documentation:

path: jsapi-en->JavaScript->Objects, methods and properties->database->DBEngine->batchDelete.

The remarks there seem to imply that you can do a PK based delete with a filter on a non-PK field.

Can I call this through SOAP? Where can I get the WSDL for this API?

Avatar

Level 2

I found an interesting possible alternative in the JS API documentation:

path: jsapi-en->JavaScript->Objects, methods and properties->database->DBEngine->batchDelete.

The remarks there seem to imply that you can do a PK based delete with a filter on a non-PK field.

Can I call this through SOAP? Where can I get the WSDL for this API?

Avatar

Level 9

Hi Blair,

You can use other than primary key but chances are there more record might get deleted.  BTB right area for this query is http://help-forums.adobe.com/content/adobeforums/en/campaign-forum/adobe-campaign.html

Thanks,