Directory Access

Proxy tables can be created to reference file system directories.

The 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 SAP ASE 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. This table describes the proxy table columns that are created when this command successfully completes:

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. 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. 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, content) 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.

Note: Some file systems implement update as a deletion followed by the creation of a new directory entry; therefore the same filename could be updated multiple times if the update is not restricted. SAP recommends that you qualify the update to specific files by having the filename included in the where clause of the update. For example, this statement could cause multiple updates:
update t1 set filename=filename + 'old' where filetype = 'REG'

The problem would be avoided by adding a clause such as "and filename like "%.c"
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 SAP ASE 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 SAP ASE 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 SAP ASE 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.