Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

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!

3 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,