Access to data on client computers

SQL Anywhere allows you to load data from, and unload data to, a file on a client computer using SQL statements and functions, without having to copy files to the database server computer. To do this, the database server initiates the transfer using a Command Sequence communication protocol (CmdSeq) file handler. The CmdSeq file handler is invoked after the database server receives a request from the client application requiring a transfer of data to or from the client computer, and before sending the response. The file handler supports simultaneous and interleaved transfer of multiple files from the client at any given time. For example, the database server can initiate the transfer of multiple files simultaneously if the statement executed by the client application requires it.

Using a CmdSeq file handler to achieve transfer of client data means that applications do not require any new specialized code and can start benefitting immediately from the feature using the SQL components listed below:

READ_CLIENT_FILE function The READ_CLIENT_FILE function reads data from the specified file on the client computer, and returns a LONG BINARY value representing the contents of the file. This function can be used anywhere in SQL code that a BLOB can be used. The data returned by the READ_CLIENT_FILE function is not materialized in memory when possible, unless the statement explicitly causes materialization to take place. For example, the LOAD TABLE statement streams the data from the client file without materializing it. Assigning the value returned by the READ_CLIENT_FILE function to a connection variable causes the database server to retrieve and materialize the client file contents.
WRITE_CLIENT_FILE function The WRITE_CLIENT_FILE function writes data to the specified file on the client computer.
READCLIENTFILE authority READCLIENTFILE authority allows you to read from a file on a client computer.
WRITECLIENTFILE authority WRITECLIENTFILE authority allows you to write to a file on a client computer.
LOAD TABLE ... USING CLIENT FILE clause The USING CLIENT FILE clause allows you to load a table using data in a file located on the client computer. For example, LOAD TABLE ... USING CLIENT FILE 'my-file.txt'; loads a file called my-file.txt from the client computer.
LOAD TABLE ... USING VALUE clause The USING VALUE clause allows you to specify a BLOB expression as a value. The BLOB expression can make use of the READ_CLIENT_FILE function to load a BLOB from a file on a client computer. For example, LOAD TABLE ... USING VALUE READ_CLIENT_FILE( 'my-file' ), where my-file is a file on the client computer.
UNLOAD TABLE ... INTO CLIENT FILE clause The INTO CLIENT FILE clause allows you to specify a file on the client computer to unload data into.
UNLOAD TABLE ... INTO VARIABLE clause The INTO VARIABLE clause allows you to specify a variable to unload data into.
read_client_file and write_client_file secure features The read_client_file and write_client_file secure features control the use of statements that can cause a client file to be read from, or written to.

Note that in order to allow reading from or writing to a client file from a procedure, function or other indirect statements, a callback function must be registered. The callback function is called to confirm that the application allows the client transfer that it did not directly request.

Access to data on client computers

See also