Using Gateway scripts to move data from one database to another

Ignition
, ,
1

Would it be too taxing on our gateway to use gateway scripts to move data from one database to another every 15 minutes? I did the initial data transfer manually but this would just update the moved data from the original database. Maybe a couple hundred rows would be updated every 15 minutes. I know there are other (probably better) ways to do this but due to firewall restrictions it is very hard to get these databases connected to each other or another server to run an update script. Luckily our Ignition gateway already has the firewall opened between the DBs and the gateway so I'm wondering if this is a feasible solution.

2

Sounds like a light duty operation, as long as the source database has indices to keep the SELECT operation efficient. I recommend using multi-valued INSERTs for efficiency, and running it often enough that you can use just one of those each time (most of the time).

3

Also, don't forget to think through your edge cases.
What happens if one of the DBs is unavailable? Or one of the gateways? For a prolonged period? How do you determine which records to sync in the latest batch?

1 Like
4

What do you mean by "multi-valued INSERTs"?

5

This, handed to system.db.runPrepUpdate(), will insert three rows at once:

INSERT INTO some_table (some_column1, some_column2, some_column3)
VALUES (?, ?, ?),
  (?, ?, ?),
  (?, ?, ?)

You would provide the nine arguments in a flat list.

You would construct the SQL dynamically with the right number of inserted row placeholders and the right number of JDBC substitution arguments. Just be careful not to exceed the limit on ? placeholders. Some JDBC drivers choke at around 2000.

2 Likes
6

Ahh, so you are saying if I run it often enough that we are only updating say 100 rows with 5 ? each then I won't have to worry about the JDBC driver having issues?

7

I would have to include logic in my script for DB related issues. If our gateway is down then our Perspective sessions would be down as well and I would have bigger concerns than the script not moving data right? But determining which records to sync is still come logic I need to think on. I can't delete and repopulate the table every time because the updated rows will have foreign keys to other data.

8

Yes, under normal conditions. I would keep track of the last successfully transferred row in a project library script top-level variable. The basic operation on each interval would be something like this:

  • If the cached row ID is None, query the destination DB for its newest row ID to initialize the cache. Any error here, exit early.

  • SELECT from the source DB for all rows after the cached ID. Any error here, exit early.

  • Loop through the source data in a hundred or so rows at a time (minding the # of placeholders), constructing and executing multi-row INSERT operations. After each success, update the cached row ID. Any error, exit early.

1 Like