I am trying to use an alarm event property when using a calculated roster, the script is:
value = event[“propertyname”]
data = system.db.runPrepQuery("query")
for row in data:
builder.username(row["username"]).email([row["contact_value"]]).add()
userList = builder.build()
return userList
I’m trying to use the value as a parameter in my database query and if I replace the parameter with a static number it seems to work fine. If I try using the script above, I get the error:
Roster '<calculated>' has no users, notification for 1 alarms will go undelivered.
I would appreciate your help/suggestions in this regard. Thank you!
value= event["propertyname"]
data = system.db.runPrepQuery("SELECT contact, username FROM UserContact where location_id= ?)",[value])
“location_id” is not necessary a single value and can be multiple values.
Any idea how I can type cast my location_id variable to the correct data type?
I have to mention that in the alarm property, the location_id parameter is an integer.
I take this to mean that you are passing in a list of location ID's. If that is the case then your query syntax is incorrect and you will need to dynamically modify the query to accept multiple values.
SQL does not understand a List of integers in the same way that python does, so you need to give it a little help.
The query should look something like:
"SELECT contact, username FROM UserContact WHERE location_id IN (?)"
Where the number of parameters changes based on the number of "id's" your looking for.
Something like this:
value = event['propertyname']
query = 'SELECT contact, username FROM UserContact WHERE location_id IN (%s)' % (','.join(['?'] * len(location_id)))
data = system.db.runPrepQuery(query,location_id)
for row in data:
builder.username(row['username']).email([[row'contact_value']]).add()
userList = builder.build()
return userList
You are exactly correct!
So then if I modify my query to:
value = event['propertyname']
data = system.db.runPrepQuery ("SELECT contact, username FROM UserContact WHERE location_id IN ?)",[value])
for row in data:
builder.username(row['username']).email([[row'contact_value']]).add()
userList = builder.build()
return userList
Even tried the 2nd version and they should both work, but they don’t, I still get the same error.
Is value a python list? If not then there is no need to use IN, you can just use standard syntax 'SELECT contact, username, FROM UserContact WHERE location_id = ?'
Your SQL syntax is incorrect, the () arround the parameters are requiered when using the IN Operator.
locationid= event['locationid']
query='SELECT contact, username, FROM UserContact WHERE location_id = ?'
data = system.db.runPrepQuery (query,locationid)
for row in data:
builder.username(row['username']).email([[row'contact_value']]).add()
userList = builder.build()
return userList
locationid= event['locationid']
query='SELECT contact, username, FROM UserContact WHERE location_id IN (?)'
data = system.db.runPrepQuery (query,locationid)
for row in data:
builder.username(row['username']).email([[row'contact_value']]).add()
userList = builder.build()
return userList