Incomplete dictionary interacting with queries

Ignition
,
1

Hi!

I need a bit of help of how to do this.

I have a named query:

system.db.runNamedQuery(
	"FUNCTIONS/UPDATE",
	{
		"ID": id,
		"PARAM_1": value1,
		"PARAM_2": value2,
                [...]
		"PARAM_N": valuen
	}
)

UPDATE MY_TABLE
	PARAM_1 = COALESCE(:PARAM_1, PARAM_1),
	PARAM_2 = COALESCE(:PARAM_2, PARAM_2),
        [...]
	PARAM_N = COALESCE(:PARAM_N, PARAM_N),
WHERE ID = :ID

The function update the table depending of the ID and the values that receives that are not null.

My problems is that I recive the values in a dictionary, how may or may not contains the values to update.

For example, this two are possible values that I could receive:

dict = {
	"PARAM_1": value1,
	"PARAM_3": value3,
	"PARAM_8": value8
}
dict = {
	"PARAM_3": value3,
	"PARAM_4": value4,
	"PARAM_5": value5,
	"PARAM_8": value8
}

My first idea was to do this:

system.db.runNamedQuery(
	"FUNCTIONS/UPDATE",
	{
		"ID": id,
		"PARAM_1": dict[PARAM_1],
		"PARAM_2": dict[PARAM_2],
                [...]
		"PARAM_N": dict[PARAM_N]
	}
)

But if the dictionary doesn't contain the param it throws an exception.

Also I thought of making a loop where I check if it exist and adding a null value if not, but I don't really like, is an ugly way of doing it (in my opinion).

Any idea of how I could do this in a more elegant way?

Any restructuring of the code, query or the idea itself are valid. The only thing I can't change is how I recive the data.

Thanks in advance!

2

Hi,

Not an expert on this, but a namedQuery does not seem suitable for what you are trying to achieve.

Considering your goal, I would use system.db.runPrepUpdate | Ignition User Manual

This way, you are able to build the SQL query using a for loop and your query will be dynamic. (no need to check for null values, just add "SET col = ? " to the query in the loop based on the number of elements in your dictionary).

Beware of potential SQL Injections though, as you will be able to specify prepared arguments using "?" for the values, but you won't be able to use prepared arguments for the column names.

2 Likes
3

Not just unsuitable. It can not be done safely. It would require the use of a query string parameter which defeats the main purpose of named queries.

@danielps1818
The only safe way to accomplish this is through a script using system.db.runPrepUpdate().

With this there is no chance for SQL injection as the query structure is being provided to the driver by the code and any '?' placeholders will be properly sanitized.

The only tricky part is insuring you maintain the order of the parameters as they are handed to you as you are generating the query and parameter list.

If you need help doing that in an “elegant” way just ask. I’m away from my desk at the moment so can’t really post a script easily.

1 Like
4

I assume you're getting a key error here

system.db.runNamedQuery(
	"FUNCTIONS/UPDATE",
	{
		"ID": id,
		"PARAM_1": dict[PARAM_1],
		"PARAM_2": dict[PARAM_2],
                [...]
		"PARAM_N": dict[PARAM_N]
	}
)

when the PARAM_N does not exist correct? You could use .get which provides a default value if the key does not exist ie dict.get(PARAM_N, None) as None translates into a NULL in the db. Though at this point then are just updating the fields to their current value anyways in the db and so it's possible if you only wanted to update a single column, you would still get the full UPDATE statement for every column - this makes debugging confusing/you could potentially have to sift through a lot of useless SQL/columns.

You could do something like

def createPrepStatement(table, payload, idColumnName, idx):
	"""
	Args:
		table: database table name
		payload: dictionary - column name in keys, values to update to in values
		idColumnName: name of the id column
		id: anyType, probably int, provdied to Id column
    Returns:
        query: str: the query string with ? interpolated
        args: [any], arguments to provided to the prepUpdate statement 
	"""
	return "UPDATE %s SET %s WHERE %s=?"%(
		table, 
		', '.join(['%s=?'%(key) for key in payload.keys()]), 
		idColumnName
	), list(payload.values())+[idx]

query, args = createPrepStatement('table', {'column1':1, 'columnStr':"hello"}, 'id', 1)
system.db.runPrepQuery(query, args)

Dynamically make the update statement to only update what is needed. Now if you needed to debug your SQL Statements would not have any no-ops.

1 Like
5

Amazing the dict.get(value, default_value)

Didn't knew it existed.
I'm going to try this:

system.db.runNamedQuery(
	"FUNCTIONS/UPDATE",
	{
		"ID": id,
		"PARAM_1": dict.get('PARAM_1', None),
		"PARAM_2": dict.get('PARAM_2', None),
                [...]
		"PARAM_N": dict.get('PARAM_N', None)
	}
)

Thanks a lot!

6

Why the insistence on a Named Query?

2 Likes
7

Because a system.db.runPrepUpdate() in my case would be something like this:

system.db.runPrepUpdate(
    "UPDATE MY_TABLE SET (?,?,?,?,?,?,[...],?) WHERE ID = (?)",
    [dict[PARAM_1], dict[PARAM_2], dict[PARAM_3], dict[PARAM_4], [...], dict[PARAM_N], ID],
)

and the problem of not having the key PARAM_X in the dict still occurs. Also I don't know if the WHERE clause works correctly.

In my opinion, named queries are more "elegant" than writting the query in code and gives a level of abstraction that the system.db.runPrepUpdate() don't.

Is not that aren't useful, I simply prefer the other way.

How would you do this?

8

Did you see the function I wrote? As long as the key value of the dictionary is the column name in the database it will write the query for you for only the columns that exist in your dictionary.

I am not against named queries but I am against No-Ops. I would not want to have to debug a query where there is a number of someColumn='previousValue' littered throughout each one. It makes things confusing.

2 Likes
9

Using the named query requires you to assign to every column. That has negative consequences in many concurrent applications, and makes more work for the DB.

Build SQL for a prep update that only has the assignments for the assignments actually needed. (Named queries cannot do this.)

Place this common task in a project library script. Where you would have use runNamedQuery(), call the library function instead.

Just as maintainable, at least, IMNSHO. (Project scripts are stored in a git-friendly form in the gateway filesystem.)

1 Like
10

Okey, okey, okeeyyy.

It just clicked.

I a bit dense today. I'll try it and I'll let you know.
Sorry for the misunderstanding and the patience. I really appreciate your help!

I'm going to try it now. Thank you!

PS: Doing an update with coalesce does actually update it in the db? If I do what I wrote with coalesce even with Null values in each param it gets a full update?

1 Like
11

Yes your coalesce statement there is only on the value of what you're setting the column to so you will see SET for each column, whether it's a new value or the existing db value due to a NULL parameter depends on the dictionary you feed in.

12

Depends on the DB. Some will notice that the value is identical, but all databases that do that will still have to check, and that impacts the execution plan.

13

In their Ignition logs they would see a UPDATE table SET column_1=1, ...,column_N=n for every column as the database would be the one that does this since you mention execution plan, I assume this is not happening on the JBDC level at all.

14

And a drill with a screw is more elegant than a hammer and nail, but if you have a nail you wouldn't really want to use a drill to drive it.

I would agree that the abstraction provided by named queries is great, but they are not always the right tool for the job.

Personally, I would approach this with a script that looked like:

setList, paramList = [','.join(k + '= ?' for k, _ in payload.iteritems()), [v for _,v in payload.iteritems()]]
query = 'UPDATE MY_TABLE {} WHERE id = ?'.format(setList)
paramList.extend(rowId)
system.db.runPrepUpdate(query, paramList)

It's essentially the same as what @bkarabinchak.psi already posted, just uses a different string formatting method. His is also more generic allowing you to also change the table.

2 Likes