Hiding gmail password in an email xbasic function and a couple other xbasic questions

[twwtc]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

Do I even need a pause in this email function or will gmail put all (maybe 700 to 1000) emails in a que and send in a matte of time? (an hour or so)
Thanks
Tony

[twwtc]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

I have finally been able to get back to work on my sending email UX. I think all is working as it should with one exception. I need to be able to "pause" sending emails to allow mail program to keep up.
In my old dbf application (as you can see from my initial post) this was done thru "cycle-sleep-cycle" for lack of better terms on my part.

How is this done in SQL?

Below is my current working code that was pretty much written or explained thru this thread:

function emailSendSMTP as c (e as p)

dim cn as sql::Connection
dim flag as l
dim sqlStmt as c
dim rs as sql::ResultSet
'dim args as sql::arguments
dim cntEmail as n = 0
dim pm as P
dim ps as P

'change this to your AlphaDAO connection string name
flag = cn.open("::Name::Golf")

if flag then

'args.set("argSelectemail","No")
sqlStmt = "SELECT email from customers WHERE email <> '' and Notify = 'E' OR email <> '' and Notify = 'B';"

'first get a record count
flag = cn.Execute(sqlStmt)
if flag then
'ALL OK
rs = cn.ResultSet
while rs.NextRow()

'send your emails
if (email_smtp_open(ps, "smtp.gmail.com", 465, ""[email protected], "MyPassword","SSL"))then
pm.to = rs.data("email")
pm.from = "MyName"
pm.subject = e._currentRowDataNew.subject
pm.html_message = e._currentRowDataNew.messageBody
email_smtp_send(pm, ps)
end if
email_smtp_close(ps)

cntEmail = cntEmail + 1
end while
emailSendSMTP = "emailResult('" + cntEmail + "');"

else
'there was an error - close the connection and exit
cn.Close()
dim msg as c
msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
emailSendSMTP = jscmd
exit function
end if

else
'there was an error - close the connection and exit
dim msg as c
msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
emailSendSMTP = jscmd
exit function
end if

cn.FreeResult()
cn.close()


end function

Thanks
Tony

[twwtc]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

Not at this point, will continue working on it and post back when I make some progress.
Thanks!

[Davidk]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

Yes... as long as you have a carrier that is not blank AND you have a member with the word 'Yes'... you'll get record(s) returned. Is member actually a character field?

You ask as though it's not working... what's wrong? I usually uppercase key words... which is what you've done for everything except the word 'and'.

[twwtc]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

Sorry, but two hours later ......

should this work?

"SELECT fulltxtemail,member FROM texts WHERE carrier <> '' and member = 'Yes';"

[twwtc]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

David, the second method worked!
Peter, I am going to try use Mandrill instead of Gmail.
Thanks!!

[Peter.Greulich]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

You can hide your password in a function and then call the function. Put OPTION ENCRYPTED_TOKENS in the first line of your function. Then no one should be able to read the aex.

[Davidk]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

No args required... and you have to be careful of what you ask for. This is probably what you want...

sqlStmt = "SELECT fulltxtemail from texts WHERE carrier IS NOT NULL;"


Sometimes... depending on the circumstances...

sqlStmt = "SELECT fulltxtemail from texts WHERE carrier <> ''; "

[twwtc]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

David and others ....

How do you word the "args.set" and/or "sqlStmt" to filter results to only include records where field "Carrier" is not blank
(old dbf method I think is isnotblank("Carrier"))

I have tried IS NOT NULL and "ISNOT NULL" in the "args.set", but this does not work

args.set("argSelectEmail","??????")
sqlStmt = "SELECT fulltxtemail from texts WHERE carrier = :argSelectEmail;"

flag = cn.Execute(sqlStmt,args)
if flag then

rs = cn.ResultSet
while rs.NextRow() ...............

Thanks
Tony

[twwtc]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

David
Thanks for taking the time to help!
Tony

[Davidk]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

Just keep sticking with it... and you'll see very soon that it really makes sense. Here's your code... commented... but with some corrections. You've got the table and field reversed. You usually select fields from a table... so it actually is Select emailfield from tblemail

dim cntEmail(3) as n = 0 this is just setting up a count variable... to hold the number of emails we process

'change this to your AlphaDAO connection string name
flag = cn.open("::Name::NWSQLServer")

if flag then

args.set("argSelectEmail(3)","Yes") this is just an argument variable name... I like to give my argument names something a bit meaningfull.
sqlStmt = "SELECT email(1) from email(2) WHERE field_1(2) = :argSelectEmail(3);" here email(1) is the field you want, email(2) is the tablename and email(3) is that argument we just created. Alpha replaces the argument with the value (in this case "Yes") when the SQL Statement gets parsed.

'first get a record count
flag = cn.Execute(sqlStmt,args)
if flag then
'ALL OK
rs = cn.ResultSet
while rs.NextRow()
'send your emails
cntEmail(3) = cntEmail(3) + 1 here we just add 1 to our processed email counter
end while
emailSendSMTP = "email(3)Result('" + cntEmail(3) + "');" when all done, the ajax callback send back this client-side javascript command... to run the Javascript function called emailResult... and we give this function a parameter... cntEmail... which the Client-side can read and use.

[twwtc]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

David

I used your example and it appears to be working, thanks!

This all appears really complicated, but I would like to start picking up a little along the way.

This example is a little difficult with all the use of "email" in the code. (my mistake)
I highlighted each use of "email" and was wondering if you check my opinion on what each refers to:

1. table
2. field
3. arbitrary name

Thanks!
Tony



function emailSendSMTP as c (e as p)

debug(1)


dim cn as sql::Connection
dim flag as l
dim sqlStmt as c
dim rs as sql::ResultSet
dim args as sql::arguments
dim cntEmail(3) as n = 0

'change this to your AlphaDAO connection string name
flag = cn.open("::Name::NWSQLServer")

if flag then

args.set("argSelectEmail(3)","Yes")
sqlStmt = "SELECT email(1) from email(2) WHERE field_1(2) = :argSelectEmail(3);"

'first get a record count
flag = cn.Execute(sqlStmt,args)
if flag then
'ALL OK
rs = cn.ResultSet
while rs.NextRow()
'send your emails
cntEmail(3) = cntEmail(3) + 1
end while
emailSendSMTP = "email(3)Result('" + cntEmail(3) + "');"

else
'there was an error - close the connection and exit
cn.Close()
dim msg as c
msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
emailSendSMTP = jscmd
exit function
end if

else
'there was an error - close the connection and exit
dim msg as c
msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
emailSendSMTP = jscmd
exit function
end if

cn.FreeResult()
cn.close()


end function

[Davidk]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

The statement... "SELECT email from email WHERE field_1 = :argSelectEmail;" is returning just 1 column.

rs is set up as a ResultSet Object. Everything returned from SQL in in this object. One of the properties in rs is data.

pm.to = rs.data(1)

or

pm.to = rs.data("email")

[twwtc]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

David
How do I get email addresses from into the pm.to =????
From my old example "pm.to = t.Email"
Thanks
Tony

[twwtc]

Re: Hiding gmail password in an email xbasic function and a couple other xbasic questions

Thanks for all the replies, advice, and especially the specific code example! This will be a great help!
I looked into Mandrill and signing up is much more intensive after Dec 2015. Will try to tackle that another day.
Thanks again!
Tony