Gossamer Forum
Home : General : Perl Programming :

MySQL Db updates from a pm fail to commit

Quote Reply
MySQL Db updates from a pm fail to commit
Greetings,

I have a Perl module using CGI::Application and CGI::Session.

I have built it so the sessions go against MySQL since there are other Db activities for it to carry out also.

In the very first call to the module I check for an existing session (which is a cookie if it exists already) and is it doesn't exist I create the session and update the session record with some extra info:

-------- Code ------------

# get the session, or create a new one
my $ipadr = $ENV{REMOTE_ADDR};
if ($sid eq undef) {
$session = new CGI::Session("driver:MySQL", $q, {Handle => $dbh});
$session->flush;
$sid = $session->id;


# Update session with originating IP address
my $statement = qq[Update sessions set SessionStart = Now(), SessionIp="$ipadr" where Id="$sid"];
my $rc = $dbh->do($statement)
or die "Couldn't execute update statement: ", DBI->errstr();

}
else {
# get the existing session
$session = new CGI::Session("driver:MySQL", $sid, {Handle => $dbh});
$sid = $session->id;
}
-------End Code ----------

The session is created OK, but the update statement isn't reflected in the table although there is no failure registered by dbh.

Now if I execute the exact same update code:

---------- Code ----------
# Update session with originating IP address
my $statement = qq[Update sessions set SessionStart = Now(), SessionIp="$ipadr" where Id="$sid"];
my $rc = $dbh->do($statement)
or die "Couldn't execute update statement: ", DBI->errstr();
--------- End Code -------

from an external .pl script opening in a separate window the update succeeds.

Also, if I do an insert into a different table (a shopping cart) during my response from the pm, it also fails to show up in the table data although no error registers!

------ Code ------------
# Verify the product exists
$sth = $dbh->prepare(qq{select id, productcode from products where id = $pid})
or die "Couldn't prepare product check statement: ", DBI->errstr();;
$sth->execute() or die "Couldn't execute session check command: ", DBI->errstr();;

if ($sth->rows > 0) {
my $strSQL = "Insert into ShoppingCart (SessionId, ProductId, UnitPrice) values ('" . $sid .
"', " . $pid . ", " . $pcost . ")";
my @data = $sth->fetchrow_array();

# Add the item
$rc = $dbh->do(qq[$strSQL])
or die "Couldn't execute Shopping Cart insert statement: ", DBI->errstr();

# Update products in the database
$rc = $dbh->do(qq{Update products set quantity=(quantity-1) where Id = $pid})
or die "Couldn't execute tag product statement: ", DBI->errstr();

}

------ End Code -----------------

Obviously I am relatively new to Perl so what am I confusing about Db access from my pm script versus a separate pl script?

Appreciate any help!
Cheers, Mate!
Subject Author Views Date
Post MySQL Db updates from a pm fail to commit pcrosley 3155 Nov 13, 2005, 9:18 AM