# 22. Virtuoso Functions Guide - pop3_get

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Virtuoso Functions Guide
Administration
Aggregate Functions
Array Manipulation
BPEL APIs
Backup
Compression
Cursor
Date & Time Manipulation
Debug
Dictionary Manipulation
Encoding & Decoding
File Manipulation
Free Text
Hashing / Cryptographic
LDAP
Locale
Mail
mime_body
mime_part
mime_tree
nntp_auth_get
nntp_auth_post
nntp_get
nntp_post
pem_certificates_to_...
pop3_get
smime_sign
smime_verify
smtp_send
uuvalidate
Miscellaneous
Number
Remote SQL Data Source
Replication
SOAP
SQL
String
Transaction
Type Mapping
UDDI
User Defined Types & The CLR
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web Server & Internet
XML
XPATH & XQUERY
Functions Index

## pop3_get

get messages from a POP3 server
array pop3_get (in host varchar, in user varchar, in password varchar, in buffer_size integer, [in command varchar], [in exclude_uidl_list vector]);
Description

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.

Parameters
host – The host to connect with. IP address or hostname:port. There is no default for port, so to connect to the standard port for POP3, use <hostname/IP address>:110
user – string user id in remote host.
password – string password in remote host.
buffer_size – integer maximum total length of message text for messages/uidls to be retrieved.
command – Command string. Valid values are empty, 'uidl' or 'delete'
exclude_uidl_list – A vector containing UIDLs. A message whose UIDL appears in this list will not be retrieved or deleted.
Return Types

A vector of vectors containing UIDL/Message text strings or a 'flat' vector containing UIDL strings.

Errors

SQLState | Error Code | Error Text | Description
--- | --- | --- | ---
2E000 | PO001 | Cannot resolve host in pop3_get |
08001 | PO002 | Cannot connect in pop3_get |
08006 | PO003 | No response from remote POP3 server |
08006 | PO004 | Not valid user in remote POP3 server |
08006 | PO005 | UIDL command to remote POP3 server failed |
08006 | PO006 | Could not get output of UIDL from remote POP3 server. |
08006 | PO007 | LIST command to remote POP3 server failed. |
08006 | PO008 | Could not get output of LIST from remote POP3 server. |
| PO009 |  |
08006 | PO010 | Failed reading output of LIST command on remote POP3 server |
08006 | PO011 | Could not DELE messages from remote POP3 server |
08006 | PO012 | Could not QUIT from remote POP3 server |
08000 | PO013 | Argument 6 to pop3_get must be a vector |
08006 | PO014 | Misc. error in connection in pop3_get |

Examples
Get messages from remote POP3

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.

See Also

mime_tree, RFC1725