Database Source Not Found

Ignition
, ,
1

I have been breaking my head over this for a couple of hours now and I can't figure out what is wrong with this I am trying to make a simple db update query but I keep getting an error everytime I run it this is the script:

def runAction(self, event):
	# Define the query with placeholders
	queryName = "UPDATE levelChecker SET width = '5.6' WHERE IDValue = 'A55638U89'"
	
	# Define the arguments as a list
	#args = [self.view.params.Value]
	
	databaseName = "DBSecondaryConnection"
	
	# Run the query and get the number of rows affected
	rows = system.db.runUpdateQuery(query = queryName, database = databaseName, tx = 5000)

and here is the error:

	Error running action 'dom.onClick' on screens/popups/databaseEditorPopup@6n8lW90/root/FlexContainer_0/Button: Traceback (most recent call last): File "<function:runAction>", line 11, in runAction at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(UPDATE levelChecker SET width = '5.6' WHERE IDValue = 'A55638U89', Transaction datasource unknown, 5000, false)

I looked up the error and it is saying it is related to the fact that the database connection doesn't exist. But I doublechecked the name and even did a script to get the name of it. i am also pulling information and inputting information to it from transaction groups and tag queries. I am doing in it scripting because eventually this script needs be dynamic where the table name, column and IDValue change. Anyone have any ideas on why this isn't working?

1 Like
2

Consider setting up a Named Query. They allow you to specify the database connection and tests the query aswell. The its as simple as running the system.db.runNamedQuery() function.

3

You're providing a tx, which expects a transaction id. Where does it come from ? you're passing 5000, which doesn't look like a transaction id to me, more like a timeout delay.

If you did mean to supply a transaction id, make sure it's correct and the transaction is opened.
Otherwise, remove that parameter.

4 Likes
4

I addition to what @pascal.fragnoud said, if for whatever reason you don't want to use a named query, and instead wish to do it all in script, then you should be using system.db.runPrepUpdate() that would look like this:

def runAction(self,event):
    query = 'UPDATE levelChecker SET width = ? WHERE IDValue = ?'
    args = [5.6,'A55638u89']
    databaseName = 'DBSecondaryConnection'
    system.db.runPrepUpdate(query,args,databaseName)
2 Likes
5

Side note: Many older IA functions do not accept keyword arguments, or don't accept them for the mandatory args. (system.db.runUpdateQuery is a very old function.) Newer functions have full support for keyword arguments, and usually say so in the documentation.

6

Correct me if I am wrong, but you have specified the DB name, but aren't actually calling it in the system.db call?

7

Nope, not wrong. Corrected code.

1 Like
8

Ok that seemed to work thank you. However, this brings me to my next issue I want to be able to dynamically pass the column name as well. I changed my code to this but I don't get an error thrown but it also doesn't pass the update to the db:

    query = 'UPDATE levelChecker SET ? = ? WHERE IDValue = ?'
    args = [self.view.params.Column, self.getSibling("NumericEntryField").props.value, self.view.params.DPIN]
    databaseName = 'DBSecondaryConnection'
    system.db.runPrepUpdate(query,args,databaseName)
9

Column names aren't values. In SQL, they are part of a query's structure, which is used to prepare the execution plan. So they have to be injected into the query string itself, not passed as a parameter. Because of the high risk associated with this, you should always check such a string substitution against the known column names for a table. (Get them from your DB's information_schema if you want to automate it.)

1 Like
10

Yes. That makes sense I had forgotten about that. Everything is working now as expected. Thank you everyone's help!