Create directory access servers

You use the CREATE SERVER statement or Create Directory Access Server Wizard in Sybase Central to create a directory access server.

When you create a directory access server, you can control the number of subdirectories that can be accessed and whether the directory access server can be used to modify existing files.

The following steps are required to set up a directory access server:

Create a remote server for the directory (requires DBA authority).
Create external logins for the database users who can use the directory access server (requires DBA authority).
Create proxy tables to access the directories on the computer (requires RESOURCE authority).

To create and configure a directory access server (Sybase Central)

Connect to the database as a user with DBA authority.
In the left pane, double-click Directory Access Servers.
From the File menu, choose New » Directory Access Server.
Follow the instructions in the Create Directory Access Server Wizard.

To create and configure a directory access server (SQL)

Connect to the host database as a user with DBA authority.

Create a remote server using the CREATE SERVER statement.

For example:

CREATE SERVER my_dir_tree
CLASS 'directory'
USING 'root=c:\Program Files';

Create an external login using the CREATE EXTERNLOGIN statement.

For example:
CREATE EXTERNLOGIN DBA TO my_dir_tree;
Create a proxy table for the directory using the CREATE EXISTING TABLE statement.

For example:
CREATE EXISTING TABLE my_program_files AT 'my_dir_tree;;;.';
In this example, my_program_files is the name of the directory, and my_dir_tree is the name of the directory access server.

Example

The following statements create a new directory access server named directoryserver3 that can be used to access up to three levels of subdirectories, create an external login to the directory access server for the DBA user, and create a proxy table named diskdir3.

CREATE SERVER directoryserver3
CLASS 'DIRECTORY'
USING 'ROOT=c:\mydir;SUBDIRS=3';
CREATE EXTERNLOGIN DBA TO directoryserver3;
CREATE EXISTING TABLE diskdir3 AT 'directoryserver3;;;.';

Using the sp_remote_tables system procedure, you can see all the subdirectories located in c:\mydir on the computer running the database server:

CALL sp_remote_tables( 'directoryserver3' );

Using the following SELECT statement, you can view the contents of the file c:\mydir\myfile.txt:

SELECT contents
FROM diskdir3
WHERE file_name = 'myfile.txt';

Alternatively, you can select data from the directories:

-- Get the list of directories in this disk directory tree.
SELECT permissions, file_name, size
FROM diskdir3
WHERE PERMISSIONS LIKE 'd%';
-- Get the list of files.
SELECT permissions, file_name, size
FROM diskdir3
WHERE PERMISSIONS NOT LIKE 'd%';