| Chas. Owens 2007-11-16, 7:01 pm |
| On Nov 16, 2007 1:52 AM, Pradeep Kumar <prady184u@gmail.com> wrote:
snip
> NOW I WANTED TO KNOW THE PROCEDURE FOR TAKING THE CODE AS INPUT FROM A
> TEXT FILE & CREATE THE SPECIFIED TABLE
snip
Don't yell.
Warning, this is untested code and may blow up in your face. Second
warning, this code is very inefficient due to its use of AutoCommit,
this should be set to 0 and you should be committing after every
create table and after every thousand or so inserts (I was just too
lazy to put that in). Third warning, I have not scanned the code for
stupidity (I already see at least one: I don't have to use $1, $2, I
could do the assignment in the if).
#!/usr/bin/perl
use warnings;
use strict;
my $dbh = DBI->connect(
'dbi:mysql:dbname=foo',
'',
'',
{
AutoCommit => 1,
ChopBlanks => 1,
RaiseError => 1,
PrintError => 0,
}
) or die DBI->errstr;
my @cols;
my $insert;
while (<DATA> ) {
chomp;
if (/^(\w+)>(.*)/) {
my ($tabname, $tabbody) = ($1, $2);
$dbh->do("CREATE TABLE $tabname ( $tabbody )");
@cols = map { (split " ")[0] } split ",", $tabbody;
$insert = $dbh->prepare("insert into $tabname (" .
(join ", ", @cols) . ") values (" .
(join ", ", (('?') x @cols)) . ")");
next;
}
die "corrupt file" unless @cols;
$insert->execute(split ",");
}
$dbh->disconnect;
__DATA__
Details> name VARCHAR(12), id INTEGER, area VARCHAR(32)
prady,2039,india
sandy,2398,india
sam,1234,aussie
Rob,2345,Eng
extraDetails>name1 VARCHAR(12), name2 VARCHAR(12)
prady,sandy
sandy,Rob
Rob,sam
sam,prady
|