Command Line Interface
RazorSQL has a command line interface for users to call RazorSQL operations without actually launching the RazorSQL graphical program. The command line interface can be used for users who want to run scheduled programs such as nightly batch scripts. The command line interface can also be used to launch RazorSQL and have it connect using data passed in to the command line interface.
RazorSQL version 5.2 and newer includes a GUI tool for generating command line calls. The
GUI tool is available via the following menu option:
DB Tools -> Command Line Call Generator
Please use the above tool to generate commands. This tool will contain only commands and parameters that are supported for the version of RazorSQL you are using.
As of RazorSQL version 10, the following commands are supported.
Backup Database: This command will generate a backup file for the database. Depending
on the database type, it can generate DDL for Tables, Views, Indexes, Functions, Procedures, and Triggers.
It will also generate SQL insert statements for table data.
Backup Table: This command will generate DDL for the table and SQL insert statements for
data in the table.
Execute SQL Script: This command will execute a file of SQL statements. The statements
must be non-select statements such as create, insert, update, delete, etc.
Export Query Results / Table Data: This command will execute a query and generate a file
containing the results. There are several output types supported for the file such as a delimited file,
SQL insert statements, JSON, XML, HTML, or Excel.
Import Table Data: This command will import data from a delimited file into a database table.
Compare Query Results: This command will generate a file that does a comparison against
two query result sets.
Convert Table: This command will generate DDL and SQL insert statements to convert a
table from one database type to another database type, for example, MySQL to PostgreSQL.
Test Connection: This command will test a RazorSQL connection profile to see if the
database connection succeeds.
Connect (Launch GUI): This command will launch the RazorSQL GUI and make a connection
using the connection data passed in to the command line process. This command does not need a
RazorSQL connection profile to exist to make the connection. This command is useful for connections
that require auto generated tokens to connect such as Amazon RDS databases using IAM authentication.
For example, a script can be written to generate the -connect command passing in the authentication
token that was generated earlier in the script.
Executing a Command
To use the RazorSQL command line interface, RazorSQL must be launched from a
command prompt or terminal window or script. There are potentially slight differences depending
on whether the operating system is Windows, Mac, or Linux / Unix.
The format to run RazorSQL in command mode is the following:
java -jar razorsql.jar -command "parameter1" "parameter2" . . .
For example:
OS X / Linux / Unix
Change directory to the RazorSQL installation directory and execute the following:
java -jar razorsql.jar -export "Oracle Express" "select * from employee" csv "/Users/user/employee.txt" yes
Windows
Change directory to the RazorSQL installation directory and execute the following:
jre\bin\java -jar razorsql.jar -export "Oracle Express" "select * from employee" csv "C:\data\employee.txt" yes
For commands that may take a large amount of memory, the initial and max heap
size settings for RazorSQL can be increased using the following syntax:
java -Xms64M -Xmx384M -jar razorsql.jar -command "parameter1" "parameter2" . . .
This command will start RazorSQL with 64MB of initial memory, with a maximum
memory of 384MB.
Supported Commands
RazorSQL 7.3 adds the following command:
- -convert (for converting a table from one database type to another)
RazorSQL 5.5.4 adds the following command:
- -compare (for comparing the results of two queries)
RazorSQL versions 5.2 adds the following commands:
- -backup (for backing up a database or schema)
- -backuptable (for backing up a database table)
- -execute (for executing an SQL script file)
RazorSQL versions 5.1.6 and earlier support the following commands:
- -export (for exporting a database table)
- -import (for importing data into a database table)
Export Tables
The -export
command has the following parameters:
NOTE: Any parameters with spaces need to be enclosed in quotes (" ")
parameter 1 [required]: -export (the command to execute)
parameter 2 [required]: profile_name (the name of the profile to use to get the connection)
NOTE: The profile must have the save password option checked
parameter 3 [required]: query or path to file containing a query (the query to run to get the data to export)
if this value is a path to an existing file, RazorSQL will read the contents of the file and execute it as a query
parameter 4 [required]: export_format (csv,tsv,xls,insert)
parameter 5 [required]: file to export data to
parameter 6 [optional]: show_columns (yes,no)
parameter 7 [optional]: convert nulls to blanks (yes,no)
parameter 8 [optional]: trim spaces (yes,no)
parameter 9 [optional]: date format (MM = month, dd = day, yyyy = year)
parameter 10 [optional]: timestamp format (MM = month, dd = day, yyyy = year, HH = hour, mm = minute, ss = second, SSS = millisecond)
parameter 11 (RazorSQL 5.6.2 or later) [optional]: characters to append at the end of each row
parameter 12-14 for database connection password override, fetch size override, and multi-value insert syntax
(Use the DB Tools -> Command Line Call Generator to determine if your version of RazorSQL supports
these parameters)
Import Table Data
The -import command has the following parameteres:
NOTE: Any parameters with spaces need to be enclosed in quotes (" ")
parameter 1 [required]: -import (the command to execute)
parameter 2 [required]: profile_name (the name of the profile to use to get the connection).
Database connection data can be passed instead of a profile name using the following pipe delimited
format:
JDBC URL|JDBC Driver Class|JDBC Driver File System Location|User Name|Password
NOTE: The profile must have the save password option checked
parameter 3 [required]: delimited_file_location (the location of the comma, tab, or space delimited file)
parameter 4 [required]: delimiter (valid options are COMMA, SPACE, or TAB)
parameter 5 [required]: database (the database that contains the table, enter "null" if this is not applicable)
parameter 6 [required]: schema_or_owner (the schema or owner that contains the table, enter "null" if this is not applicable)
parameter 7 [required]: table (the name of the table to import the data to)
parameter 8 [optional]: database password override
parameter 9: File start row [optional]: (use DB Tools->Command Line Call Generator to determine support)
parameter 10: Show output [optional]: (use DB Tools->Command Line Call Generator to determine support): true or false - determines whether statements executed are printed.
parameter 11: Use Prepared Statements [optional]: (use DB Tools->Command Line Call Generator to determine support): true or false - RazorSQL will try to use prepared statements
for better performance if supported by the database and the data types on the table.
parameter 12: File Encoding [optional]: (use DB Tools->Command Line Call Generator to determine support): The encoding of the import file. If null or blank,
the default operating system encoding will be used.
parameter 13: Halt on Error [optional]: (use DB Tools->Command Line Call Generator to determine support): true or false - determines whether SQL statements
are printed to the standard output
Backup Schema / Database
The -backup command has the following parameters:
NOTE: Any parameters with spaces need to be enclosed in quotes (" ")
parameter 1 [required]: -backup (the command to execute)
parameter 2 [required]: profile_name (the name of the profile to use to get the connection)
NOTE: The profile must have the save password option checked
parameter 3 [required]: database (the database that contains the objects. Enter "null" if this is not applicable)
parameter 4 [required]: schema (the schema that contains the objects. Enter "null" if this is not applicable)
parameter 5 [required]: SQL statement separator (enter the text to use to separate SQL statements generated, for example, ;)
parameter 6 [required]: backup file encoding (enter null for default or the name of the encoding, e.g., "UTF-8")
parameter 7 [required]: backup file location (enter the path to the backup file)
parameter 8 [required]: fully qualify object names (YES or NO)
parameter 9 [required]: objects to back up (comma separated list of objects to back up, e.g., tables,views,indexes,procedures,functions,triggers)
parameter 10 [optional]: only generate DDL. Do not generate SQL insert statements for table data.
Backup Table
The -backuptable command has the following parameters:
NOTE: Any parameters with spaces need to be enclosed in quotes (" ")
parameter 1 [required]: -backuptable (the command to execute)
parameter 2 [required]: profile_name (the name of the profile to use to get the connection)
NOTE: The profile must have the save password option checked
parameter 3 [required]: database (the database that contains the objects. Enter "null" if this is not applicable)
parameter 4 [required]: schema (the schema that contains the objects. Enter "null" if this is not applicable)
parameter 5 [required]: table (the name of the table to backup)
parameter 6 [required]: SQL statement separator (enter the text to use to separate SQL statements generated, for example, ;)
parameter 7 [required]: backup file encoding (enter null for default or the name of the encoding, e.g., "UTF-8")
parameter 8 [required]: backup file location (enter the path to the backup file)
parameter 9 [required]: fully qualify object names (YES or NO)
parameter 10 [optional]: only generate DDL. Do not generate SQL insert statements.
Execute SQL Script
The -execute command has the following parameters:
NOTE: Any parameters with spaces need to be enclosed in quotes (" ")
parameter 1 [required]: -execute (the command to execute)
parameter 2 [required]: profile_name (the name of the profile to use to get the connection)
NOTE: The profile must have the save password option checked
parameter 3 [required]: sql file location (the location of the SQL script to execute)
parameter 4 [required]: sql statement separator
parameter 5 [required]: sql file encoding (enter null for default or the name of the encoding, e.g., "UTF-8")
parameter 6 [required]: batch size (how many statements to execute per network call)
parameter 7 [optional]: (use DB Tools->Command Line Call Generator to determine support): database password override
parameter 8 [optional]: (use DB Tools->Command Line Call Generator to determine support): Show output: true or false
parameter 9 [optional]: (use DB Tools->Command Line Call Generator to determine support): Halt on Error: true or false
Compare
The compare command has the following parameters:
NOTE: Any parameters with spaces need to be enclosed in quotes (" ")
parameter 1 [required]: -compare
parameter 2 [required]: profile_name1 (the name of the connection profile to use to get the connection)
NOTE: The profile must have the save password option checked
parameter 3 [required]: query1 (the query to execute on profile_name1)
parameter 4 [required]: profile_name2 (the name of the connection profile to use to get the connection)
NOTE: The profile must have the save password option checked
parameter 5 [required]: query2 (the query to execute on profile_name2)
parameter 6 [required]: output_file (the location of the output file that the compare results will get written to)
Convert
The convert command has the following parameters:
NOTE: Any parameters with spaces need to be enclosed in quotes (" ")
parameter 1 [required]: -convert
parameter 2 [required]: profile_name (the name of the connection profile to use to get the connection)
NOTE: The profile must have the save password option checked
parameter 3 [required]: database (the database or catalog the table is in - enter null if not applicable)
parameter 4 [required]: schema (the schema the table is in - enter null if not applicable)
parameter 5 [required]: table (the name of the table to convert)
parameter 6 [required]: statement separator (the separator for SQL insert statements / DDL statements)
parameter 7 [required]: sql file encoding (enter null for default or the name of the encoding, e.g., "UTF-8")
parameter 8 [required]: conversion file (the location of the file to write out the converted data to)
parameter 9 [required]: qualify object names (YES or NO: whether to fully qualify table names)
parameter 10 [required]: ddl only (YES or NO: whether to only include create / alter statements with no inserts)
parameter 11 [required]: new database type (the type of database to convert to -Oracle, MySQL, etc.)
parameter 12 [required]: new table name (the name of the converted table)
parameter 13 [optional]: password override (use this password instead of the password on the connection profile)
parameter 14 [optional]: inserts only (YES or NO: whether to only include SQL insert statements and no DDL statements)
