Hi,

Can someone guide me to start using MYSQL in PERL?

__________________
-Murali..

hey,

I can give you a nice introduction to a database interface with MySQL and Perl.

Despite the power, the stability, and the flexibility of MySQL, it's still only a database engine and not an application, per se. Although it can be accessed with the mysql client, the client is not suitable for most non-technical users. Therefore, developers must use a programming language to build user interfaces. A few of the popular languages provide application program interfaces (APIs) to interact with MySQL: Perl, PHP, Python, and Java. The basics of building a MySQL interface with Perl.

Let's start by connecting to the database:

use DBI;

my $dsn = 'DBI:mysql:my_database:localhost';
my $db_user_name = 'admin';
my $db_password = 'secret';
my ($id, $password);
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

Let's assume we've received as form input a nickname and password from a login screen. So right now,

$input_nickname = 'Cowlick' and $input_password = 'udder'

We want to verify that the entered password matches what we have in our database.

my $sth = $dbh->prepare(qq{
select id, password from users
where nickname = $input_nickname
});
$sth->execute();

Notice there is no command-terminating semi-colon.

How do we get the results? Since we only expect one row,

($id, $password) = $sth->fetchrow_array();
$sth->finish(): # we're done with this query
if ($input_password eq $password) # case-sensitive
{
... # login successful
}

What if our result is more than one row? Successive calls to

$sth->fetchrow_array()

will return the rest of the result set.

my $sth = $dbh->prepare(qq{
select nickname, favorite_number from users
});
$sth->execute();
while (my ($nickname, $favorite_number) =
$sth->fetchrow_array()) # keep fetching until
# there's nothing left
{
print "$nickname, $favorite_number\n";
}
$sth->finish();

If we want to save the entire result set first for processing later,

my (@matrix) = ();
while (my @ary = $sth->fetchrow_array())
{
push(@matrix, [@ary]); # [@ary] is a reference
}
$sth->finish();

A reference, for C programers, can be thought of as a pointer. The Matrix is now an array of array references, or a two-dimensional array.

You can access row $i with:

@{matrix[$i]}

Or, you can access a specific row and column ($i, $j) in the table with:

$matrix[$i][$j]

For MySQL operations that don't return a result you can use the do method instead of prepare then execute.

$dbh->do("insert into message_votes
(message_id, user_id, vote) values (1, 3, 'good')");

When you're done with the database:

$dbh->disconnect();

MySQL That should be enough to get you started. You can see that using Perl DBI is a matter of calling a method with the MySQL command as a string.

__________________
Venkat
knowledge is Power

Hi Venkat,

Now i can create a perl file for connecting database, then how to execute that?

Shall i need to execute in the command prompt? else shall i execute it via Internet Browser?

__________________
-Murali..

The Perl language includes a specialized syntax for writing regular expressions (RE, or regexes), and the interpreter contains an engine for matching strings to regular expressions. The regular expression engine uses a backtracking algorithm, extending its capabilities from simple pattern matching to string capture and substitution.

_________________________

Keyword Research Georgia Health Insurance