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.
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.
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
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))
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)
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
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.
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.
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:
(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:
Keep in mind that even if the function doesn’t throw an error you might be sending an incorrect value to your table.
