slok
asked on
CGI and DBI "cache' problem?
I got the following CGI program which takes in one field 's_off_no' from a query from.
Problem is it only works one time and after that, even though I am submitting different queries values, it is still using the very first value to do the database queries.
any suggestions?
========================== ========== =========
do 'dbi-lib.pl';
use CGI ':standard';
use DBI;
use strict;
use vars qw($baseURL);
# Declare base URL for return from this page
$baseURL = "../index.html";
my $q = new CGI;
my $user = $q->param('user');
my $passwd = $q->param('passwd');
my $action = $q->param('action');
my $s_off_no = $q->param('s_off_no');
printHead('TDPS - Delete Offence');
# check that user is logon
if (($user eq '') || ($passwd eq '')) {
goLogon();
return;
}
print EOM;
table border=0 cellpadding=0 cellspacing=0 width=100%>
tr>
td>
EOM
searchform();
queryresult($s_off_no);
print EOM;
hr>
EOM
print EOM;
/td>
/tr>
/table>
EOM
printclose();
sub searchform {
print EOM;
form action="query.pl" method="post">
input type="hidden" name="user" value="$user">
input type="hidden" name="passwd" value="$passwd">
table border="0" cellpadding="2" cellspacing="2">
tr>
td colspan="2" class="pagetitle">Delete Offence Details/td>
/tr>
tr>
td>Offence Number/td>
td>input type="text" name="s_off_no" size="5" maxlength="5"> input type="submit" value="Search">/td>
/tr>
/table>
/form>
EOM
}
sub queryresult {
#my $s_off_no = $s_off_no;
my ($s_off_no) = @_;
my $debug = 1; # 0 to off, 1 to turn it on
my $results;
my $dbuser = '111';
my $dbpasswd = '222';
# Query to check if driver exist
my $query1 = "select veh_reg_no from offence " .
"where off_no = $s_off_no";
# Database DBI driver name
my $dbname = "DBI:Oracle:";
# Create database handle
my $dbh = DBI->connect($dbname, $dbuser, $dbpasswd);
if (!$dbh) {
showSQLerror("Connecting to the database");
return;
} else {
# set AutoCommit 1 = ON, 0 = OFF (1 is default)
# transaction control is *required* here, hence set OFF
$dbh->{AutoCommit} = 0;
}
if ($debug == 1) {
print("
query1: $query1");
}
$results = run_statement($dbh,$query1 ,"Y");
if ($results == 0) {
printErrors('Invalid Offence No.!');
return;
} elsif ($results == -1) {
$dbh->rollback;
$dbh->disconnect;
return;
} else {
$dbh->commit;
$dbh->disconnect;
print("
$results");
}
}
sub run_statement {
my ($dbh,$sql,$getNo)=@_;
my $new_offNo;
# Create a statement handle - prepare the statement
my $sth = $dbh->prepare($sql);
if (!$sth) {
showSQLerror("Preparing SQL statement");
return (-1);
}
# Execute the statement against the database
$sth->execute;
if ($DBI::errstr) {
showSQLerror("Executing SQL Statment");
$sth->finish;
return (-1);
}
if ($getNo eq "Y") {
($new_offNo) = $sth->fetchrow;
} else {
$new_offNo = 0;
}
$sth->finish;
return ($new_offNo);
}
=============
Problem is it only works one time and after that, even though I am submitting different queries values, it is still using the very first value to do the database queries.
any suggestions?
==========================
do 'dbi-lib.pl';
use CGI ':standard';
use DBI;
use strict;
use vars qw($baseURL);
# Declare base URL for return from this page
$baseURL = "../index.html";
my $q = new CGI;
my $user = $q->param('user');
my $passwd = $q->param('passwd');
my $action = $q->param('action');
my $s_off_no = $q->param('s_off_no');
printHead('TDPS - Delete Offence');
# check that user is logon
if (($user eq '') || ($passwd eq '')) {
goLogon();
return;
}
print EOM;
table border=0 cellpadding=0 cellspacing=0 width=100%>
tr>
td>
EOM
searchform();
queryresult($s_off_no);
print EOM;
hr>
EOM
print EOM;
/td>
/tr>
/table>
EOM
printclose();
sub searchform {
print EOM;
form action="query.pl" method="post">
input type="hidden" name="user" value="$user">
input type="hidden" name="passwd" value="$passwd">
table border="0" cellpadding="2" cellspacing="2">
tr>
td colspan="2" class="pagetitle">Delete Offence Details/td>
/tr>
tr>
td>Offence Number/td>
td>input type="text" name="s_off_no" size="5" maxlength="5"> input type="submit" value="Search">/td>
/tr>
/table>
/form>
EOM
}
sub queryresult {
#my $s_off_no = $s_off_no;
my ($s_off_no) = @_;
my $debug = 1; # 0 to off, 1 to turn it on
my $results;
my $dbuser = '111';
my $dbpasswd = '222';
# Query to check if driver exist
my $query1 = "select veh_reg_no from offence " .
"where off_no = $s_off_no";
# Database DBI driver name
my $dbname = "DBI:Oracle:";
# Create database handle
my $dbh = DBI->connect($dbname, $dbuser, $dbpasswd);
if (!$dbh) {
showSQLerror("Connecting to the database");
return;
} else {
# set AutoCommit 1 = ON, 0 = OFF (1 is default)
# transaction control is *required* here, hence set OFF
$dbh->{AutoCommit} = 0;
}
if ($debug == 1) {
print("
query1: $query1");
}
$results = run_statement($dbh,$query1
if ($results == 0) {
printErrors('Invalid Offence No.!');
return;
} elsif ($results == -1) {
$dbh->rollback;
$dbh->disconnect;
return;
} else {
$dbh->commit;
$dbh->disconnect;
print("
$results");
}
}
sub run_statement {
my ($dbh,$sql,$getNo)=@_;
my $new_offNo;
# Create a statement handle - prepare the statement
my $sth = $dbh->prepare($sql);
if (!$sth) {
showSQLerror("Preparing SQL statement");
return (-1);
}
# Execute the statement against the database
$sth->execute;
if ($DBI::errstr) {
showSQLerror("Executing SQL Statment");
$sth->finish;
return (-1);
}
if ($getNo eq "Y") {
($new_offNo) = $sth->fetchrow;
} else {
$new_offNo = 0;
}
$sth->finish;
return ($new_offNo);
}
=============
ASKER
well, I print out the "stupid_value" everytime I submit the query from the form. And the time is different.
But my database query is still using the first "query" parameter.
But my database query is still using the first "query" parameter.
Probably I cann't help you. I see the code and i didn't understand the why of your problem.
Did you try the GET method in the form?
Did you try the GET method in the form?
ASKER
I manage to solve the problem. though I don't know why.
By moving codes like
==
my $q = new CGI;
my $user = $q->param('user');
my $passwd = $q->param('passwd');
my $action = $q->param('action');
my $s_off_no = $q->param('s_off_no');
==
and any other validation code into subroutine, it solves the problem with 'caching'. Not sure why if these codes exist in the "main", it will have a 'caching' effect.
I will delete this question in a week's time.
Thanks for the help.
By moving codes like
==
my $q = new CGI;
my $user = $q->param('user');
my $passwd = $q->param('passwd');
my $action = $q->param('action');
my $s_off_no = $q->param('s_off_no');
==
and any other validation code into subroutine, it solves the problem with 'caching'. Not sure why if these codes exist in the "main", it will have a 'caching' effect.
I will delete this question in a week's time.
Thanks for the help.
what is your server? are you using mod-perl, mason, ... or something like that?
ASKER
I'm running Apache 1.3.19 with mod_perl on win2k.
Perl used is ActivePerl.
Perl used is ActivePerl.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I probably would not go and test your sample codes.
But the proposed solution makes sense.
Will accept it as others might find it useful.
Thanks a lot for all the help. Definitely a learning experience.
But the proposed solution makes sense.
Will accept it as others might find it useful.
Thanks a lot for all the help. Definitely a learning experience.
sub searchform {
my $time=time();
print EOM;
form action="query.pl" method="post">
input type="hidden" name="user" value="$user">
input type="hidden" name="passwd" value="$passwd">
input type="hidden" name="stupid_value" value="$time">
table border="0" cellpadding="2" cellspacing="2">
tr>
td colspan="2" class="pagetitle">Delete Offence Details/td>
/tr>
tr>
td>Offence Number/td>
td>input type="text" name="s_off_no" size="5" maxlength="5"> input type="submit" value="Search">/td>
/tr>
/table>
/form>
EOM
}