gsqlcmd Execution Options

gsqlcmd Execution Options

/?

Use this option to show an extended command-line help.

For example:

gsqlcmd /?

Use the help mode to get complete help.

/append

Use this option to append output data to the output file.

For example:

gsqlcmd convert input\AAPL.CSV data.csv /append

You can process a set of input files specifying a single output file without the /append option like

gsqlcmd convert input\*.CSV data.csv

/check

Use this option to check the status codes of the web server responses in the download mode.

For example, create a file like urls.txt with a list of URLs to check:

http://www.savetodb.com/
https://www.savetodb.com/
https://www.savetodb.com/test

And run a command:

gsqlcmd download @urls.txt /check

You will get the result like this:

http://www.savetodb.com/ 301 https://www.savetodb.com/
https://www.savetodb.com/ 200
https://www.savetodb.com/test 404

/formatFile=<format file>

Use this option to substitute source column names with the names from the format file.

You can create such files using the make-fmt mode.

/fromFiles=<mask>

Use this option to update database records using file contents.

For example:

gsqlcmd exec website.com "UPDATE contents SET content = @text WHERE alias = @filename" /fromFiles=*.htm

In this example, the website.com is a named connection of a website database.

The predefined @filename and @text parameters have a file name without extension and the file contents.

Use the :filename and :text parameter names instead of @filename and @text for MySQL, PostgreSQL, Oracle, DB2, NuoDB, and Snowflake.

You can use this feature in conjunction with the /toFiles option that allows exporting records to separate files like:

gsqlcmd exec website.com "SELECT alias, content FROM contents" alias.htm /toFiles

These two options allow exporting and importing CMS contents to edit the contents offline using any text tool or editor.

/limit=<number of rows>

Use this option to limit the number of output rows.

For example:

gsqlcmd select db dbo.cashbook /limit=10

gsqlcmd select cashbook.csv /limit=10

Use /limit=1 to generate a single command for each file in the make and import modes.

For example, a batch file to insert file contents into the downloads table looks like

set template=INSERT INTO downloads (filename, contents) VALUES (@filename, @content)

gsqlcmd import db *.json "/table=%template%" /set=filename=FileName();contents=FileText() /limit=1

Without the /limit option, gsqlcmd imports all rows from a parsed input file.

Target objects and SQL templates can get values from a parsed file or calculated by functions like FileName() and FileText().

For example, the downloads table used above has the filename and contents fields. So, the command can be easier:

gsqlcmd import db *.json /table=downloads /set=filename=FileName();contents=FileText() /limit=1

/[location=]local | user | app

Use this option to specify a used location of the gsqlcmd.exe.config configuration file.

The local option defines a configuration file in the current directory. It is the default value.

Named connections from such files are available in the current directory only.

The user option defines a configuration file in the %LocalAppData%\Gartle\gsqlcmd directory.

Named connections from this file are available for the current user only.

The app option defines the configuration file in the gsqlcmd installation folder.

Named connections from this file usually are available for any user on the machine.

/offset=<number of rows>

Use this option to skip the specified number of rows in the output.

For example:

gsqlcmd select cashbook.csv /offset=10 /limit=10

The first row has offset 0.

/options=<file>

Use this option to specify a file with command-line options.

For example:

/options=options.json /options=options.txt

You can use multiple files in JSON or text format.

Below is a sample of a Google client secret file in the JSON format:

{"installed":{
    "client_id":"123456789012-abcdefghijklmnopqrstuvwxyz123456.apps.googleusercontent.com",
    "project_id":"searchconsoleapi-123456",
    "auth_uri":"https://accounts.google.com/o/oauth2/auth",
    "token_uri":"https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
    "client_secret":"Abcdefghijklmnopqrstuvwx",
    "redirect_uris":["urn:ietf:wg:oauth:2.0:oob","http://localhost"]
}}

Below is an equivalent in the text format:

client_id=123456789012-abcdefghijklmnopqrstuvwxyz123456.apps.googleusercontent.com
project_id=searchconsoleapi-123456
auth_uri=https://accounts.google.com/o/oauth2/auth
token_uri=https://oauth2.googleapis.com/token
auth_provider_x509_cert_url=https://www.googleapis.com/oauth2/v1/certs
client_secret=Abcdefghijklmnopqrstuvwx
redirect_uris=urn:ietf:wg:oauth:2.0:oob
redirect_uris=http://localhost&quot

Note that options with underscores are equivalent to options without the underscores.

For example, client_id and clientId are equal.

You can place line comments in the text format starting with the # character like:

# Comment

/[serverType=]mssql | sqlce | mysql | oracle | db2 | nuodb | pgsql | snowflake | sqlite | foxpro | dbf | excel | csv

Use this option to define a target database platform for code generation.

Use it if you do not specify the connection as a positional parameter.

For example:

gsqlcmd make-create data.csv create.sql /table=dbo.payments /mssql

Also, use this option to filter connections and providers in the show-connections and show-providers modes.

For example:

gsqlcmd show-connections /oracle

gsqlcmd show-providers /mysql

/set=<parameter>=<value | function>[;...]

Use this option to define parameter values and values for columns absent in source files.

For example:

gsqlcmd make db AAPL.csv AAPL.sql /table=dbo.prices /insert /set=Symbol=AAPL;LoadDate=FileDateTime(AAPL.CSV)

In this example, the tool will use the AAPL value for the Symbol column and the AAPL.CSV file time for the LoadDate column.

See a list of functions available with the /set option.

Use the following forms to declare parameters:

  • @Parameter in SQL scripts and commands for Microsoft SQL Server, Microsoft SQL Server Compact, and SQLite;
  • :Parameter in SQL scripts and commands for MySQL, MariaDB, PostgreSQL, Oracle Database, IBM DB2, NuoDB, and Snowflake;
  • {Parameter} in URLs.

Do not use the /set option with scripts that contain parameter declarations in SQL code.

For example, if the script contains codes for creating stored procedures and triggers.

gsqlcmd determines parameter types using actual value types like strings, numbers, or datetimes.

Use single quotes to strongly define string type values like /set=p1='123'.

/taskFile=<file name>

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

You can specify a task file as a positional parameter or using this option.

Here is a sample of a batch file with typical usage of task files:

@echo off

set url="https://www.nasdaq.com/symbol/{symbol}/dividend-history"

gsqlcmd download %url% src\{symbol}.htm /taskfile=symbols.txt /echoOutputFileName

@pause

In this sample, both the URL and the output file name contain the {symbol} parameter.

You can specify a list of values for this parameter in the symbols.txt task file like:

Symbol
AAPL
FB
MSFT

gsqlcmd will execute the specified command for every row, replacing {symbol} parameter with a value.

You can use task file values in the /set and /add options like

/set=Symbol={Symbol} /add=Symbol={Symbol}

The /set option sets the script parameter value, while the /add option adds an output column with the specified value.

Here is a sample of a batch file with two parameters:

@echo off

set url="https://www.msn.com/en-us/finance/stocks/optionsajax/{symbol}/?date={date}"

gsqlcmd download %url% src\{symbol}-{date}.JSON /taskfile=symbols.txt /echoUrl

@pause

You can specify two parameters in separate columns:

Symbol  Date
AAPL    20200117
AAPL    20200619

Also, you can use a CSV format.

In any case, you can easily update task files from a database using SELECT commands.

gsqlcmd Enterprise allows getting task values from a database using the form like <connection>::<query>.

For example:

@echo off

set url="https://www.nasdaq.com/symbol/{symbol}/dividend-history"

gsqlcmd download %url% src\{symbol}.htm "/taskfile=db::SELECT Symbol FROM dbo.symbols" /echoOutputFileName

@pause

/toFiles

Use this option to export database data to separate files.

For example:

gsqlcmd website.com "SELECT alias, content FROM contents" alias.htm /toFiles /outputCodepage=65001

In this example, the website.com is a named connection of a website database.

The command exports the content field's values to files named with the alias column values (change alias.htm to your field name).

The SQL query output must contain exactly two fields used as a file name and the file content.

As a result, you can edit the contents of the exported files using any tool or text editor.

Then you can update the database with the file contents using the following command:

gsqlcmd exec website.com "UPDATE contents SET content = @text WHERE alias = @filename" /fromFiles=*.htm

See details in the /fromFiles option.