?
Solved

CGI and DBI "cache' problem?

Posted on 2002-05-12
8
Medium Priority
?
245 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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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…
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
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
Suggested Courses

600 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