LevSelector.com New York
home > Sybase ISQL

Sybase isql utility
 
 
Sybase isql utility home - top of the page -

 Isql - Interactive SQL parser to Sybase SQL Server

Synopsis

 isql  [-e] [-F] [-p] [-n] [-v] [-X] [-Y]
       [-a display_charset  ] [-c  cmdend  ] [-E  editor  ]
       [-h  headers  ] [-H  hostname  ] [-i  inputfile ]
       [-I  interfaces_file  ] [-J  client_charset ]
       [-l  login_timeout  ] [-m  errorlevel  ] [-o  outputfile  ]
       [-P  password  ] [-s  colseparator ] [-S  server  ]
       [-t  timeout  ] [-U username  ] [-w  columnwidth  ]
       [-y sybase_directory  ] [-z  language  ] [-A  size ]
To terminate a command: go
To clear the query buffer: reset
To call the default editor: vi
To execute an operating system command: !! command
To exit from isql : quit or exit

Most important Options

-o outputfile - specifies the name of an operating system file to store the output from isql . Specifying the parameter as follows: -o outputfile is similar to: > outputfile .

-P password -  specifies your current SQL Server password. If you do not specify the -P flag, isql prompts for a password. If your password is NULL, use the -P flag at the end of the command line without any password.

-S server -  specifies the name of the SQL Server to connect to. isql looks this name up in the interfaces file. If you specify -S with no argument, isql looks for a server named SYBASE. Without -S , isql looks for the server specified by your DSQUERY environment variable.

-U username -  specifies a login name. Logins are case-sensitive.

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

Comments

Examples

  1. isql -Ujoe -Pabracadabra

  2.  

     
     
     

    1> select *
    2> from authors
    3> where city = "Oakland"
    4> vi

    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.

  3. isql -U alma

  4. Password:

    1> select *
    2> from authors
    3> where city = "Oakland"
    4> reset
    1> quit

    reset clears the query buffer. quit returns you to the operating system.

  5. isql -a mac -J roman8

  6.  

     
     
     

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


Comprehensive list of Options

-e - echoes input.

-F - enables the FIPS flagger. With this option, the server flags any non-SQL standard commands sent.

-p - prints out performance statistics.

-n - removes numbering and the prompt symbol (>) from input lines.

-v - prints the version and copyright of the isql software that you are using, and exits.

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

If isql crashes, the system creates a core file which 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.

-Y - tells the SQL Server to use chained transactions.

-a display_charset - allows you to run isql from a terminal whose character set differs from that of the machine on which isql is running. -a in conjunction with -J specifies 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.

In Japanese language environments, the -q flag is required to translate Hankaku Katakana (half-width characters) into Zenkaku Katakana (full-width characters). Use with the argument "zenkaku" and with the -J flag to indicate the client's Japanese character set ( sjis or eucjis ). The zenkaku.xlt file was designed to translate only from terminal display to SQL Server, not from SQL Server to the terminal.

Note: The ascii_7 character set is compatible with all character sets. If either the SQL Server's or client's character set is set to ascii_7, any 7-bit ASCII character is allowed to pass between client and server unaltered. Other characters produce conversion errors. See "Converting Character Sets Between SQL Server and Clients" in the System Administration Guide for more information on character set conversion.
-c cmdend - changes the command terminator. By default, terminate commands and send them to SQL Server by typing "go" on a line by itself. When you change the command terminator, do not use SQL reserved words or control characters.

-E editor - specifies an editor other than the default editor vi .

-h headers - specifies how many rows to print between column headings. The default prints headings only once for each set of query results.

-H hostname - sets the client hostname.

-i inputfile - specifies the name of an operating system file to use for input to isql . The file must contain command terminators ("go" by default).

Specifying the parameter as follows:

 -i  inputfile
is equivalent to:
 <  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, you must 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 SQL Server. Without -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 SQL Server convert to and from client_charset , the character set used on the client. A filter converts input between client_charset and the SQL 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. (See "Converting Character Sets Between SQL Server and Clients" in the System Administration Guide for more information about character sets and the associated flags.)

-l login_timeout - specifies the maximum timeout value allowed when connecting to SQL Server.

-m errorlevel - customizes the error message display. For errors of the severity level specified or higher only the message number, state, and error level display; 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 follows:

 -o  outputfile
is similar to:
 >  outputfile
-P password -  specifies your current SQL Server password. If you do not specify the -P flag, isql prompts for a password. If your password is NULL, use the -P flag at the end of the command line without any password.

-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 -  specifies the name of the SQL Server to connect to. isql looks this name up in the interfaces file. If you specify -S with no argument, isql looks for a server named SYBASE. Without -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, a 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. Logins are case-sensitive.

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

-y sybase_directory - specifies a SYBASE directory other than the default $SYBASE directory.

-z language - is 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 a SQL Server at installation, or add them afterwards with the utility langinstall or the stored procedure sp_addlanguage .

-A size - specifies the network packet size to use for this isql session For example:

 isql -A 2048
sets the packet size to 2,048 bytes for this isql session. size must be between the values of the default network packet size and max network packet size configuration parameters, one-third the size of the additional network memory configuration parameter, and it must be a multiple of 512.
 
 
 
Using the isql utility home - top of the page -

 

Using the isql Utility

This chapter introduces the interactive SQL utility isql and discusses some isql topics: changing the command terminator, the interaction of the performance option and command terminator values, setting the network packet size, and input and output files.

How to Use Transact-SQL with the isql Utility

You can use SQL directly from the operating system with the standalone utility program isql . You must have an account, or login, on SQL Server. To access the account, enter this command at your operating system prompt:
 isql
The following prompt appears:
 Password:
Type your password at the prompt and press the Return key. The password does not appear on the screen as you type. The isql prompt appears, as follows:
 1>
You can now start issuing Transact-SQL commands. The isql program sends the commands to SQL Server, formatting the results and printing them to standard output. There is no maximum size for an isql statement.

Terminate a command by typing the default command terminator go on a new line. For example:

 isql 
 Password: 
 

1> select * 
 2> from authors 
 3> where city = "Oakland" 

4> go
To exit isql , type "quit" or "exit" on a line by itself.

Formatting isql Output

Table 3-1 describes the command line options that change the format of isql output:

Table 3-1: Format options for isql
Option  Default  Meaning 
-h headers  Number of rows to print between column headings 
-s colseparator Single space  Changes the column separator character 
-w linewidth  80 characters  Changes the line width 

To include each command issued to isql in the output, use the -e option; use the -n option to remove numbering and prompt symbols. For example:

 isql -e -n -o output 
 Password:
 select * 
 from authors 
 where city = "Oakland"

 go 
 quit
 cat output
 select * 

from authors 
 where city = "Oakland" 

 au_id       au_lname                             au_fname 

 phone        address                                  

 city               state  country      postalcode  

 ----------- ------------------ --------------------------  

------------ ----------------------------------------  

-------------------- ----- ------------ ----------  

 213-46-8915 Green                                 Marjorie 

 415 986-7020 309 63rd St. #411                        

 Oakland              CA    USA          94618       

 274-80-9391 Straight                               Dick         

 415 834-2919 5420 College Av.                         

 Oakland              CA    USA          94609       

 724-08-9931 Stringer                             Dirk 

 415 843-2991 5420 Telegraph Av.                       

 Oakland              CA    USA          94609       

 724-80-9391 MacFeather                           Stearns 

 415 354-7128 44 Upland Hts.                           

 Oakland              CA    USA          94612       

 756-30-7391 Karsen                               Livia 

 415 534-9219 5720 McAuley St.                         

 Oakland              CA    USA          94609       

(5 rows affected)
Note that the output file does not include the command terminator.

Correcting Input

If you make an error when typing a Transact-SQL command, you can:

set Options That Affect Output

Table 3-2 lists the set options that affect Transact-SQL output. For more information, refer to the set command in the SQL Server Reference Manual .

Table 3-2: set options that affect Transact-SQL output
set Option  Default  Meaning 
char_convert  Off  Turns character set conversion off and on between SQL Server and a client; it also starts a conversion between the server character set and a different client character set 
fipsflagger Off  Warns when any Transact-SQL extensions to entry level SQL92 are used 
flushmessage  Off  Sends messages as they are generated 
language  us_english  Sets the language for system messages 
nocount  Off  Turns off report of number of rows affected 
noexec Off  Compiles each query but does not execute it; often used with showplan 
parseonly  Off  Checks the syntax of queries and returns error messages without compiling or executing the queries
showplan  Off  Generates a description of the processing plan for a query; does not print results when used inside a stored procedure or trigger 
statistics io 
statistics time 
Off Displays performance statistics after each execution 
statistics subquerycache  Off  Displays the number of cache hits, misses, and rows in the subquery cache for each subquery 
textsize  32KB  Controls the number of bytes of text or image data returned 

Changing the Command Terminator

If you include the command terminator argument ( -c ), you can choose your own terminator symbol; "go" is the default value for this option. You must always enter the command terminator without blanks or tabs in front of it.

For example, to use a period as the command terminator, invoke isql as follows:

 isql -c.
A sample isql session with this command terminator looks like this:
 1> select name from sysusers 
 2> .
 name
 
  ----------- 
 sandy 
 kim 
 leslie
(3 rows affected)
Using the isql command terminator option with scripts requires advance planning:

Performance Statistics Interaction with Command Terminator Values

isql provides a performance statistics option ( -p ). For example:
   isql -p
 1> select * from
sysobjects 
 2> go
returns the following statistics:
 1 xact: 
 Clock Time (ms.):
total = 2000 avg = 2000 (0.50 xacts per sec.)
This means that a single transaction took 2,000 milliseconds, so the average is one transaction per 2,000 milliseconds. The clock time value reflects the entire transaction, which starts when Client-Library builds the query and ends when Client-Library returns the information from SQL Server.

You can gather performance statistics based on executing one or more transactions. To gather statistics on more than one transaction, specify a number after the command terminator ( go , by default). For example:

 isql -p
 1> select * from
sysobjects 
 2> go 3
instructs SQL Server to execute three select * transactions and report the performance statistics. SQL Server returns:
 3
xacts: 
 Clock Time (ms.): total = 1000 avg = 333 (3.00 xacts per
sec.)

Setting the Network Packet Size

The -A size option specifies the network packet size to use for this isql session For example:
 isql -A 2048
sets the packet size to 2,048 bytes for this isql session. To check, type:
 select * from sysprocesses
The value appears under the network_pktsz heading.

size must be between the values of the default network packet size and max network packet size configuration parameters, one-third the size of the additional network memory configuration parameter, and must be a multiple of 512. SQL Server uses the closest available packet size that is a multiple of 512 if there is not enough memory available.

Use packet sizes larger than the defaults to perform I/O-intensive operations, such as readtext or writetext operations.

Setting or changing SQL Server's packet size does not affect the remote procedure call's packet size.

Input and Output Files

You can specify input and output files on the command line with the -i and -o options.

isql does not provide formatting options for the output. However, you can use the -n option to eliminate the isql prompts, and use other tools to reformat the output.

If you use the -e option, isql echoes the input to output. The resulting output file contains both the queries and their results.

UNIX Command Line Redirection

The UNIX redirection symbols, "<" and ">", provide a similar mechanism to the -i and -o options, as follows:
 isql -Usa -P  password  < input > output
You can direct isql to take input from the terminal, as in the following example:
 isql -Usa -P  password  <<
EOF > output
 select * from table
 go
 EOF
"<<EOF" instructs isql to take input from the terminal until the string "EOF." You can replace "EOF" with any character string.

Similarly, the following example signals the end of input with Ctrl-d:

 isql -Usa -P  password  << > output