SQLFile

SQLFile has 110+ functions and stored procedures, including:

• Functions for importing and exporting data between databases and the file system.
• Functions for compressing / decompressing data, converting between encodings and converting between binary, base64 and hexadecimal representations of large binary objects.
• Functions for retrieving directory contents listings - with optional recursion.
• Functions for file and directory path name manipulation - including generating temporary and random, cryptographically secure file & directory names.
• Functions for copying, moving, deleting, replacing, encrypting, decrypting & checking the size of files.
• Functions for checking and setting file attributes and timestamps.
• Functions for creating, deleting, moving and checking directories.
• Functions for checking and setting directory timestamps.
• Functions for checking and setting files access and audit rules.
• Functions for retrieving drive and volume information such as available free space.

Most of the file operations are implemented as scalar functions that can be applied to tables of files etc. so that many files can be read, written or updated in parallel with a single statement.

Native Look and Feel
All TotallySQL Transact-SQL extensions are implemented using familiar programming objects within SQL (scalar and table-valued functions and stored procedures) , keeping the syntax clean and making them as fast and easy to use as built-in functions.

SQLFile Features

• Read, write & update multiple files & directories in parallel
• Read entire file contents as single scalar binary or text values
• Read text file contents as single-column tables (1 row per line)
• Read & parse character-delimited text files as multi-column tables
• Export single scalar binary or text values as whole files
• Append scalar text values to existing files
• Write character-delimited text files directly from tables, view or ad-hoc queries
• Compress / decompress text or binary values within the database for more efficient storage
• Create cryptographically secure unique random file & directory names
• Create uniquely-name temporary files in the system temporary folder
• Retrieve directory contents listings as single or multi-column tables - with optional recursion
• Encrypt or decrypt files in the file system
• Copy, move, replace, delete, & check the existence & size of files
• Create, move, delete, check for existence of & get parent or root of directories
• Read or set file attributes (e.g. readonly, hidden, system, archive, temporary, offline, compressed, encrypted)
• Read or set timestamps on files and directories (created, last accessed, last written)
• Check, create, alter, remove or purge access and audit rules for files and directories
• Compress / decompress scalar binary and text values
• Convert large binary objects between their binary, Base64 text and Hexadecimal text representations
• Convert text file contents between different code pages / encodings
• Retrieve drive type, volume, format, readiness, available free space, total free space & size information
• Parse file path strings to retrieve directory name, file name, file extension, root folder information
• Easily alter & combine path strings & test for validity (without accessing the file system)
• Return as a table a list of logical drives on the server