|
-S |
Sybase Server |
|
-U |
Sybase Userame |
|
-P |
Sybase User Password |
|
-D |
Sybase Database |
|
-T |
Maximum Time-out waiting for Results from Sybase [14400 seconds] |
|
-t |
Maximum Time-out waiting for a Connection from Sybase [10 seconds] |
|
-C |
Input single SQL command |
|
-I |
Input SQL command file [standard input] |
|
-B |
Batch terminator [go] |
|
-b |
Maximum batch length [200000] |
|
-m |
Maximum input record length [100000] |
|
-x |
Maximum image length [256] |
|
-F |
Output results file [standard output] |
|
-N |
Null values are displayed with this text [] |
|
-X |
Column delimiter string for output results file fields [|] |
|
-Y |
Row terminator string for output results file [standard OS terminator] |
|
-R |
Maximum starting size of the existing results file [0 bytes] |
|
-L |
Output messages log [output results file] |
|
-M |
Maximum starting size of the existing messages log [0 bytes] |
|
-E |
E-mail address when error |
|
-K |
(upper case K) Keyword or keyword list |
|
-k |
(lower case k) Keyword value or keyword value list |
|
-O |
Options |
|
p |
Interactive mode |
|
P |
Interactive mode with prompts |
|
o |
Target server is a Replication or Open Server. It is not an ASE Server. |
|
x |
Look for batch terminator at the end of the line. |
|
r |
Discard all results |
|
c |
Do not comment results file |
|
i |
Do not echo your input as comments in the results file |
|
t |
Prefix comments with a time stamp |
|
b |
Remove leading and trailing blanks from results' columns |
|
s |
Put results into vertical columns; do not use the | delimiter |
|
e |
Continue to next batch even when I get an error from Sybase |
|
E |
Always exit with success (0) even when I get an error from Sybase |
|
a |
Do not put abbreviated non-error info messages in the error log |
|
l |
Put full non-error info messages into the error log |
|
z |
Treat server severity 0 as informational as well as severity 10 |
|
y |
Ignore stored procedure creation dependency errors |
|
Exit Codes |
1. psSQL terminates with a meaningful exit code. If the exit code is 0
then psSQL finished without detecting any errors from the Sybase server.
If the exit code is not 0, then Sybase did not like your commands.
2. You can get the text associated with the exit code by running psShowMessage:
psShowMessage -Ccode
3. psSQL does understand the difference between errors verses informational
messages whether the messages come from SQL Server, Backup Server or
Replication Server. If you write your own Open Server, then make 10 your
severity level for informational messages and set the severity for an error
to anything other than 0 or 10. If you are using the DBPowerSuite Open Server
library libpsOpenServer, the library uses severity 10 for information and 100
for an error. |
|
Stopping psSQL in the Middle of Your File |
1. By default, psSQL stops processing your file when it either 1) comes to
the end of your file or 2) comes to the end of a batch--Sybase having
thown an error during the execution of the batch. (Batches are
separated by the word 'go'.) Commands after the bad batch are not sent
to the server.
2. You can change this 'stop on error' behavior. By putting -Oe on the command
line, psSQL will continue to the next batch in spite of prior errors.
Yet still, when psSQL finally ends, it will still end with a non-zero exit
code if there were errors.
3. You can conditionally exit your file with an error by coding a
'raiserror' command in your SQL file. 'raiserror' commands force
the Sybase data server to throw an error.
4. You can conditionally quit your file in the middle and have psSQL end with
a *successful* exit code. You do this with a specific SQL 'print' command:
print 'exit psSQL'
And at the end of the batch which executes this command, and assuming no
prior errors, psSQL will exit successfully. |
|
Interactive Mode |
1. To engage interactive mode, use the -Op option. To see the list of
interactive commands native to psSQL, type 'help' at the psSQL prompt.
By default, psSQL executes in non-interactive mode. You will not see the
psSQL prompt. Also, certain other psSQL-native commands will not
be available to you. For example, you can not use the native commands
history, recall or clean. |
|
Non-Interactive Modes |
1. You can send a single command to the Sybase server by coding it right on the
command line. To do this, use the -C option and enclose your command in
quotes.
2. You can send a file of several commands and batches to the Sybase server. Use
the -I option along with your file name.
3. If you do not use -C or -I, then psSQL looks for command input from
standard input. In this fashion, you can use here-files from within shell
scripts or pipe input into psSQL from other programs. |
|
SQL, Backup, Replication, LTM, Net-Gateway or Open Server |
1. By default, psSQL thinks you are connecting to a SQL Server; therefore,
the first thing psSQL is going to do is to send the 'use database'
command to the server. psSQL gets the name of the database from your
-D command line argument.
2. If you are connecting to *anything* that is not a SQL Server, then use
-Oo on the command line. This will prevent 'use database' from being
sent to the server. |
|
Keyword Substitution |
1. By using the command line arguments -Kold_string and -knew_string, you can
make psSQL replace every occurance of old_string in your input commands with
new_string. In this way, you can avoid using 'sed' prior to running a
SQL script through psSQL. Do not worry, I will not modify your original
input file in any way.
2. Actually, old_string can be more than a single key word. Consider this
on your command line:
-K '_STATE _COUNTRY' -k 'NC USA'
psSQL will replace every occurance of _STATE with NC. psSQL will also
replace every occurance of _COUNTRY with USA. You can place up to
10 key words next to -K or -k.
3. There are 3 reserved built-in keywords: __DBNAME__, __DATABASE__ and
__USERNAME__. Everytime psSQL finds __DBNAME__ or __DATABASE__ in your
input, psSQL replaces it with the -D psSQL command line argument. Everytime
psSQL finds __USERNAME__ in your input, psSQL replaces it with the -U
command line argument. (There are 2 underscores before and after these
special keywords.) |
|
Results File |
1. All Sybase servers send psSQL two types of data. The two types of data are
1. Results
2. Messages
By default, psSQL sends both kinds of data to the results file. You specify
a results file with the -F command line argument. If you not specify the -F
your results go to standard output.
2. At this point, let us discuss the differences between 'results' and
'messages' data. Results data is the rows of data produced from select and fetch
commands, the return codes from stored procedure and the rows-affected counts
from select/update/delete/insert SQL commands.
Messages data, on the other hand, is information created by your SQL
'print' commands and 'raiserror' commands. Also, Sybase servers create
Messages type data all the time. Everytime a SQL Server changes database
it sends Messages type data. Everytime a Sybase Server throws an error,
it sends Messages type data.
3. With psSQL, you can split the results data from the messages data. If you
specify the -L command line argument, you direct messages to go to a different
destination than the results. If you do not specify -L, the messages will
go to the same destination as your results.
The advantage of having your results and your messages go to the same
destination is that you will know what messages go with what results.
You will be able to debug your input commands better.
The disadvantage is is that message type data is difficult to parse
from the surrounding results. I advise that you only separate results
from messages when you are certain that Sybase will process all your
commands successfully and your results are input to another program.
4. You can tell psSQL to append new results to an existing results file by
using the -R option. With the -R, you can specify the maximum size of
existing results file. If the existing results file is over this size,
psSQL appends a '.old' to the existing file, and then creates a new
one for new results to be written into. If the existing results file
is less than the -R maximum size, then psSQL just appends its new results
to the existing file.
5. Using the 'save' psSQL native command, you can create many results files
within a single psSQL session. To see the details of 'save', run psSQL
interactively and type 'help'.
6. Discarding results. You can tell psSQL to execute your commands but
throw away all results. No results file will be created. Use the -Or to
accomplish this. |
|
Comments in your Results File |
1. By default, psSQL will put comments in your results file. Comments take only
2 forms. They either start with the # or they are totally blank lines.
2. If you are going to use the results as input into another program, then you
most likely do not want comments in your file. To remove comments, use the
-Oc option. If you don't use -Oc, then your program must filter out the
comments by using the criteria described in # 1.
3. There may be occasions when you want to time how long your commands take.
If you use -Ot, then psSQL will prefix a time stamp to every comment it
makes.
4. By using nocomment, comment or timecomment psSQL native commands, you can
change the way psSQL comments your results during a session. |
|
Column Delimiters in the Results File |
1. By default, psSQL delimits every column with the | character in non-interactive
mode.
2. By default, psSQL mimics isql in the way it writes results in interactive mode.
Columns are separated by varying number of spaces in order to make nice
vertical columns.
3. To change the default, you can use the -X command line option. Or you can use
the psSQL native command 'delimit'. If you specify a number for the delimiting
character, I assume your number is the ASCII decimal equivalent of the desired
character. If you specify a letter to begin with, then I use that letter.
4. For BCP operations, | is the definitive delimiting character. Also, it
the perfect delimiter if you are programming with DBPowerSuite' UFile class.
5. If you specify -Os on the command line, you get isql-like result output.
'delimit -1' also will get you isql-like output.
6. If your results will be read into Excel, use -X9 as the delimiter.
9 is the ASCII decimal equivalent of a tab. Excel will read such
tab-delimited results files directly. Each Sybase column will fall
into its own column of Excel cells. |
|
Messages Log File |
1. By default, Sybase Messages type data will go to the same destination
as the results data. Refer to the 'Results File' section. If you
declare a -Llog command line argument, then all Sybase's messages will
go to file 'log' instead.
2. You can tell psSQL to append new messages to an existing log file by
using the -M option. With the -M, you can specify the maximum size of
existing messages file. If the existing messages file is over this size,
psSQL appends a '.old' to the existing file, and then creates a new
one for new messages to be written into. If the existing messages file
is less than the -M maximum size, then psSQL just appends its new messages
to the existing file.
E-mail. You can tell psSQL to e-mail all the error messages to anybody
on the internet. If you specify the -E command line argument *and* there
are errors (not just information messages, but actual errors), then psSQL
will e-mail all the errors when psSQL exits. |