Passing different datatypes tp a named query

Hi all,

This may be another newbie question, but I have not found a solution.

I am setting up a power table to be able to edit entries in my database table using a named query.

I have put this together.

#Get the query parameters
	tableName = self.tableName
	colName = colName
	newValue = newValue
	#get the id value of the row changing.
	id = self.data.getValueAt(rowIndex, 0)
	
	system.db.runNamedQuery('table_edit', {'tableName' :tableName, 'colName' :colName, 'newValue' :newValue, 'id' :id})

	system.db.refresh(self, 'data')

Now, this works as long as I am passing a string in newValue. HOWEVER, one of my columns is a boolean. The datatype for newValue in the query is string.

I could run a different query based on the column name, but I am trying to use the same named query to update multiple tables, hence the tableName property.

Can I dynamically change the datatype in the query?

There are only 2 columns that would change, one is string, the other boolean.

Any help or ideas will be greatly appreciated.

Thanks, Steven

Instead of changing the type in the Named Query, why don’t you convert the Boolean value to a String in your script before passing the value?

    tableName = self.tableName
	colName = colName
	newValue = str(newValue). # convert this to always be a string value
	id = self.data.getValueAt(rowIndex, 0)
	
	system.db.runNamedQuery('table_edit', {'tableName' :tableName, 'colName' :colName, 'newValue' :newValue, 'id' :id})

	system.db.refresh(self, 'data')

@cmallonee,

I’m sorry I did not explain it properly. The datatype in the database table is expecting a boolean.

One of the columns in the power table is a string, the other is a boolean. The newValue parameter in the query is a string.

image

So I can pass the value of binder_name as a string, but I cannot pass the archive as a string.

Thanks, Steven

Ah, I see. So the problem is that the NamedQuery will only work for certain tables, because the expected datatype of the column could change, but not the type of the incoming value.

To my knowledge there is not a way to make this “update-via-Named Query” work for all possible scenarios. I think you’d be better off using system.db.runUpdateQuery() (docs) (or runPrepUpdateQuery() )and just building the query string from the values you’re already getting.

I will look at this tonight.

Thanks, Steven

This is a great idea, but I cannot seem to get the syntax correct.

system.db.runUpdateQuery("UPDATE binder_names SET binder_name = '{newValue}' WHERE id = {id}")

It appears not to like the id parameter. What am I doing wrong?

Thank, Steven

You’re dealing with Python now, so formatting is handled differently:

system.db.runUpdateQuery("INSERT INTO RecipeSteps (StepNum, Gallons) VALUES (%d, %f)" % (nextStepNum, gallons))

See how the different types (double and float in this case) are passed in with a representation of their type?
So you’d want something like this:

    tableName = self.tableName
	colName = colName
	newValue = newValue
	#get the id value of the row changing.
	id = self.data.getValueAt(rowIndex, 0)
    system.db.runUpdateQuery("UPDATE binder_names SET binder_name=%b WHERE id=%d" % (newValue, id))
        

Where can I get better examples or documentation on the %b, %d, & %f? This does not work but if I had a reference I could study up. I searched the Python tutorial and the Ignition manual and cannot find anything.

I am assuming the order the variables are called is the same as the order they are listed in the values list and the % indicates that is the list of values.

I still get this error…

File "<extension-method onCellEdited>", line 32, in onCellEdited
ValueError: unsupported format character 'b' (0x62) at index 37

Thanks so much for the help!!

Steven

This is a pretty good reference, and it uses the form I’m more familiar with: .format(). I think I gave you a bit of incorrect information. I think the %b should actually be %r. Try changing that and see if you get better results.

I would actually have written the query like this:

    system.db.runUpdateQuery("UPDATE binder_names SET binder_name={0} WHERE id={1}".format(newValue, id))

So not to throw a monkey wrench into this but I would suggest using the system.db.runPrepUpdate rather than the system.db.runUpdateQuery. Docs here: https://docs.inductiveautomation.com/display/DOC80/system.db.runPrepUpdate

Reformatted your query will look this like:

tableName = self.tableName
colName = colName
newValue = newValue
#get the id value of the row changing.
id = self.data.getValueAt(rowIndex, 0)
query = "UPDATE binder_names SET binder_name=? WHERE id=?"
args = [newValue,id]
system.db.runPrepUpdate(query = query, args = args)
2 Likes

runPrepUpdate is strictly better, there’s basically no reason to ever not use it.

huh… I thought it was the other way around. Learn something new every day.

@cmallonee,

Thanks for the resource. I have added that to my links for reference and study.

So changing the %b to %r produces this error…
SQL error for “UPDATE binder_names SET binder_name=u’Changed in ’ WHERE id=14”: ERROR: type “u” does not exist
Position: 37

newValue is “Changed in”. I print it to the console ahead of the update.

Using the .format version of the query yields this error…
SQL error for “UPDATE binder_names SET binder_name=Changed in WHERE id=14”: ERROR: syntax error at or near “WHERE”
Position: 49

Again, thanks for all the help!!

Steven

It looks like there’s still a disconnect in which column is being supplied here; you specified that you wanted a boolean value, which is what %r should be accepting during the format/insertion process - but u'Changed in ' shows that you’re supplying a string value for newValue. If you supply a true boolean value you should be fine. Basically, the insertion is not correct because the type of the value does not match.

The .format() error you encountered is due to the fact that you are still using a string for newValue. in this instance the insertion is a complete insertion of the value as a str but we’re not wrapping the value within the query. Again, this shouldn’t be an issue if you supply an actual boolean value. If you use this same structure for a STRING value, then you need to wrap the value in quotes within the query. When I do that, I prefer to use single quotes as the outer quotes and double quotes internally so that I don’t need to escape anything.

# This will supply your value to the query as a string every time
system.db.runUpdateQuery('UPDATE binder_names SET binder_name="{0}" WHERE id={1}'.format(newValue, id))

You could also switch to using runPrepUpdate() as the others recommended. The example from @bschroeder looks like it should work without any of this type conversion nonsense.

ok, this is for a string…

system.db.runUpdateQuery("UPDATE binder_names SET binder_name=%b WHERE id=%d" % (newValue, id))

it produces this error…

File "<extension-method onCellEdited>", line 32, in onCellEdited
ValueError: unsupported format character 'b' (0x62) at index 37

Is %b for a string?

This is for the boolean...

system.db.runUpdateQuery(“UPDATE binder_names SET archive=%r WHERE id=%d” % (newValue, id))

This works as expected.

I want to learn each way so I better understand.

Thanks for the explanations.

Steven

%b is actually for the Binary type. %s is for Strings. I made a faulty assumption when I first suggested %b.

The one that works (%r) only works because %r is accessing a “secret” function of the value: look at this documentation under the “Value Conversion” portion.

1 Like

@cmallonee,

So this one works for the boolean…

system.db.runUpdateQuery("UPDATE binder_names SET archive=bool({0}) WHERE id={1}".format(newValue, id))

Note I had to add the bool() function to the achive variable.

Thanks!!

Steven

If it works, it works, but I would expect that to fail because the boolean casting you put in place should be treated as part of a string - which it seems to be according to my test:
Screen Shot 2020-10-28 at 10.28.45AM
(see how “bool” is still present in the string?)

When I then take that string and attempt to use it in MySQL I get a failure:
Screen Shot 2020-10-28 at 10.27.17AM

Keep in mind that even if the function doesn’t throw an error you might be sending an incorrect value to your table.

Ok, this works.

system.db.runUpdateQuery("UPDATE binder_names SET binder_name='%s' WHERE id=%d" % (newValue, id))

Lots of moving parts to get the types just right. But I understand it much better now.

On to the runPrepUpdate now.

Thanks, Steven

@bschroeder, @PGriffith, & @cmallonee

So the runPrepUpdate is much easier and it works with both the string and the boolean.

query = "UPDATE binder_names SET binder_name=? WHERE id=?"
args = [newValue,id]
system.db.runPrepUpdate(query = query, args = args)

So is there a way to make the colName (binder_name) a variable in this case?

and since I am learning, the tableName as well?

Right now I use if statements to capture the col and the run that query. If I could use a variable I could just have one query.

I sure appreciate everyone’s help!!

Steven