The output from xSQL interactive commands can be redirected to a file or suppressed by the
outputcommand:output console|null|<filename>This command switches output of the SQL engine between the console, a null device (suppress output) and a disk file. For example the following commands will direct the output of the successive commands and/or SQL statements to file
S.csv, then redirect the output to the console:output S.csv select ... output consoleFormatting Query Output
As mentioned in Step 2, the output from select statements can be formatted for more readable display. In fact there are a variety of ways to style the output and format specific types of output fields.
Output styles
By default xSQL displays query results as seen in previous examples. This is termed
TEXTformat. But output can also be displayed inHTML,XML,CSV, orLISTformats.TEXT
For example, run the
account.sqlscript and then run the followingselect:XSQL>script account.sql XSQL>select * from Account; userid name lastlog ------------------------------------------------------------------------------ 101 Dennis Hamer 01/23/2015 14:15:03 102 Edith Jackson 02/12/2015 19:30:16 103 Eric Harmon 02/14/2015 09:34:47This is the standard or
TEXTformat.CSV
To switch the above query result to
CSVformat, we use theformatcommand which has syntax:format CSV [delimiter ‘<C>’ [header on|off] ]For example, to use the pipe '|' character to separate fields:
XSQL>format CSV delimiter '|' XSQL>select * from Account; userid|name|lastlog|volume 101|Dennis Hamer|01/23/2015 14:15:03|12.775843 102|Edith Jackson|02/12/2015 19:30:16|420.885432 103|Eric Harmon|02/14/2015 09:34:47|0.348857And to remove the header line:
XSQL>format CSV delimiter '|' header off XSQL>select * from Account; 101|Dennis Hamer|01/23/2015 14:15:03|12.775843 102|Edith Jackson|02/12/2015 19:30:16|420.885432 103|Eric Harmon|02/14/2015 09:34:47|0.348857LIST
The LIST option displays each field on a separate line, which can be convenient for long field values (i.e. arrays or sequences):
XSQL>format LIST XSQL>select * from Account; userid: 101 name: Dennis Hamer lastlog: 01/23/2015 14:15:03 volume: 12.775843 ------------------------------------------------------- userid: 102 name: Edith Jackson lastlog: 02/12/2015 19:30:16 volume: 420.885432 ------------------------------------------------------- userid: 103 name: Eric Harmon lastlog: 02/14/2015 09:34:47 volume: 0.348857 ------------------------------------------------------- Selected records: 3HTML
The
HTMLoption represents the output as anHTMLtable:XSQL>format HTML XSQL>select * from Account; <table border> <tr><th>userid</th><th>name</th><th>lastlog</th><th>volume</th></tr> <tr><td>101</td><td>Dennis Hamer</td><td>01/23/2015 14:15:03</td><td>12.775843</td></tr> <tr><td>102</td><td>Edith Jackson</td><td>02/12/2015 19:30:16</td><td>420.885432</td></tr> <tr><td>103</td><td>Eric Harmon</td><td>02/14/2015 09:34:47</td><td>0.348857</td></tr> </table>XML
The
XMLoption displays tags corresponding to the table and field names:XSQL>format XML XSQL>select * from Account; <Account> <userid>101</userid> <name>Dennis Hamer</name> <lastlog>01/23/2015 14:15:03</lastlog> <volume>12.775843</volume> </ Account > < Account > <userid>102</userid> <name>Edith Jackson</name> <lastlog>02/12/2015 19:30:16</lastlog> <volume>420.885432</volume> </ Account > < Account > <userid>103</userid> <name>Eric Harmon</name> <lastlog>02/14/2015 09:34:47</lastlog> <volume>0.348857</volume> </ Account >Numerical format
Often it is desirable to reformat numerical data in query output. This is easily done by means of the format commands:
numformat,dtformat,strformat,seqformatandarrayformat.numformat
The
numformatcommand uses the standard Cprintf()type format string; for example:XSQL>numformat "%6.2f"Using
numformatto round theclosevalues to cents in the previoustop_close.sqlquery we would specify numeric format as follows:XSQL>script top_close.sql symbol top_index top_Close volume ------------------------------------------------------------------------------ SYM0 {34} { 97.24} {732} SYM1 {57} { 96.53} {406} SYM2 {2} { 94.87} {765} SYM3 {65} { 98.74} {953} SYM4 {62} { 93.25} {312} SYM5 {76} { 92.52} {829} SYM6 {59} { 94.83} {761} SYM7 {98} { 99.32} {583} SYM8 {37} { 97.59} {593} SYM9 {81} { 93.78} {257} Selected records: 10(Note that the format specification string is a string suitable for the C
printf()function and allowable options depend on the version of theglibclibrary installed on the development system.)Date/Time format
By default, xSQL will display date fields, like
lastlogin the current locale settings for the host computer. For example:XSQL>select lastlog from Account; lastlog ------------------------------------------------------------------------------ 01/23/2015 14:15:03 02/12/2015 19:30:16 02/14/2015 09:34:47But we can reformat the output using command
dtformatto display the date in international date format with the time in 12-hour notation as follows:XSQL>dtformat "%d/%m/%Y %I:%M:%S %p" XSQL>select * from Account; userid name lastlog ------------------------------------------------------------------------------ 101 Dennis Hamer 23/01/2015 02:15:03 PM 102 Edith Jackson 12/02/2015 07:30:16 PM 103 Eric Harmon 14/02/2015 09:34:47 AM(Note that the format specification string is a string suitable for the C
strftime()function and allowable options depend on the version of theglibclibrary installed on the development system.)To restore output to the current locale settings:
XSQL>dtformat "%c"String format
We might want to restrict string length in query output to a fixed width. For example:
XSQL>script strings.sql XSQL>select * from A; strlen s ------------------------------------------------------------------------------ 33 A string containing 33 characters 14 A short stringThen restrict the width in output to 20 characters as follows:
XSQL>strformat width=20 XSQL>select * from A; strlen s ------------------------------------------------------------------------------ 33 A string containing> 14 A short stringNote that field
sin the second query is truncated to length 20 with the>character indicating truncation. To return to the full width of string fields, specify zero width as follows:XSQL> strformat width=0It is also possible to use the index operator “[from:to]“ with strings to extract a range of characters. For example:
XSQL>select s[1:7] from A; #1 ------------------------------------------------------------------------------ A strin A shortBut in most cases it is preferable to use the
substr()function (demonstrated in step 6) to extract substrings from character string fields.Sequence format
Fields of the
sequencedata type can be ungainly in query output. By default xSQL displays the first 5 and last 5 elements of asequencewith an expression like “…<N element(s)>…” in the middle. For example, if we start xSQL with commandxSQL –i –f IBM-q1-2013.sqlthe script creates table QuoteIBM and initializes it with some historical data from 2013. The sequence field
highcan be displayed as follows:XSQL>numformat "%6.2f" XSQL>select symbol, high from Quote; symbol high ------------------------------------------------------------------------------ IBM {196.35, 196.29, 194.46, 193.78, 193.30, ...<50 element(s)>..., 213.17, 212.81, 212.50, 212.16, 213.44}With command
seqformatwe can limit the number of first and last elements to display. There are two options for how the elements are displayed. The short format displays the elements in order as above; the long format displays the index of each element followed by its value. For example we could reformat the sequence output as follows:XSQL>seqformat short 3 3 XSQL> select symbol, high from Quote; symbol high ------------------------------------------------------------------------------ IBM {196.35, 196.29, 194.46, ...<54 element(s)>..., 212.5, 212.16, 213.44} XSQL>seqformat long 2 1 XSQL>select symbol, high from Quote; symbol high ------------------------------------------------------------------------------ IBM {0:196.35, 1:196.29, ...<57 element(s)>..., 59:213.44} XSQL>seqformat short 3 0 XSQL>select symbol, high from QuoteIBM; symbol high ------------------------------------------------------------------------------ IBM {196.35, 196.29, 194.46, ...<57 element(s)>...} XSQL>seqformat short 0 0 XSQL>select symbol, high from QuoteIBM; symbol high ------------------------------------------------------------------------------ IBM {...<60 element(s)>...}It is also possible to use the index operator
[from:to]with sequences to extract a range of values. For example:XSQL>select symbol, high[1:3] from Quote; symbol #2 ------------------------------------------------------------------------------ IBM {196.29, 194.46, 193.78} XSQL>select symbol, high[57:] from Quote; #1 ------------------------------------------------------------------------------ IBM {212.50, 212.16, 213.44}Array format
Similar to
seqformatfor sequences, with commandarrayformatwe can limit the number of first and last elements to display for fields of type array. For example, we could create and initialize a table A and restrict the array field output as follows:XSQL>create table B (id integer primary key, arr array(char(10))); XSQL>insert into B values (1, ['zero', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine', 'ten']); XSQL>select * from B; id arr ------------------------------------------------------------------------------ 1 [zero, one, two, three, four, ...<1 element(s)>..., six, seven, eight, nine, ten] XSQL>arrayformat long 3 3 XSQL>select * from B; id arr ------------------------------------------------------------------------------ 1 [0:zero, 1:one, 2:two, ...<5 element(s)>..., 8:eight, 9:nine, 10:ten] XSQL>arrayformat short XSQL>select * from B; id arr ------------------------------------------------------------------------------ 1 [zero, one, two, three, four, five, six, seven, eight, nine, ten]
Now that we have seen how to tailor output format, it is useful to explore some of the powerful features of xSQL, like Distributed Database Sharding (step 4), Using Configuration Files (step 5), and more.