Informix Insert Command in CGI
As you can tell from the subject, this question is very unique. I have
written a cgi script in PERL that takes data from a form. It then creates
an Informix 'INSERT INTO' SQL statement and writes it to disk. Then the
script executes the SQL statement and finishes by removing the script.
Unfortunately it does not work. The script fails to actually execute the
SQL statement. The error log gives the following message:
'isql command' failed at /home1/httpd_1.5.2a/cgi-bin/berkdata.cgi line 101.
What is interesting (and frustrating) is that the SQL statement works when
I, or root, or nobody executes it from the command line. The script has
nobody as owner and other as group. The owner does have read, write, and
execute privileges.
The SQL statements look like this:
BEGIN WORK
insert into berkdata (case_num,empl,term_wk,week_13,emp_13,same,wk_dol,
hr_dol,wk_wrkd,outcome,int_wk)
VALUES
("5372756","1","01/04/98","04/05/98","1","1","480","12.00","13","ok",
"04/26/98");
COMMIT WORK
The CGI script is listed below. If you have any ideas as to what is going
wrong with this script, I would greatly appreciate any help.
Thank you for your time,
Elton A. Hughes
IT Specialist
NOVA PIC/City of Sunnyvale
505 W. Olive Ave., Suite 600
Sunnyvale, CA 94086
Phone - (408) 730-7235
Fax - (408) 730-7643
----------
#!/usr/bin/perl -w
# Description: berkdata.cgi takes information from berkdata.html.
# The data is checked and cleaned and then used in an on-the-fly
# sql statement.
################################################################
# Main program routines #
################################################################
require "libraries/cgi-lib.pl"; # Require libararies
&init_variables; # Init some variables
&ReadParse(*form_data); # Parse the incoming data
print "Content-type: text/html", "\\n\\n"; # Print the cgi header stuff
&print_data; # Print out the form info
&create_string; # Create the string for adding the data
&print_string; # Print out the string
&execute_string; # Run the isql command
&remove_string; # Remove the sql string from the directory
################################################################
# Place subroutines here #
################################################################
##########
sub init_variables {
$ENV{"PATH"} = ".:/usr/informix:/usr/informix/lib/sqlturbo:/usr/informix/bin";
$path = $ENV{"PATH"};
$ENV{"PATH"} = $path.":/usr/bin";
$ENV{"INFORMIXSERVER"} = "on_sun";
$ENV{"INFORMIXDIR"} = "/home4/informix";
$ENV{"INFORMIXSQLHOSTS"} = "/home4/informix/etc/sqlhosts";
$ENV{"SQLEXEC"} = "/home4/informix/lib/sqlturbo";
@field_list = ('case_num', 'empl', 'term_wk', 'week_13',
'emp_13', 'same', 'wk_dol', 'hr_dol', 'wk_wrkd',
'outcome', 'int_wk');
$add_string = "insert into berkdata (";
$ts = time;
}
##########
sub print_data {
print "<html>", "\\n";
print "<head>", "\\n";
print "<title>UCB Follow-up Data", "\\n";
print "</title>", "\\n";
print "<body>", "\\n";
print "<table border=1 cellpadding=4 cellspacing=4>", "\\n";
foreach $field_name (@field_list) {
print "<tr><th>$field_name</th><td>$form_data{$field_name}
</td></tr>", "\\n";
}
print "</table>", "\\n";
print "</body>", "\\n";
print "</html>", "\\n";
}
##########
sub create_string {
$add_string = "BEGIN WORK " . "\\n" . $add_string;
$add_string = $add_string . (join ',', @field_list);
$add_string = $add_string . ")\\n";
$add_string = $add_string . "\\tVALUES\\n(";
foreach $i (@field_list) {
$mod_data = '"' . $form_data{$i} . '"';
$second_string[++$#second_string] = $mod_data;
}
$third_string = join ',', @second_string;
$add_string = $add_string . $third_string;
$add_string = $add_string . ");\\n";
$add_string = $add_string . "COMMIT WORK \\n";
}
##########
sub print_string {
open (SQLFILE, ">$ts.sql") || die "Cannot create $ts.sql";
print SQLFILE $add_string;
chmod 0755, "$ts.sql";
}
##########
sub execute_string {
system("isql -s snov -qr $ts") || die "'isql command' failed $!";
}
##########
sub remove_string {
# unlink "/home1/httpd_1.5.2a/cgi-bin/$ts.sql";
}
##########