File access

Another class of proxy tables allowed in Adaptive Server 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)) is created. It is assumed in this case that the contents of the file are readable characters, and that individual records within the file are separated by the newline (\n) character.

You can also 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. If the file referenced by path name 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, these assumptions about the file and its contents are made:

• The file is not a directory, block special, or character special file.

• The Adaptive 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, you can:

• 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 the last) are delimited by a single tab. The last column is terminated by a newline character. 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, you can read the individual records within the Adaptive Server error log file:

  create proxy_table errorlog
  		external file at "/usr/sybase/ASE15_0/install/errorlog"
  select record from errorlog where record like "%server%"
  

  This query returns all rows from the file that match the like pattern. If the rows are longer than 255 bytes, they are truncated. You can specify longer rows by entering:

  create existing table errorlog
  (
  		record		varchar(512) null
  )
  external file at "/usr/sybase/ASE15_0/install/errorlog"
  

  In this case, records up to 512 bytes in length are returned. Since the proxy table contains only one column, the actual length of each column is determined by the presence of a newline character.

Only the select, insert, and truncate table statements are supported for file access. update and delete 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.

Trace flag 11206 is used to log messages to the error log. These messages contain information about the stages of query processing that are involved with file access.