Insert String type tag to SQL Server database

Ignition
1

I am having issues inserting a String type Query tag into a SQL Server database. The query is run from a Query tag, the string tag is another Query tag ("chemName"). The database column type for this tag is currently Text, I've tried varchar, with now difference.

Each time the query is executed, I get this error message ("acid" is the value of the String type Query tag):

Error_ExpressionEval("Invalid column name 'acid'.")

Query:

INSERT INTO chem_added
VALUES ({[.]PumpNum}, getdate(), {[.]ChemDisp_Batch}, {[.]chemName.value}, 'GAL')

Screenshot of DB table:

db table
db table965×104 10.5 KB

Related tags are expanded:
tags
tags517×1243 98.7 KB

Any help will be appreciated.

2

Don't use query tags where you need dynamic string arguments. And especially do not use query tags for INSERT or UPDATE operations. Full stop.

Use scripts with system.db.runPrepUpdate() where the strings are supplied via ? substitution, or use system.db.runNamedQuery() where your string arguments are supplied via value parameters.

4 Likes
3

Would it be better to run the query from a tag event script? The current configuration has the Query tag running the INSERT query in a tag group that is driven by a boolean status tag.

This project is the first time I've used databases.

4

If you only need to run it when the boolean changes, a gateway tag change event (not a tag valueChange event) is the right place. Share more about what you are doing if not that.

5

Triggering a Named Query from a Gateway Tag Change Event script is working. I would like to use this Gateway Event script to work with multiple instances of the UDT structure that contains all the relevant data.

Here's the Event script, it is triggered by a Value Change.
The Tag Path list contains each UDT trigger tag.

path = str(event.tagPath.getParentPath())

if not initialChange and newValue.getValue():
	name = str(system.tag.readBlocking(path+'/chemName')[0].value)
	pump = int(system.tag.readBlocking(path+'/PumpNum')[0].value)
	qty = float(system.tag.readBlocking(path+'/ChemDisp_Batch')[0].value)
	
	pram = {'chemName':name, 'qtyAdded':qty, 'pumpNum':pump, 'unit':'GAL'}
	system.db.runNamedQuery('Insert_ChemTotalData', pram)

This script is working for the first UDT instance only, I'm confused why it doesn't work for any of the others.

6

Did you save the project after updating the list of tag paths on the event?

7

Yes I did

8

You may need to add some logging. Consider moving your event script to a project library function so the logger can be cached. (Using one-liner delegation from events to project library functions is a best practice, for many reasons.)

9

I feel rely dumb right now, the query I was using to monitor the database was filtering for only one of the instances... The Event Script I posted earlier was working.

Thanks for the help @pturmel.

1 Like