Connecting to MySQL Database through PERL

FAQ: How to connect to MySQL database through PERL.


SOLUTION:
1. Install MySQL database. MySQL community server comes free of cost and is distributed under GNU General Public License.

2. I would instead recommend installing a package called WAMP server which provides MySQL community server, Apache server and PHP. It also provides many effective tools like PHPMyAdmin which can be used for database administration. Also WAMP server comes free of cost and is distributed under GNU General Public License.

3. To check if the MySQL server is running, execute:
telnet 3306
E.G: # telnet 127.0.0.1 3306

4. I would also recommend you to also install SQLYog. It is a very light and effective tool to monitor and manage MySQL database. Again it also comes free of cost and is distributed under GNU General Public License.

5. Create a database in MySQL, a non-root user and give it a password.

6. Install ‘DBI’ perl module through ‘PPM’

7. Install ‘DBD::Mysqlpp” perl module through ‘PPM’

8. You are all set to connect to MySQL database. While connecting make sure that database is already created and you are connecting through a valid user and with correct credentials. To see a sample script execute following on command prompt:
# perldoc DBD::Mysqlpp


REFERENCES:
1. http://dbi.perl.org/
2. # perldoc DBD::Mysqlpp


AUTHOR: Parag Kalra
Email-ID: paragkalra@gmail.com

Using Regular expressions in PERL - Script1

#!/usr/bin/perl
#===============================================================================
#
# FILE: test2.pl
#
# USAGE: ./test2.pl
#
# DESCRIPTION: Weekly Test2: The script has a paragraph defined at it's start.
# The script reads the each word of the paragraph and processes it
# to check following conditions:
#
# 1. has an 'a'
# 2. starts with an 'a'
# 3. has 'th'
# 4. has an 'a' or an 'A'
# 5. has a '*' in it
# 6. starts with an 'a' or an 'A'
# 7. has both 'a' and 'e' in it
# 8. has an 'a' followed by an 'e' somewhere in it
# 9. does not have an 'a'
# 10. does not have an 'a' nor 'e'
# 11. has an 'a' but not 'e'
# 12. has at least 2 consequtive vowels (a,e,i,o,u)
# 13. has at least 3 vowels
# 14. has at least 6 characters
# 15. has at exactly 6 characters
# 16. all the words with either 'aba' or 'ada' in them
# 17. all the words with either 'aba' or 'eda' in them
# 18. has a double character (e.g. 'oo')
# 19. for every word print the first vowel
#
#
# OPTIONS: ---
# REQUIREMENTS: ---
# BUGS: ---
# NOTES: ---
# AUTHOR: Parag Kalra, paragkalra@gmail.com
# COMPANY: PERSISTENT SYSTEMS LTD, http://www.persistentsys.com/
# VERSION: 1.0
# CREATED: 3/19/2009 2:11:51 PM
# REVISION: ---
#===============================================================================

use Acme::Comment;

# Declaring the Paragraph
$mypara="Finish each day and be done with it. You have done what you could. Some blunders and absurdities no doubt crept in; forget them as soon as you can. Tomorrow is a new day; begin it well, with no pre-conceived notions and with too high a spirit so as to be able to hold back your old stuff. India is great. I love star*star. Vowels combination: axxiyywwrroo arixeupoq abajan abaada adamant abacuss edaiot Apple MAP Axe Gooogle PEANUT PqRs AIm";

# Spliting the string by space characater to store the words of the para into an array.
@para_words=split /\s+/, $mypara;

@vowels=qw/a e i o u/;

#---------------------------------------------------------------------------
# Processing each word of the array with regular expressions.
#---------------------------------------------------------------------------
foreach ( @para_words ) {
#print "Processing: $_ \n";

# 1
# Words containing 'a'
if ( $_ =~ m{a+} ) {
push @words_with_a, $_;

# Words containing 'a' but not 'e'
# As of now not working
#if ( $_ =~ m{[^e]} ) {
#push @words_having_a_not_e, $_;
#}
}


# 3
# Words having 'th'
if ( $_ =~ m{(th)+} ) {
push @words_with_th, $_;
}

# 4
# Words having either 'a' or 'A'
if ( $_ =~ m{a+}i ) {
push @words_with_Aa, $_;
}

# 5
# Words having star
if ( $_ =~ m{\*} ) {
push @words_with_star, $_;
}

# 6
# Words starting with 'a' or 'A'
if ( $_ =~ m{^a+}i ) {

push @words_starting_with_Aa, $_;

# 2
# Words starting with 'a'
if ( $_ =~ m{^a+} ) {
push @words_starting_with_a, $_;
}
}

# 7
# Words containing both 'a' and 'e'
if ( $_ =~ m{e+} && $_ =~ m{a}) {
push @words_having_both_ae, $_;
}

# 8
# Words in which 'a' is followed by 'e'
if ( $_ =~ m{(ea)+} ) {
push @words_having_a2e, $_;
}

# 9
# Words which doesnot have 'a'
# As of now not working with carat
if ( $_ !~ m{a} ) {
push @words_without_a, $_;
}

# Multiline comment
/*
if ( $_ !~ m{a+} ) {
push @words_without_a, $_;

if ( $_ !~ m{e+} ) {
push @words_without_a_e, $_;
}
}
*/

# 10
# Words except 'a' and 'e'
if ( $_ !~ m{e+} && $_ !~ m{a+} ) {

push @words_without_a_e, $_;

}

# 11
# Words having 'a' not having 'e'
if ( ($_ !~ m{e}) && ($_ =~ m{a}) ) {
push @words_having_a_not_e, $_;
}


# 12
# Words having 2 consecutive vowels
$var=$_;
foreach ( @vowels ) {
$v=$_;
#print "Current vowel: $v";

for ( $i=0; $i<5 ; $i++ ) {
#print "$v$vowels[$i] \n";
#if ( $var =~ /(($v){1})($vowels[$i]{1})/ ) {

# Matching the pattern for consecutive vowels
if ( $var =~ /$v$vowels[$i]/i ) {
#print "$var \n";
push @words_having_2_con_vowels, $var;
}

}
}

# 13
# Words containing at least 3 vowels
if (tr/aeiouAEIOU/aeiouAEIOU/ >= 3 ) {
push @words_having_3_vowels, $_;
}


# Pattern for matching 3 vowels
#for ( $p=0; $p<5 ; $p++ ) {
#for ( $q=0; $q<5 ; $q++ ) {
#for ( $r=0; $r<5 ; $r++ ) {
#if ( $var =~ /^(.*)($vowels[$p])(.*)$vowels[$q](.*)$vowels[$r](.*)$/ ) {
#if( grep $var, @words_having_3_vowels){
#push @words_having_3_vowels, $var;
#}
#}
#}
#}
#}


# Pattern for matching 3 consecutive vowels
# As of now under-construction
#if ( $_ =~ /[aeiou]{3,}/ ) {
# push @words_having_3_vowels, $_;
#}

# 14
# Words having at least 6 characters
if ( $_ =~ /(.){6,}/ ) {
push @words_with_atl_6_chs, $_;
}

# 15
# Words having exactly 6 characters
# As of now not working for '-'
#if ( $_ =~ /\b(.){6}\b/ ) {
if ( $_ =~ /^(.){6}$/ ) {
push @words_with_6_chs, $_;
}

# 16
# Words containing either 'aba' or 'ada'
if ( $_ =~ m{(aba)|(ada)}) {
push @aba_ada, $_;
}

# 17
# Words containing either 'aba' or 'eda'
if ( $_ =~ m{(aba)|(eda)}) {
push @aba_eda, $_;
}

# 18
# Words having identical adjoining characters
if ( $_ =~ /(.)\1/ ) {
push @words_with_2_chs, $_;
}


# 19
#---------------------------------------------------------------------------
# Printing the word and it's first vowel if at all it contains
#---------------------------------------------------------------------------
$mystring = $_;
#@chars = map substr( $mystring, $_, 1), 0 .. length($mystring) -1;
@chars = unpack('a'x length($mystring), $mystring);

if($mystring !~ /[aeiou]/i){
print "'$mystring' has no vowel \n";
} else {
foreach(@chars){
#print "$_ \n";
if($_ =~ /[aeiou]/i){
print "First vowel of '$mystring': $_ \n";
last;
}

}
}

}

print "\n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words containing 'a': @words_with_a \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words starting with 'a': @words_starting_with_a \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words containing 'th': @words_with_th \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words containing either 'A' or 'a': @words_with_Aa \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words containing star: @words_with_star \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words starting with 'A' or 'a': @words_starting_with_Aa \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words containing both 'a' and 'e': @words_having_both_ae \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words in which 'a' is followed by 'e': @words_having_a2e \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words which doesn't have 'a': @words_without_a \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words except 'a' and 'e': @words_without_a_e\n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words having 'a' but not 'e': @words_having_a_not_e \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words having 2 consecutive vowels: @words_having_2_con_vowels \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words having at least 3 vowels: @words_having_3_vowels \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words having at least 6 characters: @words_with_atl_6_chs \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words having exactly 6 characters: @words_with_6_chs \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words having 'aba' and 'ada': @aba_ada \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words having 'aba' and 'eda': @aba_eda \n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "Words having 2 similar and adjoint characters: @words_with_2_chs\n";
print "----------------------------------------------------------------------------------------------------------------------------------------\n";
print "\n";

Script to ask the numbers from user as a input and return the numbers which are above average.

#!/usr/bin/perl
#===============================================================================
#
# FILE: above_avg.pl
#
# USAGE: perl above_avg.pl
#
# DESCRIPTION: Script to ask the numbers from user as a input and return the numbers which are above average.
#
# OPTIONS: ---
# REQUIREMENTS: ---
# BUGS: ---
# NOTES: ---
# AUTHOR: Parag Kalra, paragkalra@gmail.com, www.paragkalra.com (),
# COMPANY: As of now Persistent System LTD, www.persistentsys.com
# VERSION: 1.0
# CREATED: Wednesday 11 February 2009 07:06:21 IST
# REVISION: ---
#===============================================================================


print "Please enter the numbers: \n";
@nums=;

@more_avg=&above_avg(@nums);

#=== FUNCTION ================================================================
# NAME: above_avg
# PURPOSE: To calculate numbers above average
# PARAMETERS: ????
# RETURNS: ????
# DESCRIPTION: ????
# THROWS: no exceptions
# COMMENTS: none
# SEE ALSO: n/a
#===============================================================================
sub above_avg {

print "You have entered following numbers: \n ";
print "@_ \n";
$avg_value=&avg(@_);

print "Numbers greater than average - $mean are: \n";

foreach (@_) {
if ($_ > $avg_value) {
#@return_more=(@return_more,$_);
push(@return_more,$_);

}


}

print " @return_more \n";
}


#=== FUNCTION ================================================================
# NAME: avg
# PURPOSE: To calculate mean value
# PARAMETERS: ????
# RETURNS: ????
# DESCRIPTION: ????
# THROWS: no exceptions
# COMMENTS: none
# SEE ALSO: n/a
#===============================================================================
sub avg {
$sum=0;
$count=0;
foreach (@_) {
$sum=$sum+$_;
$count=$count+1;
}
$mean=$sum / $count;
print "Total numbers entered: $count \n";
print "Sum of numbers: $sum \n";
print "Mean of numbers: $mean \n";
$mean;
}

Using GVIM as a PERL IDE !!!

People who do extensive PERL scripting on UNIX and love "vi' editor. Here's some interesting stuff for you:

http://www.vim.org/scripts/script.php?script_id=556

After unzipping it in the folder "$HOME/.vim/", read the file "$HOME/.vim/README.perlsupport"

Using variables in bash script.

#!/bin/bash
#===============================================================================
#
# FILE: vars_bash.sh
#
# USAGE: ./vars_bash.sh
#
# DESCRIPTION: Using variables in bash script.
#
# OPTIONS: ---
# REQUIREMENTS: ---
# BUGS: ---
# NOTES: ---
# AUTHOR: Parag Kalra (), paragkalra@gmail.com, www.paragkalra.com
# COMPANY: As of now Persistent System LTD, www.persistentsys.com
# VERSION: 1.0
# CREATED: Sunday 01 February 2009 11:16:38 IST IST
# REVISION: ---
#===============================================================================

echo "uninitialized_variable = $uni_var"

null_variable=
echo "null_variable = $null_variable"

let "num_var2 += 2"
echo "$num_var2"

num_var1=1
let "num_var1 += 4"
echo "$num_var1"

#-------------------------------------------------------------------------------
# Unsetting a variable
#-------------------------------------------------------------------------------
num_var=13
echo "$num_var"
unset num_var
echo "$num_var"


#-------------------------------------------------------------------------------
# Different ways a variable can be assigned
#-------------------------------------------------------------------------------
a=13;
echo $a

let a=a+13
echo $a

echo "Enter the value you want to assign to variable a: "
#read a

for a in 1 2 3
do
echo $a
done

list1=`ls -l`
echo $list1

list2=$(ls -l)
echo $list2

#-------------------------------------------------------------------------------
# Converting integers to strings and vice-versa
#-------------------------------------------------------------------------------
a=1234
echo $a

let a=a+1
echo $a

a=${a/35/TF}
echo $a

a=${a/TF/13}
echo $a

Using positional parameters in bash scripts

#!/bin/bash
#===============================================================================
#
# FILE: posi_param.sh
#
# USAGE: ./posi_param.sh 10 20 30 40 50 60 70 80 90 100 110 120 130 140 150
#
# DESCRIPTION: To exhibit usage of positional parameters
#
# OPTIONS: ---
# REQUIREMENTS: ---
# BUGS: ---
# NOTES: ---
# AUTHOR: Parag Kalra (), paragkalra@gmail.com, www.paragkalra.com
# COMPANY: As of now Persistent System LTD, www.persistentsys.com
# VERSION: 1.0
# CREATED: Sunday 01 February 2009 01:35:54 IST IST
# REVISION: ---
#===============================================================================

echo -e "\n \n"

echo "The arguments passed are: $*"
echo -e "Total number of arguments passed are: $# \n \n"

echo $1
echo $2
echo $3
echo $4
echo $5
echo $6
echo $7
echo $8
echo $9

echo -e "\n \n"
#-------------------------------------------------------------------------------
# Accessing positional parameters with curly braces produce unexpected result
#-------------------------------------------------------------------------------
echo $10
echo $11
echo $12
echo $13
echo $14
echo $15
echo -e "\n \n"

#-------------------------------------------------------------------------------
# This is the way to access double digit positional parameters
#-------------------------------------------------------------------------------
echo ${10}
echo ${11}
echo ${12}
echo ${13}
echo ${14}
echo ${15}
echo -e "\n \n"

#-------------------------------------------------------------------------------
# This is strange
#-------------------------------------------------------------------------------
for a in 1 2 3 4 5
do
echo $`echo $a`
done
echo -e "\n \n"

#-------------------------------------------------------------------------------
# and it still remains strange
#-------------------------------------------------------------------------------
for a in 1 2 3 4 5
do
echo "$`echo $a`"
done
echo -e "\n \n"

#-------------------------------------------------------------------------------
# Shiting the positional parameters
#-------------------------------------------------------------------------------
#: << COMMENTED
echo "Shifting positional parameters.."
until [ -z $1 ]
do
echo $1
shift
done
echo -e "\n \n"
#COMMENTED

#-------------------------------------------------------------------------------
# Shiting the positional parameters by 5 characters
#-------------------------------------------------------------------------------
echo "Shifting positional parameters by 4 characters.."
until [ -z $1 ]
do
echo $1
shift 4
done
echo -e "\n \n"

Script to mount multiple isos.

#!/bin/bash
#===============================================================================
#
# FILE: deand.sh
#
# USAGE: ./deand.sh
#
# DESCRIPTION: To mount partition and then the iso
#
# OPTIONS: ---
# REQUIREMENTS: ---
# BUGS: ---
# NOTES: ---
# AUTHOR: Parag Kalra (), paragkalra@gmail.com, www.paragkalra.com
# COMPANY: As of now Persistent System LTD, www.persistentsys.com
# VERSION: 1.0
# CREATED: Thursday 29 January 2009 08:04:14 IST IST
# REVISION: 2
#===============================================================================

#-------------------------------------------------------------------------------
# Creating "/temp" if it doesn't exist
#-------------------------------------------------------------------------------
if [ ! -d /temp ]
then
mkdir /temp
fi

#-------------------------------------------------------------------------------
# Creating /iso if it doesn't exist
#-------------------------------------------------------------------------------
if [ ! -d /iso ]
then
mkdir /iso
fi

echo " "

#-------------------------------------------------------------------------------
# Mounting partition and then the iso
#-------------------------------------------------------------------------------
if ! mount | grep sda1
then
echo -e "Mounting partition\n"
mount /dev/sda1 /temp
else
echo -e "Partition sda1 already mounted. \n"
fi


#=== FUNCTION ================================================================
# NAME: isomount()
# DESCRIPTION: To mount the respective iso
# PARAMETERS: isoname
# RETURNS: nothing
#===============================================================================
function isomount
{
echo -e "You have selected $1 hence mounting $1 iso\n"
mount -o loop /temp/root/linuxcbt-$1/linuxcbt-$1.iso /iso
}
# ---------- end of function isomount ----------

echo -e "Please enter the ISO you want to mount: \n1. rhel4\n2. sendmail\n3. samba\n4. security\n5. rhel5dvd\n6. postfix\n7. qmail"
read iso

case $iso in

rhel4)
isomount rhel4
;;

samba)
isomount samba
;;

security)
isomount security
;;

sendmail)
isomount sendmail
;;

postfix)
isomount postfix
;;

qmail)
isomount qmail
;;

rhel5dvd)
echo -e "You have selected $iso hence trying to mount $iso \n"
if ! mount | grep rhel5iso
then
echo -ec"Mounting $iso \n"
mount -o loop /temp/root/RHEL-5.1-i386-DVD.iso /share/rhel5iso
else
echo -e "$iso already mounted. \n"
fi
;;

esac

# --- end of case ---

BASH script to copy a remote database !!!

#!/bin/bash
#===============================================================================
#
# FILE: db_sync.sh
#
# USAGE: ./db_sync.sh
#
# DESCRIPTION: To sync the remote database
#
# OPTIONS: ---
# REQUIREMENTS: ---
# BUGS: ---
# NOTES: -----
# AUTHOR: Parag Kalra (), paragkalra@gmail.com, www.paragkalra.com
# COMPANY: As of now Persistent System LTD, www.persistentsys.com
# VERSION: 1.0
# CREATED: Sunday 18 January 2009 12:44:49 IST IS
# REVISION: ---
#===============================================================================

#-------------------------------------------------------------------------------
# Storing username, password & logfile in a variable
#-------------------------------------------------------------------------------
myuser=some_user
mypasswd=some_password
mylog=/var/log/projects/db_sync.log


#-------------------------------------------------------------------------------
# Executing a SQL file to create & drop local databases.
#-------------------------------------------------------------------------------
echo "Executing SQL file - `date`" > $mylog
mysql --verbose --user=$myuser --password=$mypasswd --host=localhost < /data/projects/mysql/alter_dbs.sql #---------------------------- #Dumping the remote database on the local setup #---------------------------- echo "Dumping remote mysql databases- `date`" >> $mylog
mysqldump --verbose --user=$myuser --password=$mypasswd --host=www.paragkalra.com some_database > /data/databases/pkc_db_`date +%F`.sql


#-------------------------------------------------------------------------------
# Importing the database
#-------------------------------------------------------------------------------
echo "Importing the database - `date`" >> $mylog
mysql --verbose --user=$myuser --password=$mypasswd some_database < /data/databases/pkc_db_`date +%F`.sql
echo "Export - Import of database complete - `date`" >> $mylog