gsqlcmd supports executing native SQL scripts for any supported database platform.
Use the exec mode in the following form:
gsqlcmd [exec] <connection> [<input> [<output>]]
where input:
<query>|<file>|<mask>|@[<task>]|<command>|<table>|<view>
Here are several examples:
gsqlcmd exec db install.sql gsqlcmd exec db install.sql result.txt gsqlcmd exec db install.sql.gz gsqlcmd exec db install-*.sql gsqlcmd exec db install.zip gsqlcmd exec db @install-task.txt
You can use a file name for SQLite and SQL Server Compact databases like
gsqlcmd exec test.db install.sql gsqlcmd exec test.sdf install.sql
You can define parameter values for SQL commands and scripts using the /set option.
For example:
gsqlcmd exec db "EXEC dbo.usp_test @param1, @param2" /set=Param1=123;Param2='abc'
Declare script parameters as @<Parameter> for Microsoft SQL Server, SQL Server Compact, and SQLite,
and as :<Parameter> for MySQL, MariaDB, Oracle Database, IBM DB2, NuoDB, SnowFlake, and PostgreSQL.
Do not use the /set option with scripts that declare variables calculated by database servers.
For example, do not use the /set option with the create statements of stored procedures and triggers.
You can load script variables from task files.
For example, task.txt has the following content:
P1 P2 1 2 1 3
The test script.sql contains the following content:
SELECT @p1, '+', @p2, '=', @p1 + @p2
So, the following command produces the following result:
gsqlcmd exec master script.sql /taskfile=task.txt /noHeaders 1 + 2 = 3 1 + 3 = 4
As you can see, gsqlcmd executes the script for each line of the task file.
gsqlcmd Enterprise allows getting task values from a database using the <connection>::<query> form.
So, you can execute scripts with parameters values from a database like
gsqlcmd exec master script.sql "/taskfile=master::SELECT p1, p2 FROM ..." /noHeaders
SQL scripts can contain the -- print <Message> commands.
For example:
-- print Table ColumnTranslation created
gsqlcmd executes such commands internally and writes messages to the output.
This feature works with all databases servers in the same manner even a server does not support print commands.
gsqlcmd executes SQL commands in the ReadCommitted transaction isolation level.
So, the default rule is "all or nothing."
You can turn off a transaction mode using the /noTransaction option.
You can use this, for example, for deleting database objects using scripts as some of the deleted objects can not exist.
If a script has errors, turn on the trace mode using the /trace option.
By default, gsqlcmd writes trace messages to the console.
You can redirect the trace messages to a log file using the gsqlcmd.exe.config configuration file.