?
Solved

CGI and DBI "cache' problem?

Posted on 2002-05-12
8
Medium Priority
?
241 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
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

650 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