view release on metacpan or  search on metacpan

lib/MySQL/RunSQL.pm  view on Meta::CPAN

	FROM emloyeeDB
	WHERE start_date like "%2002%"
SQL
	
	# Run the report & write out to excel
	my $report = $rs->runsql_report->($sql);
	
	# Print the file location
	print "\n\tYour report is ready: $report\n\n";
	
	exit 0;

=head1 METHODS

=head2 MySQL::RunSQL->new(
									database => $db,
									my_cnf => $mycnf,
									group_name => $mygroup,
									host => $mysqlhost,
									port => $mysqlport
								);

Returns a MySQL::RunSQL object. The new method is invoked with the following
options:

	host		=> mysql_server
	database	=> database	 
	port		=> mysql port	default=3306
	my_cnf		=> mysql my.cnf file with both user & password set
	group_name	=> under which [group] in the my.cnf file can the 
				login information be found
					
	host, database, my_cnf, & group_name are all required.
	
=cut


sub new {
	my $class = shift;
	
	#Verify parameters
	my %args = validate (
		@_,
		{
			# all parameters are required, accept port which defaults to 3306
			database => 1,
			group_name => 1,
			my_cnf	=>	1,
			host	=>	1,
			port	=> { default => 3306 }
		}
	);
	
	
	my $self = {};

	my %dbparams = ( RaiseError => 1, PrintError => 1 );
	
	my $dsn = "DBI:mysql:database=$args{'database'};host=$args{'host'};"
			. "port=$args{'port'};mysql_read_default_group=$args{'group_name'};"
			. "mysql_read_default_file=$args{'my_cnf'};mysql_compression=1";
			
	my $user;
	my $password;
	
	#Connect to the server and share the db handle with the rest of the package	
	#my $dbh = DBI->connect($dsn, $user, $password, { %dbparams } );
	my	$dbh = DBI->connect($dsn, $user, $password, \%dbparams  );
		die $DBI::errstr unless $dbh;
	
	# Share the db handle with the rest of our package	
	$self->{'dbh'} = $dbh;
	
	
	return bless ($self, $class);	
}

=head2 MySQL::RunSQL->runsql("SELECT STATEMENT")

Takes a sql query and prints to STDOUT a comma delimited list of rows returned

=cut

sub runsql {
	my ($self, $sql) = @_;
	
	my $sth = $self->{'dbh'}->prepare($sql) or die $self->{'dbh'}->errstr;
	$sth->execute() or die $sth->errstr;
	
	while ( my @data = $sth->fetchrow_array )
	{
		say join(",", @data);
	}
	
	$sth->finish;
	
	return $self;
}


=head2 MySQL::RunSQL->runsql_fromfile($sqlfile);

Takes a file containing a sql query and prints to STDOUT a comma delimited
list of rows returned.
	
	
=cut

sub runsql_fromfile {
	my ($self, $file) = @_;
	
	my $sql;

	# Slurp file contents into $sql	
	eval {
		use autodie;
		
		local ( $/, *FH );
		open FH, "<", $file;
		$sql = <FH>;
		close FH;