Another new class of proxy tables are allowed in version 12.5 that enables SQL access to individual files within a file system. The supported syntax is:
create proxy_table <table_name> external file at " pathname" [column delimiter “<string>”]
When this command is used, a proxy table with one column (named ’record’, type varchar(255)) will be created. It is assumed in this case that the contents of the file are readable characters, and individual records within the file are separated by the newline (\n) character.
It is also possible to specify your own column names and datatypes, using the create [existing] table command:
create existing table fname ( column1 int null, column2 datetime null, column3 varchar(1024) null etc. etc. ) external file at "pathname" [column delimiter “<string>”]
Columns may be any datatype except text, image, or a Java ADT. The use of the existing keyword is optional, and has no effect on the processing of the statement. In all cases (create table, create existing table, create proxy_table), if the file referenced by pathname does not exist, it is created. If it does exist, its contents are not overwritten. There is no difference in behavior between the create table and create existing table commands.
When a proxy table is mapped to a file, some assumptions about the file and its contents are made:
The file is a regular file (i.e. not a directory, block special, or character special file);
The Adaptive Server Enterprise server process has at least read access to the file. If the file is to be created, the server process must have write access to the directory in which the file is to be created;
The contents of an existing file are in human-readable form;
Records within the file are delimited by a newline character;
The maximum supported record size is 32767 bytes;
Individual columns, except for the last one, are delimited by the column delimiter string, which can be up to 16 bytes long; the default is a single tab character;
There is a correspondence between delimited values within each record of the file and the columns within the proxy table.
With proxy tables mapped to files, it is possible to:
Back-up database tables to the file system using either select/into or insert/select. When an insert statement is processed, each column is converted to characters in the default character set of the server. The results of the conversion are buffered, and all columns (except for the last) are delimited by a single tab. The last column is terminated by a newline. The buffer is then written to the file, representing a single row of data.
Provide a SQL alternative to using bcp in and bcp out. The use of a select/into statement can easily back-up a table to a file, or copy a file’s contents into a table.
Query file content with the select statement, qualifying rows as needed with search arguments or functions. For example, it is possible to read the individual records within the Adaptive Server Enterprise errorlog file:
create proxy_table errorlog external file at "/usr/sybase/ase12_5/install/errorlog" select record from errorlog where record like "%server%"
The query will return all rows from the file that match the like pattern. If the rows are longer than 255 bytes, they will be truncated. It is possible to specify longer rows:
create existing table errorlog ( record varchar(512) null ) external file at "/usr/Sybase/ase12_5/install/errorlog"
In this case, records up to 512 bytes in length will be returned. Again, since the proxy table contains only one column, the actual length of each column will be determined by the presence of a newline character.
Only the select, insert data access statements are supported for file access. update and delete will result in errors if the file proxy is the target of these commands.
When inserting values into a file, all datatypes are first converted to char values and then delimited by the column delimiter.
Important: truncate table sets the file size to 0.
Traceflag 11206 is also used to log message to the errorlog. These messages contain information about the stages of query processing that are involved with file access.
