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.