Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Re: Deleting Old MySql Records With PERL

by neilwatson (Priest)
on Jul 13, 2016 at 19:46 UTC ( #1167727=note: print w/replies, xml ) Need Help??


in reply to Deleting Old MySql Records With PERL

One. that's an SQL question not a perl question. Two, please provide actual error messages and repeatable code.

Neil Watson
watson-wilson.ca

  • Comment on Re: Deleting Old MySql Records With PERL

Replies are listed 'Best First'.
Re^2: Deleting Old MySql Records With PERL
by Milti (Sexton) on Jul 13, 2016 at 21:00 UTC

    Here's some actual code from the cgi.

    #/usr/bin/perl -w use DBI; use CGI ':standard'; print "Content-type: text/html\n\n"; my $dbh = DBI->connect('dbi:mysql:my_db','searcher','searcherpasswd') or die "Connection Error: $DBI::errstr\n"; ##DELETE FROM my table WHERE DATE(NOW(), date) >30; DELETE FROM my table where now()-d >interval '30days';

    Here are some error messages. The first is when using the 'Commented Out' piece of code. The others are when using the other piece of code.

    Undefined subroutine &main::NOW called at e:\owner\website \cgi-bin\se +arch_ specialty.pl line 13 [Wed Jul 13 15:26:07 2016] [error] [client 72.208.178.63] String found + where operator expected at e:\owner\website \cgi-bin\search_ special +ty.pl line 14, near "interval '30days'" [Wed Jul 13 15:26:07 2016] [error] [client xx.xxx.xxx.xx] (Do you +need to predeclare interval?) Syntax error at e:\owner\website \cgi-bin\search_ specialty.pl line 14 +, near ")-d " Execution of e:\pinnacle\website\cgi-bin\search_specialty.pl aborted d +ue to compilation errors.

    According to advise from several tech sites the code should work. But, it doesn't.

      In addition to what perlfan said about SQL syntax, you also have a problem with perl syntax.

      The DELETE statement is SQL, not perl. As such, it needs to be quoted, and passed to $dbh->do("SQL here");

      Be careful to properly escape quotes, when passing SQL to do().

              There is no time like the present for postponing what you ought to be doing.

        I'm now using this code. Still getting an error message.

        use DBI; use CGI ':standard'; print "Content-type: text/html\n\n"; my $dbh = DBI->connect('dbi:mysql:my_db','searcher','searcherpasswd') or die "Connection Error: $DBI::errstr\n"; $sth = $dbh->prepare ("DELETE FROM my table where DATE <UNIX_TIMESTAMP +(DATE_SUB(NOW()-INTERVAL 30 DAY)"); $sth->execute ();

        Error message ---- DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 at e:\owner\cgi-bin\search_specialty.pl line 14.

        Line 14 is $sth->execute ();

      Your code doesn't pass any queries to the database to be executed.

      It's unnecessarily difficult to read that post as it stands. Please edit it and enclose both the code and the error messages in <code> tags. Thank you.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1167727]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (7)
As of 2019-04-18 17:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I am most likely to install a new module from CPAN if:
















    Results (103 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!