Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
Rick

Avatar / Picture

Senior Veteran
Registered:
Posts: 259
Reply with quote  #1 

This is what my boss would call a real 'head-scratcher'.

My process uses a business object to read in a set of records where the processed flag is set to 'No'. It then processes them and finally updates the external table to set the processed flag to 'Yes'. Each row has a unique ID.

The update query is simply:

"UPDATE MyTable set Processed = 'Yes' where processed = 'No' and RecordUID <= "+MyProcessData.LastRecordID


RecordUID in Mytable is an int column
LastRecordID in MyProcessData is also an int column

This should work and often it does. But sometimes it doesn't.

Example 1:
The business object reads in records 17 to 21 inclusive. The update query sets records 17 to 20 inclusive as processed but leaves record 21 unprocessed so it is read in again next time.
Example 2:
The businss object reads in records 25 to 31 inclusive. The update query sets records 25 to 26 as processed but leaves records 27 to 31 uprocessed.
Example 3:
The business object reads in records 32 to 38 inclusive. The update query sets all records as processed correctly.

Any ideas?


Thanks,
Rick


__________________

Another full day of doing nothing but rearranging zeros and ones. :)
You know it will be a good day when there is no human interaction on the schedule.

0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #2 
I'd examine (log) the LastRecordID variable you have at the point of executing the SQL, because the database will never process some records and not others, and you have the condition:

<= "+MyProcessData.LastRecordID

In our debug library there is a log SQL function that can log the whole SQL statement, including seting up and assigning parameters for SQL Server.

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
Rick

Avatar / Picture

Senior Veteran
Registered:
Posts: 259
Reply with quote  #3 
Got it!

Records are inserted into MyTable in numerical order: 38,39,40,41,42 etc. I stupidly assumed that the BO would also read and process these records in the same numerical order. Wrong! When I checked the exact eCreationTime of the folders generated from these records they were processed in the order: 40,38,41,42,39. So the LastRecordID is 39 and rows 40 to 42 get processed again next time around.

I've now put an order by clause on my BO which should enforce the correct order. I'll have to check back after the weekend to see.

Doh!

__________________

Another full day of doing nothing but rearranging zeros and ones. :)
You know it will be a good day when there is no human interaction on the schedule.

0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!