×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here
Home > Forums > MIS/IT > Operating Systems - UNIX based > Linux (server) > FAQs

Linux (server) FAQ

Perl DBI

How do I create tables with Perl's DBI? by mbaranski
faq54-316
Posted: 4 Dec 00

This is a pretty simple example of how to create a table using mysql, if you know sql, and are using another database, the syntax may be a little bit different to connect, but otherwise this should work.

#!/usr/bin/perl

########################################################################
#                         init.pl                                      #
#This file creates all of the tables, but does not set any perms or    #
#add any records.  It simply sets up the database to be filled.        #
#The database is called killer, and it connects w/ no uname or passwd  #
#Last modified on 12-4-2000 by Mike Baranski                           #
########################################################################
#If you know C/C++, the following 3 statements are roughly equivalent
#to #include <theFile.h> statements, they "import" functions.

use DBI;               #The database Independent Driver, see Perldoc DBI
use DBD::mysql;        #This particular database, mysql for this case
use strict;            #require strict pragmas, not really needed
                       #but it keeps us honest!

#######################################################################
#The max length for a varchar, it may change...                       #
#The reason we do this is so that if the length does change, you only #
#have to change one value to upadate all of the VARCHARS, instead of  #
#looking through evey line of code                                    #
#######################################################################
my $max_varchar = 255;

my $database_name = "killer"; #This is the database we're creating

#######################################################################
#The following lines actually create a live handle to the database,   #
#which is what dbh stands for (DataBase Handle)                       #
#######################################################################
my $dsn = "DBI:mysql:database=test;host=mike";
my $dbh = DBI->connect($dsn, undef, undef);

#######################################################################
#This is how to execute SQL statements on a database, this one drops  #
#the database, be sure you want to!                                   #
#######################################################################
my $sth = $dbh->prepare("DROP DATABASE IF EXISTS killer");
$sth->execute();

########################################################################
#More sql statements, it's pretty self-explanatory                     #
########################################################################
$sth = $dbh->prepare("CREATE DATABASE killer");
$sth->execute();

########################################################################
#Create a table called problems                                        #
########################################################################
$sth = $dbh->prepare("CREATE TABLE $database_name.problems (problem_number INT NOT NULL,
                      PRIMARY KEY (problem_number), company INT, category
                      VARCHAR($max_varchar),
                      description_filename VARCHAR($max_varchar),
                      closed_by INT, open_or_closed INT, entered DATE,
                      closed DATE, urgency INT,
                      contact_person VARCHAR($max_varchar),
                      labor_time INT,
                      parts_sold VARCHAR($max_varchar)
                      )");
$sth->execute();
########################################################################
#Create the Company Table                                              #
########################################################################
$sth = $dbh->prepare("CREATE TABLE $database_name.company (company_number INT NOT NULL,
                       PRIMARY KEY (company_number), address1 VARCHAR($max_varchar),
               address2 VARCHAR($max_varchar), address3 VARCHAR($max_varchar),
               main_contact VARCHAR($max_varchar), sec_contact VARCHAR($max_varchar),
                     phone_number VARCHAR($max_varchar), abbreviation VARCHAR(20),
               contract_type INT, customer_since DATE, number_of_employees INT,
                       primary_technician INT, secondary_technician INT)");

$sth->execute();
########################################################################
#Create the Employee Table                                             #
########################################################################
$sth = $dbh->prepare("CREATE TABLE $database_name.employee (employee_number INT NOT NULL,
                     PRIMARY KEY (employee_number), address1 VARCHAR($max_varchar),
             address2 VARCHAR($max_varchar), address3 VARCHAR($max_varchar),
                     phone VARCHAR(15), cell VARCHAR(15), pager VARCHAR(15),
                     email VARCHAR($max_varchar), email_pager VARCHAR($max_varchar),
                     birthday DATE, pay_rate DOUBLE)");
$sth->execute();

#######################################################################
#This properly releases the handles                                   #
#######################################################################
$sth->finish();
$dbh->disconnect();

Back to Linux (server) FAQ Index
Back to Linux (server) Forum

My Archive
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close