[Prev] [Menu] [Next]
Options for executing SQL

  • 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“ā—eDEFAULT
    value
    OPTION_ECHODisplay the query sent to the backend.OFF
    OPTION_QUIETDon't display error messages.OFF
    (display)
    OPTION_HEADERDisplay 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_BOTTOMDisplay the table bottom(rows counter).
    ex) set option_bottom=off;
    select * from test;

    code|name   |addr
    ----+-------+-----
     101|tanaka |tokyo
     111|yoshida|fukui
    		ON
    OPTION_ALIGNMENTAlign the rows of table.

    Set OFF when outputting CSV form.
    See 'OPTION_SEPARATOR'.     		ON
    OPTION_FRAMEDisplay 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_EXPANDEDChange to the expanded output mode
    ex) set option_expanded=on;
    select * from test;

    ----- RECORD 0 -----
    codeb101   
    namebtanaka
    addrbtokyo
    ----- RECORD 1 -----
    codeb111
    namebyoshida
    addrbfukui
    		OFF
    OPTION_SEPARATORSpecify 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_CAPTIONSpecify the title of the table
    OPTION_HTMLChange to the HTML mode.

    When executing 'set EXEC_SQL_OPTION CGI;', it becomes automatically HTML mode.    		OFF
    OPTION_TABLETAGSpecify the table tag of HTML TABLE.
    OPTION_HEADERTRSpecify 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
    useridnameemailTel
    1220SUZUKI IchiroXXXYYY62@nify.ne.jp0777-66-xxxx
    1249SASAKI daimajinXXXYYYc@email.com090-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;

    Member list
    userid1258
    name
    email
    Tel



  • 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 contentscompare with the options of SET statement
      -eDisplay the query sent to the backend.set OPTION_ECHO=ON
      -qDon't display error messages. set OPTION_QUIET=ON
      -TDisplay the table headers.
      		set OPTION_HEADER=OFF
      -BDisplay the table bottom
      		set OPTION_BOTTOM=OFF
      -ADon't align the rows of table.set OPTION_ALIGNMENT=OFF
      -LDisplay the outer frame of the tableset OPTION_FRAME=ON
      -XChange to the expanded output modeset OPTION_EXPANDED=ON
      -S sepSpecify the delimiter of the tableset OPTION_SEPARATOR='sep'
      -N nullSpecify the NULL valueset OPTION_NULLSTRING='null'
      -Z zeroSpecify the all bit off valueset OPTION_ZEROSTRING='zero'
      -C capSpecify the title of the tableset OPTION_CAPTION='cap'
      -HChange 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
    [Prev] [Menu] [Next]