The driver models the directories available over FTP as relational tables. Any changes to the remote data are immediately reflected in your queries, and the table definitions are dynamically retrieved. When you connect, the driver connects to SFTP and gets the list of tables.

API limitations and requirements are documented in the following sections.

The driver offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory. See SupportEnhancedSQL for more information on how the driver circumvents API limitations with in-memory client-side processing.

Tables

The depth of folders to list can be controlled by the TableDepth connection property. By default it is 1, so only directories directly in the Root will be listed.

Files may also be retrieved and listed recursively, although only from the Root table. Use the FileRetrievalDepth connection property to control this behavior.

Inserts, updates, and deletes are supported directly from tables. Inserts of files can be accomplished with the LocalFile pseudo column. An example of each of the operations is shown below:

INSERT INTO Root (IsDirectory, LocalFile) VALUES (false, 'C:\\testfolder\\myfile.txt')
INSERT INTO Root (IsDirectory, FileName) VALUES (true, 'MyFolder')
UPDATE Root SET FileName='NewName.txt' WHERE FilePath='/home/test/CDataFTPTest/myfile.txt'
UPDATE Root SET FileName='NewFolder' WHERE FilePath='/home/test/CDataFTPTest/MyFolder' AND IsDirectory=true
DELETE FROM Root WHERE FilePath='/home/test/CDataFTPTest/myfile.txt'
DELETE FROM Root WHERE FilePath='MyFolder' AND IsDirectory=true

Stored Procedures

Stored Procedures are function-like interfaces to the data source. You can use them to download files, upload files, and send protocol commands to the server.