|
-S |
ODBC Data Source |
|
-V |
ODBC Data Source's Server |
|
-U |
ODBC Data Source Userame |
|
-P |
ODBC Data Source Password |
|
-A |
ODBC Other Connection Attributes |
|
-T |
Maximum Time-out waiting for Results from the Data Source [14400 seconds] |
|
-t |
Maximum Time-out waiting for a Connection from the Data Source [10 seconds] |
|
-p |
Network packet size (bytes) [ defers to ODBC driver] |
|
-C |
Input single SQL command |
|
-I |
Input SQL command file [standard input] |
|
-B |
Batch terminator [; at the end of the line] |
|
-b |
Maximum batch length [200000] |
|
-m |
Maximum input record length [100000] |
|
-x |
Maximum output image/text length [20480] |
|
-Z |
Bypass characters [#] |
|
-F |
Output results file [standard output] |
|
-N |
Null values are displayed with this text [] |
|
-X |
Column delimiter string for output results file [|] |
|
-Y |
Row terminator string for output results file [standard operating system record terminator] |
|
-R |
Maximum starting size of the existing results file [0 bytes] |
|
-Q |
The maximum split size of the 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 connection prompts |
|
m |
Use Data Direct closed drivers |
|
u |
Set login and result time out properties |
|
n |
Send program name to the data source |
|
q |
Allow quoted identifiers |
|
x |
Look for batch terminator at the beginning 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 despite when I get an error from the data source |
|
E |
Always exit with success (0) even when I get an error from the data source |
|
a |
Do not put abbreviated non-error info messages in the error log |
|
l |
Put full non-error info messages into the error log |
|
Notes to Oracle Users |
If you are going to input PL/SQL, you will have to change your batch terminator
to '/' and tell psOSQL to look for this '/' at the beginning of the line. To tell
psOSQL to behave like this, run psOSQL prompt then type . . .
batch / start
Once done, you can append ';' at the end of lines within your PL/SQL block without psOSQL
sending these intermediate lines to Oracle. The slash '/', instead, sends your
SQL to Oracle.
This psOSQL provision is to get around the Oracle architectural mistake of
not having a single SQL language. In one Oracle SQL language, the ';' sends your
command to the server. In the other SQL language (PL/SQL), the semicolon
is a construct that concludes a line, but does not send your line to the
server. DBPowerSuite does not attempt to fix Oracle's schizophrenia. |
|
Running SQL Files & Changing Connections Within a Session |
You can run SQL files by entering
run your.sql
where 'your.sql' is the name of your SQL file. Your file itself can contain run
commands psSQL will be able to run nested SQL files up to 10 levels deep.
You can connect to a different ODBC data sources from within a session:
connect -Sdsn -Vserver -Uuser -Ppass -Tquery_to -tlogin_to -Olaun
The arguments and options of the internal connect command mean the same thing as
the arguments and options of psOSQL's command line. You can only be connected
to one data source at a time. During a session, there is no limit to the number
of connections you can make.
There are about 20 other psOSQL internal commands. Type 'help' at the prompt to
see them all. |
|
Exit Code |
1. psOSQL terminates with a meaningful exit code. If the exit code is 0
then psOSQL finished without detecting any errors from the data source.
If the exit code is not 0, then the data source did not like at least one
of your commands.
2. You can get the text associated with the exit code by running psShowMessage:
psShowMessage -Ccode
3. psOSQL does understand the difference between errors verses informational
messages. psOSQL does not count informational messages as an error. |
|
Stopping psOSQL in the Middle of Your File |
1. By default, psOSQL stops processing your file when it either 1) comes to
the end of your file or 2) comes to the end of a batch--the data source having
thown an error during the execution of the batch. (Batches are separated
by the ';' in ODBC or by your chosen batch terminator.) psOSQL does not send
commands after it detects the bad batch.
2. You can change this 'stop on error' behavior. By putting -Oe on the command
line, psOSQL will continue to the next batch in spite of prior errors.
Yet still, when psOSQL finally ends, it will still end with a non-zero exit
code if it detected errors.
3. You can conditionally exit your file with an error by coding a
data source 'raiserror' command in your SQL file. In Sybase or Microsoft
a 'raiserror' commands force the data source to throw an error.
4. You can unconditionally quit your file in the middle and have psOSQL end with
a *successful* exit code. You do this with a specific SQL 'print' command.
In the case of Sybase or Microsoft SQL Server:
print 'exit psOSQL'
And at the end of the batch which executes this command, and assuming no
prior errors, psOSQL will exit successfully. |
|
Interactive Mode |
1. To engage interactive mode, use the -Op option. To see the list of
interactive commands native to psOSQL, type 'help' at the psOSQL prompt.
By default, psOSQL executes in non-interactive mode. You will not see the
psOSQL prompt. Also, certain other psOSQL-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 data source 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 data source. Use
the -I option along with your file name.
3. If you do not use -C or -I, then psOSQL looks for command input from UNIX
standard input. In this fashion, you can use here-files from within shell
scripts or pipe input into psOSQL from other programs. |
|
Bypass Characters |
1. psOSQL will skip over lines in your SQL file which start with any of the
bypass characters. psOSQL will not send these lines to the ODBC data
source. These lines will not be a part of the batch.
2. By default, psOSQL will skip any lines beginning with a #. If you do not
want psOSQL to skip any lines, specify -Znobypass |
|
Data-Direct-ODBC, Timeout Properties, and Sending the Program Name |
1. Specify the -Om option only when you are using DataDirect Technology's closed drivers. -Om
tells psOSQL to look into DBPowerSuite's system parameter file for the closed driver license
name and password.
2. Specify the -Ou option if you want to set the login and result time out properties for
the data source. This means that the ODBC driver will timeout with an error if results
do not come back from the data source within -T seconds. Also, the ODBC driver will
timeout with an error if you cannot login to the data source within three seconds.
I made setting the timeout properties an option because some data source and/or some
ODBC drivers do not support timeout properties. For instance, Oracle does not. If
you try setting timeout properties on an Oracle data source, you will get an error.
3. Specify the -On if you want to send the program name, that is, 'psOSQL', as a
connection property to the data source. DBAs like it when you do this because
they can then see that your instance of psOSQL is connected to their database server.
-On is optional because some data sources do not support sending the program name to
the data source. |
|
Keyword Substitution |
1. By using the command line arguments -Kold_string and -knew_string, you can
make psOSQL 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 psOSQL. 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'
psOSQL will replace every occurance of _STATE with NC. psOSQL 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 psOSQL finds __DBNAME__ or __DATABASE__ in your
input, psOSQL replaces it with the -D psOSQL command line argument. Everytime
psOSQL finds __USERNAME__ in your input, psOSQL replaces it with the -U
command line argument. (There are 2 underscores before and after these
special keywords.) |
|
Results File |
1. All data sources send psOSQL two types of data. The two types of data are
1. Results
2. Messages
By default, psOSQL 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, are information created by your SQL
'print' commands and 'raiserror' commands. Also, data sources may create
Messages type data all the time. Everytime a Sybase Server changes database
it sends Messages type data. Everytime a Sybase Server throws an error,
it sends Messages type data.
3. With psOSQL, 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 the data source will process all your
commands successfully and your results are input to another program.
4. You can tell psOSQL 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,
psOSQL 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 psOSQL just appends its new results
to the existing file.
5. You can tell psOSQL to split the results file after it gets too large.
If you set the -Q parameter to 100000, then when the file gets to be larger
than 100000 bytes, psOSQL will close the file, and start a new file. psOSQL
appends a sequential number to the file name.
6. Using the 'save' psOSQL native command, you can create many results files
within a single psOSQL session. To see the details of 'save', run psOSQL
interactively and type 'help'.
7. Discarding results. You can tell psOSQL 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, psOSQL 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 psOSQL will prefix a time stamp to every comment it
makes.
4. By using nocomment, comment or timecomment psOSQL native commands, you can
change the way psOSQL comments your results during a session. |
|
Column Delimiters in the Results File |
1. By default, psOSQL delimits every column with the | character in non-interactive
mode.
2. By default, psOSQL 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 psOSQL 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 column will fall
into its own column of Excel cells. |
|
Messages Log File |
1. By default, data source 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 the data source's messages will
go to file 'log' instead.
2. You can tell psOSQL 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,
psOSQL 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 psOSQL just appends its new messages
to the existing file.
E-mail. You can tell psOSQL 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 psOSQL
will e-mail all the errors when psOSQL exits. |