perlquestion
tyndyll
<p>Good Afternoon all,
<p>I'm having problems with a script that I am writing. The purpose of it is to take a dbf file and convert it into SQL for insertion into a MySQL database. The process is
<code>
Open File
Read in columns and derive CREATE TABLE syntax
While(Reading in dbf file)
Optimise column length
print OUT data into a text file
Load data into database using LOAD INFILE syntax
</code>
<p>The script by and large works, in that it creates the appropriate table and commences the LOAD INFILE, the problem is that it does not complete the job - there are always fewer records in the table than the original file. I have changed the code to print out the statement it sends to the database and entered it straight into the MySQL console, which uploads the correct amount of records..? Can anyone give me any guidance on this please??
<p>Many thanks in advance
<p>Tyndyll
<p>(PS - still new to Perl so any style guidelines also appreciated)
<readmore>
<p><code>
use strict;
use XBase;
use DBI;
use Getopt::Std;
sub buildTable;
sub parse_data;
sub alter_table;
sub query($);
sub mod;
getopt('ijd');
our($opt_i,$opt_j,$opt_d);
if(!(defined($opt_i) && defined($opt_j) && defined($opt_d))){ exit; }
my $host = 'localhost';
my $user = '###user###';
my $pass = '###password###';
my $dbh = DBI->connect("DBI:mysql:database=$opt_d;host=$host", $user, $pass );
if(!defined($dbh)){exit(-1);}
my $table = 'jn'.$opt_j; # Table Name
my @cols = $dbh->tables; # Using cols as a temp holder
foreach(@cols){
$_=~s/`//g;
if($_ eq $table){ exit(-2); }
}
@cols = ();
$opt_i=~s/\\{1,2}/\//g;
my $dbf = new XBase $opt_i or die Xbase->errstr;
my $temp = "";
if($opt_i=~/^(.+)\/(.+?)$/){ $temp = $1."\/".$table."_".$^T; }
mod();
sub buildTable{
my @names = $dbf->field_names;
my @size = $dbf->field_lengths;
my $sql = 'CREATE TABLE '.$table.' (GENID mediumint UNSIGNED AUTO_INCREMENT NOT NULL,';
for(my $i = 0; $i <= $#names; $i++){
$sql .= $names[$i].' char('.$size[$i].') null,';
$cols[$i] = {'name'=> $names[$i], 'type'=>'n','value'=>0};
}
return $sql.='PRIMARY KEY(GENID))';
}
sub parse_data(){
open (TEMP, ">".$temp) or die("Cannot create temp file");
my $cursor = $dbf->prepare_select;
my $i = 1;
my $sql .= '';
my @queries = ();
while(my @data = $cursor->fetch){
my $j = 0;
for(@data){
s/([\'\"\;\\\/\*])/\\$1/g;
if($cols[$j]{'type'} eq 'n'){
if(/^\d$/){
if($cols[$j]{'value'} < $_){ $cols[$j]{'value'} = $_; }
}else{
$cols[$j]{'type'} = 'c';
if($cols[$j]{'value'} < length($_)){ $cols[$j]{'value'} = length($_); }
}
}else{
if($cols[$j]{'value'} < length($_)){ $cols[$j]{'value'} = length($_); }
}
$j++;
}
print TEMP $i.',"'.join('","', @data)."\"\n";
$i++;
}
}
sub alter_table(){
my @new_cols = ();
my $type = "";
for(my $i=0;$i<$#cols;$i++){
if($cols[$i]{'type'} eq 'n'){
$type = pick_type($cols[$i]{'value'}, 'mysql');
#if undefined reset and return false;
}else{
$type = 'varchar('.$cols[$i]{'value'}.')';
}
if(!defined $type){
return undef;
}else{
$new_cols[$i] = ' MODIFY '.$cols[$i]{'name'}.' '.$type.' NULL';
}
if($cols[$i]{'value'} == 0){
$new_cols[$i] = ' DROP COLUMN '.$cols[$i]{'name'};
}
}
return 'ALTER TABLE '.$table.' '.join(',',@new_cols);
}
sub pick_type($$){
my @type = ();
if($_[1] eq 'mysql'){
@type = ( {'name' => 'tinyint' , 's' => 128, 'u' => 255, },
{'name' => 'smallint' , 's' => 32767, 'u' => 65535, },
{'name' => 'mediumint' , 's' => 8388607, 'u' => 16777215, },
{'name' => 'int' , 's' => 2147483647, 'u' => 4294967295, }
)
}else{
return undef;
}
#only checking if smaller than unsigned - later check for negative and floats
for(my $i=0; $i <= $#type; $i++){
if($_[0] < $type[$i]{'u'}){ return $type[$i]{'name'}; }
}
return undef;
}
sub query($){
my $sth = $dbh->prepare($_[0]);
$sth->execute();
}
sub mod{
query(buildTable());
parse_data();
query('LOAD DATA INFILE "'.$temp.'" INTO TABLE '.$table.'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\""
LINES TERMINATED BY "\r\n"');
query(alter_table());
}
</code>
</readmore>
<!-- chromatic: edited out username and password --->