Perl

SQL Insertion

#!/usr/bin/perl
use DBI;
use DBD::mysql;
my $dbh_MySQL = DBI->connect("DBI:mysql:dbname=$DBNAME;host=$DBHOST", "$DBUSER", "$DBPASS", {'RaiseError' => 1});

&MySQLQuery("$YourQueryStringHere");

sub MySQLQuery($) {
my $query = shift;
print "$query\n";
$dbh_MySQL->do($query);
}

SQL Query

#!/usr/bin/perl
use DBI;
use DBD::mysql;
my $dbh_MySQL = DBI->connect("DBI:mysql:dbname=$DBNAME;host=$DBHOST", "$DBUSER", "$DBPASS", {'RaiseError' => 1});

my $SQLQuery = "$YourSelectStatement";

my  $sth_output = $dbh_MySQL->prepare($SQLQuery);
$sth_output->execute();
           while (my $ref = $sth_output->fetchrow_hashref()) {
               $variables = "$ref->{'SelectedObjects'}";
           }

Read a File

open FILEHANDLE, "<", "Path-to-File" or die $!; 
      while (FILEHANDLE) {
               # Do some work;
      }
close FILEHANDLE;

OR

open FILEHANDLE, "<", "Path-to-File" or die $!; 
      while ($VariableData = <FILEHANDLE>) {
               # Do some work with $VariableData;
      }
close FILEHANDLE;

Write to a File

open (FILEHANDLE, "+>", "$PathToFile") || die $!;
# Optional Loop or other conditionals
print FILEHANDLE $VariableData;
close FILEHANDLE

Open & read from one file, alter, and write to a new file

Note: There's better ways to do this now.

 open (EXPORT, "+>", "target.sql") || die "No Ouput";
 open (IMPORT, "source.sql") || die "Cant open source database";
 while ($Process=<IMPORT>)
  {
    $Process =~ s/match-this/replace-with/g;
    print EXPORT $Process;
  }
 close IMPORT;
 close EXPORT;

Add Logging to your Scripts

Note: It's best to use the syslog facility to make log entries rather than add them directly yourself which may hold the file open, preventing Logrotate from doing it's job and filling your drive with a logfile that looks like it's been deleted but really hasn't. Use of LSOF would be required to find the process holding the file open. Killing that process will be the only way to recover the 'deleted' space.

#!/usr/bin/perl -w
use strict;
use warnings;
use Sys::Syslog qw( :DEFAULT :macros );

&dologging("Message to send to the Logfile with $SomeVariableData");

sub dologging ($) {
   my $logthis=shift;
   openlog($0,,'$YourLogName');
   syslog('info', $logthis);
   closelog;
}

Running System Commands

Some might argue that using a system command in Perl is inappropriate, insecure, or inefficient. Any poorly written code can be insecure, and reinventing the wheel is many times more inefficient than a system call. Why write an entire function to perform a task that's already had an application written and in most cases mature and debugged long before? If the practice is good enough for IBM, then it's good enough for me.

The system call is simple enough in itself, but I made a function out of it. It's a good idea to PRINT the commands before actually executing them, to be certain things will behave as you expect. This is much easier to do if the actual system call is in one place.

For Silent Operation, comment out the print statement, for testing, comment out the system() command. If you don't want to log anything, comment out the &dologging call. Adding logging was detailed in the section above, or 'Add Logging to your Scripts'.

&JustDoIt("Command To Run");

Or

my $Command2Run = ;
$Command2Run='Command To Run';
&JustDoIt($Command2Run);

Or

sub JustDoIt($) {

 my $Command2Run = shift;
 print "\n$Command2Run\n"; # Good for Debugging
 $Rezult = `$Command2Run`; 
 &dologging($Command2Run); 

return $Rezult; }

You can also use:

 $Rezult = `$Command2Run`;
 system $Command2Run;
 exec|eval $Command2Run;

Sending Mail

my @mailrcpt=('user1@domain.com', 'user2@domain.com');
$body .= "Body of Email - Can contain Variables\n\n";
my $smtp;
push(@MAIL, $body);
$smtp = Net::SMTP->new('Domain-of-your-outbound-host.com');
$smtp->mail('user@Domain-of-your-outbound-host.com');
$smtp->recipient(@mailrcpt);
$smtp->data();
$smtp->datasend("Subject: Your Subject\n");
$smtp->datasend("\n");
 foreach my $mailline (@MAIL) {
    $smtp->datasend("$mailline");
 }
$smtp->dataend();
$smtp->quit;

Error Messages

die $! will print the error message generated by the Perl interpreter. To specify your own error message give your message in quotes. Eg:

die $! #  Prints Interpreter Generated Error Message
die "My Custom Error Message" 

Optionally you can pass $! to a logging facility or some other sub.

die &dologging($!)

File Modes

What you can do with files is determined by setting the 'mode' after the file handle is specified. You saw that little '+>' above in the write file example right?

mode	     operand	create	truncate
read	<		
write	>	✓	✓
append	>>	✓	

Each of the above modes can also be prefixed with the + character to allow for simultaneous reading and writing.

mode	     operand	create	truncate
read/write	+<		
read/write	+>	✓	✓
read/append	+>>	✓	

More on file handles can be found on the perlfect solutions website