gsqlcmd Command-Line Parameters
<connection>
This parameter defines a connection to a database.
You can specify the connection in three ways:
- Connection name
- Connection string
- 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
orinput\*.csv
- Using a folder name, like
input
orinput\\
- 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