Solved

CGI and DBI "cache' problem?

Posted on 2002-05-12
8
231 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
  • 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
use perl to insert into MySQL database 9 135
hard perl script 16 156
Perl Sort Question 4 133
Regex rule to match two different url 5 71
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…
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now