Invantive SQL provides access to the operating system through the OS-driver. Using SQL statements, you can read, create, copy and delete files. This wiki provides a sample for database professionals on copying multiple files from a source directory to another directory using SQL-statements, such as for creating backup files. It is also possible to bundle the SQL-statements to copy files into an anonymous stored procedure or named procedure.
The OS-driver is only available on Linux, Windows, iOS and Android. The OS-driver is typically not available, licensed and/or configured on web-based deployments of Invantive SQL.
The SQL query to copy files does not require access to the Windows Command Shell.
The first step is to select some files to copy. These source files are all in folder named
c:\temp\sample and solely the images in the base folder are selected using the
select fle.file_path from files@os('c:\temp\sample', '*.png', false /* Do not recurse. */) fle
files-table lists all files matching selection criteria: specified directory, file mask and whether to recurse. For each of the multiple files the full file name and path are returned in the column
file_path. No additional file information is returned in the results. When necessary, the table function
file_info provides statistics such as audit information and size.
The actual copying is done using an
insert into file_copy_actions as in:
insert into file_copy_actions@Os ( file_path_source , file_path_target , overwrite_existing , ignore_error , create_directory ) select fle.file_path , 'c:\temp\sample-backup\MY-COPY-' || basename(fle.file_path) , true , false , true from files@os('c:\temp\sample', '*.png', false) fle
basename function returns the file name of a full file name and path.
ignore_error is false, any error during the copying will trigger an Invantive SQL exception.
The copied files are visible in Windows Explorer:
The results of the copying of files using SQL can also be displayed using a query on
file_actions which contains all executed file actions on the OS-driver:
select * from file_actions@Os
file_actions also includes the results of other file operations such as a delete command.