Creating directory access servers (SQL)

Administrators can create directory access servers using the CREATE SERVER statement in Interactive SQL.

Prerequisites

DBA authority.

RESOURCE authority.

Context and remarks

Many.

 Create and configure a directory access server

  1. Create a remote server for the directory (requires DBA authority).

  2. Create external logins for the database users who can use the directory access server (requires DBA authority).

  3. Create proxy tables to access the directories on the computer (requires RESOURCE authority).

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

  2. Create a remote server using the CREATE SERVER statement.

    For example:

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

  3. Create an external login using the CREATE EXTERNLOGIN statement.

    For example:

    CREATE EXTERNLOGIN DBA TO my_dir_tree;

  4. 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.

Results

The directory access server is created and configured.

Next

None.

Example

Executing the statements below creates the following:

  • A new directory access server named directoryserver3 that can be used to access up to three levels of subdirectories.

  • An external login to the directory access server for the DBA user.

  • 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 as follows:

-- 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%';

Example

Assume you are a DBA and have a database that is sometimes started on computer A, with the database server named server1, and at other times is started on computer B, with the server named server2. Suppose you want to set up a directory access server that points to the local drive d:\users on computer B as well as the network server drive w:\users on computer A. Additionally, you want to set up a proxy table, located at users/userM, from which all users can get the listing of their own directories. By using variables in the USING clause of a CREATE SERVER statement and in the AT clause of a CREATE EXISTING TABLE statement, you can fulfill your needs by creating a single directory access server and a single proxy table, as follows:

  • Create a directory access server   Create the directory access server using variables for the root of the directory access server and the subdirectory level. 
    CREATE SERVER dir CLASS 'directory' USING 'root={@directory}\\users;subdirs={@subdirs}';

    Create explicit external logins for each user who is allowed to use the directory access server.

    CREATE EXTERNLOGIN DBA TO dir;
CREATE USER user1 IDENTIFIED BY sql;
   CREATE EXTERNLOGIN user1 TO dir;
CREATE USER user2 IDENTIFIED BY sql;
   CREATE EXTERNLOGIN user2 TO dir;
CREATE USER userM IDENTIFIED BY sql;
   CREATE EXTERNLOGIN userM TO dir;

  • Create a proxy table   Use one of the user accounts to create a proxy table that points to @directory\users\@curuser on the directory access server. 
    CREATE VARIABLE @directory LONG VARCHAR;
   SET @directory = 'd:';
CREATE VARIABLE @subdirs VARCHAR(10);
   SET @subdirs = '7';
CREATE VARIABLE @curuser VARCHAR(128);
   SET @curuser = 'user1';
CREATE VARIABLE @server VARCHAR(128);
   SET @server = 'dir';
CREATE EXISTING TABLE dbo.userdir AT '{@server};;;{@curuser}';

    The variables are no longer needed, so drop them by executing the following statements:

    DROP VARIABLE @curuser;
DROP VARIABLE @subdirs;
DROP VARIABLE @directory;

    Drop the external login mapping for DBA.

    DROP EXTERNLOGIN DBA TO dir;

  • Create a procedure   Create a procedure that allows all users to view the contents of their individual user directory. 


    CREATE PROCEDURE dbo.listmydir()
BEGIN
   DECLARE @directory LONG VARCHAR;
   DECLARE @subdirs VARCHAR(10);
   DECLARE @server VARCHAR(128);
   DECLARE @curuser VARCHAR(128);
-- For this example, the 'dir' remote data access server is always used.
   SET @server = 'dir'
-- The root directory is based on the SQL Anywhere server the user is connected to.
   SET @directory = IF property('name') = 'server1' THEN 'w:' ELSE 'd:' ENDIF;
-- The subdirectory limit is based on the connected user.
   SET @curuser = user_name();
-- All users get a subdirectory limit of 7, except user2, who gets a limit of 1.
   SET @subdirs = CONVERT ( VARCHAR(10), IF @curuser = 'user2' THEN 1 ELSE 7 ENDIF);
-- With all the variables set above, the proxy table dbo.userdir now points to @directory\@curuser
-- and has a subdirectory limit of @subdirs.
   SELECT * FROM dbo.userdir;
END;


 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1