SQL Insert Issues

Ignition
1

Good Morning,

I am trying to do a SQL Insert through Scripting. I have it set on the Action performed of a button. Unfortunately I can’t seem to get it to work.

Here is the script I have in place:

PO = "CT/Variables/PO Number"
Model = "CT/Variables/Model Number"
PL = "[Client]Production Line"
St = "[Client]Station"
Min = "5"
DT = "123456"

Query = "INSERT INTO Ign_CT_PauseRecords (PONumber, ModelNumber, ProductionLine, Station, Minutes, DateTime) VALUES (?, ?, ?, ?, ?, ?)"
args = [PO, Model, PL, St, Min, DT]

system.db.runPrepUpdate(Query, args)

Each Time I run the application i just get a huge list of error details I can’t understand.

07:31:39.330 [AWT-EventQueue-2] ERROR com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter - <HTML>Error executing script for event:&nbsp;<code><b>actionPerformed</b></code><BR>on component:&nbsp;<code><b>Button</b></code>.
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last):
  File "<event:actionPerformed>", line 16, in <module>

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO Ign_CT_PauseRecords (PONumber, ModelNumber, ProductionLine, Station, Minutes, DateTime) VALUES (?, ?, ?, ?, ?, ?), [CT/Variables/PO Number, CT/Variables/Model Number, [Client]Production Line, [Client]Station, 5, 123456], , , false, false)

Any assistance would be greatly appreciated, and thank you in advance.

2

On the error popup, please select full details, and cut and paste the entire exception here. (Use triple-back-tick quotes on lines before and after the text so it’ll format nicely.)
In the meantime, I’m going to make an educated guess: you are expecting the strings containing tag paths to be converted to values, and you wan’t those values to be inserted, not the strings. But jython doesn’t magically know those strings are tag paths – it’s expecting those columns in your DB to be varchar or text types.
You need a call to system.tag.read*() to request the values for those tag paths, and put the QV.value variables in your insert call.

1 Like
3

I got the tag values using the system.tag.read. I used the print command to verify the variables values.

PO = system.tag.read("CT/Variables/PO Number")                 #Corrected
Model = system.tag.read("CT/Variables/Model Number")       #Corrected
PL = system.tag.read("[Client]Production Line")                     #Corrected
St = system.tag.read("[Client]Station")                                    #Corrected
Min = "5"
DT = "123456"

#print(PO)
#print(Model)
#print(PL)
#print(St)
#print(Min)
#print(DT)

Query = "INSERT INTO Ign_CT_PauseRecords (PONumber, ModelNumber, ProductionLine, Station, Minutes, DateTime) VALUES (?, ?, ?, ?, ?, ?)"
args = [PO, Model, PL, St, Min, DT]

print(Query,args)

system.db.runPrepUpdate(Query,args)

I have found a flaw I still don’t know how to get around.

PL = system.tag.read("[Client]Production Line")
This returns the Tag Value, Quality, and Time Stamp. Then it tries to put it in my query as such:
TC Bath, Good, Mon Apr 30 08:51:55 EDT 2018
My SQL table can’t handle that. All I want to get for a variable is the Value “TC Bath”

Any Ideas on this one?

4

system.tag.read and system.tag.readAll return qualified values, not just the data point itself. Please take a closer look at the documentation I linked. You should have something like this:

PL = system.tag.read("[Client]Production Line").value

Also, please edit your comment so your code has triple-backquotes on a line before and on a line after, so it is readable.

1 Like
5

Working Good. Thank you for you time and help. I do appreciate it.

6

Is this what you mean by the triple-back-quotes?

PO = system.tag.read("CT/Variables/PO Number").value
Mod = system.tag.read("CT/Variables/Model Number").value
PL = system.tag.read("[Client]Production Line").value
St = system.tag.read("[Client]Station").value
Min = "5"
DT = "123446"
1 Like
7

Ahhhh I see what it does. Thanks again for all you help.

1 Like
8

You can edit your earlier comments to make them neat with the triple-backquotes, too.

1 Like