From Ingres Community Wiki

Contents 1 References 2 Description 3 Ingres Enhancement Number 4 Notes 5 Examples 5.1 Simple Usage Example 5.2 Creating a CSV file 6 Documentation

References

Design Document is available here.

Test scripts and results are available here.

Description

A new flag for the line-mode SQL terminal monitor so that it operates in "silent mode" i.e. only the output from queries is returned, there are no headers, footers, column titles or separators, lines or row counts.

This can be useful as it allows simple reports to be created as SQL scripts and then run without needing to massage the output with grep, sed, awk etc.

This is similar to the existing "-s" flag but -s still retains some extraneous output (see example below) To maintain backwards compatibility a new currently unused flag, -S, has been used.

In addition 7 new terminal monitor commands have been implemented:

\silent - turns on silent mode

\nosilent - turns off silent mode

\titles - turns on column titles

\notitles - turns off column titles

\vdelimiter {char} - sets column separator or resets

\trim - trims space padding from column output

\notrim - returns to default i.e. columns space padded to column width defined by datatype

Ingres Enhancement Number

• Trac # 397
• SIR number 122324
• enhancement requested in ServiceDesk issue 112485

Notes

\p still works as expected so to not print out the query text \p should not be used.

A useful related change would be the ability to format individual columns at runtime. This has not been implemented.

Examples

Simple Usage Example

Given the SQL script t.sql which contains:

select first 3 ct_id, ct_code
from country
\g

This is the output from the terminal monitor with no flags:

$ sql demodb < t.sql|cat
INGRES TERMINAL MONITOR Copyright 2009 Ingres Corporation
Ingres Linux Version II 9.4.0 (int.lnx/00)NPTL login
Thu Jul 16 13:51:37 2009

continue
* * * Executing . . .


+-------------+------+
|ct_id        |ct_cod|
+-------------+------+
|          229|GB    |
|          201|ZA    |
|           39|CA    |
+-------------+------+
(3 rows)
continue
*
Your SQL statement(s) have been committed.

Ingres Version II 9.4.0 (int.lnx/00)NPTL logout
Thu Jul 16 13:51:37 2009

This is the output using the "-s" flag.

$ sql -s demodb < t.sql|cat
INGRES TERMINAL MONITOR Copyright 2009 Ingres Corporation

+-------------+------+
|ct_id        |ct_cod|
+-------------+------+
|          229|GB    |
|          201|ZA    |
|           39|CA    |
+-------------+------+
(3 rows)

Here is the output with "-S"

$ sql -S demodb < t.sql | cat
ct_id         ct_cod
          229 GB
          201 ZA
           39 CA
$

Creating a CSV file

Here's an example of using the new terminal monitor commands to create a CSV file. Here's the SQL script 'test9.sql'

/* test 9
   example of using \silent and \vdelim to generate a CSV file 
*/

/* First some data */

declare global temporary table session.tmp
(Unique_Person_id integer,
 Name varchar(30),
 date_of_birth ingresdate,
 salary money)
on commit preserve rows
with norecovery
\p\g

insert into session.tmp values (1, 'Tom','1-apr-1970',60000)
\p\g
insert into session.tmp values (2, 'Dick','31-jul-1955',100000)
\p\g
insert into session.tmp values (3, 'Harry','4-nov-1980',30000)
\p\g
insert into session.tmp values (4, 'Jane','16-mar-1967',45000)
\p\g
insert into session.tmp values (5, 'Sarah','13-sep-1946',250000)
\p\g

/* select from the table with default settings */

select * from session.tmp
\p\g
\silent
\script emp.csv
\vdelim ,
\trim 
/* select in silent mode */
select * from session.tmp
\g
\script
\nosilent
\vdelim
\notrim
select '*** File emp.csv created ***'
\g

Here's the output:

INGRES TERMINAL MONITOR Copyright 2009 Ingres Corporation
Ingres Linux Version II 10.0.0 (int.lnx/00)NPTL login
Mon Aug 24 18:34:21 2009

continue
* * * * * * * * * * * * * * /* SQL Startup File */
/* test 9
   example of using \silent and \vdelim to generate a CSV file 
*/

/* First some data */

declare global temporary table session.tmp
(Unique_Person_id integer,
 Name varchar(30),
 date_of_birth ingresdate,
 salary money)
on commit preserve rows
with norecovery
Executing . . .

continue
* * * 
insert into session.tmp values (1, 'Tom','1-apr-1970',60000)
Executing . . .

(1 row)
continue
* * insert into session.tmp values (2, 'Dick','31-jul-1955',100000)
Executing . . .

(1 row)
continue
* * insert into session.tmp values (3, 'Harry','4-nov-1980',30000)
Executing . . .

(1 row)
continue
* * insert into session.tmp values (4, 'Jane','16-mar-1967',45000)
Executing . . .

(1 row)
continue
* * insert into session.tmp values (5, 'Sarah','13-sep-1946',250000)
Executing . . .

(1 row)
continue
* * * * * 
/* select from the table with default settings */

select * from session.tmp
Executing . . .


+-------------+------------------------------+-------------------------+--------------------+
|unique_person|name                          |date_of_birth            |salary              |
+-------------+------------------------------+-------------------------+--------------------+
|            1|Tom                           |01-apr-1970              |           $60000.00|
|            2|Dick                          |31-jul-1955              |          $100000.00|
|            3|Harry                         |04-nov-1980              |           $30000.00|
|            4|Jane                          |16-mar-1967              |           $45000.00|
|            5|Sarah                         |13-sep-1946              |          $250000.00|
+-------------+------------------------------+-------------------------+--------------------+
(5 rows)
continue
* Entering silent mode
unique_person_id,name,date_of_birth,salary 
1,Tom,01-apr-1970,$60000.00 
2,Dick,31-jul-1955,$100000.00 
3,Harry,04-nov-1980,$30000.00 
4,Jane,16-mar-1967,$45000.00 
5,Sarah,13-sep-1946,$250000.00 
Exiting silent mode
* Vertical column separator reset to default
Output will be space-padded to column width
* * Executing . . .


+----------------------------+
|col1                        |
+----------------------------+
|*** File emp.csv created ***|
+----------------------------+
(1 row)
continue
* 
Your SQL statement(s) have been committed.

Ingres Version II 10.0.0 (int.lnx/00)NPTL logout
Mon Aug 24 18:34:21 2009

Here's the file 'emp.csv'

unique_person_id,name,date_of_birth,salary 
1,Tom,01-apr-1970,$60000.00 
2,Dick,31-jul-1955,$100000.00 
3,Harry,04-nov-1980,$30000.00 
4,Jane,16-mar-1967,$45000.00 
5,Sarah,13-sep-1946,$250000.00 

Documentation

Terminal Monitor Silent Mode

The line-based terminal monitor for SQL can be run in silent mode. Silent mode shows only query output; it suppresses header and footer text, column titles, separators, and row counts.

In addition, the terminal monitor has new commands, which include:

Command	Description
\[no]silent	Switches silent mode on and off.
\[no]titles	Switches column titles on and off.
\[no]trim	Trims or does not trim spaces around column data.
\vdelimiter	Specifies the vertical separator character or resets it to the default. The character can also be set to SPACE, TAB, or NONE.

This feature allows simple reports to be created as SQL scripts and then run without having to edit the output.

To start the Terminal Monitor in silent mode use the –S flag on the sql command. For example:

sql –S demodb

To invoke silent mode while in a script use \silent. For example:

sql demodb
*\silent

Each command issues a confirmation message when in non-silent mode.