# Passing different datatypes tp a named query

**URL:** <https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067>\
**Category:** Ignition\
**Created:** [October 27, 2020, 8:10pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067 "2020-10-27T20:10:54Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 27, 2020, 8:10pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/1 "2020-10-27T20:10:54Z")

</div>

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.

```auto
#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

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [October 27, 2020, 8:43pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/2 "2020-10-27T20:43:17Z")

</div>

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?

```auto
    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')

```

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 27, 2020, 9:18pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/3 "2020-10-27T21:18:31Z")

</div>

@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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/6/86b612fe59db128018afffecc6552a159f500a61.png)

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/4/9415428bb3ea0e58fc21aebf04961682685012e4.png)

So I can pass the value of binder\_name as a string, but I cannot pass the archive as a string.

Thanks, Steven

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [October 27, 2020, 9:45pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/4 "2020-10-27T21:45:10Z")

</div>

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](https://docs.inductiveautomation.com/display/DOC80/system.db.runUpdateQuery)) (or `runPrepUpdateQuery()` )and just building the query string from the values you’re already getting.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 27, 2020, 9:50pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/5 "2020-10-27T21:50:06Z")

</div>

> [@cmallonee](#):
>
> I think you’d be better off using `system.db.runUpdateQuery()` ([docs](https://docs.inductiveautomation.com/display/DOC80/system.db.runUpdateQuery)) (or `runPrepUpdateQuery()` )and just building the query string from the values you’re already getting.

I will look at this tonight.

Thanks, Steven

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 2:08pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/6 "2020-10-28T14:08:36Z")

</div>

> [@cmallonee](#):
>
> I think you’d be better off using `system.db.runUpdateQuery()` ([docs](https://docs.inductiveautomation.com/display/DOC80/system.db.runUpdateQuery)) (or `runPrepUpdateQuery()` )and just building the query string from the values you’re already getting.

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

```auto
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

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [October 28, 2020, 2:42pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/7 "2020-10-28T14:42:18Z")

</div>

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

```auto
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:

```auto
    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))
        

```

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 3:15pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/8 "2020-10-28T15:15:12Z")

</div>

> [@cmallonee](#):
>
> ```auto
> NSERT 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:
> 
> ```auto
> 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...

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

```

Thanks so much for the help!!

Steven

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [October 28, 2020, 3:30pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/9 "2020-10-28T15:30:56Z")

</div>

[This](https://www.python.org/dev/peps/pep-3101/) 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:

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

```

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [October 28, 2020, 3:39pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/10 "2020-10-28T15:39:07Z")

</div>

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](https://docs.inductiveautomation.com/display/DOC80/system.db.runPrepUpdate)

Reformatted your query will look this like:

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 28, 2020, 3:43pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/11 "2020-10-28T15:43:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [October 28, 2020, 3:44pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/12 "2020-10-28T15:44:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 4:04pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/13 "2020-10-28T16:04:31Z")

</div>

@cmallonee,

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

> [@cmallonee](#):
>
> [This](https://www.python.org/dev/peps/pep-3101/) 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:
> 
> ```auto
> system.db.runUpdateQuery("UPDATE binder_names SET binder_name={0} WHERE id={1}".format(newValue, id))
> 
> ```

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

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [October 28, 2020, 4:18pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/14 "2020-10-28T16:18:53Z")

</div>

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.

```auto
# 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.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 5:06pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/15 "2020-10-28T17:06:16Z")

</div>

> [@cmallonee](#):
>
> 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.

ok, this is for a string...

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

```

it produces this error...

```auto
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))

```auto
This works as expected.

I want to learn each way so I better understand.

Thanks for the explanations.

Steven
```

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [October 28, 2020, 5:21pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/16 "2020-10-28T17:21:25Z")

</div>

> [@steven.cox](#):
>
> Is %b for a string?

`%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](https://pyformat.info/) under the "Value Conversion" portion.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 5:24pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/17 "2020-10-28T17:24:50Z")

</div>

@cmallonee,

So this one works for the boolean…

```auto
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

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [October 28, 2020, 5:31pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/18 "2020-10-28T17:31:54Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/f/5f2be524e936cfdebf30add0208fb64a26d1e77f.png)  
(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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/7/078f3c1236329492400c7dafe4bdc9541088f040.png)

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

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 5:33pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/19 "2020-10-28T17:33:31Z")

</div>

> [@cmallonee](#):
>
> `%b` is actually for the Binary type. `%s` is for Strings. I made a faulty assumption when I first suggested `%b` .

Ok, this works.

```auto
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

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 28, 2020, 5:44pm UTC](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067/20 "2020-10-28T17:44:44Z")

</div>

@bschroeder, @paul-griffith, & @cmallonee

> [@paul-griffith](#):
>
> `runPrepUpdate` is strictly better, there’s basically no reason to ever _not_ use it.

> [@bschroeder](#):
>
> So not to throw a monkey wrench into this but I would suggest using the system.db.runPrepUpdate rather than the system.db.runUpdateQuery.

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

```auto
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

[Next page](https://forum.inductiveautomation.com/t/passing-different-datatypes-tp-a-named-query/40067.md?page=2)