[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]