Strange MySQL Problem

tyndyll has asked for the wisdom of the Perl Monks concerning the following question:

Good Afternoon all,

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

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
[download]

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??

Many thanks in advance

Tyndyll

(PS - still new to Perl so any style guidelines also appreciated)

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_IN
+CREMENT 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'} = le
+ngth($_); }
        }
      }else{
        if($cols[$j]{'value'} < length($_)){ $cols[$j]{'value'} = leng
+th($_); }
      }
      $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 a
+nd 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());
}
[download]

Back to Seekers of Perl Wisdom