Sending an email alert and searching for the mail address in a SQL database

Ignition
1

Hello,

I started working on Ignition v7.9 last week and i have a problem that i cannot seem to solve so far.

I created a Tag whose purpose is to send an email when its alarm is active. The tag is a boolean and the alarm activates when the value is 1.

Here is the script of the initial Alarme Active (i tested it by writing the value to 1 and got both the mail alert, so no problem so far) :

def alarmActive(tagPath, alarmName, alarmEvent, alarmPath, missedEvents) :

    import datetime
	import hashlib
	import time
		
	####################### ALARME IGNITION ########################

	name = ""
	display = "Home"
	date = system.date.format(system.date.now(),"dd-MM-yyyy HH:mm:ss")
	a = system.tag.getAlarmStates("ESS/ALA")
	for b in a:
		for c in b.getAlarmProperties():
			if (c.property == "name"):
				name = c.value
	
	smtp_server = 'smtp_profile'
	
	# Body of the email
	
	body = "<HTML><H1> !!! ALARM !!! <H1><BODY><p>Name of the alarm : " + name + "</p>"
	body += "<p> Site : " + display + "</p>"
	body += "<p> Date : " + str(date) + "</p>"
	body += "</BODY></HTML>"
	
	# Email send by the smtp service
	
	recipients = ['blabla@gmail.com']
	
	system.net.sendEmail(smtpProfile=smtp_server, fromAddr="noreply@gmail.com", subject="Alarm", body=body, html=1, to=recipients)

Now, what i want to do is being able to search for the email adresses in a (Microsoft) SQL database. I created a table called “fiche_contacts” in the database which currently has one line with (among other things) a column called “email”. I then changed the end of the code above to :

# Email send by the smtp service

email_query = "SELECT email FROM dbo.fiche_contacts"
recipients = []
resPds = system.db.runQuery(email_query)

resUStr = resPds[0][0]           
resStr = resUStr.encode("utf-8")     # Converting unicode to str

recipients.append(resStr)

system.net.sendEmail(smtpProfile=smtp_server, fromAddr="noreply@gmail.com", subject="Alarm", body=body, html=1, to=recipients)

Unfortunately, this does not work… The most surprising is that if i run this code in the Script Console, i get the alarm email (which means the code by itself works) !

Does anyone see a solution to this problem ?

Thank you in advance,

2

I would try specifying the database connection in the runQuery call.

resPds = system.db.runQuery(email_query,'yourDbName')

In a project scope leaving the database name as an empty string results in the projects default database being used. I don’t believe the same is true in the gateway scope.

1 Like
3

That was it ! Thank you very much @lrose :smiley: