#!/usr/local/bin/perl
# ------------------------------------------------------------------
# example using DB2 from a CGI perl script
# using 2 simple functions: do_sql() and do_query()
# ------------------------------------------------------------------
use DBI;
$ENV{DB2DIR} = '/usr/IBMdb2/V7.1';
$ENV{DB2INSTANCE} = 'db2inst1';
$ENV{INSTHOME} = '/home/db2inst1';
my $sql_flag=0;
my $script_name = (split(/\//,$0))[-1];
my $self_cgi = "$script_name";
use CGI;
my $q = new CGI;
my $sql = $q->param("sql") || "select * from test1";
my $qf = $q->param("qf")|| 0;
print $q->header("text/html"), $q->start_html("Welcome");
my $database = "mydb";
my $dbh = DBI->connect("dbi:DB2:$database","myinst","mypassword")
or die2www("Cannot log into $database\n");
&db2_form_example();
&myexit();
# ------------------------------------------------------------------
# end of the main part here
# ------------------------------------------------------------------
sub nn {"\n"};
sub br {"
"};
sub die2www {my $s=shift; print $q->p($s),$q->end_html;exit();}
sub myexit{ $dbh->disconnect if $dbh; print $q->end_html; exit(); }
# ------------------------------------------------------------------
# debug_sql($sql)
# ------------------------------------------------------------------
sub debug_sql {
my $sql = shift;
print $sql if $sql_flag;
}
# ------------------------------------------------------------------
# do_sql($dbh,$sql)
# ------------------------------------------------------------------
sub do_sql {
my ($dbh, $sql) = @_;
debug_sql "\n$sql\n";
my $rows = $dbh->do($sql)
or die2www "Error running this SQL: \n$sql\nError: $DBI::errstr\n";
return 0+$rows;
}
# ------------------------------------------------------------------
# do_query($dbh,$sql)
# ------------------------------------------------------------------
sub do_query {
my ($dbh, $sql) = @_;
debug_sql "\n$sql\n";
my $sth = $dbh->prepare($sql)
or die2www "Can't prepare SQL statement: $DBI::errstr\n";
$sth->execute
or die2www "Can't execute SQL statement: $DBI::errstr\n";
my $result_ref = [];
$result_ref = $sth->fetchall_arrayref({});
$sth->finish();
return $result_ref;
}
# ------------------------------------------------------------------
# db2_form_example()
# ------------------------------------------------------------------
sub db2_form_example {
print
$q->start_form(-name=>'form1', -method=>'POST', -action=>$self_cgi),
$q->p("Enter select SQL statement:"),
$q->radio_group( -name=>"qf", -values=>["query","do"], -default=>"query",
-labels=>{ query => "Query", do => "Just run sql" } ),br,nn,
$q->textarea(-name=>"sql", -rows=>'10', -columns=>'60',
-default=>"$sql\n"),nn,br,
$q->submit, $q->reset,nn, br,nn;
my $result;
if ($qf eq 'query') {
$result = do_query($dbh,$sql);
for my $row (@$result) {
for my $k (sort keys %$row) {
printf("
%-30s %-30s\n", $k, $row->{"$k"});
}
}
print br . ('-' x 77) . nn;
} else {
do_sql($dbh,$sql);
}
}