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?
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
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
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?
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]
.