isql Interactive SQL Utility

The isql utility is a command-line Interactive SQL utility that uses the Adaptive Server Enterprise Open Client API.

You cannot create user-defined database options in isql. If you need to add your own database options, use the dbisql Interactive SQL utility instead.

Syntax

isql 
[-b] [-e] [-F] [-p] [-n] [-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]
	[-o outputfile]
	[-P password]
	[-R remote_server_principal]	
    [-s colseparator]
	[-S server_name]
    [-t timeout]	
	-U username
	[-V [security_options]]
	[-w columnwidth]
	[-z locale_name]
	[-Z security_mechanism]
	[--conceal]
    [--URP]

Parameters

This table lists the available options for the isql utility.

isql Options
Option	Description
-b	Disables display of the table headers output.
-e	Echoes input.
-F	Enables the FIPS flagger. When you specify the -F parameter, the server returns a message when it encounters a non-standard SQL command. This option does not disable SQL extensions. Processing completes when you issue the non-ANSI SQL command.
-p	Prints performance statistics.
-n	Removes numbering and the prompt symbol (>) from the echoed input lines in the output file when used in conjunction with -e.
-v	Prints the version number and copyright message for isql and then exits.
-X	Initiates the login connection to the server with client-side password encryption. -X enables both extended password encrypted connections and password encrypted connections without plain text password reconnection. isql (the client) specifies to the server that password encryption is desired. The sever 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. If isql crashes, 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
-W	Disables both extended password and password encrypted negotiations.
-Y	Tells the Adaptive Server to use chained transactions.
-Q	Provides clients with failover property.
-a `display_charset`	Runs isql from a terminal whose character set differs from that of the machine on 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 Adaptive Server 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.
-A `packet_size`	Specifies the network packet size to use for this isql session. For example, the following sets the packet size to 4096 bytes for this isql session: isql -A 4096 To check your network packet size, enter: SELECT * FROM sysprocesses The value is displayed under the network_pktsz heading. `size` must be between the values of the default network packet size and maximum network packet size configuration parameters, 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 Adaptive Server's packet size does not affect the packet size of remote procedure calls.
-c `cmdend`	Changes the command terminator. By default, you terminate commands and send them to 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 `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.
-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`	Specifies the number of rows to print between column headings. The default prints headings only once for each set of query results.
-i `interfaces_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 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. -J `client_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 “Configuring Client/Server Character Set Conversions,” in the Adaptive Server Enterprise System Administration Guide, Volume One.
-K `keytab_file`	Specifies the path to the keytab file used for authentication in DCE.
-l `login_timeout`	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.
-m `errorlevel`	Customizes the error message display. For errors of the severity level specified or higher, only the message number, state, and error level are displayed; no error text appears. For error levels lower than the specified level, nothing appears.
-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 `password`	Specifies your Adaptive Server 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.
-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.
-S `server_name`	Specifies the name of the Adaptive Server to which to connect. 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.
-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.
--URP `string`	Allows you to supply a login redirection string for an SAP Sybase IQ server. This example specifies the logical server, database, node type and redirection setting: isql -Usa –P -–URP “LS=salogsrv;dbname=dbone;node=writer;redirect=no” or Enables setting the universal remote password `remotepassword` for clients accessing Adaptive Server. The application sets the universal remote password. For example, ctlib uses `ct_remote_pwd()` and jConnect uses the `setRemotePassword` method.
-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. In this case, users must supply their network user name with the -U option; any password supplied with the -P option is ignored. You can follow -V with a `security_options` string of key-letter options to enable additional security services. These key letters are: -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
-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.
-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. You can add languages to an Adaptive 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.
--conceal	Hides your input during an isql session. The --conceal option is useful when entering sensitive information, such as passwords. The syntax for the --conceal option is: isql --conceal [':?' \| 'wildcard'] The maximum length of `wildcard`, a 32-byte variable, is 80 characters. Note: --conceal is silently ignored in batch mode.

Usage

Use this syntax for isql_r if you are using threaded drivers.

Use this syntax for isql if you are using threaded drivers in the IBM platform.

You must set the SYBASE environment variable to the location of the current version of Adaptive Server before you can use isql.

This table lists the commands you can execute within interactive isql:

Commands Available Within isql
Command	Description
:r filename	Reads an operating system file into the command buffer. Do not include the command terminator in the file; once you have finished editing, enter the terminator interactively on a line by itself.
:R filename	Reads an operating system file into the command buffer and then displays it. Do not include the command terminator in the file; once you have finished editing, enter the terminator interactively on a line by itself.
use database_name	Changes the current database.
!! os_command	Executes an operating system command. Place at the start of a line.
> file_name	Redirects the output of the T-SQL command to `file_name`. The following example inserts the server version into `file_name`: select @@version go > file_name
>> file_name	Appends the output of the T-SQL command to `file_name`. The following example appends the server version into `file_name`: select @@version go >> file_name
\| command	Pipes the output of the T-SQL command to an external command. The following example finds all instances of “sa” in the listing produced by sp_who: sp_who go \| grep sa
vi (UNIX) edit (Windows)	Calls the default editor.
reset	Clears the query buffer.
quit or exit	Exits isql.

The 5701 (“changed database”) server message is no longer displayed after login or issuing a use database command.

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.

When you include the -X parameter, the password-enabled connection proceeds according to server capabilities:

If the server can handle both extended password and password encryption, extended password encryption negotiations are used.
If the server can handle password encryption only, password encryption negotiations are used.
If the server cannot handle password encryption or extended password encryption, the first connection attempt fails and the client attempts to reconnect using a plain text password.

To use isql interactively, give the command isql (and any of the optional parameters) 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 another command terminator, if the -c parameter is used. You can 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:

select x = 1
go 100

The results display 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 not defined, the default is vi on UNIX and edit on Windows.

For example, if your EDITOR environment variable is set to “emacs,” then you must invoke it from within isql with “emacs” as the first word on the line.

To clear the existing query buffer, type reset on a line by itself. isql discards any pending input. 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 password < input_file

The file must include a command terminator. 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 password < input_file > output_file

isql displays only six digits of float or real data after the decimal point, rounding off the remainder.

You can include comments in a Transact-SQL statement submitted to Adaptive Server by isql. Open a comment with “/*”. Close it with “*/”, as shown in the following example:

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, use the following to comment out the go command:

/* 
**go 
*/

Do not use the following:

/* go */

isql defines the order of the date format as month, date, and year (mm dd yyyy hh:mmAM (or PM)) regardless of the locale environment. To change this default order, use the convert function.

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.

Note: In an isql session, isql recognizes :?, or the value of wildcard, as wildcards only when these characters are placed at the beginning of an isql line.

Examples

Puts you in a text file 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 -Ualma
Password:
1> select *
2> from authors
3> where city = "Oakland"
4> reset
1> quit

Specifies that you are running isql from a Macintosh against a server that is using the roman8 character set:

isql -a mac -J roman8

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)

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)

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