What is the best way to insert information from dataset tag to a database on a scheduled basis.
This is a dataset from an external system that is parsed together into a dataset tag. I want to run a weekly update to a database.
You can set up a scheduled script in the newer versions under the Gateway Events part of your project to run once weekly. From there you can read your dataset tag, iterate through the rows and run your appropriate SQL.
Would this be something system.db.refresh would be used for?
Scheduled Gateway event scripts are not available in 7.9. Consider creating an expression tag that goes true at specified times, then use that to trigger a gateway tag change script.
That function is to refresh a property. Not useful for what I think you're tryng to do.
Are all of these going to be inserts, or are there some updates, as well?
1 Like
It would be updates and inserts. It is an employee database with roles that will get updated and adding new employees would be an insert.
I don't think this would work the way we handle our employee information, but maybe I am missing something. Our ERP system is our main source of roles and users. I am attempting to pull the information from the ERP which is put into a dataset tag and push it into a database to use though other Ignition instances and other software.
Is this a separate user system than the one that clients use to login into the Vision client? If it is not and these users are the same usernames/passwords people use to login Ignition, then you're gateway is already interfacing with it via a User Source and @JordanCClark 's suggestion of a User Management component should work.
However, if its not, then do what he suggested - setup a tag change event on an expression tag that turns true at the moment you want it to run.
Then you'll want to iterate through this dataset (examples how - Datasets - Ignition User Manual 7.9 - Ignition Documentation) and per row run your INSERT or UPDATE query.
Copying from the documents it would be something like
ds = system.tag.read("your/path/to/DatasetTag").value
for row in range(ds.getRowCount()):
# your logic here
I might suggest using PyDataSets as then you can unpack in the for loop and do something like
ds = system.dataset.toPyDataSet(system.tag.read("your/path/to/DatasetTag").value)
for (column1, column2, column3) in ds:
# your logic here with column1,2,3 etc
You may find some inspiration from this thread.
An example window comparing two datasets.
If you look further odwn about six posts or so, you' see a script that can be used in that window to check for adds / changes / deletions.
Hi,
I need to insert PLC tags into database table for every 15 minutes using scheduled events.
can you please guide.
since I am doing first time I just using messageBOX to popup for every one minute using scheduled events. I tried as below. I don't know its correct or not.
In console I am calling like this,
Can you please show how I can do with this.
Thank you
Scheduled events run in the gateway. They cannot pop up a message box or do any GUI operations. Use a logger from system.util.getLogger() to send progress or error messages to the gateway log.
ok Thank you.
But How can I insert data into database for every 15 mins using scheduled events.
I have tried this. Its inserting but its nor inserting s shceduled
any Video example is there for this requiremnt.
Thank you
Normally, I'd suggest to post the error, use triple backquotes to format your code and the error into a more readable format instead of screenshots. That way it's easier to try to replicate your problem. That said...
You're running in the gateway scope. So:
- Any errors will show up in the log.
- You must specify the name of the database connection in runPrepUpdate(). I suggest to people to get into the habit of always specifying the database connection, so that there is no room for doubt.
1 Like