use strict;
use DBI;

# Before execute this perl script, create a database file as follows.
# sqlite3.exe test.db
# On the sqlite prompt, type ".quit" to quit sqlite.
# Then, you will find out new database file (test.db in the same folder).

my $sDbFile   = "test.db";
my $sServer   = "dbi:SQLite:dbname=$sDbFile";    # for mysql, use "DBI:mysql:perlmysqldb"
my $sUser     = "";
my $sPassword = "";
my $sTable    = "members";

# Open a connection
my $oDbCon = DBI->connect($sServer, $sUser, $sPassword, {
	PrintError       => 0,
	RaiseError       => 1,
	AutoCommit       => 1,
	FetchHashKeyName => 'NAME_lc',
});


# Create a table
my $sSql = "CREATE TABLE ".$sTable." (".
           "	id       INTEGER PRIMARY KEY,".
           "	name     VARCHAR(100),".
           "	score    INTEGER,".
           "	email    VARCHAR(100) UNIQUE NOT NULL,".
           "	phone    VARCHAR(20)".
           ")";

eval {
	$oDbCon->do($sSql);
};
if ($@) {
	print $@."\n";
	print "Table (".$sTable.") was removed from the database.\n\n";
	$oDbCon->do("drop table ".$sTable.";");
	$oDbCon->do($sSql);
}


# Insert 4 sets of data
$oDbCon->do('INSERT INTO members (id, name, score, email, phone)'.
              'VALUES (1, \'EBKim\', 90, \'itanimal@gmail.com\', \'033-123-4567\')');
$oDbCon->do('INSERT INTO members (id, name, score, email, phone)'.
              'VALUES (2, \'GDJin\', 80, \'gdjin@gmail.com\', \'033-123-4321\')');
$oDbCon->do('INSERT INTO members (id, name, score, email, phone)'.
              'VALUES (3, \'JBPark\', 70, \'jbpark@gmail.com\', \'033-123-7890\')');
$oDbCon->do('INSERT INTO members (id, name, score, email, phone)'.
              'VALUES (4, \'IHYoo\', 80, \'ihyoo@gmail.com\', \'033-333-7890\')');


# Get each record from a table
print "--------------------------------------------\n";
my $oCom = $oDbCon->prepare("select * from members;");
$oCom ->execute();
while(my @aCurRec = $oCom->fetchrow_array()){
	printf("%-1s %-6s %3d %-18s %-12s\n", $aCurRec[0], $aCurRec[1], $aCurRec[2], $aCurRec[3], $aCurRec[4]);
}       
$oCom->finish();

# Close the connection
$oDbCon->disconnect;


# For more information, refer to the following link
# http://www.mysqltutorial.org/perl-mysql/perl-mysql-select/