isql

Description

Interactive SQL parser to Adaptive Server.

The utility is located in:

• (UNIX) $SYBASE/$SYBASE_OCS/bin.

• (Windows) the utility is isql.exe, located in %SYBASE%\%SYBASE_OCS%\bin.

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]

Examples

Example 1

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

Example 2

The reset command 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

Example 3

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

isql -a mac -J roman8

Example 4

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)

Example 5

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)

Example 6

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

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.

• Set the SYBASE environment variable to the location of the current version of Adaptive Server before use 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. 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 this 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 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. 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:

  isql -U alma -Ppassword < 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:

  isql -U alma -Ppassword < input_file > output_file
  

• Case is significant for the isql flags.

• 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 this 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 this to comment out the go command:

  /*
  **go
  */
  

  Do not use this:

  /*
  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. 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.

  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.

Setting the correct network packet size can greatly increase the performance of Adaptive Server.

The -A size option specifies the network packet size to use for an isql session. The default value is 2048 bytes.

This 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 in the sysprocesses table.

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 packet size does not affect the packet size of remote procedure calls.

The commands you execute from within interactive isql are:

• :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. This 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.This example appends the server version to file_name:

  select @@version
  go >> file_name
  

• | command

  Pipes the output of the T-SQL command to an external command. This 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.

See also