Create email list from database

Ignition
1

I am trying to use the system.net.sendEmail to distribute emails dynamically from email addresses stored in DB.
I can type them in manually and the function works. When I try to dynamically build the list I get the following error “GatewayException: Could not send email because none of the recipients had valid email addresses.”

I see exactly why this is happening and so here is where I need help:

  • database has email addresses stored in the following format: bob@gmail.com
  • I run a scalar query to get the items and it returns the following format: “bob@gmail.com”,"tom@gmail.com"
    [query code] email = system.db.runScalarQuery(""“SELECT STUFF (( SELECT ',”’ + Email+ ‘"’ FROM SYS_EWO_Email where pillar = ‘%s’ FOR XML PATH (’’)),1,1,’’) as email"""%(pillar),“MFG_SVCS”)

here is where I need help. I don’t know how to turn this into a list.
What I am attempting to do is just add brackets to the unicode string:
to = “[”+email+"]"
this gives me [“bob@gmail.com”,"tom@gmail.com"] type unicode.

My overall question is how do I make a list (an email list) from the database?

2

Stop running a scalar query (which returns a single value). If you switch to sytem.db.run(Prep)Query, you will get a PyDataset of results back. PyDatasets act roughly the same as a nested Python list-of-lists; see the user manual.
So you can iterate your returned list directly and extract the list of email addresses that way.

I mentioned runPrepQuery above because any string substitution using %s is potentially subject to a SQL injection attack. runPrepQuery avoids this problem by issuing a proper prepared statement to the DB.

2 Likes
3

Thanks.
I was running a prepQuery up until this morning but for some reason I was stuck in my mind of needing to return them all in one query - hence the ‘for xml’.
I did as you advised and it is working.

1 Like
4

OK - ultimately I was trying to get to where I was sending a report out using the system.report.executeAndDistribute()
In the manual it clearly calls out " Note: To , cc , bcc , and replyTo must be Python lists.".
Using the code that worked for system.net.sendEmail fails with an error stating “$PyDataSet cannot be cast to org.python.core.PyList”.
-Just for clarification, I can type the emails in manually and the script works and sends email.

Any advice for this scripting function?

5

7.9 is worse at this in a couple crucial ways: for one, PyDatasets and PyRows don’t pretend to be lists very well, and most scripting functions don’t do very intelligent type-checking on what you pass into them.

The first thing I would try is list(yourData) to try to coerce it into a list. The second thing I’d try, if that doesn’t work, is a list comprehension: [value for value in yourData].