[Prev] [Menu] [Next]

How to use CGI (Web application)


  • Setting of web server software.

    The case of using Pgbash as CGI, LD_LIBRARY_PATH environment variable is necessary to specify the library path name of PostrgeSQL. In Apache web server , it is specified near the head of "httpd.conf" as follows.

    SetEnv LD_LIBRARY_PATH /usr/local/pgsql/lib

  • How to describe a shell script

    The next commands have to be specifyed at the top of a shell script.

    #!/usr/local/bin/pgbash
    exec 2>&1 ............ (1)
    echo "Content-type: text/html" ........................ (2)
    echo ""
    set EXEC_SQL_OPTION CGI; ......(3)

    (1) exec 2>&1
    'exec 2>1' changes the standard error output to the standard output. Although this is not indispensable, it can display the 'Bash' error message on a homepage.

    (2) Content-type
    'echo "Content-type: text/html"' and 'echo ""' are required for CGI.

    (3) set EXEC_SQL_OPTION CGI;
    'set EXEC_SQL_OPTION CGI;' sets the data, which is inputted on a homepage, into the shell variable. And it changes the output mode to the HTML output mode.

    'GET/POST' method of HTML FORM statement can be used. In addition, if cookie is used, cookie values are registered to the following shell variables.
      $HTTP_NCOOKIE        : Number of cookie.
      ${HTTP_COOKIEKEY[i]} : key name of cookie.
      ${HTTP_COOKIEVAL[i]} : value of cookie.
     (i = 0 -- HTTP_NCOOKIE-1 )
     
  • How to make a shell script.

    For example, it is assumed that a next HTML file is on a web server and that 'passwd', 'MYNAME' and 'ADDRESS' is inputted.

    <HTML>
    <FORM METHOD=POST ACTION="test.sh">
    <INPUT TYPE=password NAME=passwd >
    <INPUT TYPE=text NAME="MYNAME" VALUE="sakaida">
    <INPUT TYPE=text NAME="ADDRESS" VALUE="Osaka">
    <INPUT TYPE=submit VALUE=submit>
    </HTML>

    This case, three data which inputted from a homepage are registered as $passwd, $MYNAME and $ADDRESS by using 'set EXEC_SQL_OPTION CGI;'.

    #!/usr/local/bin/pgbash
    exec 2>&1
    echo "Content-type: text/html"
    echo ""
    set EXEC_SQL_OPTION CGI;
    #
    connect to dbname as db1 user nopbody $passwd;
    insert into test values( '$MYNAME','$ADDRESS');
    select * from test order by uname limit 100;

    Pgbash can not use the multibyte name for the shell variable.

  • How to use EXEC_SQL_PREPARE

    By using EXEC_SQL_PREPARE shell variable, the data inputted from the homepage is compoundable.

    (1) Example-1
    The next case, EXEC_SQL_PREPARE shell variable is filled with 'insert into test values('SAKAIDA','KOBE');'.

    <HTML>
    <FORM METHOD=POST ACTION="test.sh">
    <INPUT TYPE=password NAME=passwd >
    <INPUT TYPE=text NAME="MYNAME" VALUE="SAKAIDA">
    <INPUT TYPE=text NAME="MYADDR" VALUE="KOBE">

    <INPUT TYPE=hidden NAME="EXEC_SQL_PREPARE"
       VALUE="insert into test('FMYNAME','FMYADDR');">

    <INPUT TYPE=submit VALUE=submit>
    </HTML>

    This case, in HTML INPUT tag, TYPE has to be 'hidden' and NAME has to be EXEC_SQL_PREPARE at the head position.(For example, EXEC_SQL_PREPARE or EXEC_SQL_PREPARE_insert.) And SQL is specifyed at the VALUE of a HTML INPUT tag. SQL string can include 'colon + name'.

    #!/usr/local/bin/pgbash
    exec 2>&1
    echo "Content-type: text/html"
    echo ""
    set EXEC_SQL_OPTION CGI;
    #
    connect to dbname as db1 user nopbody $passwd;
    exec_sql "$EXEC_SQL_PREPARE"


    (2) Example-2
    The next case, EXEC_SQL_PREPARE_where shell variable is filled with "uname='SAKAIDA', addr='KOBE'".

    <HTML>
    <FORM METHOD=POST ACTION="test.sh">
    <INPUT TYPE=password NAME=passwd >
    <INPUT TYPE=text NAME="MYNAME" VALUE="SAKAIDA">
    <INPUT TYPE=text NAME="MYADDR" VALUE="KOBE">

    <INPUT TYPE=hidden NAME="EXEC_SQL_PREPARE_where"
       VALUE="uname='FMYNAME' and addr='FMYADDR');">

    <INPUT TYPE=submit VALUE=submit>
    </HTML>

    #!/usr/local/bin/pgbash
    exec 2>&1
    echo "Content-type: text/html"
    echo ""
    set EXEC_SQL_OPTION CGI;
    #
    connect to dbname as db1 user nopbody $passwd;
    select * from test where $EXEC_SQL_PREPARE_where;


[Prev] [Menu] [Next]