TMS: ISQL

Previous: Trx Up: TMS Utilities Next: Using Isql

Sybase SQL Server(TM)Utility Programs for UNIX (Chapter 1): Utility Programs Reference

2.6 Isql

- Interactive SQL parser to 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

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 SQL Server. The results are formatted and printed on standard output. Exit isql with quit or exit.
Send a command to SQL Server by typing the command terminator ( go by default) at the beginning of a line. 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:
```
 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:
```
 isql -c"." -csend 
```
"send", the second value for -c , overrides ".", the first value. 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 . Execute operating system commands by starting a line with "!!" followed by the command. Call alternate editors this way, without defining EDITOR.
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; this cancels the current query and returns the user 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 terminator(s). 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 command:
```
:r  filename  
```
Do not include a command terminator in the file; once you have finished editing, enter the terminator interactively on a line by itself.
You can include comments in a Transact-SQL statement submitted to SQL Server by isql . For more information, see "Comments" in the SQL Server Reference Manual .

Examples

isql -Ujoe -Pabracadabra

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.
isql -U alma
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.
isql -a mac -J roman8
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

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

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

Previous: Trx Up: TMS Utilities Next: Using Isql

[ JACH | JCMT | UKIRT | Computer Services | JCMT TMS ]

Please address any comments, suggestions or requests to:

Remo Tilanus

rpt@jach.hawaii.edu

Last modified: Mon May 5 10:25:50 HST