Persistent Value not being specified for Query

What happens if you don't declare a Persistent value when you call ExecuteQuery()? What does Ensemble set as the key value for your query? I have a query that I've executed on SQL Server, and I get 15 rows, but because this is my second time querying the data Ensemble thinks it exists. 

 

The Method in question is SelectProviderClarityAudit. If I call this query multiple times it is not returning the same number of results each time in Ensemble.

Class osuwmc.CPD.ClarityDBPoll Extends Ens.BusinessOperation [ ClassType = "", ProcedureBlock ]
{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Parameter INVOCATION = "Queue";

Property InitDSN As %String;

Method OnInit() As %Status
{
Set ..InitDSN = ..Adapter.DSN
Kill $$$EnsRuntimeAppData(..%ConfigName)
//Set ..Adapter.ConnectAttrs = "QueryTimeout:45" ; try this too just in case...
Quit $$$OK
}

Method SelectProviderClarityAudit(pInput As osuwmc.CPD.DataStructures.SelectClaritySERAuditRecords, Output pResponse As EnsLib.SQL.Snapshot) As %Status
{
set rs = ##class(%ListOfObjects).%New()
set pResponse = ##class(EnsLib.SQL.Snapshot).%New()
set pResponse.MaxRowsToGet = -1
set tSC = rs.Insert(pResponse)

set select = "SELECT DISTINCT LTRIM(RTRIM(HX.ID_INTERNAL)) as DoctorNumber,SUBSTRING(SER.PROV_NAME,0,CHARINDEX(',', SER.PROV_NAME)) AS LastName,"
set select = select_"SUBSTRING(SER.PROV_NAME,CHARINDEX(',', SER.PROV_NAME) + 1, LEN(SER.PROV_NAME)) AS FirstName,"
set select = select_"PROV_TYPE as ProviderType,LTRIM(RTRIM(REFSPEC.ABBR)) as SpecAbbr,LTRIM(RTRIM(REFSPEC.NAME)) as SpecDesc,REPLACE(SSN,'-','') as SSN,SEX as Gender,"
set select = select_"CASE WHEN BIRTH_DATE = '1900-01-01 00:00:00.000' then NULL ELSE BIRTH_Date END as DOB,NPI,"
set select = select_"EMAIL,HX.USER_ID as EditingUser,CAST(HX.Instant as date)as EditingDate,'' as ProcessedDate,REFERRAL_SOURCE_TYPE as ReferralSourceType FROM item_audit_hx AS HX "
set select = select_"left join item_audit_vals_hx AS VALS on HX.INI=VALS.INI and HX.ID_Internal=VALS.ID_Internal "
    set select = select_"and HX.Instant_Internal=VALS.Instant_Internal INNER JOIN CLARITY_SER as SER on LTRIM(RTRIM(HX.ID_INTERNAL))=SER.PROV_ID INNER JOIN "
    set select = select_"CLARITY_SER_2 as SER2 on SER.PROV_ID=SER2.PROV_ID LEFT OUTER JOIN "
set select = select_"CLARITY_SER_SPEC as SPEC on SER.PROV_ID = SPEC.PROV_ID INNER JOIN "
set select = select_"ZC_SPECIALTY as REFSPEC on SPEC.SPECIALTY_C = REFSPEC.SPECIALTY_C and SPEC.LINE = 1 "
set select = select_"WHERE HX.ini in('SER')and HX.USER_ID <> '209' and (HX.ITEM like '21%' OR HX.ITEM = '.2') AND NPI IS NOT NULL "
set select = select_"AND CAST(HX.INSTANT as Date) > CAST(getdate()-3 as Date) AND CAST(HX.INSTANT as Date) > CAST(getdate()-1 as Date) "

$$$LOGINFO(select)
set tSC = ..Adapter.ExecuteQuery(.rs,select)

//$$$LOGINFO(tSC)

  set tSC = rs.GetSnapshot(.pResponse)
  quit tSC
}

Method SelectClarityActiveAddresses(pInput As osuwmc.CPD.DataStructures.SelectClarityActiveAddresses, pResponse As EnsLib.SQL.Snapshot) As %Status
{
set select = "SELECT [PROV_ID] as DoctorNumber,[ADDR_UNIQUE_ID] as SERAddrKey,[EXT_PRACTICE_NAME] as ExtPracticeName"
set select = select_",[ADDR_LINE_1] as Address1,[ADDR_LINE_2] as Address2,[CITY],ZC_STATE.ABBR as State,[ZIP]"
    set select = select_",[PRIMARY_ADDR_YN] AS PrimaryAddress,[PHONE],[FAX],[ACTIVE_YN] as ACTIVE,[EMAIL],[EXT_ADDR_ID] as CPDAddrKey"
    set select = select_",[DIRECT_ADDR] as DirectAddr,[SOURCE_OF_IMPORT] as ImportSource "
    set select = select_"FROM [Clarity].[dbo].[CLARITY_SER_ADDR] INNER JOIN [dbo].[ZC_STATE] on CLARITY_SER_ADDR.STATE_C = ZC_STATE.STATE_C "
    set select = select_"WHERE PROV_ID= ? and ACTIVE_YN = 1"

$$$LOGINFO(select)
set tSC = ..Adapter.ExecuteQuery(.rs,select,pInput.DoctorNumber)
  set tSC = rs.GetSnapshot(.pResponse)
  quit tSC
}

XData MessageMap
{
<MapItems>
<MapItem MessageType="osuwmc.CPD.DataStructures.SelectClaritySERAuditRecords">
<Method>SelectProviderClarityAudit</Method>
</MapItem>
<MapItem MessageType="osuwmc.CPD.DataStructures.SelectClarityActiveAddresses">
<Method>SelectClarityActiveAddresses</Method>
</MapItem>

</MapItems>
}

}
 

Thanks

Scott Roth

The Ohio State University Wexner Medical Center

  • 0
  • 44
  • 2
  • 1

Answers

What happens if you don't declare a Persistent value when you call ExecuteQuery()?

What is " Persistent value"?

What does Ensemble set as the key value for your query?

What is "key value"?

 

That said, if you execute the same query against external database, and the data in the external database does not change you should get the same results.

After you finish working with resultset the first time (and before creating it a second time) you can call:

Set sc = rs.Close()

Maybe that would help.

No luck but thanks.

I took the query from $$$LOGINFO(select)  and executed it fine in SQL Server. It showed Ensemble should of returned 185 rows.

For every database pull Ensemble stores either that whole message or a key value so it knows that record has already been read.  I can't figure out how to get around that when I do a ExecuteQuery() call inside my Operation.

I think that's for services only. They store information about processed rows in: these 2 globals

$$$DoneRowTable(key)
^CacheTemp.Adapter.sqlrow(..BusinessHost.%ConfigName, ..%InstKey, key)

Where DoneRowTable macro is resolved into

^Ens.AppData(..BusinessHost.%ConfigName, "adapter.sqlrow", key)

You can check if there's anything for your BO.

The globals are set in OnTask method of inbound adapter.