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

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 (3346 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 (3331 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

Dec 18, 2009, 3:41 PM

Post #3 of 3 (3323 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)

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


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN