Back
  1. Home
  2. EnsLib.SQL.InboundAdapter not allowing to pull same rows when service is restarted

EnsLib.SQL.InboundAdapter not allowing to pull same rows when service is restarted

Ray Rizk's picture
Ray Rizk 25 May 2018
Beginner, Business Service, Ensemble

Has anyone used ClearRuntimeAppData,  I am using the EnsLib.SQL.InboundAdapter and pulls the SQL query fine, but if the service needs to keep pulling the same rows the next time the service is started but it will not, due to the parameter “key Field Name”

  • 0
  • 0
  • 178
  • 5
  • 2
Ray Rizk's picture
Ray Rizk 25 May 2018

Answers

Robert Cemper's picture
Answer by Robert Cemper 25 May 2018

according to your description I'd  rather assume that you look for this functionality, especially concerning %LastKey

classmethod ClearStaticAppData(pConfigName As %String)

Clear static data for a config item. This is normally used to store already-processed status for input files, and other persistent values related to adapters, such as the %LastId for the SQL Inbound Adapter.

or 

classmethod InitializeLastKeyValue(pConfigName As %String, pNewLastKey As %String = 0) as %String 

0   0
Accepted answer
Ray Rizk's picture
Comment by Ray Rizk 25 May 2018

Hi Robert,

I tried the following three different ways and they did not work.

Parameter ADAPTER = "EnsLib.SQL.InboundAdapter";

Method OnInit() As %Status
{
   Do ..Adapter.InitializePersistentValue(..%ConfigName,"%LastKey",0)
   Quit $$$OK
}


Method OnInit() As %Status
{
   Do ..Adapter.ClearStaticAppData(..%ConfigName)
   Quit $$$OK
}

Method OnInit() As %Status
{
   Do ..Adapter.ClearRuntimeAppData(..%ConfigName)
   Quit $$$OK
}

0 0
Robert Cemper's picture
Answer by Robert Cemper 25 May 2018

You are right!  This may work only in special situations.

I reproduced your case simplified.

SQL inbound adapter keeps a list of processed ids:
if your id is in there it is skipped. Could be there is some other trick but not as in docs. 

the list looks like this

ENSDEMO>zw ^Ens.AppData("SQLservice")
^Ens.AppData("SQLservice","adapter.sqlparam","%LastKey")=198
^Ens.AppData("SQLservice","adapter.sqlrow",196)=1
^Ens.AppData("SQLservice","adapter.sqlrow",197)=1
^Ens.AppData("SQLservice","adapter.sqlrow",198)=1
^Ens.AppData("SQLservice","adapter.sqlrow",199)=1
^Ens.AppData("SQLservice","adapter.sqlrow",200)=1
^Ens.AppData("SQLservice","adapter.sqlrow",201)=1
^Ens.AppData("SQLservice","adapter.sqlrow",202)=1
^Ens.AppData("SQLservice","adapter.sqlrow",203)=1

Manually removing it fixed my demo.

0   0

Comments

Eduard Lebedyuk's picture
Comment by Eduard Lebedyuk 25 May 2018

Do you want to pull all the rows each time?

If so,  just clear “key Field Name”  setting and BS would process all rows every time it's run.

0 0
Ray Rizk's picture
Comment by Ray Rizk 25 May 2018

I did try clearing the “key Field Name” , it does read all rows, but it reads them multiple times and you end up with duplicate data.

0 0
Ray Rizk's picture
Comment by Ray Rizk 25 May 2018

Hi Robert,

Resolved by putting a custom the OnInit() method at the top of the custom SQL.InboundAdapter service, it release all persistent values associated with the adapter at start of the service

Method OnInit() As %Status {        
Do ..Adapter.ClearAllAppData(..%ConfigName)    
Quit $$$OK 
}

0 0