Script Error:-system.db.runPrepUpdate Cant connect to different database than to project default database?

Ignition
1

Can any one help me to understand the syntax error that i am using in this script
Action performed Execution error that the ignitionhistorydb.prddp' doesn't exist

database connection named as db and connected URL to database called production
Project default database is ingtionhistorydb.
prdp is a table of production database

Shift=event.source.parent.getComponent('Shift').selectedValue
Product=event.source.parent.getComponent('ProductList').selectedStringValue
Qty=event.source.parent.getComponent('Qty').intValue
IdPrdDP=201983245

txID=system.db.beginTransaction()
system.db.runPrepUpdate("INSERT INTO prddp (Shift, Product, Qty, IdPrdDP) VALUES (?,?,?,?)", [Shift,Product,Qty,IdPrdDP])
system.db.commitTransaction(txID)
system.db.closeTransaction(txID)
'

'Traceback (most recent call last):

File "event:actionPerformed", line 7, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO prddp (Shift, Product, Qty, IdPrdDP) VALUES (?,?,?,?), [3, PRD-A, 231, 201983245], , , false, false)

caused by Exception: Error executing system.db.runPrepUpdate(INSERT INTO prddp (Shift, Product, Qty, IdPrdDP) VALUES (?,?,?,?), [3, PRD-A, 231, 201983245], , , false, false)
caused by GatewayException: SQL error for "INSERT INTO prddp (Shift, Product, Qty, IdPrdDP) VALUES (?,?,?,?)": Table 'ignitionhistorydb.prddp' doesn't exist
caused by SQLSyntaxErrorException: Table 'ignitionhistorydb.prddp' doesn't exist

Ignition v7.5.14 (b1594)
Java: Oracle Corporation 1.8.0_211
'

2

txID=system.db.beginTransaction(database="production")

3

Jpart,
Thanks for your response and I did the same as you said , but now the error looks like below.

"Traceback (most recent call last):

File “event:actionPerformed”, line 7, in

java.lang.Exception: java.lang.Exception: Error executing system.db.beginTransaction(production, 2, 30000)

caused by Exception: Error executing system.db.beginTransaction(production, 2, 30000)
caused by GatewayException: Datasource "production" does not exist in this Gateway.
caused by SQLException: Datasource "production" does not exist in this Gateway.

Ignition v7.5.14 (b1594)
Java: Oracle Corporation 1.8.0_211

"

4 
txID=system.db.beginTransaction(database = "IGNITION DATABASE CONNECTION NAME HERE")
system.db.runPrepUpdate(“INSERT INTO prddp (Shift, Product, Qty, IdPrdDP) VALUES (?,?,?,?)”, [Shift,Product,Qty,IdPrdDP], tx = txID)
system.db.commitTransaction(txID)
system.db.closeTransaction(txID)
5

J, Thanks for the help and is working fine…Great. job!

6

J,
Would help me to understand what is wrong in this first syntax?
Cant we use argument and parameters runScalarQuery?

Blockquote

system.db.runScalarQuery("SELECT idproductList FROM productlist WHERE productList = (?)",[Product],tx=txID1)
Result# QLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?)' at line 1

system.db.runScalarQuery("SELECT idproductList FROM productlist WHERE productList = 'PRD-A',tx=txID1)

Result# 10 , which is correct

Blockquote

7

No, you cannot. But there's system.db.runScalarPrepQuery() for that.

1 Like
8

Phil, Thanks of the response.
But is that not available on 7.5? Also, I dont see any examples under 7.8 knowledge base (system.db.runScalarPrepQuery - Ignition User Manual 7.8 - Ignition Documentation)
Is that some thing new on 7.9?
I get an error on 7.5
AttributeError: 'com.inductiveautomation.ignition.common.script.Scr' object has no attribute 'runScalarPrepQuery'

Blockquote

Product = event.source.parent.getComponent("ProductList").selectedStringValue
system.db.runScalarPrepQuery("SELECT productlist FROM idproductlist WHERE idproductList = ?", [Product])

9

Ah, sorry. Missed that you are using v7.5. My advice would be to upgrade to a supported version.... /-:

2 Likes
10

Phil 8.0 has Namedquery and is much easier on that.

11

if transaction is aborted, then i need to record that data into some table .
or
Need to open a pop up and display that to corresponding Operator.

how to do that.