Invantive UniversalSQL 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 UniversalSQL.
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 files-table:
select fle.file_path
from files@os('c:\temp\sample', '*.png', false /* Do not recurse. */) fle
The 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
The basename function returns the file name of a full file name and path.
Since ignore_error is false, any error during the copying will trigger an Invantive UniversalSQL 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
The table file_actions also includes the results of other file operations such as a delete command.