10.4.1 Creating a MySQL Table in a Perl Program

Submitted by admin on Wed, 2007-11-14 10:05

10.4.1 Creating a MySQL Table in a Perl Program

In MySQL or any other database server, a database needs to be first created. A database can be created using an SQL command. This can be done by opening a MySQL client on the command line. One is advised to consult appropriate documentation on MySQL. Once a database has been created, one can use the database, and can create tables within the used database. Database servers other than MySQL also provide similar functionality, but the commands used may be different. To create a table, we need to use an SQL command. This SQL command can be run on the MySQL client using a command-line interface. The same SQL command used for creating a table can be embedded in a Perl program as well. This is precisely what the following program illustrates.

Program 10.10

#!/usr/bin/perl
#creatDB.pl
#Create a table

use DBI;
use strict;

my $databaseDriver = "mysql";
my $databaseName = "test";
my $databaseDSN = "DBI:$databaseDriver:$databaseName";
my $databaseUserName = "Project";
#this is for illustration only; a better idea is to prompt for the password
#and not hardcode it in the program
my $databasePassword = "Project";

#connect to DB and make a handle
my $dbh = DBI -> connect ($databaseDSN, $databaseUserName, $databasePassword)
    or die $DBI::errstr;
#issue a query to create a table
my $sth = $dbh -> prepare(
               qq{create TABLE friends
                  (STUDENT_ID INT (6) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
                         SSNUM INT (9) UNSIGNED NOT NULL UNIQUE,
                         NAME CHAR (50) NOT NULL,
                         AGE INT(3) NOT NULL,
                         HOMETOWN CHAR (100) NOT NULL,
                         SCHOOL CHAR (150) NOT NULL,
                         NOTES CHAR (255)
                 )});
$sth -> execute () or warn $dbh->errstr();
$sth -> finish();

$dbh -> disconnect();

The program uses the DBI.pm module that provides general connectivity to databases. The driver used in mysql.pm. In other words, the functionality provided by the MySQL-dependent mysql.pm driver. However, we do not have to use the mysql.pm driver. The DBI.pm method connect installs the appropriate driver when needed. There may be other ways to install drivers, but let us just use connect to do so implicitly. A database with the name test has already been created by the database administrator prior to executing the current program. Usually, the database is created using the MySQL command-line interface or by running a script containing SQL commands. The database administrator also has created a user Project on the machine on which the Perl program and the mysqld, i.e., the MySQL server are running. For the user Project on the local host or machine, the administrator has given the
password Project.

To use the database from within the Perl program, the program must establish a connection to the database. One must note that the database server a program connects to, does not have have to run on the same machine where the Perl program is running. The database server can be running on any other machine that is accessible over the network, as long as the network connectivity exists and the proper user-password-machine combination has been established by a database administrator. The proper access privileges need to be granted by a database administrator even if the Perl program is running on the same machine as the database server mysqld. The connection to the database from within the program is established using the connect method of the DBI.pm
module. It is a class method.

my $dbh = DBI -> connect ($databaseDSN, $databaseUserName, $databasePassword)

or die $DBI::errstr;

$dbh is a database handle. The database handle is used to communicate with the database. If connect succeeds, a database handle has been created. If connect fails, it returns undef and the program dies with a message. connect can be called with various numbers of arguments. One is advised to consult MySQL documentation for the alternatives. The form we use here requires three arguments: a database source name or DSN argument, called $databaseDSN here, the name of the
database user and the associated password. The DSN argument is formed by concatenating the string DBI and the database driver name and the database name, and gluing them with the colon (:). The Perl program and the database server are running on the same machine.

If a connection can be established to the named database source $databaseName with the

$databaseDriver using the user name and password specified, we have the handle $dbh to work with later in the program. In the current program, we simply create a table within the database

$databaseName in MySQL running on the local machine. The database administrator has given the user Project privilege to create tables within the test database and access the tables within test. To create a table called friends, the MySQL query or request that needs to be issued is given below.

create TABLE friends

(STUDENT_ID INT (6) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,

SSNUM INT (9) UNSIGNED NOT NULL UNIQUE,

NAME CHAR (50) NOT NULL,

AGE INT(3) NOT NULL,

HOMETOWN CHAR (100) NOT NULL,

SCHOOL CHAR (150) NOT NULL,

NOTES CHAR (255)

)

This query or statement can be issued directly using a MySQL client, usually on a the command-line, but possibly using a graphical user interface as well. However, when the command is issued from inside a Perl program, there are two steps: preparation and its execution. This is why, we create a statement handle $sth by prepareing the query. qq simply double quotes the SQL query like a string. The query uses SQL although the SQL used is specific to MySQL. It may be a little different with other database servers such as Oracle or FileMaker Pro or Microsoft SQL server. There is no other way but to consult the appropriate server-specific documentation to be able to formulate
the SQL query correctly. In general, the data types allowed, and the options that can be used are different in different versions of SQL. In the MySQL version of SQL, the create command takes the keyword TABLE followed by the name of the table being created. Here, we are creating a table called friends. Following the name of the table, the fields or column names are specified separated by the comma (,). The fields to be created are STUDENT_ID, SSNUM, AGE, HOMETOWN, SCHOOL and NOTES. Every SQL table needs a key or a primary key. Here, STUDENT_ID field represents the primary key. It is an unsigned number with up to six digits in it. It is specified using the attribute or option PRIMARY KEY. A primary key must also have the NOT NULL attribute. In other words, when we enter data, the field must always have a value. The field STUDENT_ID also has the AUTO_INCREMENT attribute. That is, the value of the key is an integer that is automatically incremented by one for every row or record entered into the table. The second field is SSNUM representing a Social Security Number. It is an unsigned integer with up to 9 decimal digits. The field is required in every row or record of data. The third field is AGE, a 3-digit integer, and is required as well. HOMETOWN
and SCHOOL are character or text data and are required. HOMETOWN can contain up to 100 characters and SCHOOL can contain up to 150 characters. The CHAR data type is fixed length. In other words, the specified amount of space is allocated in each record or row, whether it is used or not. Unused length is padded automatically by MySQL using its own padding character, which is the empty string. The last field is NOTES that is a 255-byte character string. This field need not be field in each record.

Once a statement has been prepared, we have a statement handle. Some database drivers convert the statement into an internal format during preparation while others do not do anything at all. Here, the statement handle is $sth. The statement is executed by calling the execute method on the statement handle. execute sends the statement to the database server using the $dbh database handle. If there are problems during execution, the error string is available using the errstr
method of the database handle. If the execution of the statement takes place without any problem, the program wraps up the handling of the statement using the finish method on the statement handle $sth.

When this program is run on the command prompt, assuming everything is flawless, nothing is seen on the terminal. To verify that the program has run and done what it is supposed to, we can run the MySQL client mysql on the command-line. An extended interaction is given below.

pikespeak[139]: mysql -u Project -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 152 to server version: 3.23.22-beta

Type 'help' for help.

mysql> show databases;

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

| Database |

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

| AP_news_archive |

| cprfproj |

| kalita |

| kasireddy |

| movies |

| mysql |

| prohilla |

| reuters |

| test |

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

9 rows in set (0.00 sec)

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

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

| Tables_in_test |

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

| SITES |

| friends |

| testac |

| testad |

| testae |

| trainingSet |

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

6 rows in set (0.00 sec)

mysql> select * from friends;

Empty set (0.00 sec)

mysql> describe friends;

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

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

| AGE | int(3) | | | 0 | | select,insert,update,references |

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

7 rows in set (0.00 sec)

The user is Project. The -p command-line option prompts for a password. Entering the correct password puts us in an interaction with the MySQL server. We can run the

show databases;

command to see all the databases that are known by the MySQL server. Here, there are nine databases: AP_news_archive, cprfproj, kalita, kasireddy, movies , mysql, prohilla, reuters and test . Our program works with the last database called test. We then run the

use test;

MySQL command to start working with the database test. We use the

show tables;

MySQL command to look at the tables in the test database. There are 6 tables: SITES, friends, testac, testad, testae, trainingSet . The table friends is the one the Perl program created. We use the SQL command

select * from friends;

to look at the rows or records in this newly created table. Obviously, there are no records since we have entered no data into the table. We use the MySQL command

describe friends;

to look at the fields or columns in the table created. The information about the fields is printed in columnar form in a table. The fields are the ones we have declared in the Perl program. The information showed confirms that the Perl program is able to communicate with the MySQL database server, and that the MySQL database server has executed the SQL statement that is passed to it by the Perl program.

If we run the program another time, we get the following message.

DBD::mysql::st execute failed: Table 'friends' already exists at createDB.pl line 29.

Table 'friends' already exists at createDB.pl line 29.

Add new comment

On Perl

10.4.1 Creating a MySQL Table in a Perl Program

10.4.1 Creating a MySQL Table in a Perl Program