Use exec() to run OS commands or other programs on files returned in query results, using their fullpath. On Windows the basic file management commands such as move, copy, del and rename are always available because exec() runs in a shell.

Run 'command' with option '/opt1' on multiple files

SELECT exec('command',  '/opt1',   fullpath)
FROM files
WHERE type = 'f'
and ...

  • The first argument to exec() should be a string specifying the command/program to run. Each argument or option for the command should be a separate argument to exec(), generally one of these will be the fullpath of the file you want to operate on.

  • Progams you invoke should be on the PATH, or in the directory where you launched Crab, or you must provide the full path for the program. Use the windows 'start' command to run gui applications.

  • Use the WHERE clause to specify which objects to run 'command' on.
    No WHERE clause means run it on every object scanned

  • To run only on direct children of 'SourceDirectory', i.e. not recursive

  •     WHERE parentpath = 'C:\somepath\SourceDirectory\'

    To run on all children of 'SourceDirectory,' i.e. recursive

    WHERE parentpath like 'C:\somepath\SourceDirectory\%'

  • You should test the logic of queries that change the filesystem with an 'echo' command before use

  • Don't use exec() with commands that give run-time prompts, exec() doesn't display them.

  • exec() runs sychronously, meaning exec() waits for each command to complete before starting the next.

    What it means for a command to complete varies from one program to another, e.g. if you open an .mp3 file in windows media player, the .mp3 file will start playing immediately and the command will complete immediately. So if you play 100 mp3's in quick succession you'll only hear the last one. Similarly, if you open files in explorer, the command is complete when the explorer window has opened, not when you have finished interacting with the window. If you use exec() to open 100 files in explorer, you'll get 100 open windows.

Run 'command' on every file in directory 'MyProject'

SELECT exec('command', fullpath)
FROM files
WHERE type = 'f'
and parentpath = 'C:\SomePath\MyProject\';

This is the generic template for running a command on every file in the 'MyProject' directory.

The example is non recursive because we're only picking up files where the parentpath is equal to 'MyProject'.

E.g. Recursively delete all .pyc files from 'MyProject' directory

Here the 'del' command is used with its /f option. This option forces deletion of readonly files without prompting for confirmation.

WARNING: If you ran this query without a WHERE clause it would delete every file scanned

SELECT exec('del', '/f', fullpath)
FROM files
WHERE parentpath like 'C:\somepath\MyProject\%'
and extension = '.pyc';

This example is recursive because we're using a wildcard pattern to filter parentpath, which is satisfied by objects in the 'MyProject' directory tree at any depth.

E.g. Open in File Explorer the directory that has the most Excel spreadsheets

SELECT exec('explorer', parentpath), count(*)
FROM files
WHERE extension like '.xls%'
GROUP BY parentpath ORDER BY count(*) DESC LIMIT 1;

This query counts files which have an extension starting '.xls' (so it matches '.xls' and '.xlsx' files), and groups the count by parent directory. Then it sorts the parent directories by this count and returns just one row - the parent directory with the most Excel files. Finally the query runs 'explorer' to open that directory in File Explorer.

E.g. Play a random Jack Johnson song

SELECT exec('start', 'wmplayer', fullpath) FROM files
WHERE fullpath like '%jack%' and fullpath like '%jo%son%' and extension = '.mp3'
ORDER BY random() LIMIT 1;

This query returns .mp3 files which have 'jack' and 'johnson' or 'jonson' somewhere in the filename or the directory name. The results are sorted randomly and the first row is returned. The exec() call uses the Windows start command to open windows media player on the file at fullpath. If you haven't set up media player, substitute 'itunes' or similar.

Run 'command' 1000 times

SELECT exec('command')
FROM iterator
WHERE i <= 1000;

This is the generic template for running a command multiple times.

iterator is a virtual table with one field i which goes from 1 to 1000000000

Unzip files

The open source utility 7za.exe supports unpacking .zip files at the Windows command line. It is distributed with Crab, you can find it at CrabHome\CrabData\7za.exe.

Unzip files to directory 'target Dir'

SELECT exec('7za.exe',  'e',  '-aos',  fullpath,  '‑oC:\somepath\target Dir\')
FROM files
WHERE extension = '.zip'
and ...

  • Use '7za.exe' with either -aoa or -aos, so as to suppress run-time prompts: exec() doesn't display them.

    • Use the '-aoa' option to always overwrite (automatic overwrite: always) when there is a name collision

    • Use the '-aos' option to never overwrite (automatic overwrite: skip) when there is a name collision

  • Avoid runtime prompts by using the '-y' option to always overwrite

  • Option 'e' specifies Extract files from archive without using directory names.

  • Option '-o' specifies the output directory. No space allowed between the o and the path.

  • Assumes 7za.exe is in a directory on the path. If not, use its full path, e.g. exec('C:\Users\johnsmith\CrabHome\CrabData\7za.exe', ...

  • Specify which files to unzip using the WHERE clause.
    No WHERE clause means run it on every object scanned

E.g. Recursively unzip all files from 'Source Dir' to 'Target Dir'

Here the -aos option tells 7za.exe to never overwrite files, and the -o option specifies a target directory. 'Target Dir' must exist before the query is run.

SELECT exec('C:\Users\johnsmith\CrabHome\CrabData\7za.exe', 'e', '-aos, fullpath,'‑oC:\Users\johnsmith\Target Dir\')
FROM files
WHERE parentpath like 'C:\Users\johnsmith\Source Dir\%'
and extension = '.zip';

Tip: Test exec() queries by running them with the echo command

Prior to running a query that will modify the filesystem you should prefix your exec() function call with an echo command to check exactly which commands will be run on which files. E.g.

SELECT exec('echo', '7za.exe', 'e', '-y', fullpath,'‑oC:\Users\johnsmith\Target Dir\')
FROM files
WHERE parentpath like 'C:\Users\johnsmith\Source Dir\%'
and extension = '.zip';

Only when you're happy that the right files are being processed should you remove the 'echo'

SELECT exec('7za.exe', 'e', '-y', fullpath,'‑oC:\Users\johnsmith\Target Dir\')
FROM files
WHERE parentpath like 'C:\Users\johnsmith\Source Dir\%'
and extension = '.zip';

Tip: Suppress exec() echo to screen

By default exec() writes every command that is run to your Command Line window, together with its output. If you are running hundreds of thousands or millions of commands this will be slow, and the screen will be a mess.

You can resolve this by throwing away the output, use the following command before running the query:

%output NUL

Any error messages will still go to the Command Line window, as will subsequent CRAB> prompts

To get output back do this:

%output stdout

If you want to improve query speed and keep a log of the output, use a filename in place of NUL, e.g.

%output 'C:\somepath\MyLogFile.txt'

Tip: Check if files are still present with pathexists()

Crab doesn't track files that are moved or deleted after a scan. To check that a file or directory is still present at query run time use the pathexists() function.

E.g. to unzip a bunch of files when some have been moved or deleted since the last scan:

SELECT exec('7za.exe', 'e', '-y', fullpath,'‑oC:\Users\johnsmith\Target Dir\')
FROM files
WHERE parentpath like 'C:\Users\johnsmith\Source Dir\%'
and extension = '.zip'
and pathexists(fullpath);

Tip: Use shell features with exec()

Because exec() always runs in a shell on Windows, you can use pipes and I/O redirection. If you build strings to pass to the shell you must concatenate double quotes around names and paths to avoid problems with any that contain spaces. E.g.

SELECT exec('dir "'|| fullpath || '" >> DirMyFiles.txt')
FROM files;

We use Google and Clicky Analytics to count how many people visit, and how many come back.
For now, if you don't want a cookie, please browse in incognito mode.

© 2019 Etia UK