I have a Insert Query execution method. Which is being used to insert the data in the database.
def onMessageReceived(self, payload):
user = self.session.props.auth.user.userName
if payload['confirm']:
data = payload.get('data', [])
# Prepare the insert data
insert_data = [
{
'BrassTag': row['BrassTag'],
'BrassTag_Desc': row['BrassTag_Desc'],
'user': str(user)
} for row in data if row['BrassTag']
]
# Build the query and values
q, v = build_insert_string("BrassTag", insert_data)
# Debugging: Print the query and values
system.perspective.print("Query: {}".format(q))
system.perspective.print("Values: {}".format(v))
# Specify the database name, it is important.
database_name = 'test_mdcmaster'
# Execute the query
if q:
system.db.runPrepUpdate(q, v, database_name)
#self.refreshBinding('props.data')
# Clear the table data after insert
self.getChild("Tbl_Insert_New_BrassTag").custom.BrassTag_Ins = []
self.getChild("Tbl_Insert_New_BrassTag").props.data = [{
"BrassTag": {
"editable": True,
"style": {
"backgroundColor": "white"
},
"value": ""
},
"BrassTag_Desc": {
"editable": True,
"style": {
"backgroundColor": "white"
},
"value": ""
}
}]
system.perspective.closePopup('Add_BrassTag')
In this Method I have Hardcoded the name of my Database, which is not a good practice because if I change my database then I have to update it in every script.
database_name = 'test_mdcmaster'
I'm trying to get the name of the default database using this approach
connectionInfo = system.db.getConnectionInfo()
dbType = connectionInfo.getValueAt(0, "Name")
system.perspective.print(dbType )
But not getting any results. Upon examining connectionInfo
I have received Perspective.Designer.Workspace -- Dataset [0R ? 10C]
What could be the issue here. Do I have to enable any settings?
Depending on the scope, if you are in Vision or Perspective, Database is optional, and use the default.
Edit: link to the docs system.db.runPrepUpdate | Ignition User Manual
While creating this script, the query wasn't working without specifying the database name, that's why I had to hard code it in the script.
There are a few ways you can do this and it depends on how you want your system to react when you change the database name.
- You can create a new session custom property called dbConn and place your database name in there. Then, set your Named Query Database Connection to
<Parameter>
and pass in the session custom property as a parameter into your named query, something like
params = {
"database": self.session.custom.dbConn,
.... other params here ...
}
system.db.runNamedQuery("path", params)
-
You can set a default database connection for a project, in the Project properties window. Then, you would specify the NQ Database Connection to be <Default>
.
-
You can create a memory tag that will hold your database connection name. This is especially useful if you need to execute a query in a global scope (e.g. tag change script) and want that database connection name to be dynamic as well.
What I did, I have defined a function in the Scripting.Project Library
def projConName():
connectionInfo = system.db.getConnections()
dbType = connectionInfo.getValueAt(0, "Name")
return dbType
and accessing the name wherever is needed.
database_name = defaultDatabaseConnection.projConName()
Is this a good approach.
I have also figured out that the issue was with the function name, I had to use getConnections()
not getConnectionInfo()
I don't think getConnections()
is getting the default project database... From what I can see in the docs, it gets all of the connections that are defined on the gateway. It looks like you may only have one, so in this case it works, but if what I'm assuming is true, I don't believe this is a good future-proof way to accomplish your task.
I'm interested to see why you have to pass in a database connection name if you have a project default database specified and you're calling system.db.runPrepUpdate
from the Perspective scope. Can you add a screenshot of your project configuration, specifically the "Default Database"
portion?
1 Like
Sure, but where can i find that configuration in Status
or Config
?
In the Designer, go to Project -> Project Properties. Then take a screenshot of the Project / General page.
If you notice, your default database connection is in italics, so it is not valid. That is probably why your calls were failing.
1 Like
So is db
different from test_mdcmaster
? Are those two different database connections? Also, as bschroeder pointed out, you either just changed those values and they haven't been saved yet or that field is not valid.
1 Like
No, Db is nothing
I only have 1 database connection established
Then you need to change your default database connection in the proj cr properties from db to the connection you want.
Then you should be able to remove the database name from your script and it'll use the default connection defined in the project properties.
I would try to select that test_mdcmaster
database in your Project Properties - it should show up in that dropdown. Its interesting that you don't have a connection called db
but it appears there. Almost like it came from a gateway backup or a project export.
@bschroeder @flexware Thanks to both of you the issue is fixed now. I don't have to specify the Database name in the script now.
2 Likes