Situation:
I have a translation map where one of the record-level local variables is a DB Query to get the next value from a sequence. I don't use this value for every record, so it is embedded within a WHEN condition.
I'm seeing the same local variable value for every record. This is causing problems as when I use this variable, it needs to get the next unique sequence. I put the map into debugger mode and can see the same value for every record.
Can you help me find what I'm missing?
I've set the $varUniqueID to DBQuery {select EMP.ID_SEQ.nextval from dual , $var_EMP_DBConnInfo, true}
the varUniqueID evaluates to the same value for each record.
Cause:
This behavior will occur when DB caching is enabled. Since the same query is executed multiple times, Adeptia will cache the result and return that value instead of running the query on the database for each record
Solution:
Disable DBQuery caching (Actions > Enable DBQuery Caching)
Note: Since the caching has been disabled, you will experience a performance hit when processing large data sets using DB Queries within the map.
Comments
1 comment
I changed the local variable $varUniqueID to be this:
DBQuery {select EMP.ID_SEQ.nextval from dual where $var_LineCounter = $var_LineCounter , $var_EMP_DBConnInfo, true}
where var_LineCounter is defined as position( ) for each record. This seems to be working as expected and I'm getting a new sequence even with the caching on.
Article is closed for comments.