Calculated roster throwing an error

Ignition
1

Hello all,

Happy New Year! :tada:

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!

1 Like
2

We’ll need to see your query, or an example of it if the actual query has sensitive information.

3

Thank you @lrose!

This is the query:

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.

4

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
1 Like
5

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.

6

That isn’t the same as the code I supplied.

  1. 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 = ?'

  2. Your SQL syntax is incorrect, the () arround the parameters are requiered when using the IN Operator.

1 Like
7

You are correct! That was a typo from my side.

I have tried both versions:
1)

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

I get the same error.

8

Is value a list?

9

You were correct @lrose. I had some subquery syntax issues and that’s why I did not get the correct result. Appreciate your help as always.

10

That doesn't look right. .email(row['contact_value']) maybe ?

1 Like