Directory access

Proxy tables can be created to reference file system directories. The supported syntax is:

create proxy_table <table_name>
external directory at "directory pathname[;R]"

The directory path name must reference a file system directory visible to and searchable by the Adaptive Server Enterprise process. A proxy table that maps column names to attributes of files that exist within the directory is created. If the ‘;R’ (indicating “recursion”) extension is added to the end of the path name, Component Integration Services includes entries in all subordinate directories. Table 2-3 contains a description of the proxy table columns that are created when this command successfully completes:

**Table 2-3: Proxy table columns**
Column name	Datatype	Description
id	numeric(24) – on 32-bit machines numeric(36) – on 64-bit machines	Identity value consisting of values from st_dev and st_ino. These two values are converted first to a single string (format: “%d%014ld”), and the string is then converted to a numeric value.
filename	varchar(n)	The name of the file within the directory specified in at ‘pathname’, or within directories subordinate to pathname. The total length (n) of filename is limited to 255 bytes.
size	int	For regular files – specifies the number of bytes in the file. For directories – block special or character special, this is not defined.
filetype	varchar(4)	The file type – legal values are: FIFO, for pipe files; DIR for directories; CHRS for character special files; BLKS for block special files; REG for ordinary files; UNKN for all other file types. Links are automatically expanded, and do not appear as a separate file type.
access	char(10)	Access permissions, presented in a more or less ‘standard’ UNIX format: “drwxrwxrwx”
uid	varchar(n)	The name of the file owner. The value of n is specified by the system definition L_cuserid, which is 9 on all UNIX systems except HP/Compaq Tru64, where it is 64. This value is 0 on Windows systems.
gid	varchar(n)	The name of the owning group. The value of n is specified by the system definition L_cuserid, which is 9 on all UNIX systems except HP/Compaq Tru64, where it is 64. This value is 0 on Windows systems.
atime	datetime	Date/time file data was last accessed.
mtime	datetime	Date/time when file was last modified.
ctime	datetime	Date/time when file status was last changed.
content	image	The actual physical content of the file (for regular files only). NULL if the file is not a regular file.

A proxy table that maps to a file system directory can support the following SQL commands:

select – file attributes and content can be obtained from the proxy table using the select command. Built-in functions that are designed to handle text values are fully supported for the content column, (for example, textptr, textvalid, patindex, pattern).
insert – new files or directories can be created using the insert command. The only columns that have meaning are filename, filetype, and content. The rest of the columns should be left out of the insert statement, and are ignored if they are located. The content column is ignored if file type is DIR, which indicates that a new directory is to be created.

To create a new directory, enter:
```
insert D1 (filename, filetype) values ("newdir", "DIR")
```
To create a new file, enter:
```
insert D1 (filename, filetype) values ("newdir/newfile", "This is an example.")
```

delete – files or directories may be removed by the use of the delete command. A directory can be removed only if it is empty. For example:

/* delete the files only */
delete D1 where filename = ’newdir/newfile’    
/* deletes the directory (if empty) */
delete D1 where filetype = ’DIR’ and filename = ’newdir’

update – only the name of a file may be changed using the update command.
readtext – the contents of a file may be retrieved using the readtext command.
writetext – the contents of a file may be modified using the writetext command.

No other SQL commands operate on proxy tables.

Regular file content is available only if the Adaptive Server process has sufficient privileges to access and read the file, and if the file type indicates an “ordinary” file. In all other cases, the content column is NULL. For example:

	select filename, size, content
		from directory_table
		where filename like ‘%.html’

returns the name, size and content of regular files with a suffix of “.html,” if the Adaptive Server process has access privileges to the file. Otherwise, the content column will be NULL.

create proxy_table fails if the path name referenced by directory path name is not a directory, or is not searchable by the Adaptive Server Enterprise process.

If trace flag 11206 is turned on, messages are written to the error log that contain information about the contents of the directories and the query processing steps needed to obtain that information.