- How to specify options
There are two kinds of options. the first is the steady option which SET statement specifies, and the second is the temporary option which 'exec_sql' command specifies.
The steady option is valid to all SQL, until it is changed.
The temporary option is effective to the SQL which exec_sql specifies.
- The steady option by using SET statement
The steady option is specified as follows.
In SET statement, there is no distinction of a capital letter and a small letter.
set EXEC_SQL_OPTION DEFAULT;
set EXEC_SQL_OPTION CGI;
set option_name[=value];
(1) DEFAULT
'set EXEC_SQL_OPTION DEFAULT;' sets the value of all options to the default.
(2) CGI
'set EXEC_SQL_OPTION CGI;' sets a shell script to CGI mode.
(3) Other options
The other options can be set ON/OFF(or TRUE/FALSE) or the value.
When ON/OFF omits, it means that ON was set. For example, 'set OPTION_ECHO;' is equal to 'setOPTION_ECHO=ON;'.
example)
set OPTION_ECHO; / set OPTION_ECHO=ON; / set OPTION_ECHO=TRUE;
set OPTION_QUIET=OFF; / set OPTION_QUIET=FALSE;
set OPTION_SEPARATOR=',';
set OPTION_NULLSTRING="*N*";
option_name | āe | DEFAULT value |
OPTION_ECHO | Display the query sent to the backend. | OFF |
OPTION_QUIET | Don't display error messages. | OFF (display) |
OPTION_HEADER | Display the table headers.
ex) set option_header=on;
select * from test;
code|name |addr
----+-------+-----
101|tanaka |tokyo
111|yoshida|fukui
(2 rows)
ex) set option_header=off;
select * from test;
101|tanaka |tokyo
111|yoshida|fukui
(2 rows)
| ON |
OPTION_BOTTOM | Display the table bottom(rows counter).
ex) set option_bottom=off;
select * from test;
code|name |addr
----+-------+-----
101|tanaka |tokyo
111|yoshida|fukui
| ON |
OPTION_ALIGNMENT | Align the rows of table.
Set OFF when outputting CSV form.
See 'OPTION_SEPARATOR'.
| ON |
OPTION_FRAME | Display the outer frame of the table.
ex) set option_frame=on;
select * from test;
+------+---------+-------+
| code | name | addr |
+------+---------+-------+
| 101 | tanaka | tokyo |
| 111 | yoshida | fukui |
+------+---------+-------+
(2 rows)
| OFF |
OPTION_EXPANDED | Change to the expanded output mode
ex) set option_expanded=on;
select * from test;
----- RECORD 0 -----
codeb101
namebtanaka
addrbtokyo
----- RECORD 1 -----
codeb111
namebyoshida
addrbfukui
| OFF |
OPTION_SEPARATOR | Specify the delimiter of the table.
ex) set option_expanded=on;
set option_separator='F';
select * from test;
----- RECORD 0 -----
code:101
name:tanaka
addr:tokyo
----- RECORD 1 -----
code:111
name:yoshida
addr:fukui
ex) set option_header=off;
set option_bottom=off;
set option_expanded=off;
set option_alignment=off;
set option_separator=',';
select * from test;
[CSV form]
101,tanaka,tokyo
111,yoshida,fukui
| | |
OPTION_NULLSTRING OPTION_ZEROSTRING | Specify the NULL or all_bit_off value.
ex) set option_nullstring='\N';
set option_zerostring='\0';
select * from test;
code|name |addr
----+-------+-----
101|tanaka |tokyo
110|\N |\0
210|sakaida|osaka
ex) set option_nullstring='';
set option_zerostring=''
select * from test;
code|name |addr
----+-------+-----
101|tanaka |tokyo
110| |
210|sakaida|osaka
| |
OPTION_CAPTION | Specify the title of the table |
|
OPTION_HTML | Change to the HTML mode.
When executing 'set EXEC_SQL_OPTION CGI;', it becomes automatically HTML mode. | OFF |
OPTION_TABLETAG | Specify the table tag of HTML TABLE. |
|
OPTION_HEADERTR | Specify the TABLE HEADER tag.(Mainly, this is used, when specifying the background color of all the rows of a HTML table header.)
ex) set OPTION_HEADERTR='
<TR BGCOLOR=#92CDCD>';
|
|
OPTION_HEADERTH |
Specify TH tag of the TABLE header using the comma delimiters. (It can be used when changing the length and color of each rows.)
ex) set OPTION_HEADERTH='
<TH bgcolor=#0088FF>,
<TH nowrap>,
<TH>';
When the number of tags specified by OPTION_HEADERTH does not fulfill the number of rows of the retrieval results, it is considered that the remaining rows are <TH>.
|
|
OPTION_BODYTAG |
Specify TD tag of the TABLE body using the comma delimiters. (It can be used when changing the length and color of each rows.)
ex) set OPTION_BODYTAG='
<TD bgcolor=#0088FF>,
<TD nowrap>,
<TD>';
When the number of tags specified by OPTION_BODYTAG does not fulfill the number of rows of the retrieval results, it is considered that the remaining rows are <TD>.
|
|
OPTION_INPUTTAG |
Display the retrieval result changed to the updatable field form.
See example-2.
| OFF | |
OPTION_INPUTSIZE |
When OPTION_INPUTTAG is ON, specify the length of each rows.
ex) set option_inputsize='-1,32,48,0';
When the length of row is 0, it is determined automatically. When the length of row is -1, this means that it can not be inputted in the field.
When the number of tags specified by OPTION_INPUTSIZE does not fulfill the number of rows of the retrieval results, it is considered that the remaining rows are 0.
| | |
(example-1)
set OPTION_CAPTION='<P align=left><FONT SIZE=5><B>Member list</B></FONT></P>';
set OPTION_TABLETAG='<table bgcolor=#D1EEEE border=4 cellspacing=0 cellpadding=2>';
set OPTION_HEADERTR='<tr bgcolor=#96CDCD>';
set OPTION_HEADERTH='<TH bgcolor=#0088FF nowrap>,<TH nowrap>,<TH nowrap>';
set OPTION_BODYTAG='<TD bgcolor=#0088FF nowrap>,<TD nowrap>,<TD nowrap>';
select * from member where userid>0 order by userid;
Member list
userid | name | email | Tel |
1220 | SUZUKI Ichiro | XXXYYY62@nify.ne.jp | 0777-66-xxxx |
1249 | SASAKI daimajin | XXXYYYc@email.com | 090-xxxx-yyyy |
(2 rows)
(example-2)
set OPTION_CAPTION='<P align=left><FONT SIZE=5><B>Member list</B></FONT></P>';
set OPTION_TABLETAG='<table border=1 bgcolor=#D1EEEE cellspacing=1 cellpadding=2>';
set OPTION_HEADER=OFF;
set OPTION_BOTTOM=OFF;
set OPTION_EXPANDED=ON;
set OPTION_INPUTTAG=ON;
set OPTION_INPUTSIZE='-1, 32, 48, 0';
select * from member where userid=1258;
The temporary options of exec_sql command
The exec_sql command is used, in order to specify the temporary options and to perform SQL.
exec_sql option "SQL"
option | contents | compare with the options of SET statement |
-e | Display the query sent to the backend. | set OPTION_ECHO=ON |
-q | Don't display error messages. | set OPTION_QUIET=ON |
-T | Display the table headers.
| set OPTION_HEADER=OFF |
-B | Display the table bottom
| set OPTION_BOTTOM=OFF |
-A | Don't align the rows of table. | set OPTION_ALIGNMENT=OFF |
-L | Display the outer frame of the table | set OPTION_FRAME=ON |
-X | Change to the expanded output mode | set OPTION_EXPANDED=ON |
-S sep | Specify the delimiter of the table | set OPTION_SEPARATOR='sep' |
-N null | Specify the NULL value | set OPTION_NULLSTRING='null' |
-Z zero | Specify the all bit off value | set OPTION_ZEROSTRING='zero' |
-C cap | Specify the title of the table | set OPTION_CAPTION='cap' |
-H | Change to the HTML mode | set OPTION_HTML=ON |
(example-3) Output the retrieval results to the CSV file.
exec_sql -S ',' -TBA "select * from test where code >= 111"
111,222,333
44,5555,66666
5555,88,9999
|
|