Database loads from web services

My web application needs to use data that has a primary source in a sql database. Unfortunately, there is a firewall between outsystems and the database, and no access will be granted for db calls. So I can't use the direct database integration. Instead I have to use a web service.

It has been decided that the best process is to keep a copy of the table at the outsystems database end, and load that via the web service.

The web service only allows the return of a limited number of records (for example, 100). So for the initial load of the outsystems ("local") table, I need to find the last record id currently loaded, request a batch of results from the web service starting after that id, and then process the 100 rows that come back to add them to the local database. Then I need to repeat that process, until all records are loaded.

I have that working, but now I want to be able to cancel the process. I added a session variable that says whether the script should keep looping, and a button on screen to set that variable to stop the script. The problem is, when the script is running the button press never registers.

I need a way to call the part of the process that loads one batch of data from the web service, that still leaves that app able to process the cancel button.

Or at a broader level, is there a better pattern for this?


Mike,

I would use timers to do the loading rather than on the load of the web page.  If you really need to, you can actually start your timer from the Preparation rather than having it running on a schedule.

How you stop it would depend on your use case.  Can you give us a little more info?

I basically have two cases, initial load, and sync.

For initial load, I need to repeatedly call the web service loading in the next batch of data until the table is full.

For sync, I need to do the same thing, but only grabbing records with rowversion > then max loaded rowversion. Rowversion on the sql tables will let me keep track of rows that have been changed and need to be loaded to the outsystems db. I could be in a position of loading 10,000 rows 100 at a time.

In production, I might not need to be able to stop the load process. But during development, I'd like to be able to cancel the process, since it could run a long time. I could be in a position of loading 10,000 rows 100 at a time. If something is going wrong, or I just want to update the process, I want to stop it.

I initially went the timer route, and then found that there was no good way to cancel the running process.

This also brought up another issue: I can't figure out how to clear an outsystems database table (delete from x), other than setting up a loop, retrieving an aggregate with a certain number of rows, deleting those rows, and then looping. Is there no way to run sql directly against the table?

Solution

This is a good example of a place where a timer would be helpful, as Kevin says. One correction I would make is that if you want to manually start the timer, you should do so via ServiceCenter instead of triggering in the preparation of a dummy web page. You can find and trigger timers in ServiceCenter > Factory > eSpaces > {Module Name} > Timers [tab]. 

I would also recommend you take a look at the heavy timers section of the best practices master class in order to make the timer as robust as possible. You can find that section here: https://youtu.be/sIlEUJ_2kLE?t=8068

My final recommendation is to implement the interrupt signal, create a site property which holds your flag for whether or not it is okay to continue timer execution. You could make it something like Site.IsSynchronizationOk and just use True or False to do a check to see if you should continue processing on each iteration or not.

Solution

Grayson Udstrand wrote:

This is a good example of a place where a timer would be helpful, as Kevin says. One correction I would make is that if you want to manually start the timer, you should do so via ServiceCenter instead of triggering in the preparation of a dummy web page. You can find and trigger timers in ServiceCenter > Factory > eSpaces > {Module Name} > Timers [tab]. 

I would also recommend you take a look at the heavy timers section of the best practices master class in order to make the timer as robust as possible. You can find that section here: https://youtu.be/sIlEUJ_2kLE?t=8068

My final recommendation is to implement the interrupt signal, create a site property which holds your flag for whether or not it is okay to continue timer execution. You could make it something like Site.IsSynchronizationOk and just use True or False to do a check to see if you should continue processing on each iteration or not.

Given the use case, I completely agree. I would avoid having any of this kicked off from a web page unless that's somehow part of the requirements.

@Mike deleting all of the records in an entity is the one real use case for advanced SQL that I've found :)  



Grayson Udstrand wrote:

This is a good example of a place where a timer would be helpful, as Kevin says. One correction I would make is that if you want to manually start the timer, you should do so via ServiceCenter instead of triggering in the preparation of a dummy web page. You can find and trigger timers in ServiceCenter > Factory > eSpaces > {Module Name} > Timers [tab]. 

I would also recommend you take a look at the heavy timers section of the best practices master class in order to make the timer as robust as possible. You can find that section here: https://youtu.be/sIlEUJ_2kLE?t=8068

My final recommendation is to implement the interrupt signal, create a site property which holds your flag for whether or not it is okay to continue timer execution. You could make it something like Site.IsSynchronizationOk and just use True or False to do a check to see if you should continue processing on each iteration or not.


OK, so what do I put as the schedule? Does "when published" mean that it automatically kicks off when a release of the app is published? one table to load, I'd rather have them kicked off as an admin driven action than at startup

Mike Fox wrote:

Grayson Udstrand wrote:

This is a good example of a place where a timer would be helpful, as Kevin says. One correction I would make is that if you want to manually start the timer, you should do so via ServiceCenter instead of triggering in the preparation of a dummy web page. You can find and trigger timers in ServiceCenter > Factory > eSpaces > {Module Name} > Timers [tab]. 

I would also recommend you take a look at the heavy timers section of the best practices master class in order to make the timer as robust as possible. You can find that section here: https://youtu.be/sIlEUJ_2kLE?t=8068

My final recommendation is to implement the interrupt signal, create a site property which holds your flag for whether or not it is okay to continue timer execution. You could make it something like Site.IsSynchronizationOk and just use True or False to do a check to see if you should continue processing on each iteration or not.


OK, so what do I put as the schedule? Does "when published" mean that it automatically kicks off when a release of the app is published? one table to load, I'd rather have them kicked off as an admin driven action than at startup

Just leave the schedule blank :)  Then the only way to start it is in ServiceCenter. 


ha, I thought it was required. Thanks.