User's Guide Part VI. SQL Anywhere Reference Chapter 49. SQL Anywhere System Procedures and FunctionsA set of system extended procedures are included into SQL Anywhere databases. These procedures are owned by the DBO user ID that owns the Transact-SQL-compatibility system views.
The following sections describe each of the stored procedures.
SQL Anywhere includes a set of three system procedures for sending electronic mail using Microsoft's Messaging API standard (MAPI). These system procedures are implemented as Extended Stored Procedures: each of the procedures 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:
The following procedure notifies a set of people that a backup has been completed.
CREATE PROCEDURE notify_backup()BEGINCALL 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.
To start an e-mail session.
[ [ variable = ] CALL ] xp_startmail (
... [ mail_user = mail-login-name ]
... [, mail_password = mail-password ]
... )
Anywhere.
None.
xp_startmail is a system stored procedure that starts an e-mail session. It is implemented as a user-defined function.
xp_startmail returns an integer. The return value is zero if the login is successful, and non-zero otherwise.
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_user argument is an Exchange profile name, and you should not include a password in the procedure call.
The xp_startmail system procedure uses the following return codes:
| Return code | Meaning |
|---|---|
| 0 | Success |
| 2 | Failure |
To send an e-mail message.
[ [ variable = ] CALL ] xp_sendmail (
... [ recipient = mail-address ]
... [, cc_recipient = mail-address ]
... [, bcc_recipient = mail-address ]
... [, "message" = message-body ]
... [, include_file = file-name ]
... )
Anywhere.
Must have executed xp_startmail to start an e-mail session.
xp_sendmail is a system stored procedure that sends an e-mail message once a session has been started using :name_startmail:ename.. It is implemented as a user-defined function.
xp_sendmail returns an integer. The return value is zero if the message is successfully sent, and non-zero otherwise.
The argument values are strings. The message parameter name requires double quotes around it as MESSAGE is a keyword.
The xp_sendmail system procedure uses 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 |
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')
To close an e-mail session.
[ variable = ] [ CALL ] xp_stopmail ()
Anywhere.
None.
xp_stopmail is a system stored procedure that starts 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.
The xp_stopmail system procedure uses the following return codes:
| Return code | Meaning |
|---|---|
| 0 | Success |
| 3 | Failure |
The other system extended stored procedures included are:
The following sections provide more detail on each of these procedures.
To carry out an operating system command from a procedure.
[ variable = CALL ] xp_cmdshell ( string )
Anywhere.
None.
xp_cmdshell is a system stored procedure that executes a system command and then returns control to the calling environment.
The following statement lists the files in the current directory in the file C:\TEMP.TXT
xp_cmdshell('dir > c:\\temp.txt')
To retrieve version and name information about the database engine or server.
xp_msver ( string )
The string must be one of the following, enclosed in string delimiters.
| Argument | Description |
|---|---|
| ProductName | The name of the product (Sybase SQL Anywhere) |
| ProductVersion | The version number, followed by the build number. The format is as follows: |
| CompanyName | Returns the following string: |
| 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 |
String containing information appropriate to the argument.
Anywhere.
None.
xp_msver returns product, company, version, and other information.
select xp_msver( 'ProductVersion') Versionxp_msver('FileDescription') Description
Sample output is as follows:
| Version | Description |
|---|---|
| 5.5.02 (1438) | Sybase SQL Anywhere Windows NT |
To build up a string from a format string and a set of input strings.
[ variable = CALL ] xp_sprintf ( out-string,
... format-string
... [ input-string, ... ] )
Anywhere.
None.
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.
The following statements put the string Hello World ! into the variable mystring.
CREATE VARIABLE mystring CHAR(254) ;xp_sprintf( mystring, 'Hello %s', 'World!' )
To extract substrings from an input string and a format string.
[ variable = CALL ] xp_scanf ( in-string,
... format-string
... [ output-string, ... ] )
Anywhere.
None.
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 value of these placeholders in the are filled in by the output-string arguments.
All arguments must be strings of less than 254 characters.
The following statements put the string World! into the variable mystring.
CREATE VARIABLE mystring CHAR(254) ;xp_scanf( 'Hello World!', 'Hello %s', mystring )