Issue in using Insert Named Query on a Populated Table

Ignition
, , ,
1

I have a popup called AddBrassTag that contains a table, Tbl_Insert_New_BrassTag. When I enter data into this table, commit the edit (Edit Cell Commit) and press Confirm, the data is passed to another table called Table on a different popup, ConfirmSave.

On the ConfirmSave popup, there's a "Save" button (BtnSave). When I press this button, I want to save the data from the Table on the ConfirmSave popup into the database using an Insert Named Query.

Here's the script I'm using to handle this:

def runAction(self, event):
    # Retrieve the table data
    table_data = self.getSibling("Table").props.data
    brassTag_Ins = []

    # Iterate over the table data and populate the BrassTag_Ins array
    for row in table_data:
        if row["BrassTag"]["value"]:
            brassTag_Ins.append({
                "BrassTag": row["BrassTag"]["value"],
                "BrassTag_Desc": row["BrassTag_Desc"]["value"]
            })

    # Now, assign the populated brassTag_Ins to the custom property
    self.getSibling("Table").custom.BrassTag_Ins = brassTag_Ins

    # Initialize tbl with the newly populated brassTag_Ins
    tbl = self.getSibling("Table").custom.BrassTag_Ins
    lastBrassTag = tbl[len(tbl) - 2]["BrassTag"]

    # Run the Named Query to save each row in tbl
    for row in tbl:
        if row["BrassTag"]:
            system.db.runNamedQuery("Query_BrassTag_Insert", 
                                    {"BrassTag": row['BrassTag'], 
                                     "BrassTag_Desc": row['BrassTag_Desc'], 
                                     "user": self.session.props.auth.user.userName})

    # Clear the table and BrassTag_Ins array after saving
    self.getSibling("Table").custom.BrassTag_Ins = []
    self.getSibling("Table").props.data = [{
                                              "BrassTag": {
                                                "editable": True,
                                                "style": {"backgroundColor": "white"},
                                                "value": ""
                                              },
                                              "BrassTag_Desc": {
                                                "editable": True,
                                                "style": {"backgroundColor": "white"},
                                                "value": ""
                                              }
                                            }]

Issue:

Despite using the above script, no data is being saved into the database.

I've attempted to store the table data in a custom array BrassTag_Ins and then retrieve it for saving, but this approach also isn't working.

Request:

Could anyone guide me on where I might be making a mistake or what I might be missing? I appreciate any advice or insights you can offer.

Thank you for your time!

2

The approach is not "ignitionesque".

Instead of scripting the whole thing, you should rely on bindings a little more.
For example, bind the BrassTag_Ins property to the table data, and use a transform to process that data.
Then your button can reference that custom property and do the insert.
I'm also not sure why you're clearing the table afterward, if it's just a confirm popup. Just close it.

BUT, I think there's a whole other level of things that should change.
Don't send your data to the confirm popup, make the confirm popup send a message back to the calling view to tell it that whatever was asked has been confirmed.
Catch that message from the AddBrassTag popup, and do your insert there.

That way, you have a confirm popup that you can reuse everywhere you need confirmation, instead of creating new ones all over the place, and the logic is handled in the view that has the data.

PS: you don't need to send private messages, we'll see the question and answer if we can.

3

How can I use the Transform -- of "Table"? -- in this context. Is populating Table is not enough to get the data?

4

Frankly, don't bother. Use the approach I suggested, with messages.

Here's my own confirm popup view:

view's json 
{
  "custom": {},
  "params": {
    "handler": "handler",
    "message": "message",
    "payload": null,
    "popup_id": "popid",
    "send_cancel": true,
    "source_id": null
  },
  "propConfig": {
    "params.handler": {
      "paramDirection": "input",
      "persistent": true
    },
    "params.message": {
      "paramDirection": "input",
      "persistent": true
    },
    "params.payload": {
      "paramDirection": "input",
      "persistent": true
    },
    "params.popup_id": {
      "paramDirection": "input",
      "persistent": true
    },
    "params.send_cancel": {
      "paramDirection": "input",
      "persistent": true
    },
    "params.source_id": {
      "paramDirection": "input",
      "persistent": true
    }
  },
  "props": {
    "defaultSize": {
      "height": 240,
      "width": 380
    }
  },
  "root": {
    "children": [
      {
        "meta": {
          "name": "Label"
        },
        "position": {
          "grow": 1
        },
        "propConfig": {
          "props.text": {
            "binding": {
              "config": {
                "path": "view.params.message"
              },
              "type": "property"
            }
          }
        },
        "props": {
          "style": {
            "overflow": "auto",
            "overflowWrap": "break-word",
            "textAlign": "center"
          }
        },
        "type": "ia.display.label"
      },
      {
        "children": [
          {
            "events": {
              "component": {
                "onActionPerformed": {
                  "config": {
                    "script": "\t\n\tpayload \u003d {\n\t\t\u0027confirm\u0027: True,\n\t\t\u0027source_id\u0027: self.view.params.source_id\n\t}\n\tif self.view.params.payload:\n\t\tpayload.update(self.view.params.payload)\n\tsystem.perspective.sendMessage(\n\t\tmessageType\t\u003d self.view.params.handler,\n\t\tpayload\t\t\u003d payload,\n\t)\n\tsystem.perspective.closePopup(self.view.params.popup_id)"
                  },
                  "scope": "G",
                  "type": "script"
                }
              }
            },
            "meta": {
              "name": "Button_confirm"
            },
            "position": {
              "basis": "50%"
            },
            "props": {
              "image": {
                "icon": {
                  "path": "material/check"
                }
              },
              "text": "Confirmer"
            },
            "type": "ia.input.button"
          },
          {
            "events": {
              "component": {
                "onActionPerformed": {
                  "config": {
                    "script": "\tif self.view.params.send_cancel:\n\t\tpayload \u003d {\n\t\t\t\u0027confirm\u0027: False,\n\t\t\t\u0027source_id\u0027: self.view.params.source_id\n\t\t}\n\t\tif self.view.params.payload:\n\t\t\tpayload.update(self.view.params.payload)\n\t\tsystem.perspective.sendMessage(\n\t\t\tmessageType\t\u003d self.view.params.handler,\n\t\t\tpayload\t\t\u003d payload,\n\t\t)\n\tsystem.perspective.closePopup(self.view.params.popup_id)"
                  },
                  "scope": "G",
                  "type": "script"
                }
              }
            },
            "meta": {
              "name": "Button_cancel"
            },
            "position": {
              "basis": "50%"
            },
            "props": {
              "image": {
                "icon": {
                  "path": "material/close"
                }
              },
              "primary": false,
              "text": "Annuler"
            },
            "type": "ia.input.button"
          }
        ],
        "meta": {
          "name": "buttons"
        },
        "position": {
          "basis": "32px",
          "shrink": 0
        },
        "props": {
          "style": {
            "gap": "10px"
          }
        },
        "type": "ia.container.flex"
      }
    ],
    "meta": {
      "name": "root"
    },
    "props": {
      "direction": "column",
      "style": {
        "gap": "5px",
        "padding": "5px"
      }
    },
    "type": "ia.container.flex"
  }
}

Copy the json, create a new flex based view, shift + right click on it and select paste json.
Check the buttons scripts, see how it works, and make your own.

Then, on your AddBrassTag popup's root, add a script. In there, you can add a message handler. The name you give it is the value you'll pass to the handler parameter of the popup, so that it can send this message when the "confirm" button is clicked.
In the handler, check payload['confirm'], and handle the inserts there.

As a bonus, here's the function I use for bulk inserts:

def build_insert_string(table, data, columns=None):
	"""
	Build the query string and the values list for a multi-insert query.
	Use with `system.db.runPrepUpdate`
	params:
		table (string):						The table to insert into
		data (list of dicts):				A list containing a dict for each row to insert, where the keys are the columns names
		columns_names (list of strings):	The columns to be inserted. If None, they're deduced from the first row's keys.
	return:
		a tuple where:
			- the first element is the query string formatted for a `prepUdate` (with question marks as place holders)
			- the second element is the list of values
	"""
	if not data:
		return None, None
	if columns is None:
		columns = data[0].keys()
	marks = "({})".format(','.join("?" for _ in columns))
	marks = ",".join(marks for _ in data)
	col_names = ','.join(columns)
	q = "insert into {} ({}) values {}".format(table, col_names, marks)
	values = [row[c] for row in data for c in columns]
	return q, values

edit: The names of the parameters may be confusing. handler is the message handler, message is the message that will be displayed in the popup.

1 Like
5

You're going to have better luck executing this as a Prep Query, than as a Named Query. Anytime that you don't know exactly what you need to query/update, a using a Named Query can become cumbersome quickly.

On top of that, you are running a Query for every row in the table, that has the potential to hammer your db significantly if a number of records need to be updated, which will make the UX less than desirable.

You'll be better off with something similar to this (Note: You didn't share your actual query so this is just a guess at what is going on):

def runAction(self,event):
    libraryName.saveBrassTags(self.getSibling("Table").props.data,self.session.props.aut.user.userName)

In a project library:

def saveBrassTags(brassTagData,user):
    args = [row[col]['value'] for row in brassTagData if row['BrassTag']['value'] for col in ('BrassTag','BrassTag_Desc')]
    query = """
            INSERT INTO brassTags
            VALUES {}
            WHERE user = ?
            """
    query.format(",".join(["(?,?)"] * len(brassTagData))
    args.append(user)
    system.db.runPrepUpdate(query,args)

This will allow a single insert operation for multiple rows. As for the general approach, I would do as @pascal.fragnoud has recommended.

6

I am very thankful to you for sharing the detailed solution.

But Besides of this purpose I have few more tables where I will be showing a user old and updated values for confirmation. I was thinking maybe I can use the same solution there.

I am very new to Ignition and trying to learn it step by step.
Just learned how to open a popup, then transferred the data from one table to another. Now trying to insert it into the table. On all of the resources on web, I couldn't found an example of parameter based named Insert Query.

7

this is the simple query that I will be using INSERT INTO BrassTag(BrassTag, BrassTag_Desc, user) VALUES (:BrassTag, :BrassTag_Desc, :user);

8

It is indeed a bit different if you want to show the user a diff in the confirmation popup.
But I'd probably still make the popup a simple confirmation, though more "specialized" than the one I shared. Pass it 2 datasets, with the old and new data, and display them in 2 tables or whatever is best suited for your data.
But that would be the only difference, you'd still send a message to confirm or cancel, catch that message from the calling view, and handle whatever should happen from there.

If you handle the logic from the popup, then it's inherently not reusable, or you'll need to parameterize the logic to suit whatever you throw at it.
That's how you end up with a bunch of ifs and checks and whatnot.
Don't do this. Keep it simple.

Use the bulk insert function I posted above. The parameters are described in the doc string.
Use it like this:

q, v = build_insert_string("table_name", data)
system.db.runPrepUpdate(q, v)
9

In that case, my suggested code would look like this:

def saveBrassTags(brassTagData,user):
    args = [row[col]['value'] for row in brassTagData if row['BrassTag']['value'] for col in ('BrassTag','BrassTag_Desc')]
    query = """
            INSERT INTO brassTag (BrassTag,BrassTag_Desc,user)
            VALUES {}
            """
    query.format(",".join(["(?,?," + user + ")"] * len(brassTagData))
    system.db.runPrepUpdate(query,args)

@pascal.fragnoud 's code does the same thing, just more generically.

Note that, if all columns are being inserted, then technically you do not need the columns list in the query.

10

Finally I have reached here, the data is being passed to the Confirmation Popup and message is being received on the Add_BrassTag view again. Now Stuck in building the query for insert.
I am assuming that I need to use the Named Query to write the data.

I am using this script to write the data when confirm message is being received.

def onMessageReceived(self, payload):
    if payload['confirm']:
        # Extract data from payload
        data = payload.get('data', [])
        
        # Prepare the insert data
        insert_data = [
            {
                'BrassTag': row['BrassTag'],
                'BrassTag_Desc': row['BrassTag_Desc'],
                'user': self.session.props.auth.user.userName
            } for row in data if row['BrassTag']
        ]
        
        q, v = Query_BrassTag_Insert("BrassTag", data)
        system.db.runPrepQuery(q, v)
        
        # Execute the query
        if q and v:
            system.db.runPrepUpdate(q, v)
        
        # Clear the table data after insert
        self.getSibling("Tbl_Insert_New_BrassTag").custom.BrassTag_Ins = []
        self.getSibling("Tbl_Insert_New_BrassTag").props.data = [{
            "BrassTag": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            },
            "BrassTag_Desc": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            }
        }]

But Getting this Logical error in the Logs

Error running Popup/Add_BrassTag@PL5dgDy-E/root.onMessageReceived(self, payload): Traceback (most recent call last): File "<function:onMessageReceived>", line 15, in onMessageReceived NameError: global name 'Query_BrassTag_Insert' is not defined

Named Query Name: Query_BrassTag_Insert
Query Itself : INSERT INTO BrassTag(BrassTag, BrassTag_Desc, user) VALUES (:BrassTag, :BrassTag_Desc, :user);

Any Guess what I am doing wrong here?

11

As the error says: You're using something that doesn't exist.
Query_BrassTag_Insert isn't defined anywhere. This is NOT a named query, it's the function I posted above. here: Issue in using Insert Named Query on a Populated Table - #4 by pascal.fragnoud

Also, remove the runPrepQuery call.
The rest of the script looks ok to me. Though you don't need to check if both q and v are None. if q: should be enough.

The next step would be to add some error handling, to warn the user if something went wrong.
something like:

try:
	system.db.runPrepUpdate(q, v)
	clear table
except Throwable as e:
	warn user that something went wrong
	raise

Throwable is the java error object, you can import it with from java.lang import Throwable.
You need it for database errors.
I also usually re raise errors after warning the user. This will make it bubble up to the gateway logs, and it gives me a chance to handle the error logically somewhere else if needed (warning the user is good, but it may not be enough in some cases)

12

I think I'm very close. Just some problem with the query. Error executing system.db.runPrepUpdate(insert into BrassTag (BrassTag_Desc,BrassTag) values (?,?), [desc, 6500], db, , false, false)

def onMessageReceived(self, payload):
    if payload['confirm']:
        # Extract data from payload
        data = payload.get('data', [])
        
        # Prepare the insert data
        insert_data = [
            {
                'BrassTag': row['BrassTag'],
                'BrassTag_Desc': row['BrassTag_Desc'],
                'user': self.session.props.auth.user.userName
            } for row in data if row['BrassTag']
        ]
        
        q, v = build_insert_string("BrassTag", data)
                
        # Execute the query
        if q:
            system.db.runPrepUpdate(q, v)
        
        # Clear the table data after insert
        self.getSibling("Tbl_Insert_New_BrassTag").custom.BrassTag_Ins = []
        self.getSibling("Tbl_Insert_New_BrassTag").props.data = [{
            "BrassTag": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            },
            "BrassTag_Desc": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            }
        }]
        
        
def build_insert_string(table, data, columns=None):
	"""
	Build the query string and the values list for a multi-insert query.
	Use with `system.db.runPrepUpdate`
	params:
		table (string):						The table to insert into
		data (list of dicts):				A list containing a dict for each row to insert, where the keys are the columns names
		columns_names (list of strings):	The columns to be inserted. If None, they're deduced from the first row's keys.
	return:
		a tuple where:
			- the first element is the query string formatted for a `prepUdate` (with question marks as place holders)
			- the second element is the list of values
	"""
	if not data:
		return None, None
	if columns is None:
		columns = data[0].keys()
	marks = "({})".format(','.join("?" for _ in columns))
	marks = ",".join(marks for _ in data)
	col_names = ','.join(columns)
	q = "insert into {} ({}) values {}".format(table, col_names, marks)
	values = [row[c] for row in data for c in columns]
	return q, values

Error Message : Error running Popup/Add_BrassTag@PL5dgDy-E/root.onMessageReceived(self, payload): Traceback (most recent call last): File "<function:onMessageReceived>", line 19, in onMessageReceived at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(insert into BrassTag (BrassTag_Desc,BrassTag) values (?,?), [desc, 6500], db, , false, false)

13

You're building insert_data, but passing data to the runPrepUpdate function.

If you still have issues, you can log the values and see if they are what you think they are.
You can also try to run that query in the database query browser (you can access it from the "tools" menu).

14

@pascal.fragnoud
@lrose

Thank you so much guys, finally it has worked.
There were two issues one, I wasn't logged in, two, I had to mentioned the name of database "Explicitly" in the onMessageReceived method.

here is the complete code of the view side.

def onMessageReceived(self, payload):
    if payload['confirm']:
        # Extract data from payload
        data = payload.get('data', [])
        
        # Prepare the insert data
        insert_data = [
            {
                'BrassTag': row['BrassTag'],
                'BrassTag_Desc': row['BrassTag_Desc'],
                'user': payload['user']
            } for row in data if row['BrassTag']
        ]
        
        # Build the query and values
        q, v = build_insert_string("BrassTag", insert_data)
        
        # Debugging: Print the query and values
        system.perspective.print("Query: {}".format(q))
        system.perspective.print("Values: {}".format(v))
                
        # Specify the database name, it is important.
        database_name = 'test_mdcmaster'
        
        # Execute the query
        if q:
            system.db.runPrepUpdate(q, v, database_name)
        
        # Clear the table data after insert
        self.getChild("Tbl_Insert_New_BrassTag").custom.BrassTag_Ins = []
        self.getChild("Tbl_Insert_New_BrassTag").props.data = [{
            "BrassTag": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            },
            "BrassTag_Desc": {
                "editable": True,
                "style": {
                    "backgroundColor": "white"
                },
                "value": ""
            }
        }]

def build_insert_string(table, data, columns=None):
    if not data:
        return None, None
    if columns is None:
    	# Specify the order explicitly, this should match with the insert query order
        columns = ['BrassTag', 'BrassTag_Desc', 'user']  
    marks = "({})".format(','.join("?" for _ in columns))
    marks = ",".join(marks for _ in data)
    col_names = ','.join(columns)
    q = "insert into {} ({}) values {}".format(table, col_names, marks)
    values = [row[c] for row in data for c in columns]
    return q, values
1 Like
15

Nicely done.

One last thing: Put the build_insert_string function in a library script, then use the full path to call it.
Remove the hardcoded list of columns and use the columns parameter to pass that list instead.
Actually, the keys in the inserted_data dicts seem to match those column names, so you don't even need to specify that list.
And leave the docstring in there. Your library functions should be documented.

For example, mine is in "lib/queries", and I call it like this:

lib.queries.build_insert_string('table_name', data)

image

basically, take the function exactly as I posted it, put it the library, and call that.

16

One last question tough!
Can I use a table to show the data instead of a label on the popup?

17

Yes, if you're passing data formatted for a table, there's no reason you can't use a table.
But in this case I suggest you make another popup for this, and keep the basic popup_confirm for later use.
Copy the view, paste it, then replace what needs to be replaced.

18

Can I get the guidance of the steps needs to be followed? :blush:

19

It should be pretty easy:

  • add a data (or whatever you want to call it) parameter to your popup
  • add a table component to the table
  • bind the table's prop.data property to the data parameter

That's it.
As long as the data you pass to the popup is formatted properly for a table, it will work.
So if you just want to show a copy of the table the user built in the calling view... you can just pass that directly. It's already formatted for a table.

20

"Data Formatted for Table" makes me confused

{"data":[{"BrassTag_Desc":"desc","BrassTag":"12300"}],"user":null}
This is what I am currently getting from my View.
Is this format not good to show on a table?