EasyCFM.COM ColdFusion Forums / Coding Help! / CFPOP and Insert to DB

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: CFPOP and Insert to DB

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

Timbo
10-23-2002 @ 4:28 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 5
Joined: Oct 2002

I know this is basic stuff for most but since I'm just getting started, I'm totally lost.

Anyway, I'd like to download the subject, time and message number from my POP3 server and then insert the results into the appropriate DB fields and then delete the mail messages on the server.

1. Retrieve MESSAGENUMBER, SUBJECT, DATE
2. Insert these three data values into the appropriate DB fields
3. Delete messages off of mail server.

So far I've accomplished retrieving the email header information and outputting it on my page as MESSAGENUMBER,SUBJECT,DATE (no space, comma separated).

Now, inserting and deleting the messages is where the problem begins.  I have no idea where to start.

Make sense?  Gosh I hope so.  Anyway, any help or direction would be greatly appreciated.

Timbo

Webmaster
10-23-2002 @ 4:37 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

You would insert them to the database like you would any other data. There's a tutorial on this site that explains how you insert data into a database:

http://www.easycfm.com/tutorials/index.cfm?tutorial_id=1

If you need further help, please let me know.

Thanks,
Pablo Varando
EasyCFM.COM, LLC.

Timbo
10-23-2002 @ 4:47 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 5
Joined: Oct 2002

Thanks for your quick response Pablo!

So if I must use form elements, then I most likely have to set the value of these elements to the email fields retrieved?  Then automatically post the form on the update of the last field?

I'm taking shots in the dark here so bare with me.  I could be getting anywhere from 100-1000 emails and hour to the server.  So this process should be automated and running 24x7.  If you think I'm on the right track, please let me know.

Thanks again Pablo.

Timbo
  

Webmaster
10-23-2002 @ 9:18 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

You wouldn't use form elements, you would instead use:

<!--- retrieve the message headers.... --->
<CFPOP..... name="EMails">

<cfloop query="EMails">
  <cfquery name="qInsertEmails" datasource="MyDSN">
    INSERT INTO EMailsTable(Subject,From)
    VALUES('#Subject#','#From#')
  </cfquery>
</cfloop>

Keep in mind that this is just an example and will not work as is, just meant to show you what I mean...


Thanks,
Pablo Varando
EasyCFM.COM, LLC.

Timbo
10-24-2002 @ 12:41 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 5
Joined: Oct 2002

Pablo,

Yee-ha!  It works!!!  At least when I use one data field that is.  When I add the date field or from field, I get a syntax error.

The error is: Syntax error in INSERT INTO statement.

The code is:

<CFPOP SERVER="mail.servername"
     USERNAME="username"
     PASSWORD="password"
     ACTION="GetHeaderOnly"
     Name="EMails">

<cfloop query="EMails">
     <cfquery name="qInsertEmails" datasource="emailtitles">
     INSERT INTO tblnames(subject,date)
     VALUES('#subject#','#date#')
     </cfquery>
</cfloop>


I’ve changed the second field to #from# and the error still occurs.

Also, I was trying to implement a delete routine to follow right after the insert.  No luck there either.  Do I just repeat the same CFPOP routine as above but change the ‘Action’ statement?  Doesn’t seem to work.  FYI, I put this routine inside the <cfloop> above.


     <CFPOP SERVER="mail.servername"
     USERNAME="username"
     PASSWORD="password"
     ACTION="Delete"
     Name="DeleteEmails"
     MESSAGENUMBER=#Emails.messagenumber#

Thanks again for your help Pablo!


Timbo
10-24-2002 @ 1:20 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 5
Joined: Oct 2002

Figured out the problem.  I was using a reserved word (date) in my database.  I renamed it and both fields come in fine.

Now to figure out the delete routine...

Tim

Timbo
10-24-2002 @ 1:30 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 5
Joined: Oct 2002

Okay, got the delete to work but it only deletes the first messagenumber returned from EMails.  How do I get it to continue through the loop?


<CFPOP SERVER="mail.servername.com"
     USERNAME="username"
     PASSWORD="password"
     ACTION="GetHeaderOnly"
     Name="EMails">

<cfloop query="EMails">
     <cfquery name="qInsertEmails" datasource="emailtitles">
     INSERT INTO tblnames(subject, emaildate)
     VALUES('#subject#', '#date#')
     </cfquery>

<CFPOP SERVER="mail.servername.com"
     USERNAME="username"
     PASSWORD="password"
     ACTION="Delete"
     Name="DeleteEMails"
     MESSAGENUMBER=#Emails.messagenumber#>

</cfloop>




Website Designed and Developed by Pablo Varando.