- 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;
|
|