Get Help

exec

Runs one command — typically a SQL query — against a data source defined in the project and prints the rows it returns.

Synopsis 

catcli exec --dataSource <name> --command <text> [-p <path>] [-l <level>] [-q] [--format <form>]

Options

Short Long Value Meaning
-d --dataSource name Required. Name of a data source defined in the project.
-c --command text Required. The command to run — SQL, DAX or whatever the data source’s provider understands. Quote it.
-p --project path Project file, or a directory with exactly one *.cat.yaml. Default: the current directory.
-l --loggingLevel level None (default), Error, Information, Debug, … — see Logging.
-q --quiet Hide the “working offline” notice.
--format table | csv Output form. Default: table at a terminal, csv when standard output is redirected (a pipe or a file). No short name — -f is the test-name filter on run and show.

What it does

Opens the project so that the data source — its provider, connection string, environment variables — is exactly what the tests use, sends the command through that provider, and prints what comes back. It is a troubleshooting tool: it answers what does this query return through CAT, with this connection? for data sources that have no query tool of their own (Excel and CSV files, for instance) and for checking a connection string without writing a test.

It is also a data export: pipe or redirect standard output and exec writes plain CSV, one row per line, with nothing else in the stream — catcli exec -d AERO_PROD -c "SELECT * FROM DIM.GATES" > rows.csv. Pass --format csv to get the same form at a terminal, or --format table to keep the framed table through a pipe.

Output

At a terminal (or with --format table): the result set as a table — one column per result column, numbers and dates right-aligned, NULL shown as an empty cell — followed by the row count:

╭──────────┬───────────────┬───────────╮
│ GateCode │ Terminal      │ Capacity  │
├──────────┼───────────────┼───────────┤
│ A1       │ North         │       180 │
│ A2       │ North         │       180 │
╰──────────┴───────────────┴───────────╯

The command returned 2 rows.

When the provider reports an error, the error message is printed after the (empty) table:

!!! Error: Invalid object name 'DIM.GATE'.

Through a pipe or a file (or with --format csv): standard output carries only the CSV — a header row with the column names, then one row per result row, nothing else. NULL is an empty field; an empty string is "" — that is how a reader tells the two apart:

GateCode,Terminal,Capacity
A1,North,180
A2,North,180

The row count and, when the provider reports an error, its message go to standard error instead, so standard output stays a stream a CSV reader can parse without stripping a trailer:

The command returned 2 rows.

or, on a provider error (standard output is then empty):

The command returned 0 rows.
Error: Invalid object name 'DIM.GATE'.

Exit code

0 when the project opened and the command was sent — also when the command itself failed; the failure is reported in the output text only. 1 when the project could not be opened or the data source name does not exist. 2–7 from the sign-in and plan check — see Exit codes.

Examples 

# query a data source of the project in the current directory
catcli exec -d AERO_PROD -c "SELECT * FROM DIM.GATES"

# same, long names, explicit project
catcli exec --dataSource AERO_PROD --command "SELECT COUNT(*) FROM DIM.GATES" --project "D:\Testing\Aero.cat.yaml"

# the connection does not work? see what CAT does while it opens the project
catcli exec -d AERO_PROD -c "SELECT 1" -l Information

# export a query's result to a file
catcli exec -d AERO_PROD -c "SELECT * FROM DIM.GATES" > gates.csv

# force the framed table even though the output is redirected
catcli exec -d AERO_PROD -c "SELECT * FROM DIM.GATES" --format table > gates.txt
  • Introduction — conventions, sign-in, exit codes.
  • Data sources and Providers — what each provider accepts as a command.
  • show — the data-source names of the project are in its summary.
← open
new →