How to replay certain rows from a SQL source using a SQL adapter (EnsLib.SQL.InboundAdapter)

Hello Community,

When using an SQL type Business Service, it may happen that we need to replay certain rows from the source table.

Let's take as an example the Business Service "from customer SQL" using the generic class EnsLib.SQL.Service.GenericService

 

Different cases arise, depending on the settings used on this Business Service.

First case :

If the Business Service only uses a KeyFieldName

In this case, to reprocess IDs 123, 456 and 789, it will be enough to delete them from the global ^Ens.AppData for the Business Service concerned on the "adaptor.sqlrow" reference:

kill ^Ens.AppData("from customer SQL","adapter.sqlrow",123)
kill ^Ens.AppData("from customer SQL","adapter.sqlrow",456)
kill ^Ens.AppData("from customer SQL","adapter.sqlrow",789)

Another option: if you do not have access to the terminal or if you want to avoid handling system globals of IRIS interoperability, it is also possible to duplicate the Business Service temporarily by indicating in the request the rows that you wish to reprocess

Example :

With the query :

Looking at the content of the global ^Ens.AppData, we confirm that the rows concerned have been reprocessed under the name of our new Business Service "from customer SQL - temporary" :  

NB: if you delete the temporary Business Service after reprocessing the desired rows, remember that the global ^Ens.AppData will not be deleted. Which means that in the event of recreation of the Business Service under the same name, with rows already processed by this temporary Business Service, these will not be taken into account. The solution will therefore be either to delete the data concerned directly on ^Ens.AppData, or to ensure that the temporary Business Service name is always unique. Example below with a copy of "from customer SQL" to "from customer SQL - restatement of January 8 2024"

And the result in ^Ens.AppData :

 

Second case:

If the Business Service is configured with a deletion request (DeleteQuery) like :

UPDATE app.customer SET processed=1 WHERE ID=?

And a query like:

In this case, to reprocess the client corresponding to ID 123, it will be enough to execute an update on the source like:

UPDATE app.customer SET processed=0 WHERE ID = 123

And to reprocess IDs 123, 456 and 789, a request like

UPDATE app.customer SET processed=0 WHERE ID in (123, 456, 789)

NB: this case assumes that you have access to the SQL source with a user account authorized to update the table concerned, which is not always guaranteed.

Other option:  as in the first case, you always have the possibility to create a new temporary Business Service by copying the initial Service and adapting it to the reprocessing needs. (see Anoher option of the first case)

 

Third case:

If the Business Service uses the parameter %LastKey, it will not be possible to indicate separately the different rows to be reprocessed as in the previous case, but only the IDs greater than the value of %LastKey.

With a query like :

In this case, simply update the global ^Ens.AppData by indicating the minimum value of %LastKey for the relevant Business Service of the "adaptor.sqlparam" reference:

set ^Ens.AppData("from customer SQL","adapter.sqlparam","%LastKey")=120

Other option: again, as in the first case, you always have the possibility of creating a new temporary Business Service by copying the initial Service and adapting it to the reprocessing needs. (see Another option of the first case)