In the need of creation of a database and a way to add an interface for interaction with that, today I learned how to work on MySQL.

Also got to work at Perl DBI for interaction with MySQL. Database access steps are similar to those in other languages:

for using MySQl, go here.

once your MySQL database is ready and assuming that you have created the database named mydb (how ? refer the link above !), you can continue for you safari into Perl DBI.

we begin our database programming by connecting to it with following steps.

use DBI; # to use Perl DBI ;

my $dsn = ‘DBI:mysql:mydb:localhost’; # DSN string

my $db_user_name = “myusername”; # user name for db
my $db_password = “mypasswd”; # passwd for db

my $dbh = DBI->connect($dsn, $db_user_name, $db_password); # connect to mydb

Now, we are ready for processing queries and statements on our database.

for a query like ‘select’ which return some record sets, the process is:

$statement = “select * from mytable”;
my $sth = $dbh->prepare($statement);
$sth->execute or die “can’t execute the query: ” . $sth->errstr;

Now for traversing through the records (rows) we have APIs to help:

while(@row = $sth->fetchrow_array) {

# access @row as an array:
# $row[0] is first field(column)
# $row[1] is second field (column) …
}

# … other stuff

$sth->finish; # reinitialize the handle when needed. we are done.

for the queries like ‘insert’ which do not return any records:

$statement = “insert into mytable values(0, “Manoj”, “402A, West Avenue”)”; $dbh->do($statement) or print $DBI::errstr;

finally,

$dbh->disconnect; # disconnect the db.

following are some links that can come to your rescue in case you get toads !

>> http://safari.oreilly.com/1565926994
>> http://www.perl.com/pub/a/1999/10/DBI.html