I hadn't worked with the DBI for 5 years, but I got the basics working as shown below. I used an SQLite database. SQLite is included in my Perl 5.20 distribution so I didn't have to install anything. I took chacham's suggestion and just used varchars so that I could get on with the demo. You probably have other data type.
Update: You will notice that for the import, I started a new transaction and did the commit after slamming in the data. Here it doesn't matter, but your real DB will be large and this will reduce the creation time considerably.
#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use DBI qw(:sql_types);
my $dbfile = "./Testing.sqlite";
if (-e $dbfile) {unlink $dbfile or die "Delete of $dbfile failed! $!\n
+";}
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError =
+> 1})
or die "Couldn't connect to database: " . DBI->errstr;
$dbh->do ("CREATE TABLE weird
( id integer PRIMARY KEY AUTOINCREMENT,
common varchar(10) DEFAULT '',
data2 varchar(10) DEFAULT '',
data3 varchar(10) DEFAULT '',
data4 varchar(10) DEFAULT '',
data5 varchar(10) DEFAULT '',
data6 varchar(10) DEFAULT '',
data7 varchar(10) DEFAULT '',
data8 varchar(10) DEFAULT ''
);
");
$dbh->do("BEGIN");
import_data();
$dbh->do("COMMIT");
my $sth = $dbh->prepare('SELECT * FROM weird');
$sth->execute;
my $refAoA = $sth->fetchall_arrayref; #ref to Array of Arrays
print Dumper $refAoA;
=Dumper prints out....
$VAR1 = [
[
1, <-- the auto-incremented primary key
'DATAX',
'DATA2',
'DATA3',
'DATA4',
'DATA5',
'DATA6',
'DATA7',
'DATA8'
],
[
2,
'DATAY',
'DATA2',
'DATA3',
'DATA4',
'DATA5',
'DATA6',
'DATA7',
'DATA8'
]
];
=cut
sub import_data
{
my $add = $dbh->prepare("INSERT INTO weird ( common, data2,data3,da
+ta4,
data5,data6,data7,data
+8)
VALUES(?,?,?,?,?,?,?,?)");
my @row;
while (my $line = <DATA>)
{
next if $line =~ /^\s*$/; #skip blank lines
if ($line =~ /^\s*RECZ/)
{
#print "@row\n";
$add->execute(@row); #DBI insert
@row = (); #start new row
}
else
{
my @data = (split(' ',$line))[1,2];
push @row,@data;
}
}
}
=each row looks like this....
DATAX DATA2 DATA3 DATA4 DATA5 DATA6 DATA7 DATA8
DATAY DATA2 DATA3 DATA4 DATA5 DATA6 DATA7 DATA8
=cut
__DATA__
REC1 DATAX DATA2 1
REC2 DATA3 DATA4 2
REC3 DATA5 DATA6 3
REC4 DATA7 DATA8 4
RECZ 5
REC1 DATAY DATA2 6
REC2 DATA3 DATA4 7
REC3 DATA5 DATA6 8
REC4 DATA7 DATA8 9
RECZ 10
|