isql

Interactive SQL parser to the SAP ASE server.

The utility is located in:

(UNIX) $SYBASE/$SYBASE_OCS/bin.
(Windows) %SYBASE%\%SYBASE_OCS%\bin, as isql.exe.

Syntax

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]
	[-M LabelName LabelValue]
	[-o outputfile]
	[-P password]
	[-R remote_server_principal]
	[-s col_separator]
	[-S server_name]
	[-t timeout]
	[-U username]	[-v]
	[-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]
	[--URP remotepassword

Parameters

-a display_charset – 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.
Note: The ascii_7 character set is compatible with all character sets. If either the SAP ASE character set or the client character set is set to ascii_7, any 7-bit ASCII character can pass unaltered between client and server. Other characters produce conversion errors. For more information on character set conversion, see the System Administration Guide.
-A packet_size – specifies the network packet size to use for this isql session. For example, to set the packet size to 4096 bytes for the isql session, use:
```
isql -A 4096
```
To check your network packet size, use:
```
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. The default value is 2048.

Use larger-than-default packet sizes to perform I/O-intensive operations, such as readtext or writetext operations. Setting or changing the SAP ASE packet size does not affect remote procedure calls’ packet size.
-b – disables the display of the table headers output.
-c cmdend – changes the command terminator. By default, you terminate commands and send them to the server by typing “go” on a line by itself. When you change the command terminator, do not use SQL reserved words or control characters.
-D database – selects the database in which the isql session begins.
-e – echoes input.
-E editor – specifies an editor other than the default editor vi. To invoke the editor, enter its name as the first word of a line in isql.
-F – 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.
-h headers – specifies the number of rows to print between column headings. The default prints headings only once for each set of query results.
-H hostname – sets the client host name.
-i inputfile – specifies the name of the operating system file to use for input to isql. The file must contain command terminators (the default is “go”).
- Specifying the parameter 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, specify your password as the first line of the input file.
-I interfaces_file – specifies the name and location of the interfaces file to search when connecting to the SAP ASE server. If you do not specify -I, isql looks for a file named interfaces in the directory specified by your SYBASE environment variable.
-J client_charset – specifies the character set to use on the client. The parameter requests that the SAP ASE server convert to and from client_charset, the character set used on the client. A filter converts input between client_charset and the SAP ASE 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 Configuring Client/Server Character Set Conversions, in the System Administration Guide, Volume One.
-K keytab_file – (used only with Kerberos security) specifies a Kerberos keytab file that contains the security key for the user name specified with the -U option. To create a keytab, see your Kerberos documentation. If you do not specify the -K option, the isql user must be logged in to Kerberos with the same user name as specified with the -U option.
-l login_timeout – specifies the maximum timeout value allowed when connecting to the SAP ASE 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 -ttimeout parameter.
-m errorlevel – customizes error message appearance. 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.
-M LabelName LabelValue – (Secure SQL Server only) enables multilevel users to set the session labels for the this isql session. 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”).
-n – removes numbering and the prompt symbol (>) from the echoed input lines in the output file when used with -e.
-o outputfile – specifies the name of an operating system file to store the output from isql. Specifying the parameter as -o outputfile is similar to > outputfile
-p – prints performance statistics.
-P password – specifies your SAP ASE password. If you do not specify the -P flag, isql prompts for a password. If your password is NULL, use the-P flag without any password.
-Q – provides clients with failover property. See Using Sybase Failover in a High Availability System.
-R remote_server_principal – specifies the principal name for the server as defined to the security mechanism. By default, a server’s principal name matches the server’s network name (which is specified with the -S parameter or the DSQUERY environment variable). Use the -R parameter when the server’s principal name and network name are not the same.
-s colseparator – resets the column separator character, which is blank by default. 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.
-S server_name – specifies the name of the SAP ASE server to which to connect. isql looks this name up in the interfaces file. If you specify -S without server_name, isql looks for a server named SYBASE. If you do not specify -S, isql looks for the server specified by your DSQUERY environment variable.
-t timeout – specifies the number of seconds before a SQL command times out. If you do not specify a timeout, the command runs indefinitely. This affects commands issued from within isql, not the connection time. The default timeout for logging into isql is 60 seconds.
-U username – specifies a login name. Login names are case-sensitive.
-v – prints the version and copyright message of isql and then exits.
isql is available in both 32-bit and 64-bit versions. They both reside in the same directory and are differentiated by their executable file names (isql and isql64). Enter isql -v or isql64 -v to see the detailed version string of the isql you are using.
-V security_options – specifies network-based user authentication. With this option, the user must log in to the network’s security system before running the utility, and users must supply the network user name with the -U option; any password supplied with the -P option is ignored.
Follow -V with a security_options string of key-letter options to enable additional security services. These key letters are:
- c – enables data confidentiality service.
- d – enables credential delegation and forwards the client credentials to the gateway application.
- i – enables data integrity service.
- m – enables mutual authentication for connection establishment.
- o – enables data origin stamping servic.e
- q – enables out-of-sequence detection
- r – enables data replay detection
-W – disables both extended password and password encrypted negotiations.
-w columnwidth – 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.
-x trusted.txt_file – specifies an alternate trusted.txt file.
-X – 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.

For details on encrypted passwords, see the user documentation for the Open Client Client-Library.
-y sybase_directory – sets an alternate Sybase home directory.
-Y – tells the SAP ASE server to use chained transactions.
-z locale_name – specifies 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 SAP ASE server during installation or afterward, using the langinstall utility (langinst in Windows) or the sp_addlanguage stored procedure.
-Z security_mechanism – 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 ini subdirectory below the Sybase installation directory. If no security_mechanism name is supplied, the default mechanism is used. For more information on security mechanism names, see the description of the libtcl.cfg file in the Open Client and Open Server Configuration Guide.
--appname "application_name" – allows you to change the default application name isql to the isql client application name. This simplifies:
- Testing of SAP ASE 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 an SAP ASE 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.
- 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.
Note: You can also set the client application name in ocs.cfg using the CS_APPNAME property.
--conceal [':?' | 'wildcard'] – 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 :?.

Note: --conceal is silently ignored in batch mode.
--help – displays a brief description of syntax and usage for the isql utility consisting of a list of available arguments.
--history [p]history_length [--history_file history_filename] – Loads the contents of the command history log file, if it exists, when isql starts. By default, the command history feature is off. Use the --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.
--retserverror – forces isql to terminate and return a failure code when it encounters a server error with a severity greater than 10. When isql encounters this type of abnormal termination, it writes the label “Msg” together with the actual SAP ASE error number to stderr, and returns a value of 2 to the calling program. isql prints the full server error message to stdout.
--URP remotepassword – enables setting the universal remote password remotepassword for clients accessing an SAP ASE server.

Examples

Query edit – 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
```
Clearing and quitting – reset clears the query buffer, and quit returns you to the operating system:
```
isql -Ualma
Password:
1> select *
2> from authors
3> where city = "Oakland"
4> reset
1> quit
```

Column separators – 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)

Credentials – (MIT Kerberos) Requests credential delegation and forwards the client credentials to MY_GATEWAY:
```
isql -Vd -SMY_GATEWAY
```

Passwords – Changes password without displaying the password entered. This example uses “old” and “new” as prompt labels:

$ isql -Uguest -Pguest -Smyase --conceal
sp_password
:? old
,
:?:? new
----------------
old
new
Confirm new
Password correctly set.

(Return status 0)

Hide input – 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, the 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)
```
This example of --conceal uses a custom wildcard, and the prompt labels "role" and "password" to activate a role for the current user:
```
$ isql -UmyAccount --conceal '*'
Password:
1> set role
2> * role
3> with passwd
4> ** password
5> on
6> go

role
password
Confirm password
1>
```

Return server error – 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 >

Application name – Sets the application name to the name of the script that started the isql session:
```
isql --appname $0
```
History – Loads and saves the command history using the default log file:
```
isql -Uguest -Ppassword -Smyase --history p1024
```
Run isql with configuration file – 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
```
Load and save command history – Loads and saves the command history using the default log file:
```
isql -Uguest -Ppassword -Smyase --history p1024
```
Delete log – 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
```

All commands in command history – 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>

Most recent commands – Lists the two most recent commands issued:

isql -Uguest -Ppassword -Smyase --history p1024
1> h -2

[2] select db_name()
[3] select @@servername

1>

Recall labeled command from history – Recalls the command labeled 1 from the command history:
```
isql -Uguest -Ppassword -Smyase --history p1024
1> ? 1

1> select @@version
2>
```
Recall last-issued command from history – Recalls the latest issued command from the command history:
```
isql -Uguest -Ppassword -Smyase --history p1024
1> ? -1

1> select @@servername
2> 
```
Set directory – Sets an alternate Sybase home directory using the -y option:
```
isql -y/work/NewSybase -Uuser1 -Psecret -SMYSERVER
```
Roles – Activates a role for the current user. This example uses a custom wildcard and the prompt labels “role” and “password”:
```
$ isql -UmyAccount --conceal '*'Password:
set role
* role
with passwd
** password
on
go

role
password
Confirm password
```

Application name – 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

Deleting history – 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
```