Pop3_get is used to retrieve and delete messages from a server running the Post Office Protocol version 3 as defined in rfc1725. In its default form it returns a vector of vectors containing messages retrieved from the POP3 server. Each vector within the vector contains a pair of VARCHAR UIDL and VARCHAR Message body, i.e. to get the message body of the second message retrieved, one would use aref (aref (msg_vec, 1), 1). Total length of messages retrieved will not exceed the value of buffer_size parameter in bytes.
The optional parameter command can be used to control output or delete messages. When command is passed a VARCHAR 'uidl', pop3_get outputs single vector containing VARCHAR UIDLs. The buffer_size constraint is effective here. Thus, the vector will only contain UIDLs of messages whose total message text length does not exceed buffer_size bytes. These message lengths are cumulated in the order returned by the POP3 server.
Command 'delete' will cause retrieved messages to be deleted from the server after retrieval.
A vector of vectors containing UIDL/Message text strings or a 'flat' vector containing UIDL strings.
This example retrieves messages from a remote POP3 server and stores them in a table.
create table MY_MSGS (MSG_ID INTEGER IDENTITY, MSG_HOST VARCHAR, MSG_UIDL VARCHAR, MSG_TEXT LONG VARCHAR, primary key (MSG_ID, MSG_HOST, MSG_UIDL)); create procedure get_msgs (in pop_host varchar, in pop_uid varchar, in pop_pwd varchar) { declare msg_vec any; declare inx integer; msg_vec := pop3_get (concat (pop_host, ':110'), pop_uid, pop_pwd, 10000000, 'delete'); inx := 0; while (inx < length (msg_vec)) { insert into MY_MSGS (MSG_HOST, MSG_UIDL, MSG_TEXT) values (pop_host, aref (aref (msg_vec, inx), 0), aref (aref (msg_vec, inx), 1)); inx := inx + 1; } }
Here is a test run. Just for the fun, let's get the message subjects, too.
SQL> get_msgs('pop.xs4all.nl', 'ghard', '|_337h4x0R'); SQL> select MSG_UIDL, length (MSG_TEXT), get_keyword ('Subject', aref (mime_tree (MSG_TEXT), 0)) from MY_MSGS; MSG_UIDL callret callret VARCHAR NOT NULL INTEGER VARCHAR _______________________________________________________________________________ 1003930514.maildrop7.14798 3482 [Fwd: Linux Expo] 1003930555.maildrop7.15349 7683 [Fwd: SOAP options example] 2 Rows. -- 8 msec.