O.K. I've finished my code to create a MySQL db and/or table, and I'd
appreciate hearing your comments. It works well, but it needs to be cleaned up
before going live. I've left some testing code in. Go ahead and be brutally
honest. I've learned so much from this list already.
Thanks,
Marc
---------
#!/usr/local/bin/perl
use warnings;
use strict;
use CGI::Carp "fatalsToBrowser";
use feature 'say';
use DBI();
use CGI;
my $q = CGI->new();
say $q->header(), $q->start_html( -title => "Create MySQL db and table" );
my $username = 'root';
my $password = 'root';
my $host = "127.0.0.1:8889";
my $db = "test_dbb";
# table name is set below
# Connect to the server to create the db, if it doesn't exist already.
my $dbh_db = DBI->connect("DBI:mysql:database=;$host",
$username, $password,
{'RaiseError' => 1});
my $qry_db = "CREATE DATABASE IF NOT EXISTS $db ";
$dbh_db->do($qry_db) or die ("Can't create db!\n");
if ($dbh_db) { say "Database '$db' was created successfully.<br /><br /><br
/>" }
# Disconnect from the database.
$dbh_db->disconnect();
# Connect to the database to create the table and fields.
my $dbh_table = DBI->connect("DBI:mysql:$db;$host",
$username, $password,
{'RaiseError' => 1});
my ($primary_key, @fieldnames);
my $file_path = "../htdocs/carts/sql/data/orders.tsv";
use File::Basename;
my $table = fileparse($file_path, qr/\.[^.]*/);
open (my $file_fh, '<', $file_path) || die ("Can't open $file_path for
reading");
my $count = 0;
my $qry_table = "CREATE TABLE IF NOT EXISTS $table ( ";
while (my $line = <$file_fh>) {
last if $line =~ m/#/; # can place comments below the ### line
chomp $line;
next if (! $line); # skip any blank lines in the file...
$line =~ tr|a-zA-Z0-9,_\t \(\)||cd;
$count++;
my @fields = split (/\t/ , $line);
push ( @fieldnames, $fields[0] );
if ($count == 1) {
$primary_key = $fields[0];
}
my $key = $fields[0];
my $keytype = $fields[1];
if ($keytype) {
$qry_table .= "`$key` $keytype, ";
}
else {
$qry_table .= "`$key` TEXT, ";
}
}
$qry_table .= "PRIMARY KEY (`$primary_key`) ) ENGINE=MyISAM DEFAULT
CHARACTER SET latin1 COLLATE latin1_general_ci;";
$dbh_table->do($qry_table) or die ("Can't create table!\n");
if ($dbh_table) { say "Table '$table' was created successfully.\n" }
close $file_fh;
say $q->end_html();
$dbh_table->disconnect();
__DATA__
Here is the partial contents of the orders.tsv file:
orderid smallint(6) unsigned NOT NULL
orderstatus text
userlogin text
user_uid text
date text
shiptype text
Credit text
minibasket text
totalcalcs text
shipcost text
chargetotal decimal(7,2)
comments text
couponid text
authamt text
PmtAVS text
PmtAuth text
PmtType text
TransID text
coupontype text
IP_Address text
taxexempt text
###
Comments:
The first field listed in this file is the Primary Key.
--
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
http://learn.perl.org/
