Execution Options for gsqlcmd

Execution Options for gsqlcmd

/?

Use this option to display extended command-line help.

For example:

gsqlcmd /?

You can also use the help mode for comprehensive assistance.

/append

This option appends output data to the specified output file.

For example:

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

You can process multiple input files and specify a single output file without the /append option like this:

gsqlcmd convert input\*.CSV data.csv

/check

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

First, create a file named urls.txt with a list of URLs to check:

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

Then run the command:

gsqlcmd download @urls.txt /check

The output will look like this:

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

/formatFile=<format file>

Use this option to replace source column names with those from the specified format file.

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

/fromFiles=<mask>

This option updates database records using the contents of specified files.

For example:

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

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

The predefined @filename and @text parameters represent the file name (without extension) and the file contents, respectively.

For MySQL, PostgreSQL, Oracle, and Snowflake, use :filename and :text instead of @filename and @text.

You can combine this feature with the /toFiles option to export records to separate files:

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

These two options enable you to export and import CMS content, allowing for offline editing with 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 instance, a batch file to insert file contents into the downloads table would look like this:

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 the parsed input file.

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

For example, the downloads table has filename and contents fields, so the command can be simplified:

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

/[location=]local | user | app

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

The local option points to a configuration file in the current directory, which is the default setting. Named connections from this file are available only in the current directory.

The user option points to a configuration file in the %LocalAppData%\Gartle\gsqlcmd directory, making named connections available only for the current user.

The app option points to the configuration file in the gsqlcmd installation folder, allowing named connections to be accessible to all users on the machine.

/offset=<number of rows>

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

For example:

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

Note that the first row has an offset of 0.

/options=<file>

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

For example:

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

You can use multiple files in either JSON or text format.

Here's a sample Google client secret file in 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"]
}}

Here's the equivalent in 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

Note that options with underscores are equivalent to those without underscores. For example, client_id and clientId are treated as the same.

You can include line comments in the text format, starting with the # character:

# Comment

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

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

This is necessary if you don't specify the connection as a positional parameter.

For example:

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

You can also use this option to filter connections and providers in the show-connections and show-providers modes:

gsqlcmd show-connections /oracle

gsqlcmd show-providers /mysql

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

Use this option to define parameter values and values for columns that are 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 case, the tool uses the value AAPL for the Symbol column and the timestamp of AAPL.CSV for the LoadDate column.

Refer to the list of functions available with the /set option.

You can declare parameters using the following formats:

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

Avoid using the /set option with scripts that already contain parameter declarations in SQL code, such as those for creating stored procedures and triggers.

gsqlcmd determines parameter types based on actual value types like strings, numbers, or datetimes. Use single quotes to explicitly define string type values, e.g., /set=p1='123'.

/taskFile=<file name>

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

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

Here's a sample batch file demonstrating 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 example, both the URL and the output file name contain the {symbol} parameter. You can provide a list of values for this parameter in the symbols.txt task file like this:

Symbol
AAPL
FB
MSFT

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

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

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

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

Here's another sample 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

You can also use a CSV format.

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

gsqlcmd Enterprise allows you to retrieve task values from a database using the format <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, website.com is a named connection to a website database.

The command exports the values from the content field to files named after the alias column values (replace alias.htm with your field name).

The SQL query output must contain exactly two fields: one for the file name and one for the file content.

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

After editing, 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

Refer to the /fromFiles option for more details.

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.