gsqlcmd Command-Line Parameters

gsqlcmd Command-Line Parameters

<connection>

This parameter defines a connection to a database.

You can specify the connection in three ways:

  1. Connection name
  2. Connection string
  3. Filename

Connection Name

Using a connection name is the preferred option. It allows you to use short names for encrypted connection strings stored in the configuration files.

For example, gsqlcmd.exe.config can include the following lines (shown unencrypted):

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="master" connectionString="Provider=MSOLEDBSQL;Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI" providerName="System.Data.OleDb" />
    <add name="mssql" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Test;Password=pass;User ID=user" providerName="System.Data.SqlClient" />
    <add name="mysql" connectionString="Server=localhost;Password=pass;User ID=user;Database=Test" providerName="MySql.Data.MySqlClient" />
    <add name="ora" connectionString="Provider=OraOLEDB.Oracle;Password=pass;User ID=user;Data Source=localhost/Test;PLSQLRSet=True" providerName="System.Data.OleDb" />
    <add name="pgsql" connectionString="Server=localhost;Password=pass;User ID=user;Database=rtd" providerName="Npgsql" />
    <add name="sqlite" connectionString="Data Source=test.db;Version=3;" providerName="System.Data.SQLite" />
  </connectionStrings>
</configuration>

This setup allows you to use names like master, mssql, mysql, and others as command line connection parameters.

For example:

gsqlcmd exec master "select name from sys.databases"

To edit connections visually, use the edit-connections mode with gConnectionManager.

Connection String

The second option lets you specify connection strings in the format <ProviderName>;<ConnectionString>.

For example:

gsqlcmd exec "System.Data.OleDb;Provider=MSOLEDBSQL;Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI" "select name from sys.databases"

Here are several examples of valid connection strings:

System.Data.OleDb;Provider=MSOLEDBSQL;Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI
System.Data.Odbc;Driver=ODBC Driver 13 for SQL Server;Server=.\SQLEXPRESS;Database=master;Trusted_Connection=Yes
System.Data.Odbc;DSN=master
System.Data.SqlClient;Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI

You can omit the System.Data.OleDb and System.Data.Odbc providers. For example, these strings are also valid:

Provider=MSOLEDBSQL;Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI
Driver=ODBC Driver 13 for SQL Server;Server=.\SQLEXPRESS;Database=master;Trusted_Connection=Yes
DSN=master

You can pass connection strings using environment variables. For example:

set connection="Provider=MSOLEDBSQL;Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI"

gsqlcmd %connection% "select name from sys.databases"

Make sure to use double quotes for such variables.

File Name

You can use local database file names as connections.

For example:

gsqlcmd exec test.xlsx "SELECT * FROM [sheet1$]"

gsqlcmd automatically finds suitable providers and builds the connection strings.

To test the actual connection string, use the command:

gsqlcmd show text.xlsx connectionString

<input query>

You can use inline queries enclosed in double quotes.

For example:

gsqlcmd exec db "SELECT * FROM SYS.ALL_USERS"

gsqlcmd also supports the SELECT, FROM, WHERE, and ORDER BY clauses for web and file data sources.

For example:

gsqlcmd select "SELECT 'AAPL' AS Symbol, RecordDate, CashAmount AS Amount FROM https://www.nasdaq.com/symbol/AAPL/dividend-history" /limit=1

gsqlcmd Enterprise supports the <connection>::<input query> format, allowing you to execute scripts against a specified connection.

For example, the following command selects data from the dbo.data table of the db2 connection and imports it into the dbo.data table of the db connection:

gsqlcmd import db "db2::SELECT * FROM dbo.data" /table=dbo.data

<input file>

You can specify absolute or relative paths for input files.

For example:

gsqlcmd exec db application-install.sql

gsqlcmd automatically extracts gzip files.

For example, you can select data from a compressed data.xml file with the following command:

gsqlcmd select data.xml.gz

gsqlcmd Enterprise supports the <connection>::<input file> format, allowing you to execute scripts against a specified connection.

For example, the following command selects data from the db2 connection and imports it into the dbo.data table of the db connection:

gsqlcmd import db db2::select-data.sql /table=dbo.data

<input mask>

You can specify a set of files to process in several ways:

  • Using a mask, like install-*.sql or input\*.csv
  • Using a folder name, like input or input\\
  • Using a zip file, like input\\options.zip

gsqlcmd sorts the files alphabetically before processing.

To control the execution order, name your files accordingly. For example:

install-01.sql
install-02.sql

You can then use a simple command like:

gsqlcmd exec db install-*.sql

You can also zip files and use a command like:

gsqlcmd exec db install.zip

<input command>

gsqlcmd executes special database engine commands internally.

For example:

gsqlcmd exec test.db CreateDatabase

SQLite commands include:

CreateDatabase

<input collection>

The get-schema mode requires a collection name as an input parameter.

See details in the mode description.

<input url>

This parameter must be a valid HTTP or HTTPS URL.

For example:

gsqlcmd download https://www.nasdaq.com/symbol/AAPL/dividend-history aapl.htm

URLs can include parameters defined in task files using the {parameter} format.

For example:

gsqlcmd download https://www.nasdaq.com/symbol/{symbol}/dividend-history {symbol}.htm

@<task file>

Task files allow you to specify sets of input and output parameters and options.

For example, you can list input files like install-01.sql and install-02.sql in a file named task.txt:

install-01.sql
install-02.sql

You can then execute the command:

gsqlcmd exec db @task.txt

Use @ to indicate that the input file is a task file. Otherwise, gsqlcmd will treat it as a script or data source.

You can omit @ in the download mode since its first parameter accepts URLs only.

You can also omit the task file name after @ to read task lines from stdin. For example:

gsqlcmd exec db @ < task.txt

The task file used as a positional parameter can contain one, two, or three parameters separated by spaces, depending on the mode.

For example, the convert mode accepts three parameters. You can create a file like this:

https://www.nasdaq.com/symbol/AAPL/dividend-history aapl.htm aapl.csv
https://www.nasdaq.com/symbol/MSFT/dividend-history msft.htm msft.csv

Then, use the command:

gsqlcmd convert @task.txt

In the examples above, the task file has no headers.

You can also use task files with headers to specify positional parameters and options:

URL                                                   FileName  OutputFileName  Symbol
https://www.nasdaq.com/symbol/AAPL/dividend-history   aapl.htm  aapl.csv        AAPL
https://www.nasdaq.com/symbol/MSFT/dividend-history   msft.htm  msft.csv        AAPL

You can also use task files in CSV format:

URL;FileName;OutputFileName;Symbol
https://www.nasdaq.com/symbol/AAPL/dividend-history;aapl.htm;aapl.csv;AAPL
https://www.nasdaq.com/symbol/MSFT/dividend-history;msft.htm;msft.csv;AAPL

URL, FileName, and OutputFileName are special names for the input positional parameters.

Other columns can be used as options or command parameter values. For example, you can add options like /add, /rootPath, or /pages.

You can specify task files with the /taskFile option.

This allows you to specify positional parameters in the command line and options in a task file.

For the example shown above, you can place the Symbol column in a task file and use the following command:

gsqlcmd convert https://www.nasdaq.com/symbol/{symbol}/dividend-history {symbol}.htm {symbol}.csv /taskFile=task.txt

See details in the /taskFile option description.

<output file>

This optional parameter defines the output file name.

By default, gsqlcmd writes output data to the console.

The output file extension determines the default output format:

*.txt  - text
*.csv  - CSV
*.htm  - HTML
*.html - HTML
*.xml  - XML
*.json - JSON

Use the /asText, /asCsv, /asHtml, /asXml, and /asJson options to specify the output format explicitly.

<output mask>

You can specify an output file mask based on input URLs or file names to construct output file names.

Here are several examples:

Input Mask      Output Mask   Actual Input file   Actual Output File
AAPL.htm        *.csv         AAPL.htm            AAPL.csv
*.htm           *.csv         AAPL.htm            AAPL.csv
options-*.htm   *.csv         options-AAPL.htm    options-AAPL.csv
htm\*.htm       csv\*.csv     htm\AAPL.htm        csv\AAPL.csv
csv\*.csv       output\*      csv\AAPL.csv        output\AAPL.csv
csv\*.csv       output\*.*    csv\AAPL.csv        output\AAPL.csv
csv\*.csv       output.csv    csv\AAPL.csv        output.csv

You can also use the output file mask with input URLs, as gsqlcmd extracts file names from URLs.

For example:

gsqlcmd download https://www.gsqlcmd.com/download.htm *

gsqlcmd uses specific query parameters or the last segments of URLs to determine file names.

For example, the following command saves the output to AAPL.json:

gsqlcmd download "https://finance.services.appex.bing.com/Market.svc/ChartAndQuotes?symbols=AAPL&chartType=1y" *.json

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.