Solved

CGI and DBI "cache' problem?

Posted on 2002-05-12
8
236 Views
Last Modified: 2010-03-05
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);
}
=============
0
Comment
Question by:slok
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 10

Expert Comment

by:oleber
ID: 7008575


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


}
0
 
LVL 3

Author Comment

by:slok
ID: 7010422
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.

0
 
LVL 10

Expert Comment

by:oleber
ID: 7010546
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?


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:slok
ID: 7010745
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.
0
 
LVL 10

Expert Comment

by:oleber
ID: 7010837
what is your server? are you using mod-perl, mason, ... or something like that?
0
 
LVL 3

Author Comment

by:slok
ID: 7010890
I'm running Apache 1.3.19 with mod_perl on win2k.
Perl used is ActivePerl.
0
 
LVL 10

Accepted Solution

by:
oleber earned 100 total points
ID: 7010926
try the original code but set a '{' at the beginning of your code and a '}' at the end of your code.

I had that some problem when I was working with mod-perl.

Sorry I forget that problem.


Take care with the values that come from the form. You can have serios problems.

Think in having the s_off_no = "1; delete offence"
cann't this do bad things to your database?


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);
}
0
 
LVL 3

Author Comment

by:slok
ID: 7011054
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question