We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
Using Perl and MySQL
Posted on 2002-06-03
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('applic
$response=$ua->simple_requ
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.
$req=HTTP::Request->new("GET", $url);
$req->content_type('applic
$response=$ua->simple_requ
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.
[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
-
3
2-
2
7 Comments
$tda->execute or die "Error executing: ", $dbh->errstr;
$dbh->commit; ## <- you need to commit the update.
$dbh->commit; ## <- you need to commit the update.
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.
$tda->execute or die "Error executing: ", $dbh->errstr;
is commented out the web page will not return output.
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...
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...
Also add this to your script:
use CGI::Carp qw(fatalsToBrowser);
It will cause 'die' messages to go to your browser.
use CGI::Carp qw(fatalsToBrowser);
It will cause 'die' messages to go to your browser.
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.
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.
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;
$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;
Featured Post
![[Webinar] Lessons on Recovering from Petya](https://filedb.experts-exchange.com/files/public/2017/7/11/dcb3272a-62a8-4274-a34e-d1771610ec3b.png)
Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.
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…
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…
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…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll
618 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.