gsqlcmd Command-Line Parameters

gsqlcmd Command-Line Parameters

<connection>

The parameter defines a connection to a database.

There are three options to specify the connection:

  1. Connection name
  2. Connection string
  3. Filename

Connection Name

The first option is the best as you can use short names of encrypted connection strings stored in the configuration files.

For example, gsqlcmd.exe.config can contain 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="db2" connectionString="Driver=IBM DB2 ODBC DRIVER;Hostname=localhost;Port=50000;Protocol=TCPIP;Database=Test;Pwd=pass;UID=user;LONGDATACOMPAT=1" providerName="System.Data.Odbc" />
    <add name="nuodb" connectionString="Server=localhost;Password=pass;User=user;Database=Test" providerName="NuoDb.Data.Client" />
    <add name="pgsql" connectionString="Server=localhost;Password=pass;User ID=user;Database=rtd" providerName="Npgsql" />
    <add name="sqlce" connectionString="Data Source=test.sdf" providerName="System.Data.SqlServerCe.4.0" />
    <add name="sqlite" connectionString="Data Source=test.db;Version=3;" providerName="System.Data.SQLite" />
  </connectionStrings>
</configuration>

This allows using names like master, mssql, mysql, and others as the command line connection parameter.

For example:

gsqlcmd exec master "select name from sys.databases"

Use the edit-connections mode to edit connections in a visual mode using gConnectionManager.

Connection String

The second option allows specifying connection strings like <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 the 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 System.Data.OleDb and System.Data.Odbc providers. For example, the strings below 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"

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 finds suitable providers and builds the connection strings automatically.

You can test the actual connection string using the command:

gsqlcmd show text.xlsx connectionString

<input query>

You can use inline queries in double quotes.

For example:

gsqlcmd exec db "SELECT * FROM SYS.ALL_USERS"

gsqlcmd also internally 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> form that allows executing the script against the specified connection.

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

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

<input file>

You can use absolute and relative paths of input files.

For example:

gsqlcmd exec db application-install.sql

gsqlcmd extracts gzip files automatically.

For example, you can use the following command to select data from the compressed data.xml:

gsqlcmd select data.xml.gz

gsqlcmd Enterprise supports the <connection>::<input file> form that allows executing the script against the specified connection.

For example, the following command selects data from the db2 connection and imports data 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.

So, you can set the desired execution order naming files accordingly. For example, you can name files like:

install-01.sql
install-02.sql

and use a simple command like:

gsqlcmd exec db install-*.sql

Also, you can 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:

CreateDatabase

SQL Server Compact commands:

CreateDatabase | Compact | Repair | Shrink | Upgrade

<input collection>

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

See details in the mode description.

<input url>

The 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 contain parameters defined in task files using the {parameter} form.

For example:

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

@<task file>

Task files allow specifying sets of input and output parameters and options.

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

install-01.sql
install-02.sql

and then execute the command:

gsqlcmd exec db @task.txt

Use @ to force using the input file as a task file. Otherwise, gsqlcmd will use it as a script or data source.

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

You can omit the task file name after @ to read the 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. So, 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

and use the command:

gsqlcmd convert @task.txt

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

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

For example:

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

Also, you can use task files in the 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 of the input positional parameters.

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

Note that you can specify task files with the /taskFile option.

So, you can specify positional parameters in a 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 an output file name.

By default, gsqlcmd writes output data to the console.

The output file extension defines 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 build 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 use the output file mask with input URLs also as gsqlcmd extracts file names from URLs.

For example:

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

gsqlcmd uses specific query parameters like Symbols or the last URL segments to find a file name.

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