Updating a data table in Perspectives

Ignition
1

Hello all,
I am brand new to Ignition and have zero scripting or database experience. I come from a PLC and ladder logic background, so I am trying to get adjusted to this programming environment. I am trying to update a data base from within Ignition using a data table. I have tried a few different options, but I am not having luck. The original goal was to be able to read and write from Excel through an Ignition interface. But I am not sure that is possible, so I have shifted gears to trying to just update a table in general. I currently have a query tag that looks at a SQL Lite database table. I can read from it, but I cannot seem to figure out how to write new rows.
I some input boxes to put in the desired new data and then an update button with the script pasted below. But this seems to not work still. Any help that could provided of some direction would be greatly appreciated. This project is an internal one I am working on but soon I have a project coming up where I need to be able to log a few months' worth of data so I was hoping this would get me comfortable with this software.

value1 = {QuoteWrite}
value2 = {CustomerWrite}
value3 = {ReceivedWrite}
value4 = {Assignedwrite}
value5 = {DueWrite}
value6 = {SentWrite}
query = INSERT INTO Assignments (Quote, Customer, Received, Assigned, Due, Sent) VALUES (value1, value2, value3, value4, value5, value6)
args = [value1, value2, value3, value4, value5, value6]
system.db.runPrepUpdate(query, args)

Thank you again in advance!

2

I believe you'll need tripled double quotes around your query, and in the VALUES, you should use question marks that will be replaced with your args.

Try:

query = """INSERT INTO Assignments (Quote, Customer, Received, Assigned, Due, Sent) VALUES (?,?,?,?,?,?)"""
3

Triple-quotes allow one to wrap the SQL (or other text) over several lines.

With your nice short variable names you should be able to reduce the code to this with no loss in legibility.

query = """INSERT INTO Assignments (Quote, Customer, Received, Assigned, Due, Sent)  
           VALUES (?, ?, ?, ?, ?, ?)"""
args = [{QuoteWrite}, {CustomerWrite}, {ReceivedWrite}, {Assignedwrite}, {DueWrite}, {SentWrite}] 
system.db.runPrepUpdate(query, args)
1 Like
4

Have you tried adding in the db connection variable to your runPrepUpdate?

Something like databaseConnection = "Insert_Name_of_SQLLite_Connection_Name_Here"

And then modify your runPrepUpdate to system.db.runPrepUpdate(query, args, database=databaseConnection)

5

While value1 = {QuoteWrite} is valid python syntax, QuoteWrite is not defined anywhere (unless it's just a place holder you used for clarity), but the {} are not needed here as I don't believe you are really wanting to define 6 single element sets. Also the {} in Jython do not do the same thing as they do in the Expression Language. Do not mix the two.

A few things to note:

  1. You don't need a query tag for the operation that you have described here. I'm not saying that it wont work, but seeing as you are trying to get familiar with the platform I would discourage you from using query tags to store large quatities of data, and tags in general for things that do not need to be Project Global.
  2. You have not shown how the table is bound to the data that you are attempting to modify. runPrepUpdate() will update the data in the database, that doesn't mean that the data in the table will be instantly refreshed, and often additional steps must be taken.
  3. Prep query's use the ? place holder syntax, not using that syntax will result in the query failing as it will have undifined values.

I would expect a script on an update button to look something like this, assuming that all components are in the same container.

args = [self.getSibling("QuoteWrite").props.text,
        self.getSibling("CustomerWrite").props.text,
        self.getSibling("RecievedWrite").props.text,
        self.getSibling("Assignedwrite").props.text,
        self.getSibling("DueWrite").props.text,
        self.getSibling("SentWrite").props.text]

query = """ INSERT INTO Assignments (Quote, Customer, Received, Assigned, Due, Sent)
        VALUES (?,?,?,?,?,?)"""
system.db.runPrepUpdate(query, args)
self.refreshBinding('table.data')
1 Like