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