PERL Tutorial #6

So now we can connect to databases but what we really need to do is create our own. The code to create a database called fone is as follows:-

#! /usr/bin/perl

use DBI;
use strict;

my ($dsn)="DBI:mysql:mysql:localhost";
my ($user_name)="linux";
my ($password)="linux";
my ($dbh, $sth);

$dbh=DBI->connect($dsn,$user_name, $password, { RaiseError => 0, PrintError => 0 })
	or die "Could not connect\n";

$sth=$dbh->prepare("CREATE DATABASE fone")
	or die "Unable to create\n";

$sth->execute();

$sth->finish();
$dbh->disconnect;
exit(0);

This is similar to the previous file in that all the variables required are the same. We connect as before except that in the { curly brackets } I have switched off the error checking in favour of a simpler die statement. Note that we have to connect to an existing database (if ther aren't any on your system then you will have to create one using the mysql monitor) before we can create our own. The name of the database we wish to create is contained within the SQL statement CREATE DATABASE fone. We then connect to mysql and report an error if we can't. Once the database has been created, running the program again will result in an error as it has already been created. To be sure that this is working, perhaps it would be nice to be able to delete the database again. Here is how:-

#! /usr/bin/perl

use DBI;
use strict;

my ($dsn)="DBI:mysql:fone:localhost";
my ($user_name)="linux";
my ($password)="linux";
my ($dbh, $sth);
my (@ary);

$dbh=DBI->connect($dsn,$user_name, $password)
	or die "Could not connect";

$sth=$dbh->prepare("DROP DATABASE fone");
$sth->execute();

$sth->finish();
$dbh->disconnect;
exit(0);

This works in a similar way to the previous program except that the prepare statement contains a different command to delete (or drop) the database. Again. once deleted, a rerun of the program will show an error. Now you can create and delete the same database over and over again particularly to test the robustness of the error routines. Now we perhaps we would like to add tables.

#! /usr/bin/perl

use DBI;
use strict;

my ($dsn)="DBI:mysql:fone:localhost";
my ($user_name)="linux";
my ($password)="linux";
my ($dbh, $sth);

$dbh=DBI->connect($dsn,$user_name, $password, { RaiseError => 0, PrintError => 0 })
	or die "Could not connect\n";

$sth=$dbh->prepare("CREATE TABLE fone(id int auto_increment,
				name varchar(30) not null,
				number varchar(20) not null,
				primary key(id))")
	or die "Unable to create\n";

$sth->execute();

$sth->finish();
$dbh->disconnect;
exit(0);

We first logon to the mysql database engine as usual then select the database to use. Now we can try to write a table to the selected database using an SQL command held in the prepare statement as before which looks like this:-

$sql="CREATE TABLE fone(id int auto_increment,
				name varchar(30) not null,
				number varchar(20) not null,
				primary key(id))";

This will create a table called fone with a field called id that will automatically create an integer starting from 1 for each record. Then a name is created which is a string that must contain a value (hence the not null clause). Finally, we add a number which is held as a string for simplicity and identify that the primary key is id. A primary key is the main value that is used to identify a record because there may be duplicate names or numbers in the database whereas id is unique. Just to prove creation has taken place, we'll delete it again:

<?php   

	$domain = "localhost";
	$user = "linux";
	$password = "linux";
	$new_db = "fone";
	$sql="DROP TABLE fone";

	$conn = mysql_connect($domain, $user, $password)
		OR DIE("Sorry - could not connect to MySQL");

	if($new_db)
	{
		$result=mysql_select_db($new_db, $conn);
		if(!$result)
		{
			DIE("Sorry - could not select database");
		}

		$result=mysql_query($sql,$conn);

		if(!$result)
		{
			DIE("Sorry - could not drop table");
		}
	}

	$result2=@mysql_list_tables($new_db);
	for($row=0; $row < mysql_num_rows($result2); $row++)
	{ 
		$list .= mysql_tablename($result2, $row)." | "; 
	} 
?>

<html>
	<head>
		<title>Deleting tables</title>
	</head>
	<body>
		Current tables: <?php echo($list); ?> <hr>
	</body>
</html>

Once again we use an SQL command (DROP TABLE fone) to delete (or drop) the table. Note that an error occurs if you attempt to delete the table again or create it again before it has been deleted. You might want to adapt the program in the previous tutorial to view the new database and tables. Next time we'll get into actually putting data into the table. To give you a clue, the SQL statement to achieve this will be:-

INSERT INTO fone (name,number) VALUES (fred, 01234 12345)

Until the next time...

Back