Interactive SQL parser to Adaptive Server. This utility is in $SYBASE/$SYBASE_OCS/bin.
isql [-b] [-e] [-F] [-n] [-p] [-v] [-W] [-X] [-Y] [-Q] [-a display_charset] [-A packet_size] [-c cmdend] [-D database] [-E editor] [-h header] [-H hostname] [-i inputfile] [-I interfaces_file] [-J client_charset] [-K keytab_file] [-l login_timeout] [-m errorlevel] [-MLabelName LabelValue] [-o outputfile] [-P password] [-R remote_server_principal] [-s col_separator] [-S server_name] [-t timeout] [-U username] [--URP remotepassword [-V [security_options]] [-w column_width] [-x trusted.txt_file] [-y sybase_directory] [-z localename] [-Z security_mechanism] [--appname “application_name”] [--conceal [':?' | 'wildcard']] [--help] [--history [p]history_length [--history_file history_filename]] [--retserverror]
Allows you to run isql from a terminal where the character set differs from that of the machine on which isql is running. Use -a with -J to specify the character set translation file (.xlt file) required for the conversion. Use -a without -J only if the client character set is the same as the default character set.
The ascii_7
character set is compatible with all character sets. If either the
Adaptive Servers or the client’s character set is set to ascii_7, any
7-bit ASCII character is allowed
to pass unaltered between client and server. Other characters produce
conversion errors. Character set conversion issues are discussed
thoroughly in the Adaptive Server Enterprise System Administration Guide.
Specifies the network packet size to use for this isql session. For example, the following sets the packet size to 4096 bytes for the isql session:
isql -A 4096
To check your network packet size, enter:
select * from sysprocesses
The value appears under the network_pktsz heading.
packet_size must be between the values of the default network packet size and maximum network packet size configuration variables, and must be a multiple of 512.
Use larger-than-default packet sizes to perform I/O-intensive operations, such as readtext or writetext operations.
Setting or changing the Adaptive Server packet size does not affect remote procedure calls’ packet size.
Disables the display of the table headers output.
Resets the command terminator. By default, you can terminate commands and send them to Adaptive Server by typing “go” on a line by itself. When you reset the command terminator, do not use SQL reserved words or control characters. Escape shell metacharacters such as , ? ( ) [ ] $ and so on.
Selects a database in which the isql session begins.
Echoes input.
Specifies an editor other than your default editor (such as vi). To invoke it, enter its name as the first word of a line in isql.
Enables the FIPS flagger. When you specify the -F parameter, the server returns a message when it encounters a nonstandard SQL command. This option does not disable SQL extensions. Processing completes when you issue the non-ANSI SQL command.
Specifies the number of rows to print between column headings. The default prints headings only once for each set of query results.
Sets the client host name.
Specifies the name of an operating system file to use for input to isql. The file must contain command terminators (“go” by default).
Specifying the parameter as follows is equivalent to < inputfile:
-i inputfile
If you use -i and do not specify your password on the command line, isql prompts you for it.
If you use < inputfile and do not specify your password on the command line, you must specify your password as the first line of the input file.
Specifies the name and location of the interfaces file to search when connecting to Adaptive Server. If you do not specify -I, isql looks for an interfaces file, interfaces located in the Sybase release directory.
Specifies the character set to use on the client. -Jclient_charset requests that Adaptive Server convert to and from client_charset, the character set used on the client. A filter converts input between client_charset and the Adaptive Server character set.
-J with no argument sets character set conversion to NULL. No conversion takes place. Use this if the client and server use the same character set.
Omitting -J sets the character set to a default for the platform. The default may not necessarily be the character set that the client is using. For more information about character sets and the associated flags, see the Adaptive Server Enterprise System Administration Guide.
Used only with DCE security. keytab_file specifies a DCE keytab file that contains the security key for the user name specified with -U option. Create keytab files using the DCE dcecp utility. See your DCE documentation.
If -K is not supplied, the bcp user must be logged in to DCE with the same user name as specified with the -U option.
Specifies the maximum timeout value allowed when connecting to Adaptive Server. The default is 60 seconds. This value affects only the time that isql waits for the server to respond to a login attempt. To specify a timeout period for command processing, use the -t timeout parameter.
Customizes the error message display. For errors of the severity level specified or higher, only the message number, state, and error level appear; no error text appears. For error levels lower than the specified level, nothing appears.
(Secure SQL Server only) enables multilevel users to set the session labels for the bulk-copy. Valid values for LabelName are:
curread (current read level) is the initial level of data that you can read during this session. curread must dominate curwrite.
curwrite (current write level) is the initial sensitivity level that is applied to any data that you write during this session.
maxread (maximum read level) is the maximum level at which you can read data. This is the upper bound to which you as a multilevel user can set curread during the session. maxread must dominate maxwrite.
maxwrite (maximum write level) is the maximum level at which you can write data. This is the upper bound to which you as a multilevel user can set curwrite during a session. maxwrite must dominate minwrite and curwrite.
minwrite (minimum write level) is the minimum level at which you can write data. This is the lower bound to which you as a multilevel user can set curwrite during a session. minwrite must be dominated by maxwrite and curwrite.
LabelValue is the actual value of the label, expressed in the human-readable format used on your system (for example, “Company Confidential Personnel”).
Removes numbering and the prompt symbol (>) from echoed input lines in the output file when used with -e.
Specifies the name of an operating system file to store the output from isql. Specifying the parameter as -o outputfile is similar to > outputfile.
Prints performance statistics.
specifies your current Adaptive Server password. This option is ignored if -V is used. Passwords are case-sensitive and can be 6 – 30 characters in length. If your password is NULL, use -P without any password.
Provides clients with failover capability. See the Adaptive Server Enterprise Using Sybase Failover in a High Availability System.
Specifies the principal name for the server. By default, a server’s principal name matches the server’s network name (which is specified with the -S option or the DSQUERY environment variable). Use -R when the server’s principal name and network name are not the same.
Resets the column separator character, which, by default, is blank. To use characters that have special meaning to the operating system (for example, “|”, “;”, “&”, “<“, “>”), enclose them in quotes or precede them with a backslash.
The column separator appears at the beginning and the end of each column of each row.
Specifies the name of the Adaptive Server to connect to. isql looks this name up in the interfaces file. If you specify -S with no argument, isql looks for a server named SYBASE. If you do not specify -S, isql looks for the server specified by your DSQUERY environment variable.
Specifies the number of seconds before a SQL command times out. If you do not specify a timeout, a command runs indefinitely. This affects commands issued from within isql, not the connection time. The default timeout for logging in to isql is 60 seconds.
Specifies a case-sensitive login name.
Enables setting the universal remote password remotepassword for clients accessing Adaptive Server.
Specifies network-based user authentication. With this option, the user must log in to the network’s security system before running isql. In this case, users must supply their network user name with the -U option; any password supplied with the -P option is ignored.
-V can be followed by a security_options string that enables additional security services:
c – enable data confidentiality service.
d – enable credential delegation and forward the client credentials to the gateway application.
i – enable data integrity service.
m – enable mutual authentication for connection establishment.
o – enable data origin stamping service.
q – enable out-of-sequence detection.
r – enable data replay detection.
Prints the version and copyright message of the isql and then exits.
SDK binaries like isql have the same names in both the 32-bit and 64-bit products. Installing Adaptive Server, the SDK, or Open Server 64-bit products with other Sybase 32-bit products overwrites the 32-bit binaries. Starting with Adaptive Server 15.0.2 and SDK/Open Server 15.0 ESD #9, the 64-bit binaries are replaced with 32-bit binaries on all 64-bit UNIX platforms. Since 32-bit binaries are included in the 64-bit EBF, the -v option of isql is no longer a valid way to check the EBF number for 64-bit products. Instead, use the UNIX strings and grep commands to confirm the EBF numbers for both Open Client and Open Server.
For example, to find the string containing the EBF number in the libsybct64.a library, enter:
strings -a libsybct64.a | grep Sybase
This returns a string similar to:
Sybase Client-Library/15.5/P/DRV.15.5.0/SPARC/Solaris 8/BUILD1550-001/64bit/OPT/Mon Aug 10 23:04:17 2009
To find the string containing the EBF number in the libsybsrv64.a library, enter:
strings -a libsybsrv64.a | grep Sybase
This returns a string similar to:
Sybase Server-Library/15.5/P/DRV.15.5.0/SPARC/Solaris 8/BUILD1550-001/64bit/OPT/Mon Aug 10 23:06:27 2009
sets the screen width for output. The default is 80 characters. When an output line reaches its maximum screen width, it breaks into multiple lines.
Specifies that if the server to which isql is attempting to connect supports neither normal password encryption nor extended password encryption, plain text password retries are disabled. If this option is used, the CS_SEC_NON_ENCRYPTION_RETRY connection property is set to CS_FALSE, and plain text (unencrypted) passwords will not be used in retrying the connection.
Specifies an alternate trusted.txt file.
Initiates the login connection to the server with client-side password encryption. isql (the client) specifies to the server that password encryption is desired. The server sends back an encryption key, which isql uses to encrypt your password, and the server uses the key to authenticate your password when it arrives.
This option can result in normal or extended password encryption, depending on connection property settings at the server. If CS_SEC_ENCRYPTION is set to CS_TRUE, normal password encryption is used. If CS_SEC_EXTENDED_ENCRYPTION is set to CS_TRUE, extended password encryption is used. If both CS_SEC_ENCRYPTION and CS_SEC_EXTENDED_ENCRYPTION are set to CS_TRUE, extended password encryption takes precedence.
If isql fails, the system creates a core file that contains your password. If you did not use the encryption option, the password appears in plain text in the file. If you used the encryption option, your password is not readable.
Sets an alternate Sybase home directory.
Tells the Adaptive Server to use chain transactions.
The official name of an alternate language to display isql prompts and messages. Without -z, isql uses the server’s default language. Add languages to an Adaptive Server during installation, or afterward using the utility langinst or the sp_addlanguage stored procedure.
Specifies the name of a security mechanism to use on the connection.
Security mechanism names are defined in the libtcl.cfg configuration file located in the $SYBASE/ini directory. If no security_mechanism name is supplied, the default mechanism is used. See the description of the libtcl.cfg file in the Open Client and Open Server Configuration Guide for UNIX.
Allows you to change the default application name isql to the isql client application name. This simplifies:
Testing of Adaptive Server cluster routing rules for incoming client connections based on the client application name.
Switching between alternative settings for isql in $SYBASE/$SYBASE_OCS/config/ocs.cfg, such as between debugging and normal sessions.
Identification of the script that started a particular isql session from within Adaptive Server.
application_name is the client application name. You can retrieve the client application name from sysprocesses.program_name after connecting to your host server.
application_name has a maximum length of 30 characters. You must enclose the entire application name in single quote or double quote characters if it contains any white spaces that do not use the backslash escape character. You can set the application_name to an empty string.
You can also set the client application name in ocs.cfg using the CS_APPNAME property.
Hides your input during an isql session. The --conceal option is useful when entering sensitive information, such as passwords.
wildcard, a 32-byte variable, specifies the character string that triggers isql to prompt you for input during an isql session. For every wildcard that isql reads, isql displays a prompt that accepts your input but does not echo the input to the screen. The default wildcard is :?.
--conceal is silently ignored in
batch mode.
See “Using prompt labels and double wildcards in an isql session”.
Displays a brief description of syntax and usage for the isql utility consisting of a list of available arguments.
Loads the contents of the command history log file, if it exists, when isql starts. By default, the command history feature is off. Use --history command line option to activate it.
p – indicates command history persistence; in-memory command history is saved to disk when isql shuts down. If you do not use the p option, the command history log is deleted after its contents are loaded into memory.
history_length – this parameter, which is required if you use --history, is the number of commands that isql can store in the command history log. The maximum value of history_length is 1024; if a larger value is specified, isql silently truncates it to 1024.
--history_file history_filename – indicates that isql must retrieve the command history log from history_filename. If p is specified, isql also uses history_filename to store the current session’s command history. history_filename can include an absolute or a relative path to the log file. A relative path is based on the current directory. If you do not indicate a path, the history log is saved in the current directory.
When --history_file is not specified, isql uses the default log file in $HOME/.sybase/isql/isqlCmdHistory.log:
For information about listing, recalling, and reissuing past commands, see “Using command history”.
Forces isql to terminate and return a failure code when it encounters a server error of severity greater than 10. When isql encounters this type of abnormal termination, it writes the label “Msg” together with the actual Adaptive Server error number to stderr, and returns a value of 2 to the calling program. isql prints the full server error message to stdout.
Opens a text editor where you can edit the query. When you write and save the file, you are returned to isql. The query appears; type “go” on a line by itself to execute it:
isql -Ujoe -Pabracadabra
1>select *
2>from authors
3>where city = "Oakland"
4>vi
reset clears the query buffer. quit returns you to the operating system.
isql -U alma Password:
1>select *
2>from authors
3>where city = "Oakland"
4>reset
5>quit
Creates column separators using the “#” character in the output in the pubs2 database for store ID 7896:
isql -Usa -P -s# 1> use pubs2 2> go 1> select * from sales where stor_id = “7896” #stor_id#ord_num #date # #-------#-----------------------#---------------------------------# #7896 #124152 # Aug 14 1986 12:00AM# #7896 #234518 # Feb 14 1991 12:00AM#
(2 rows affected)
For MIT Kerberos, requests credential delegation and forwards the client credentials to MY_GATEWAY:
isql -Vd -SMY_GATEWAY
In this retserverror example, isql returns 2 to the calling shell, prints “Msg 207” to stderr, and exits, when it encountered a server error of severity 16.
guest> isql -Uguest -Pguestpwd -SmyASE --retserverror 2> isql.stderr 1> select no_column from sysobjects 2> go
Msg 207, Level 16, State 4: Server 'myASE', Line 1: Invalid column name 'no_column'.
guest> echo $? 2 guest> cat isql.stderr Msg 207 guest>
When you use the --help option, isql returns a brief description of syntax and usage for the isql utility consisting of a list of available arguments.
guest> isql --help
usage: isql [option1] [option2] ... where [options] are... -b Disables the display of the table headers output. -e Echoes input. -F Enables the FIPS flagger. -p Prints performance statistics. -n Removes numbering and the prompt symbol when used with -e. -v Prints the version number and copyright message. -W Turn off extended password encryption on connection retries. -X Initiates the login connection to the server with client-side password encryption. -Y Tells the Adaptive Server to use chained transactions. -Q Enables the HAFAILOVER property. -a display_charset Used in conjunction with -J to specify the character set translation file (.xlt file) required for the conversion. Use -a without -J only if the client character set is the same as the default character set. -A packet_size Specifies the network packet size to use for this isql session. -c cmdend Changes the command terminator. -D database Selects the database in which the isql session begins. -E editor Specifies an editor other than the default editor vi. -h header Specifies the number of rows to print between column headings. -H hostname Sets the client host name. -i inputfile Specifies the name of the operating system file to use for input to isql. -I interfaces_file Specifies the name and location of the interfaces file. -J client_charset Specifies the character set to use on the client. -K keytab_file Specifies the path to the keytab file used for authentication in DCE. -l login_timeout Specifies the number of seconds to wait for the server to respond to a login attempt. -m errorlevel Customizes the error message display. -M labelname labelvalue Used for security labels. See CS_SEC_NEGOTIATE for more details. -o outputfile Specifies the name of an operating system file to store the output from isql. -P password Specifies your Adaptive Server password. -R remote_server_principal Specifies the principal name for the server as defined to the security mechanism. -s col_separator Resets the column separator character, which is blank by default. -S server_name Specifies the name of the Adaptive Server to which to connect. -t timeout Specifies the number of seconds before a SQL command times out. -U username Specifies a login name. Login names are case sensitive. -V [security_options] Specifies network-based user authentication. Valid [security_options]: c - Enable data confidentiality service. i - Enable data integrity service. m - Enable mutual authentication for connection establishment. o - Enable data origin stamping service. q - Enable out-of-sequence detection. r - Enable data replay detection. d - Requests credential delegation and forwards client credentials. -w column_width Sets the screen width for output. -y sybase_directory Sets an alternate location for the Sybase home directory. -z localename Sets the official name of an alternate language to display isql prompts and messages. -Z security_mechanism Specifies the name of a security mechanism to use on the connection. -x trusted.txt_file Specifies an alternate trusted.txt file location. --retserverror Forces isql to terminate and return a failure code when it encounters a server error of severity greater than 10. --conceal [wildcard] Obfuscates input in an ISQL session. The optional wildcard will be used as a prompt.
Sets an alternate Sybase home directory using the -y option:
isql -y/work/NewSybase -Uuser1 -Psecret -SMYSERVER
In this example of --conceal, the password is modified without displaying the password entered. This example uses “old” and “new” as prompt labels:
$ isql -Uguest -Pguest -Smyase --conceal 1> sp_password 2> :? old 3> , 4> :?:? new 5> go
old new Confirm new Password correctly set. (return status = 0)
In this example of --conceal, password is modified without displaying the password entered. This example uses the default wildcard as the prompt label:
$ isql -Uguest -Pguest -Smyase --conceal 1> sp_password 2> :? 3> , 4> :?:? 5> go
:? :? Confirm :? Password correctly set. (return status = 0)
Activate a role for the current user. This example of --conceal uses a custom wildcard and the prompt labels “role” and “password”:
$ isql -UmyAccount --conceal '*' Password: 1> set role 2> * role 3> with passwd 4> ** password 5> on 6> go
role password Confirm password 1>
Sets the application name to “isql Session 01”:
isql -UmyAccount -SmyServer --appname "isql Session 01" Password: 1>select program_name from sysprocesses 2>where spid=@@spid 3>go
program_name ------------------- isql Session 01
Sets the application name to the name of the script that started the isql session:
isql --appname $0
This sample ocs.cfg file allows you to run isql normally or with network debug information. Because the configuration file is read and interpreted after the command line parameters are read and interpreted, setting CS_APPNAME to isql sets the application name back to isql:
;Sample ocs.cfg file [DEFAULT] ;place holder [isql] ;place holder [isql_dbg_net] CS_DEBUG = CS_DBG_NETWORK CS_APPNAME = "isql"
To run isql normally:
isql -Uguest
To run isql with network debug information:
isql -Uguest --appname isql_dbg_net
Loads and saves the command history using the default log file:
isql -Uguest -Ppassword -Smyase --history p1024
Deletes myaseHistory.log after loading its contents to memory. The session’s command history is not stored:
isql -Uguest -Ppassword -Smyase --history 1024 --history_file myaseHistory.log
Lists all the commands stored in the command history:
isql -Uguest -Ppassword -Smyase --history p1024 1> h
[1] select @@version [2] select db_name() [3] select @@servername
1>
Lists the two most recent commands issued:
isql -Uguest -Ppassword -Smyase --history p1024 1> h -2
[2] select db_name() [3] select @@servername
1>
Recalls the command labeled 1 from the command history:
isql -Uguest -Ppassword -Smyase --history p1024 1> ? 1
1> select @@version 2>
Recalls the latest issued command from the command history:
isql -Uguest -Ppassword -Smyase --history p1024 1> ? -1
1> select @@servername 2>
Following are the commands you can use at isql prompt:
To terminate a command:
go
To clear the query buffer:
reset
To execute an operating system command:
!! command
To exit from isql:
quit
or
exit
To redirect the output of a T-SQL command to a new file, or overwrite the file if it already exists:
>
To redirect the output of a T-SQL command to a new file, or append to the file if it already exists:
>>
To pipe the output of a T-SQL command to an external application from within an isql session:
|
isql is built with Client-Library. isql is built using the nonthreaded client libraries.
isql_r is a threaded version of isql. You must use isql_r if a security service, such as Kerberos, or a directory service, such as LDAP, is used.
Error message format differs from earlier versions of isql. If you have scripts that perform routines based on the values of these messages you may need to rewrite them.
To use isql interactively, enter isql (and any of the optional flags) at your operating system prompt. The isql program accepts SQL commands and sends them to Adaptive Server. The results are formatted and printed on standard output. Exit isql with quit or exit.
Terminate a command by typing a line beginning with the default command terminator go or other command terminator if the -c option is used. You may follow the command terminator with an integer to specify how many times to run the command. For example, to execute this command 100 times, type the following:
select x = 1
go 100
The results appear once at the end of execution.
If you enter an option more than once on the command line, isql uses the last value. For example, if you enter the following command, “send”, the second value for -c, overrides “.”, the first value:
isql -c. -csend
This enables you to override any aliases you set up.
To call an editor on the current query buffer, enter its name as the first word on a line. Define your preferred callable editor by specifying it with the EDITOR environment variable. If EDITOR is undefined, the default is vi.
For example, if the EDITOR environment variable is set to emacs, invoke it from isql using emacs as the first word on a line.
Execute operating system commands by starting a line with two exclamation points (!!) followed by the command.
To clear the existing query buffer, type reset on a line by itself. This entry uses isql to discard any pending point. You can also press Ctrl+C anywhere on a line to cancel the current query and return to the isql prompt.
Read in an operating system file containing a query for execution by isql as follows:
isql -U alma -P****** < input_file
The file must include command terminators. The results appear on your terminal. Read in an operating system file containing a query and direct the results to another file as follows:
isql -U alma -P****** < input_file > output_file
isql flags are case-sensitive.
isql displays only six digits of float or real data after the decimal point, rounding off the remainder.
When using isql interactively, read an operating system file into the command buffer using:
:r filename
Do not include a command terminator in the file; enter the terminator interactively once you have finished editing.
When using isql interactively, read and display an operating system file into the command buffer using:
:R filename
When using isql interactively, you can change the current database using:
use databasename
You can include comments in a Transact-SQL statement submitted to Adaptive Server by isql. Open a comment with “/*”. Close it with “*/” as the following example demonstrates:
select au_lname, au_fname /*retrieve authors’ last and first names*/ from authors, titles, titleauthor where authors.au_id = titleauthor.au_id and titles.title_id = titleauthor.title_id /*this is a three-way join that links authors **to the books they have written.*/
If you want to comment out a go command, it should not be at the beginning of a line. For example, to comment out the go command, use:
/*
**go
*/
Do not use:
/*
go
*/
isql defines the order of the date format as month, date, and year (mm dd yyyy hh:mm AM or PM), regardless of the locale environment. To change this default order, use the convert function.
Command |
Description |
---|---|
> |
Redirects command output to a file. File is overwritten if it exists. |
>> |
Redirects command output to a file. The output is appended to the file if the file already exists. |
| |
Pipes the output of a command to an external application. |
reset |
Clears the query buffer. |
quit or exit |
Exits from isql. |
vi |
Calls the editor. |
!! command |
Executes an operating system command. |
:r filename |
Reads an operating system file. |
:R filename |
Reads and displays an operating system file. |
use dbname |
Changes the current database to dbname. |
In an isql session, the default prompt label is either the default wildcard :? or the value of wildcard. You can customize the prompt label by providing a one-word character string with a maximum length of 80 characters, after a wildcard. If you specify a prompt label that is more than one word, the characters after the first word are ignored.
Double wildcards such as :?:? specify that isql needs to prompt you twice for the same input. The second prompt requests you to confirm your first input. If you use a double wildcard, the second prompt label starts with Confirm.
In an isql session, isql recognizes :? or
the value of wildcard as wildcards only when you
place these characters at the beginning of an isql line.
The command history feature is available only in command mode. Also, only commands that are issued interactively in isql are included in the command history. Examples of commands that are not included in the command history are those that are executed using the -i command line option or as part of a redirected input such as:
isql -Uguest -Ppassword -Smyase --history p1024 --history_file myaseHistory.log <<EOF exec sp_x_y_z go EOF
Command history contains the most recent commands issued in an isql session. When history_length is reached, isql drops the oldest command from the history and adds the newest command issued.
If you do not specify an alternate log file, and if the $HOME or %APPDATA% environment variable used by the default log file is not defined, an error message appears and the command history log is not saved.
In an isql session, use the h [n] command to display the command history. A page can display up to 24 lines of commands. If the command history contains more than 24 lines, press Enter to display the next set of commands or enter “a” to display all commands in one page. Enter “q” to return to isql.
n – indicates the number of commands to appear. If n is positive, the commands that appear start from the oldest command in the history. If n is negative, the n most recent commands appear.
Use the ? n | ?? command to recall and reissue a command from the command history.
n – when n is positive, isql looks for the command labeled with the number n and loads this to the command buffer. When n is negative, isql loads the nth most recent command issued.
?? – recalls the latest command issued and is equivalent to ? -1.
When a command is recalled from history, the recalled command overwrites the command in the command buffer.
You can edit a recalled command before resubmitting the command to the server.
sp_addlanguage, sp_addlogin, sp_configure, sp_defaultlanguage, sp_droplanguage, and sp_helplanguage in the Adaptive Server Enterprise Reference Manual.