isql

Description

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

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]
[--conceal [':?' | 'wildcard']]
[--help]
[--retserverror]

Parameters

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

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

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, 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>

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 ouput of a T-SQL ommand 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 build using the non-threaded 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, give the command 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 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 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
  

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

• When using isql interactively, read an operating system file into the command buffer with the following command:

  :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 with the following command:

  :R filename
  

• When using isql interactively, you can change the current database with the following command:

  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:

  /*
  

  **go
  

  */
  

  Do not use the following:

  /*
  

  go
  

  */
  

• isql defines the order of the date format as month, date, and year (mm dd yyy hh:mm AM or PM), regardless of the locale environment. To change this default order, user 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.

See also

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