External file and directory access with data import/export and compression.
Functions included in SQLFile.
SQLFile extends and enhances your T-SQL programming environment by exposing the full range of .NET Framework file & directory management functions - and adds the ability to directly import and export data, and to compress both binary and text data stored internally in the database. SQLFile functions include: read & write binary, text & delimited files (e.g. CSV); check for, create, move, copy & delete files & directories; encrypt & decrypt files; compress & decompress file images in the database; manage file access & audit rules; check & alter file attributes; get disk information.
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.
- 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. read only, 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
TotallySQL are specialists in application development centered around Microsoft SQL Server, and in particular in creating CLR-based function libraries that enable advanced processing to be carried out within the database itself. They provide a range of off-the-shelf function libraries and can also build bespoke libraries to your specification. They also provide a range of database and data management services - including database design, ETL services, data cleansing and data migration.