File Translator

The file translator, known by the type name file, exposes stored procedures to leverage file resources. It will commonly be used with the TEXTTABLE or XMLTABLE table functions to use CSV or XML formatted data.

Execution Properties

Name Description Default

Encoding

The encoding that should be used for CLOBs returned by the getTextFiles procedure. The value should match an encoding known to Teiid, see also TO_CHARS and TO_BYTES.

The system default encoding

ExceptionIfFileNotFound

Throw an exception in getFiles or getTextFiles if the specified file/directory does not exist.

true (false prior to 8.2)

Tip
See override an execution property and the example below to set the properties.

VDB DDL Override Example

CREATE SERVER "file-override"
    FOREIGN DATA WRAPPER file
    OPTIONS(
        Encoding 'ISO-8859-1', "ExceptionIfFileNotFound" false
    );

CREATE SCHEMA file SERVER "file-override";

VDB XML Override Example

<model name="file">
    <source name="file" translator-name="file-override" connection-jndi-name="java:/file"/>
</model>

<translator name="file-override" type="file">
    <property name="Encoding" value="ISO-8859-1"/>
    <property name="ExceptionIfFileNotFound" value="false"/>
</translator>

Usage

getFiles

getFiles(String pathAndPattern) returns
TABLE(file blob, filePath string, lastModified timestamp, created timestamp, size long)

Retrieve all files as BLOBs matching the given path and pattern.

call getFiles('path/*.ext')

If the path is a directory, then all files in the directory will be returned. If the path matches a single file, it will be returned.

The '*' character will be treated as a wildcard to match any number of characters in the path name - 0 or matching files will be returned.

If '*' is not used and the path doesn’t exist and ExceptionIfFileNotFound is true, then an exception will be raised.

getTextFiles

getTextFiles(String pathAndPattern) returns
TABLE(file clob, filePath string, lastModified timestamp, created timestamp, size long)
Note
the size reports the number of bytes

Retrieve all files as CLOB(s) matching the given path and pattern.

call getTextFiles('path/*.ext')

All the same files a getFiles will be retrieved, the only difference is that the results will be CLOB values using the encoding execution property as the character set.

saveFile

Save the CLOB, BLOB, or XML value to given path

call saveFile('path', value)

deleteFile

Delete the file at the given path

call deleteFile('path')

The path should reference an existing file. If the file does not exist and ExceptionIfFileNotFound is true, then an exception will be thrown. Or if the file cannot be deleted an exception will be thrown.

JCA Resource Adapter

See File Data Source, the FTP Data Source and the Admin Guide in general for configuration information.

Native Queries

Note
This feature is not applicable for the File translator.

Direct Query Procedure

Note
This feature is not applicable for the File translator.

results matching ""

    No results matching ""