Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » System Procedures » System procedures

xp_sprintf system procedure Next Page

xp_write_file system procedure

Writes data to a file from a SQL statement.


, file_contents


The function writes file_contents to the file filename. It returns 0 if successful, and non-zero if it fails.

The filename value can be prefixed by either an absolute or a relative path. If filename is prefixed by a relative path, then the file name is relative to the current working directory of the database server. If the file already exists, its contents are overwritten.

This function can be useful for unloading long binary data into files.


DBA authority required

See also

This example uses xp_write_file to create a file accountnum.txt containing the data 123456:

CALL  xp_write_file( 'accountnum.txt', '123456' );

This example queries the Contacts table of the sample database, and then creates a text file for each contact living in New Jersey. Each text file is named using a concatenation of the contact's first name (GivenName), last name (Surname), and then the string .txt (for example, Reeves_Scott.txt), and contains the contact's street address (Street), city (City), and state (State), on separate lines.

SELECT xp_write_file( 
Surname || '_' ||  GivenName || '.txt', 
Street || '\n' || City || '\n' || State ) 
FROM Contacts WHERE State = NJ;

This example uses xp_write_file to create an image file (JPG) for every product in the Products table. Each value of the ID column becomes a file name for a file with the contents of the corresponding value of the Photo column:

SELECT xp_write_file( ID || '.jpg' , Photo ) FROM Products;

In the example above, ID is a row with a UNIQUE constraint. This is important to ensure that a file isn't overwritten with the contents of subsequent row. Also, you must specify the file extension applicable to the data stored in the column. In this case, the Products.Photo stores image data (JPGs).