A new class of proxy tables is allowed in version 12.5 that enables SQL access to file system directories and their underlying files. In order to create proxy tables mapped to directories or files, you must have “System Administrator” or “System Security Officer” privileges. The supported syntax is:
create proxy_table <table_name> external directory at "directory pathname[;R]"
The directory pathname must reference a file system directory visible to and searchable by the Adaptive Server Enterprise process. A proxy table is created which maps column names to attributes of files that exist within the directory. If the ’;R’ (indicating "recursion") extension is added to the end of pathname, CIS includes entries in all subordinate directories. The following table contains a description of the proxy table columns that are created when this command successfully completes:
Column Name |
Datatype |
Description |
|---|---|---|
id |
numeric(24) |
Identity value consisting of values from st_dev and st_ino (See stat(2)). 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. While the length of pathname is limited to 255 bytes, the total length (n) of filename is system dependent, and specified by the definition of MAXNAMLEN. For Solaris systems, this value is 512 bytes; for most other systems this will be 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 will 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 systems except Compaq Tru64, where it is 64. This value is 0 on NT 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 systems except Compaq Tru64, where it is 64. This value is 0 on NT 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. (i.e. textptr, textvalid, patindex, pattern).
insert - A new file or files can be created using the insert command. The only column values that have meaning are filename and content; the rest of the columns should be left out of the insert statement. If they are not left out, they are ignored.
delete - files may be removed by the use of the delete command.
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 will operate on tables of this type.
Regular file content is available only if the Adaptive Server Enterprise 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 will be 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 Enterprise process has access privileges to the file. Otherwise, the content column will be NULL.
The create proxy_table command fails if the pathname referenced by directory pathname is not a directory, or is not searchable by the Adaptive Server Enterprise process.
If traceflag 11206 is turned ON, then messages are written to the errorlog that contain information about the contents of the directories and the query processing steps needed to obtain that information.
