sql

Table of contents

• Synopsis
• Description
• Required argument
• Connection options
• Input and output options
  • -input-file
  • --print-queries
  • --disable-status
  • --output-format
  • --output-format-parameters
  • -output-file
  • --limit
• Examples
  • Use cloud storage in SQL commands

sql - execute SQL queries and scripts in dbvr.

Synopsis

dbvr sql [-hV]
         [-format=<outputFormat> | --output-format=<outputFormat>]
         [-in=<inputFile> | -input-file=<inputFile>]
         [--print-queries]
         [--disable-status]
         [-l=<limit> | --limit=<limit>]
         [-op=<outputFormatParameters> | --output-format-parameters=<outputFormatParameters>]
         [-out=<outputFile> | -output-file=<outputFile>]
         [-p=<dbPassword> | --password=<dbPassword>]
         [--project=<projectIdOrName>]
         [-u=<dbUser> | --user=<dbUser>]
         [-auth=<authParams> | --auth-property=<authParams>]...
         [-ext=<providerParams> | --extended-property=<providerParams>]...
         [-prop=<connectionParams> | --property=<connectionParams>]...
         [[-net=<handlerParams> | --network-handler-param=<handlerParams>]...
          [-net-save-pwd=<savePassword> | --network-handler-save-password=<savePassword>]]
         (-ds=<existDataSourceIdOrName> | --datasource=<existDataSourceIdOrName> |
          -con=<connectionSpec> | -connect=<connectionSpec> | -ds-spec=<connectionSpec> | --datasource-specification=<connectionSpec> |
          [--driver=<driver>
            [[--host=<host>]
             [--database=<dbName>]
             [--server=<server>]
             [--url=<url>]
             [--auth-model=<authModel>]
             [--port=<port>]
             [--folder=<folder>]
             [--name=<dataSourceName>]
             [--save-password=<savePassword>]]])
         [<query>]

Tip: You can also use global options with this command.

Tip: SQL execution in dbvr follows the same engine and configuration model as in DBeaver. For UI-based query execution, result export, and execution settings, see SQL execution.

Description

Execute a SQL query or script.

Tip: To inspect database objects before running SQL queries, use meta.

Required argument

<query>

SQL statement to execute.

If specified, it must be the last argument in the command.
If omitted, SQL is read from standard input or from -in/-input-file.

Connection options

Use connection settings to specify which database this command runs against.

Connect in one of these ways:

• use an existing datasource with -ds or --datasource
• provide a full connection specification with -con, -ds-spec, -connect, or --datasource-specification
• define a connection inline with --driver and connection parameters

You can also provide credentials, network handlers, and driver-specific properties.

For details on all connection settings, see Connection options.

Input and output options

-input-file

-in, -input-file=<inputFile>

Read SQL from a file instead of passing a query argument.

Tip: You can read scripts not only from local files, but also from cloud storage. For configuration, see Configure cloud providers. For usage examples, see Use cloud storage in SQL commands.

--print-queries

--print-queries

Print each SQL query above its result in the output.

Example:

dbvr sql \
  --driver=sqlite_ee \
  --url=jdbc:sqlite:/path/to/Chinook.db \
  --print-queries \
  "select * from Album limit 3;"

Output:

select * from Album limit 3
|AlbumId|Title                                |ArtistId|
|-------|-------------------------------------|--------|
|1      |For Those About To Rock We Salute You|1       |
|2      |Balls to the Wall                    |2       |
|3      |Restless and Wild                    |2       |
Rows read: 3, (54ms)

--disable-status

--disable-status

Turn off the status line that normally follows each result (row count and execution time).

Use this when you're piping output into another tool and don't want the extra line mixed in.

Example:

dbvr sql \
  --driver=sqlite_ee \
  --url=jdbc:sqlite:/path/to/Chinook.db \
  --disable-status \
  "select * from Album limit 3;"

Output:

select * from Album limit 3
|AlbumId|Title                                |ArtistId|
|-------|-------------------------------------|--------|
|1      |For Those About To Rock We Salute You|1       |
|2      |Balls to the Wall                    |2       |
|3      |Restless and Wild                    |2       |

--output-format

-format, --output-format=<outputFormat>

Write the execution result in a specific format.

Supported formats:

• csv
• txt
• json
• xml
• html
• md (markdown)
• sql
• php (source)
• parquet
• xlsx
• dbunit

Default: txt.

--output-format-parameters

-op, --output-format-parameters=prop1=value1,prop2=value2

Exporter options as a list of properties. Comma and space are delimiters.

Example:

dbvr sql \
  --driver=sqlite_ee \
  --url=jdbc:sqlite:/path/to/Chinook.db \
  -format=csv \
  -op="delimiter=;" \
  -out=orders.csv \
  "select * from Album limit 3;"

-output-file

-out, -output-file=<outputFile>

Write the execution result to a file.

Example:

dbvr sql \
  --driver=postgres-jdbc \
  --host=localhost \
  --port=5432 \
  --database=testdb \
  -u=admin \
  -p=password \
  -format=json \
  -out=path/to/orders.json \
  "select * from public.orders limit 10;"

Tip: Export formats and options use the same exporters as in DBeaver. For details on supported formats and configuration, see Data export.

Tip: You can write results not only to local files, but also to cloud storage. For configuration, see Configure cloud providers. For usage examples, see Use cloud storage in SQL commands.

--limit

-l, --limit="[offset,]limit"

Limit the number of fetched rows and optionally set an offset. Default: 1000.

Example:

dbvr sql \
  --driver=postgres-jdbc \
  --host=localhost \
  --port=5432 \
  --database=testdb \
  -u=admin \
  -p=password \
  -format=json \
  -l=20,10 \
  "select * from public.orders;"

Examples

Run a query using an existing datasource:

dbvr sql -ds=pg-local "select current_date;"

Run a script from a file:

dbvr sql -ds=pg-local -in=path/to/script.sql

Run with inline connection parameters:

dbvr sql \
  --driver=postgres-jdbc \
  --host=localhost \
  --database=testdb \
  -u=admin \
  -p=password \
  "select * from users;"

Export results to CSV:

dbvr sql \
  -ds=pg-local \
  -format=csv \
  -out=path/to/result.csv \
  "select * from users;"

Use cloud storage in SQL commands

Write query results to Azure Blob Storage:

dbvr sql \
  -ds=postgres \
  -out=azb:///output.txt?endpoint=https://myaccount.blob.core.windows.net \
  -format=txt \
  "select * from orders;"

Read a script from Google Cloud Storage and write results back:

dbvr sql \
  -ds=postgres \
  -in=gs://my-bucket/script.sql \
  -out=gs://my-bucket/output.txt \
  -format=txt

Read a script from Amazon S3 and write results back:

dbvr sql \
  -ds=postgres \
  -in=s3:///my-bucket/script.sql \
  -out=s3:///my-bucket/output.txt \
  -format=txt