CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
How to view query being sent via DBI module

 



douglazb
Novice

Dec 18, 2009, 10:12 AM

Post #1 of 3 (3951 views)
How to view query being sent via DBI module Can't Post

I want to delete all records that were added 180 days ago or earlier.

My query deletes all dates, not just dates younger then 6-months ago.

How can I view the query that is being sent?

I.e. print "$query_handle->execute($source , $year+1900,$mon-5,$mday,$hour,$min,$sec)\n"; does not work.


Code
	# Insert article into rolling180days table 
$query = "INSERT INTO `dev`.`rolling180days` (`name`, `article`, `source`, `url`, `dateadded`) VALUES ( ?, ?, ?, ?, NOW( ) )";
$query_handle = $connect->prepare($query);
$query_handle->execute( $person , $stringexpanding , $source , $url);
# Delete records older then 180 days from rolling180days table
$query = "DELETE FROM `dev`.`rolling180days` WHERE `rolling180days`.`source` = ? AND
`rolling180days`.`dateadded` < ? ? ? ? ? ?";
$query_handle = $connect->prepare($query);
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
$query_handle->execute($source , $year+1900,$mon-5,$mday,$hour,$min,$sec);



douglazb
Novice

Dec 18, 2009, 1:05 PM

Post #2 of 3 (3935 views)
Re: [douglazb] How to view query being sent via DBI module [In reply to] Can't Post

Here is your solution:

Code
	# Delete records older then 180 days from rolling180days table 
$query = "DELETE FROM `dev`.`rolling180days` WHERE `rolling180days`.`source` = ? AND
`rolling180days`.`dateadded` < ?";
$query_handle = $connect->prepare($query);
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
my $stamp = sprintf("%4d-%02d-%02d %02d:%02d:%02d", $year+1900, $mon+1, $mday, $hour, $min, $sec);
$query_handle->execute($source , $stamp);



FishMonger
Veteran / Moderator

Dec 18, 2009, 3:41 PM

Post #3 of 3 (3927 views)
Re: [douglazb] How to view query being sent via DBI module [In reply to] Can't Post

And a cleaner more readable way to write that is:

Code
use POSIX qw/strftime/; 

my $query = "DELETE FROM dev.rolling180days
WHERE rolling180days.source = ?
AND rolling180days.dateadded < ?";

# I'm making the assumption that you enabled RaiseError when creating the db object
my $sth = $dbh->prepare($query);

$sth->execute($source, strftime("%Y-%m-%d %H:%M:%S", localtime) );


 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives