isql

Description

Interactive SQL parser to Adaptive Server. This utility is in $SYBASE/$SYBASE_OCS/bin.

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

Parameters

Examples

Example 1

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 

Example 2

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

Example 3

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 4

For MIT Kerberos, requests credential delegation and forwards the client credentials to MY_GATEWAY:

isql -Vd -SMY_GATEWAY

Example 5

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>

Example 6

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.

Example 7

Sets an alternate Sybase home directory using the -y option:

isql -y/work/NewSybase -Uuser1 -Psecret -SMYSERVER

Example 8

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)

Example 9

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)

Example 10

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>

Example 11

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

Example 12

Sets the application name to the name of the script that started the isql session:

isql --appname $0

Example 13

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

Example 14

Loads and saves the command history using the default log file:

isql -Uguest -Ppassword -Smyase --history p1024

Example 15

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

Example 16

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>

Example 17

Lists the two most recent commands issued:

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

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

1>

Example 18

Recalls the command labeled 1 from the command history:

isql -Uguest -Ppassword -Smyase --history p1024
1> ? 1

1> select @@version
2>

Example 19

Recalls the latest issued command from the command history:

isql -Uguest -Ppassword -Smyase --history p1024
1> ? -1

1> select @@servername
2>

Usage

• 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.

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.

• 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.

See also

sp_addlanguage, sp_addlogin, sp_configure, sp_defaultlanguage, sp_droplanguage, and sp_helplanguage in the Adaptive Server Enterprise Reference Manual.