Reference Manual
   CHAPTER 14. System Procedures and Functions     

System extended stored procedures

A set of system extended procedures are included in Adaptive Server Anywhere databases. These procedures are owned by the dbo user ID.

The following sections describe each of the stored procedures.

  MAPI system extended stored procedures

Adaptive Server Anywhere includes three system procedures for sending electronic mail using Microsoft's Messaging API standard (MAPI). These system procedures are implemented as extended stored procedures: each procedure calls a function in an external DLL.

In order to use the MAPI stored procedures, a MAPI e-mail system must be accessible from the database server machine.

The MAPI stored procedures are:

• xp_startmail     Starts a mail session in a specified mail account by logging on the MAPI message system

• xp_sendmail     Sends a mail message to specified users

• xp_stopmail     Closes the mail session

The following procedure notifies a set of people that a backup has been completed.

CREATE PROCEDURE notify_backup()
BEGIN
   CALL xp_startmail( mail_user='ServerAccount',
                  mail_password='ServerPassword'
                     );
   CALL xp_sendmail( recipient='IS Group',
                     subject='Backup',
                     "message"='Backup completed'
                     );
   CALL xp_stopmail( )
END

The MAPI system procedures are discussed in the following sections.

  xp_startmail system procedure

Function 

To start an e-mail session.

Syntax 

[ [ variable = ] CALL ] xp_startmail (

... [ mail_user = mail-login-name ]

... [, mail_password = mail-password ]

... )

Usage 

Anywhere.

Authorization 

None.

Description 

xp_startmail is a system stored procedure that starts an e-mail session. It is implemented as a user-defined function.

The mail-login-name and mail-password values are strings containing the MAPI login name and password to be used in the mail session.

If you are using Microsoft Exchange, the mail_login_name argument is an Exchange profile name, and you should not include a password in the procedure call.

Return codes 

The xp_startmail system procedure issues one of the following return codes:

Return code	Meaning
0	Success
2	Failure

  xp_sendmail system procedure

Function 

To send an e-mail message.

Syntax 

[ [ variable = ] CALL ] xp_sendmail (

... [ recipient = mail-address ]

... [, cc_recipient = mail-address ]

... [, bcc_recipient = mail-address ]

... [, "message" = message-body ]

... [, include_file = file-name ]

... )

Usage 

Anywhere.

Authorization 

Must have executed xp_startmail to start an e-mail session.

Description 

xp_sendmail is a system stored procedure that sends an e-mail message once a session has been started to :name_startmail:ename.. It is implemented as a user-defined function.

The argument values are strings. The message parameter name requires double quotes around it, because MESSAGE is a keyword.

Return codes 

The xp_sendmail system procedure issues one of the following return codes:

Return code	Meaning
0	Success
5	Failure (general)
11	Ambiguous recipient
12	Attachment not found
13	Disk full
14	Insufficient memory
15	Invalid session
16	Text too large
17	Too many files
18	Too many recipients
19	Unknown recipient

Example 

The following call sends a message to the user ID Sales Group containing the file prices.doc as a mail attachment:

CALL xp_sendmail(recipient='Sales Group',
      subject='New Pricing',
      include_file = 'C:\\DOCS\\PRICES.DOC'
      )

  xp_stopmail system procedure

Function 

To close an e-mail session.

Syntax 

[ variable = ] [ CALL ] xp_stopmail ()

Usage 

Anywhere.

Authorization 

None.

Description 

xp_stopmail is a system stored procedure that ends an e-mail session. It is implemented as a user-defined function.

xp_stopmail returns an integer. The return value is zero if the mail session is successfully closed, and non-zero otherwise.

Return codes 

The xp_stopmail system procedure issues one of the following return codes:

Return code	Meaning
0	Success
3	Failure

  Other system extended stored procedures

The other system extended stored procedures included are:

• xp_cmdshell     Executes a system command.

• xp_msver     Returns a string containing version information.

• xp_sprintf     Builds a string from a format string and a set of input strings.

• xp_scanf     Extracts substrings from an input string and a format string.

The following sections provide more detail on each of these procedures.

  xp_cmdshell system procedure

Function 

To carry out an operating system command from a procedure.

Syntax 

[ variable = CALL ] xp_cmdshell ( string )

Usage 

Anywhere.

Authorization 

None.

Description 

xp_cmdshell is a system stored procedure that executes a system command and then returns control to the calling environment.

Example 

The following statement lists the files in the current directory in the file c:\temp.txt

xp_cmdshell('dir > c:\\temp.txt')

  xp_msver system function

Function 

To retrieve version and name information about the database server.

Syntax 

xp_msver ( string )

The string must be one of the following, enclosed in string delimiters.

Argument	Description
ProductName	The name of the product (Sybase Adaptive Server Anywhere)
ProductVersion	The version number, followed by the build number. The format is as follows: 6.5.02 (1200)
CompanyName	Returns the following string: Sybase Inc.
FileDescription	Returns the name of the product, followed by the name of the operating system.
LegalCopyright	Returns a copyright string for the software
LegalTrademarks	Returns trademark information for the software

Returns 

String containing information appropriate to the argument.

Usage 

Anywhere.

Authorization 

None.

Description 

xp_msver returns product, company, version, and other information.

Example 

• The following statement requests the version and operating system description:

  select xp_msver( 'ProductVersion') Version
     xp_msver('FileDescription') Description

  Sample output is as follows:

  Version	Description
  6.5.02 (1438)	Sybase Adaptive Server Anywhere Windows NT

Sybase Adaptive Server Anywhere Windows NT

  xp_sprintf system procedure

Function 

To build up a string from a format string and a set of input strings.

Syntax 

[ variable = CALL ] xp_sprintf ( out-string,

... format-string

... [ input-string, ... ] )

Usage 

Anywhere.

Authorization 

None.

Description 

xp_sprintf is a system stored procedure that builds up a string from a format string and a set of input strings. The format-string can contain up to fifty string placeholders (%s). These placeholders are filled in by the input-string arguments.

All arguments must be strings of less than 254 characters.

Example 

The following statements put the string Hello World! into the variable mystring.

CREATE VARIABLE mystring CHAR(254) ;
xp_sprintf( mystring, 'Hello %s', 'World!' )

  xp_scanf system procedure

Function 

To extract substrings from an input string and a format string.

Syntax 

[ variable = CALL ] xp_scanf ( in-string,

... format-string

... [ output-string, ... ] )

Usage 

Anywhere.

Authorization 

None.

Description 

xp_scanf is a system stored procedure that extracts substrings from an input string and a format string. The format-string can contain up to fifty string placeholders (%s). The values of these placeholders are placed in the output-string variables.

All arguments must be strings of less than 254 characters.

Example 

• The following statements put the string World! into the variable mystring.

  CREATE VARIABLE mystring CHAR(254) ;
  xp_scanf( 'Hello World!', 'Hello %s', mystring )