Advanced topics
From jsqsh
Contents |
Advanced topics
This page is loaded with useful advice for work with jsqsh on a day-to-day basis. For getting over the basics of connecting to the database server and running queries, please refer to the getting started page.
The command line
When a line is first read by jsqsh, the first word is separated from the line. This word is then expanded of all variables (see Variables below), followed by command expansion (see Command substitution, below). The first word of the resulting string is then analyzed to see if it is either a valid jsqsh command or alias (see Aliases, below). The jsqsh command line follows many of the same rules as Bourne shell, allowing for file redirection, pipelining, and command substitution via the same syntax.
Comments
Any line beginning with two consecutive hash marks (##) causes the entire line to be ignored completely.
Quoting
When working with jsqsh commands, quoting rules similar to those of Bourne shell are also followed. For example, variables within single quotes will not be expanded, variables within double quotes will be expanded, etc.
Buffers
Each time you create and execute a SQL statement, that statement is saved away into your SQL buffer history. The list of prior SQL statements can be displayed using the \history command, like so:
1> \history (6) select key1, count(*) from statistic_keys where statistic_name like 'Network%' group by key1 order by 1 (5) select key1, count(*) from statistic_keys where statistic_name like 'Network%' group by key1 order by 2 (4) select agent_path from ei_master..agent where agent_path like '%network%' (3) select agent_path from ei_master..agent where agent_path like '%cpu%' (2) use ei_master (1) select name from sysobjects where name like '%PAT%'
The number shown next to each item in the list corresponds to how far back it was since you last executed that statement. Additionally, this history is retained between executions of jsqsh.
Buffer syntax
Many sqsh command allow you to refer to SQL buffers via special syntax, each starting with an exclamation character (!). This syntax allows for the following forms:
| Syntax | Description |
| !. | Refers to the current SQL buffer that you are currently typing in to (but have not yet executed). |
| !.. | Refers to the most recently executed SQL buffer. |
| !! | Synonym for !.. |
| !... | Refers to the statement you executed two executions prior. Additional periods may be provided to continue moving back through executions (e.g. !....... refers to the statement executed 6 executions prior). |
| !N | This syntax allows you to refer to SQL statements executed a specific number of executions prior. For example "!0" refers to the current buffer (that has not yet been executed), "!1" refers to the most recently execute statements, "!2" the one before that and so on. |
Buffer recall
Any time you are sitting at a jsqsh prompt you may recall a previously executed SQL statement using the syntax shown above. For example, to re-execute the most recently executed SQL statement, I can do:
1> !! ... 5> \go
where the ... will be the last executed statement.
Buffer commands
JSqsh offers a number of different commands for working with buffers. The list of command can be found using the \help command and looking for any command with the prefix "buf-"
Variables
Variables are provided in jsqsh, much in the same way they are used within a standard shell. They may be used for storing and retrieving information, both within a jsqsh command as well as within a SQL batch.
For example, lets say that you have a long table name that you don't like to type over and over again, you can use a variable in place of the table name:
1> \set t="a_really_long_table_name" 1> SELECT "Count" = COUNT(*) FROM $t 2> go
Variables may also be used anywhere within a jsqsh command, such as:
1> \set g="go" 1> SELECT "Count" = COUNT(*) FROM $t 2> $g
Additionally, most aspects of jsqsh's behavior may be modified by setting special variables:
1> \set null="NO VALUE" 1> select null; +----------+ | | +----------+ | NO VALUE | +----------+
Velocity
Under the hood, jsqsh uses an engine called Velocity to implement its variable expansion logic. This means that you have access to more than just basic variable expansion; velocity supports its own small language with if/then/else logic and much more. For a detailed reference on Velocity, see the Velocity Users Guide
Aliases
Aliasing provides a mechanism for replacing a jsqsh command name with a different command name of your choosing, or creating a new command name that pre-provides a set of arguments to a different command. For example, lets say you would like to connect your production server with a single command rather than have to provide all of the necessary arguments to \connect:
1> \alias '\prod'='\connect -U me -S SQLPRD -d mssql-jtds` 2> \prod Password: ****** [SQLPRD][me][master] 1>
In reality you would want to place the \alias call in your $HOME/.jsqsh/sqshrc file so that it will be available to you every time you start jsqsh.
Piping output
The output of any jsqsh command can be redirected to an external program by use of a pipe (|). For example, lets say you were lazy and didn't want to write a WHERE clause on your SQL but wanted to only see rows that had the word "hello" in them:
1> select * from mytable 2> \go | grep hello
When jsqsh encounters a pipe character following a command, it takes everything to the right of the pipe character, expands any variables it encounters, and executes it on the operating system, sending the output of the jsqsh command to its input stream.
For me, the most common use of this feature is '\go | more'.
Redirecting output
Also, as with most shells, jsqsh provides the ability to redirect the output of a command using standard shell syntax, such as:
1> select * from mytable 2> \go > mytable.out
This will send the results of the \go command to a file called mytable.out rather than to your screen. Similarly 2> can be used to redirect errors and 2>&1 can be used to send errors to the same location as regular output.
Command substitution
This feature allows the output of an operating system command to substituted anywhere within a jsqsh command (currently this is not supported within SQL batches, like sqsh supports), simply by placing the command within backquotes. For example:
1> \set mypassword=`grep mypassword /opt/passwords.txt` 1> \connect -U me -P $mypassword -S prod -d mssql-jtds
In this example, the mypassword variable is being populated by running the operating system command 'grep mypassword /opt/passwords.txt', and then that password is being used to establish a connection to the database.
Session management
One feature unique to jsqsh over sqsh is the ability to manage multiple open connections at the same time. Each of these open connections are referred to as sessions. A new session is started by passing the -n flag to \connect which will create a new session to manage the connection. You can then use the \session command to view currently open sessions or switch between open sessions:
1> \connect -U sa -S esm-rdev -d mssql-jtds Password:******** [esm-rdev][sa][master] 1> \connect -U sa -S prod-db -d mssql-jtds -n Password:********** Current session: 4 (jdbc:jtds:sqlserver://prod-db:1433) [prod-db][sa][master] 1> \session +-----+----------+----------------------------------------------------+ | Id | Username | URL | +-----+----------+----------------------------------------------------+ | 3 | sa | jdbc:jtds:sqlserver://esm-rdev:1433 | | * 4 | sa | jdbc:jtds:sqlserver://prod-db:1433 | +-----+----------+----------------------------------------------------+
The output of the \session command shows the currently open sessions with an asterisk (*) next to session that you are currently working in. To switch to the other session, simply issue:
[prod-db][sa][master] 1> \session 3 Current session: 3 (jdbc:jtds:sqlserver://esm-rdev:1433) [esm-rdev][sa][master] 1>
And, finally to end the current session without exiting jsqsh altogether use the \end command:
[esm-rdev][sa][master] 1> \end Current session: 4 (jdbc:jtds:sqlserver://prod-db:1433) [prod-db][sa][master] 1>
Tab completion
As of version 0.9.4, jsqsh now support tab completion of SQL objects (provided you are running with readline support and your JDBC driver support meta-data lookup functionality). With this feature, after hitting the tab key at a partially completed object name, jsqsh will complete the object name for you or show you a list of matching names:
[prod-db][sa][master] 1> select * from sys<tab> SYSREMOTE_CATALOGS sysfiles SYSREMOTE_COLUMNS sysfiles1 SYSREMOTE_COLUMN_PRIVILEGES sysforeignkeys SYSREMOTE_FOREIGN_KEYS sysfulltextcatalogs
Name completion should work for database names, table names, and column names and ANSI quoted object names are recognized (e.g. "My Table"."My Column") as well as bracketed names (e.g. [My Table].[My Column]).
