create the table in perl that will be automatically updated in database using mysql

1.create the table in perl that will be automatically updated in database using mysql

1.1 First create the database as follows in mysql

[linuxpert@localhost ~]$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 2

Server version: 5.1.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases;

+--------------------+

| Database         |

+--------------------+

| information_schema |

| mysql             |

| test              |

+--------------------+

3 rows in set (0.25 sec)

mysql> create database student; Query OK, 1 row affected (0.02 sec)

mysql> show databases;

+--------------------+

| Database         |

+--------------------+

| information_schema |

| mysql             |

| student          |

| test              |

+--------------------+

4 rows in set (0.00 sec)

mysql> use student; Database changed mysql> connect; Connection id:      3

Current database: student

mysql> show tables; Empty set (0.00 sec)

now the table is empty . To create the table we use the following procedure

1.2 The following packages should be used to connect PERL with Mysql

(use new terminal)

[linuxpert@localhost ~]$ rpm -q perl-DBI

perl-DBI-1.609-4.fc13.i686

[linuxpert@localhost ~]$ rpm -q perl-DBD-MySQL

perl-DBD-MySQL-4.013-3.fc13.i686

1.3 Write the PERL script to connect with mysql as follows

#!usr/bin/perl

use DBI; #to use the build in package we use "Use", DBI is the build in package in perl

my $dbh=DBI->connect("dbi:mysql:student","root",""); #connect to database

if(!$dbh)

{

die("error:$!");

}

$sth=$dbh->prepare("create table students(rollno int,sname varchar(50))");

# create the table

$sth->execute();

$dbh->disconnect;

1.4 Run the Perl script [linuxpert@localhost ~]$ perl connect.pl now see the tables in database (“student”)

mysql> show tables;

+-------------------+

| Tables_in_student |

+-------------------+

| students        |

+-------------------+

1 row in set (0.00 sec)

2. insert the values in perl that will be automatically updated in database using mysql as follows

#!usr/bin/perl

use DBI; #to use the build in package we use "Use", DBI is the build in package in perl

my $dbh=DBI->connect("dbi:mysql:student","root",""); #connect to database

if(!$dbh)

{

die("error:$!");

}

$sth=$dbh->prepare("insert into students values(100,'thamarai')"); # create the table

$sth->execute();

$dbh->disconnect;

2.1 compile the perl [linuxpert@localhost ~]$ perl dbinsert.pl now the output is

mysql> select * from students;

+--------+----------+

| rollno | sname   |

+--------+----------+

|   100 | thamarai |

+--------+----------+

1 row in set (0.00 sec)

2.2 insert the values in perl using execute statement

#!usr/bin/perl

use DBI; #to use the build in package we use "Use", DBI is the build in package in perl

$rollno=200;

$sname="selvi";

my $dbh=DBI->connect("dbi:mysql:student","root",""); #connect to database

if(!$dbh)

{

die("error:$!");

}

$sth=$dbh->prepare("insert into students values(?,?)"); # create the table

$sth->execute($rollno,$sname);

$dbh->disconnect; compile the program as [linuxpert@localhost ~]$ perl dbinsert1.pl now the output is

mysql> select * from students;

+--------+----------+

| rollno | sname   |

+--------+----------+

|   100 | thamarai |

|   200 | selvi   |

+--------+----------+

2 rows in set (0.08 sec)

simple perl program | sample perl program | perl program download | example perl program |execute perl program | perl script | perl builder | perl beginners | perl software | perl program download | perl script | perl program windows | perl tutorial

Posted in: Perl / Python Program