Using Perl and MySQL

I have written Perl code to access a MySQL database.  I am changing the code to cache previously loaded web pages into a 4k text buffer in the database.  The internet response is loaded into a variable called $response as follows:
  $req=HTTP::Request->new("GET", $url);
  $req->content_type('application/x-www-form-urlencoded');
  $response=$ua->simple_request($req);

To save the information the following is done:
  $respstr = $response->as_string;
  $respstr1 = substr($respstr, 0, 4000);
  $cachepage = $respstr1;
  $sql = "update links set cachepage='$cachepage' where id=$id";
  $tda = $dbh->prepare($sql) or die "Error preparing: ", $dbh->errstr;
  $tda->execute or die "Error executing: ", $dbh->errstr;

The variable $cachepage is a text type in the MySQL database.  

When the execute is attempted, no response is returned and I am sure it fails since the database is not written to.
Do I need to set a length work or something to get this to work?  Thanks for any help.
LVL 2
mzehnerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
ddaConnect With a Mentor Commented:
I bet you have single or souble quotes in yout text being inserted into database. You can mask them with '\' or, better way, use placeholders and parameters to execute():

$sql = "update links set cachepage=? where id=?";
$tda = $dbh->prepare($sql) or die "Error preparing: ", $dbh->errstr;
$tda->execute($cachepage, $id) or die "Error executing: ", $dbh->errstr;
0
 
bluprintCommented:
$tda->execute or die "Error executing: ", $dbh->errstr;
$dbh->commit; ## <- you need to commit the update.
0
 
mzehnerAuthor Commented:
It still does not work even with $dbh->commit;.  There is no error message, but unless the line:
$tda->execute or die "Error executing: ", $dbh->errstr;
is commented out the web page will not return output.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Now
 
bluprintCommented:
Silly question...but are you connecting to the DB first?

You also should escape the single quotes

$sql = "update links set cachepage=\'$cachepage\' where id=$id";

I'm pretty sure this is the case...I did a project with MySQL once so this is from memory, also I don't have immediate access to some tools such as perl... In any case, it won't hurt....

One other thing, you shouldn't need to prepare that update statement. Just do:

$sql = "update links set cachepage=\'$cachepage\' where id=$id";
$dbh->do($sql);
if($DBI::err != 0) {
  print "error here!\n";
  print $dbh->errstr;
}
$dbh->commit;

Maybe something in there will move you in the right direction...
0
 
ddaCommented:
Also add this to your script:

use CGI::Carp qw(fatalsToBrowser);

It will cause 'die' messages to go to your browser.
0
 
mzehnerAuthor Commented:
bluprint:
I am connecting to the database first since I am performing successful queries prior to the code I posted above.

dda:
I tried your suggestion.  It worked with an interesting error.  I stated that I have an error in my SQL syntax near 'Gave information here'.
The interesting thing about the information is that it is part of the string that I am writing to the database.  This is why I suspect that it is a possible problem with the syntax of the variable being written.  I have not used blob or text types (beyond 255 characters) with MySQL before.

I removed:
$respstr = $response->as_string;
and the program worked responded with the text output I placed at the end of it.  It appeared to work, but I'm not sure the datbase contains the web page contents.

Therefore part of the problem was that I was converting it to a string before saving to the database.  I'm not sure why this caused a problem.  I still need to investigate to tell if the data was wriiten to the database.  It does not appear in MySQL admin.
0
 
mzehnerAuthor Commented:
Thanks, your comments helped me debug and solve the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.