use DBI;
use strict;

my $dsn       = "dbi:mysql:mysql_test";
my $user      = "root";
my $password  = "password";
my $dbh = DBI -> connect($dsn,$user,$password,{RaiseError=>1,AutoCommit => 0});

my ($i,@row,$sth);
my $out_file = "test.csv";
open(Handle_OUT,">$out_file") || die "Could not open $out_file $!\n";

#  load data [pet.txt] into TABLE [test_pet] of DATABASE [MySQL_test]
$sth = $dbh -> prepare("LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE test_pet LINES TERMINATED BY '\r\n';");
$sth -> execute;
  
# insert one more record into TABLE [test_pet]
$sth = $dbh -> prepare("INSERT INTO test_pet  VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);");
$sth -> execute;

# select all records from the [test_pet] TABLE of DATABASE [MySQL_test] and output to [test.csv]
$sth = $dbh -> prepare("SELECT * FROM test_pet");
$sth -> execute;
while (@row = $sth->fetchrow_array())
{
	for ($i=0;$i<@row;$i++)
	{
		print Handle_OUT "$row[$i]\t";
	}
	print Handle_OUT "\n";
 }

# disconnet  
$dbh->disconnect;

# close the output file  
close(Handle_OUT);
 
### -----------------------------------------------------------------
### THE FOLLOWING ARE COMMAND USED TO CREATE THE TEST DATABASE AND THE TABLE IN THE DATABASE 
## -----------------------------------------------------------------
### create database MySQL_test;		# Create a database called [MySQL_test]
### use MySQL_test;			# Select the created database [MySQL_test]
###                                     # Create an empty TABLE called [test_pet]
### CREATE TABLE test_pet (name VARCHAR(20),owner VARCHAR(20),species VARCHAR(20),sex CHAR(1), birth DATE, death DATE);
### 
###
### -----------------------------------------------------------------
### Below is the data file [pet.txt] to be loaded into TABLE [test_pet] of database [MySQL_test] 
### -----------------------------------------------------------------
### Fluffy	Harold	cat	f	1993-02-04		
### Claws	Gwen	cat	m	1994-03-17		
### Buffy	Harold	dog	f	1989-05-13		
### Fang	Benny	dog	m	1990-08-27		
### Bowser	Diane	dog	m	1979-08-31	1995-07-29	
### Chirpy	Gwen	bird	f	1998-09-11		
### Whistler	Gwen	bird		1997-12-09		
### Slim	Benny	snake	m	1996-04-29
## -----------------------------------------------------------------