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 may use a file name for SQLite and SQL Server Compact databases like
gsqlcmd exec test.db install.sql gsqlcmd exec test.sdf install.sql
Using Variables in SQL Scripts
You may 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 may 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 may 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 may execute scripts with parameters values from a database like
gsqlcmd exec master script.sql "/taskfile=master::SELECT p1, p2 FROM ..." /noHeaders
Internal Script Commands
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.
Transaction Modes
gsqlcmd executes SQL commands in the ReadCommitted transaction isolation level.
So, the default rule is "all or nothing."
You may turn off a transaction mode using the /noTransaction option.
You may use this, for example, for deleting database objects using scripts as some of the deleted objects may not exist.
Trace Mode
If a script has errors, turn on the trace mode using the /trace option.
By default, gsqlcmd writes trace messages to the console.
You may redirect the trace messages to a log file using the gsqlcmd.exe.config configuration file.