To display the option list in the prompt, execute the csql utility without specifying the database name as follows:
% csql
interactive SQL utility, version 2008 R4.0
usage: csql [OPTION] database-name valid options:
-S, --SA-mode standalone mode execution
-C, --CS-mode client-server mode execution
-u, --user=ARG alternate user name
-p, --pasword=ARG password string, give "" for none
-e, --error-continue don't exit on statement error
-i, --input-file=ARG input-file-name
-o, --output-file=ARG output-file-name
-s, --single-line single line oriented execution
-c, --command=ARG CSQL-commands
-l, --line-output display each value in a line
-r, --read-only read-only mode
--no-auto-commit disable auto commit mode execution
--no-pager do not use pager
--no-single-line turn off single line oriented execution
The following table lists the options that can be issued with the csql utility.
Option |
Description |
---|---|
-S |
Executes the csql utility in a standalone mode. |
-C |
Executes the csql utility in a client/server mode. |
-u user_name |
Specifies the user that tries to access the database. The default value is PUBLIC. |
-p password |
Specifies the password of the user that tries to access the database (if any). |
-e |
Continues the session even when an error occurs. |
-i input_file |
Executes the csql utility in a batch mode. The input_file parameter is the file name where SQL statements are stored. |
-o output_file |
Stores a result of the statement execution in the specified output_file without displaying it on the screen. |
-s |
Executes multiple SQL statements one by one in the file where they are stored consecutively. Multiple SQL statements are separated by semicolons (;). If this option is not specified, the default operation is performed. |
-c "CSQL commands" |
Executes SQL statements directly from the prompt. To use this option, enclose the SQL statement to execute in double quotes. |
-l |
Displays the query results in a line format instead of a column. By default, the results will be displayed in a column format. |
-r |
Connects to a database in read-only mode. |
--no-auto-commit |
Configures the auto-commit mode of the CSQL Interpreter to OFF. |
--no-pager |
Displays the results of the query performed by the CSQL Interpreter at once instead of page-by-page. |
--no-single-line |
Executes multiple SQL statements at once by using ;xr or ;r session command. |
Executing in a standalone mode (-S)
The following example shows how to connect to the demodb database in a standalone mode and execute the csql utility with the -S option. When you want to use the demodb database exclusively, use the -S option.
csql -S demodb
Executing in a client/server mode (-C)
The following example shows how to connect to the demodb database in a client/server mode and execute the csql utility with the -C option. In an environment where multiple clients connect to the demodb database, use the -C option. Even when you connect to a database on a remote host in a client/server mode, the error log created during the csql utility execution will be stored in the cub_client.err file on the local host.
csql -C demodb
Specifying the name of the input file to use in a batch mode (-i)
The following example shows how to specify the name of the input file that will be used in a batch mode with the -i option. In the infile file, more than one SQL statement are stored. Without the -i option specified, the CSQL Interpreter will run in an interactive mode.
csql -i infile demodb
Specifying the output file to store the execution results (-o)
The following example shows how to store the execution results to the specified file instead of displaying on the screen with the -o option. This option is useful when you want to retrieve the results of the query performed by the CSQL Interpreter at a later time.
csql -o outfile demodb
Specifying the user name (-u)
The following example shows how to specify the name of the user that will connect to the specified database with the -u option. If the -u option is not specified, PUBLIC that has the lowest level of authorization will be specified as a user. If the user name is not valid, an error message is displayed and the csql utility is terminated. If there is a password for the user name you specify, you will be prompted to enter the password.
csql -u DBA demodb
Specifying the user password (-p)
The following example shows how to enter the password of the user specified with the -p option. Especially since there is no prompt to enter a password for the user you specify in a batch mode, you must enter the password using the -p option. When you enter an incorrect password, an error message is displayed and the csql utility is terminated.
csql -u DBA -p *** demodb
Executing SQL statements one by one (-s)
The following example shows how to execute SQL statements one by one with the -s option. Use this option when you want to allocate less memory for the query execution. Multiple SQL statements are separated by semicolons (;). If this option is not specified, the default operation is performed.
csql -s -i infile demodb
Executing SQL statements directly from the shell (-c)
The following example shows how to execute more than one SQL statement from the shell with the -c option. Multiple statements are separated by semicolons (;).
csql -c "select * from olympic;select * from stadium" demodb
Displaying the results in a line format (-l)
The following example shows how to display the execution results of the SQL statement in a line format with the -l option. The execution results will be output in a column format if the -l option is not specified.
csql -l demodb
Continuing the execution even with an error (-e)
The following example shows how to continue executing subsequent SQL statements even when a syntax error or a runtime error occurs in a previous SQL statement by using the -e option. When there is an error in the SQL statement, the database will be terminated even though the -e option is specified.
csql -e demodb
Connecting to a database in read-only mode (-r)
The following example shows how to connect to a database in read-only mode by using the -r option. Creating a table or manipulating data is not allowed; only retrieving data is allowed.
csql -r demodb
No auto-commit mode (--no-auto-commit)
The following example shows how to stop the auto-commit mode with the --no-auto-commit option. If you don't configure --no-auto-commit option, the CSQL Interpreter runs in an auto-commit mode by default, and the SQL statement is committed automatically at every execution. Executing the ;AUtocommit session command after starting the CSQL Interpreter will also have the same result.
csql --no-auto-commit demodb
Displaying all the execution results at once (--no-pager)
The following example shows how to display the execution results by the CSQL Interpreter at once instead of page-by-page with the --no-pager option. The results will be output page-by-page if --no-pager option is not specified.
csql --no-pager demodb
Executing all SQL statements at once (--no single-line)
The following example shows how to execute all SQL statements at once by using ;xr or ;r session command. If you do not specify this option, SQL statements are executed without ;xr or ;r session command.
csql --no-single-line demodb