DBI Connection & Sql Functions

				
=**************************************
= Name: DBI Connection & Sql Functions
= Description:Connecting to database using DBI. Example function for Database Query are shown as well ...
eg... select, insert, delete and update.
= By: Darren Wong
=
=This code is copyrighted and has= limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=643&lngWId=6=for details.=**************************************

#######################################################################################################################
# This code shows how to make connection using DBI. Function for each SQL function are implement to ease our life
# Any comment or updates or queries or modification made pls email me at: 
#														
#												wongchinpoh@yahoo.com
#
#######################################################################################################################
#!/usr/bin/perl --
use DBI;
### Function to display the header
sub fMenuHeader()
{
	print "======================================================================\n";
	print "= Start =\n";
	print "======================================================================\n\n";	
}
### Function to display the content or consider as menu
sub fMenuContent()
{
	print "\t\t 1. Select \n";
	print "\t\t 2. Insert \n";
	print "\t\t 3. Update \n";
	print "\t\t 4. Delete \n\n";
	
	print "\t\tEnter which statement to execute:> ";	
}
### Function to display the footer
sub fMenuFooter()
{
	print "======================================================================\n";
	print "= End=\n";
	print "======================================================================\n\n";	
}
### Function to make connection with database
	## Notes * you can makes multiple connection but you requires to change the coding to support function parameter
sub dbConnection()
{
	$dbHost = "localhost";	# Host detail
	$dbName = "training";# Database name to connect
	$dbUser = "root";		# Name to connect to db
	$dbPassword = "";		# Password to connect to db
	
	$dbConnectionInfo = "DBI:mysql:database=$dbName;$dbHost"; # Build Connection string
	
	$dbh = DBI->connect($dbConnectionInfo, $dbUser);# Connect to db
}
$db = &dbConnection();	# Calling above function to make connection with db
### Function to query select statement from database
sub dbSelect($sqlSelect)
{		
	print "\t\tStatement: $sqlSelect \n\n";
	
	$result = $db->prepare($sqlSelect);
	$result->execute();
	
	while(@row = $result->fetchrow())
	{		
		$counter ++;
		print "\t\t$counter) $row[1] \n";	
	}
	
	$result->finish();
}
### Function to query insert data to database
sub dbInsert($sqlInsert)
{
	print "\t\tStatement: $sqlInsert \n\n";
	
	$result = $db->prepare($sqlInsert);
	$result->execute();		
	$result->finish();
}
### Function to query update data from database
sub dbUpdate($sqlUpdate)
{
	print "\t\tStatement: $sqlUpdate \n\n";
	
	$result = $db->prepare($sqlUpdate);
	$result->execute();		
	$result->finish();
}
### Function to query delete data from database
sub dbDelete($sqlDelete)
{
	print "\t\tStatement: $sqlDelete \n\n";
	
	$result = $db->prepare($sqlDelete);
	$result->execute();		
	$result->finish();
}
	fMenuHeader();	# Display header	
	fMenuContent(); # Display content
	$counter = 0;	# Display total record
	
while($statement = <>) # Get user input
{
	# Process Select statement
	if ($statement == 1)
	{		
		print "\n\t\t**********Result***********\n\n";		
		
		$sqlSelect = "SELECT * FROM training";	# Construct Select SQL String
		$execSql = &dbSelect($sqlSelect);		# Execute sql
		
		print "\n";
	}
	
	# Process Select statement
	elsif($statement == 2)
	{
		print "\n\t\t**********Result***********\n\n";		
		
		$sqlInsert = "Insert into training(TrainingDetail, TrainingSts) values('This is for testing', 'Pending')"; # Construct Insert SQL String	
		$execSqlInsert = &dbInsert($sqlInsert); # Execute Sql
		
		### Display new database record
		$sqlSelect = "SELECT * FROM training";
		$execSqlSelect = &dbSelect($sqlSelect);
		
		print "\n";
	}	
	
	elsif($statement == 3)
	{
		print "\n\t\t**********Result***********\n\n";		
		
		$sqlUpdate = "UPDATE training SET TrainingDetail = 'Its Fun Dude - Perl Cool & rules' WHERE TrainingId = 2"; # Construct Update SQL String		
		$execSqlUpdate = &dbUpdate($sqlUpdate); # Execute Sql
		
		### Display new database record
		$sqlSelect = "SELECT * FROM training";
		$execSqlSelect = &dbSelect($sqlSelect);
		
		print "\n";
	}
	
	elsif($statement == 4)
	{
		print "\n\t\t**********Result***********\n\n";		
		
		$sqlDelete = "DELETE FROM training WHERE TrainingDetail = 'This is for testing'"; # Construct Delete SQL String		
		$execSqlDelete = &dbDelete($sqlDelete); # Execute Sql
		
		### Display new database record
		$sqlSelect = "SELECT * FROM training";
		$execSqlSelect = &dbSelect($sqlSelect);
		
		print "\n";
	}
	$counter = 0;
}
fMenuFooter(); # Display the footer