Switch to standard view 
  Sybase logo
 
 
 



Contents

This FAQ answers the following questions:

Can the rows-affected line be suppressed in isql?

Release 11.0.x and higher

Use the nocount option to suppress the rows-affected line:

1> set nocount on
2> go

Pre-release 11 (UNIX only)

It is not possible to suppress the rows-affected line in versions of ISQL prior to 11.0.

 

Must I enter the command "go" for every command batch in isql?

All examples of SQL syntax published by Sybase use "go" as the command batch terminator. Can I use a different terminator?

Yes. The command option -c string on UNIX, Windows NT, Windows 95, DOS, or OS/2 platforms and qualifier /TERMINATOR=string on OpenVMS specifies the string to be used by isql instead of "go".

For example, to use a period (“.”) as the command terminator, invoke isql on UNIX or PC platforms 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)

See the Adaptive Server Enterprise Utility Programs manual for your platform for a description of the -c option of isql.

Keep the following points in mind when dealing with the isql command terminator:

  • Sybase-supplied scripts and stored procedures always use "go."

  • If your own scripts use "go" or another command terminator, you must edit your scripts accordingly.

  • Make sure that the selected command terminator does not allow "partial commands." For example, if you make a carriage return the command terminator, you could accidentally press the carriage return before entering a needed WHERE clause:

    1> delete from big_table  2> 

    (2855 rows affected, return status = 0)


WARNING!

Using the carriage return as a terminator can have undesired effects. Command terminators are discussed in the Adaptive Server Enterprise Utility Programs manual for your platform.


How can I change the precision of float values displayed through isql?

By default, isql displays only 6 digits of float or real data after the decimal point, rounding off the remainder. You can change the precision of display for float values in isql by using the str function.

Using the str function documented in the Sybase Adaptive Server Enterprise Reference Manual, you can display more digits as:

1> select str(floatcol, length1, length2) from <table_name> 2> go 

where length1 is the total number of characters to return, including the decimal point, blank spaces, and digits to the right and left of the decimal, and where length2 is the number of digits to the right of the decimal point.

How do I save the contents of an isql session to a file?

PC

Follow the instructions below to use SQL Advantage or redirection to save your isql session from a PC machine.



Note:

SQL Advantage, introduced in Adaptive Server Enterprise 11.5, replaces WISQL. For detailed instructions on using SQL Advantage, see the SQL Advantage User's Guide.


Using SQL Advantage

  1. Start SQL Advantage from the Sybase folder.

  2. From the Server menu, choose Connect to open a connection to Adaptive Server as the system administrator.

  3. Enter the query in the command pane of the session window.

  4. Run the query:

    CTRL -E
    Select execute in the Query menu.

    The Output window displays.

  5. Select the Output window and choose Save from the File menu.


Redirecting the output to a file

Use this option only when you know exactly what you want to type. Extraneous characters, such as backspace, will not be stored.

  1. Invoke isql with the -o option:

    isql -Uusername -Ppassword -ofile_name

    Although you do not see your isql entries, or the server's response, the isql session is stored in the file file_name.

  2. Enter the SQL statements and execute:

    1> sp_who 2> go

    1> select spid from sysprocesses 2> go

    1> exit

  3. Go to the operating system directory where the output file is stored and at the DOS prompt display the contents of the output file:

    D:\temp>type file_name

     
    1> 
    2>  spid	status	loginame	hostname	blk	dbname	cmd 
    ------ 	-----------	-----------	---------- 	-----	---------- 	----------------  
    	1 	running      sa           TS_PC33    0     master     SELECT  
    	2 	sleeping     NULL                    0     master     NETWORK HANDLER 
    	3 	sleeping     NULL                    0     master     CHECKPOINT SLEEP 
    	4 	sleeping     NULL                    0     master     DEADLOCK TUNE 
    	5 	sleeping     NULL                    0     master     MIRROR HANDLER        
    	6 	sleeping     NULL                    0     master 	HOUSEKEEPER        
    	7 	sleeping     NULL                    0     master     SHUTDOWN HANDLER  
    (7 rows affected, return status = 0)  
    
    1> 
    2>  	spid   
    	------        
    	1        
    	2        
    	3        
    	4        
    	5        
    	6        
    	7  
    (7 rows affected)  1> D:\temp>


UNIX

Follow the instructions below to use the script command or redirect output to save your isql session from a UNIX machine:

Using the UNIX script command

  1. Type the following at the operating system prompt, before invoking isql:

    script

    A file typescript is created, and information written to the screen during the isql session is saved in the file. Script records all keystrokes, including backspace and other unwanted characters. To terminate script, enter the keyboard combination CTRL–d

    See the UNIX man page for script for more information.


Redirecting the output to a file

Use redirection only when you know exactly what you want to type. Extraneous characters, such as backspace, are not stored.

  1. Invoke isql with the -o option:

    isql -Uusername -Ppassword -o file_name

    Although you will not see your isql entries, or the server's response, the isql session will be stored in the file file_name. Alternately, you can use UNIX redirection symbols instead of the -o option, as described in the Sybase Adaptive Server Enterprise Utility Programs for UNIX.


VMS

Follow the instructions below to use set host, assign, or redirect output to save your isql session from a VMS machine:

Using the set host command

  1. At the DCL prompt, type the following:

    set host 0/log=filename.log

  2. Enter your VMS username and password.

  3. Start isql.

  4. Log out. The file filename.log is created. Information written to the screen during your isql session is saved in filename.log.


Using the assign command

  1. At the DCL prompt, type the following:

    assign file_name sys$output

  2. Start isql.

    isql /u="user_name" /p="password"

  3. Enter information for this isql script, including command terminators. Although you do not see your isql entries, or the server's response, the isql session is stored in the file file_name.

  4. Enter Quit to run the isql session specified in file_name and log out of isql.

    quit

    The contents of sys$output written during your isql session is saved in file_name.

  5. Type the following command to stop assigning sys$output to file_name.

    deassign sys$output


Redirecting the output to a file

  1. Invoke isql with the /output option:

    isql /u="username" /p="password" /output=file_name

    The results of your session will be stored in file_name.


How do you call an operating system command from OpenVMS isql?

You can spawn an operating system command from edit/tpu, usually set up as the symbol eve.

Spawn an OS command from an editor

  1. Run isql.

  2. At the isql prompt, enter:

    edit

  3. Press the Do key to access the command prompt.

  4. At the command prompt, spawn a subprocess:

    Command: spawn

  5. From the DCL prompt, enter DCL commands or procedures.

  6. When finished, log out of the DCL subprocess to return to the editor:

    $ logout



WARNING!

Do not use Ctrl-Y from isql after spawning from isql. Some DCL commands will not allow you to return to isql. This can be particularly dangerous if you have open transactions in isql when the subprocess is spawned.


How do I determine the isql version on OpenVMS?

Enter the following:

isql /version

isql displays the version number and any EBFs.



Note:

The version number that displays may not match your version of Adaptive Server or Sybase SQL Server. The isql utility's version numbering, for example 10.0.4, is associated with Sybase's connectivity products, not the server.


Can I suppress column names and hyphens?

UNIX

While you cannot suppress both column names and the line of hyphens from within isql, you can suppress them on UNIX. You can delete both column names and the line of hyphens by redirecting the isql output through the tail command:

isql -Usa - Ppassword < script | tail +2

This command strips the first two lines, but only works for a single select statement in the batch where columns fit on a single line.

PC with release 11.5.x

You can use SQL Advantage to suppress everything but the data. To do so:

  1. Connect to the server from SQL Advantage.

  2. Go to File\Global preferences (or session preferences).

  3. De-select the Display Row Count message.

  4. De-select the Display Column headings.

The Preferences panel, as shown next, displays. The panel's background displays the output of

 select * from sysprocesses

without column headings and row count. You can modify the output format by using Column Aligned, or by specifying a different Delimiter. For detailed instructions on using SQL Advantage, see the SQL Advantage User's Guide.


PC and UNIX

Use the -n option to eliminate the isql prompts and other tools to reformat the output.

isql -Usa -Ppassword -n -ofile_name

VMS

Use the noprompt option to eliminate the isql prompts and other tools to reformat the output.

isql /username="username" /password="password" /noprompt /output=file_name

All platforms

A platform-independent solution is to rename the columns in the result table to null strings. This does not suppress any lines, but does cause a blank line to appear in place of the base table column names in your output.

1> select ""=title, " "=price from titles
2> go

where each string for the column name contains a different number of spaces (so they are viewed as different column names).

The query returns a result table without column headers.

----------------------------------------------------------------
You Can Combat Computer Stress         2.99
Straight Talk About Computers         19.99
Silicon Valley Gastronomic Treats     19.99
The Gourmet Microwave                  2.99
But Is It User Friendly?              22.95

How do I prevent the password from being displayed by the UNIX ps command?

On some UNIX platforms and in some versions, isql for UNIX allows the password to be displayed when you run the ps command. Newer versions of isql try to overtype the password string so that it does not display with ps. However, depending on how the operating system stores the password string, this strategy may not be successful. The password is revealed in the ps output only in cases where isql is launched with the password in the command line.

Some ways to avoid putting the password in the command line are:

  • Use the command line -i option rather than the shell redirect (<). isql then displays the password: prompt.

    % isql -U username -i input.sql -o output.out 

    Password: password

  • Write a script, placing the password in the first line that isql receives from standard input.

    isql -U username « EOF
    password
    1> use database
    2> go
    1> sp_help
    2> go 
    1> quit

    EOF

  • Echo the password and pipe it into isql. The password is not displayed by the ps command and is not part of the isql call:

    echo password | isql -U username « EOF
    1> use database
    
    2> go
    1> sp_help 
    2> go 
    1> quit

    EOF

How do I view a query plan in isql?

To view a query plan from within isql, enter the following:

1> set showplan on
2> set noexec on
3> go
1> select * from authors

2> go

This displays the plan and does not run the query.

PC with release 11.5.x

Using SQL Advantage, select the following options from the Session Preferences window to view the query plan:

  • No Execute (compile only)

  • Query Process Plan

  • Query Time Statistics

  • Query IO Statistics

The query plan displays:




Back to Top
© Copyright 2010, Sybase Inc.