Re^5: Sequential data read in MySQL/Perl

[Marshall]

Ok, does this work for you? Row number would be an auto increment field, so doesn't need to be a field in the SQL INSERT statement.

#!/usr/bin/perl
use strict;
use warnings;

my @row;

# SQL prepare statement goes here.

while (my $line = <DATA>)
{
    next if $line =~ /^\s*$/; #skip blank lines
  
    if ($line =~ /^\s*RECZ/)  #end of record
    {
       print "@row\n";  #would be DB row insert
       @row = ();       #start new row
    }
    else
    {
       my @data = (split(' ',$line))[1,2];
       push @row,@data;
    }
}

=prints
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

Update: be careful with that submit button (labeled "stumbit") You made a duplicate post that just causes "noise" that a Janitor has to clean up.

[justin423]

Can this be converted into SQL so that it writes out the data to a table instead of a print file?

[Marshall]

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

[Marshall]

Sure it can. However at this moment we have no idea of what DATA1,2,3,4 really are? Dates,integers,floats,characters? Figuring that stuff out is important. In the DB, what can go in each column matters.

BTW, what did you do to create this temporary table?

[chacham]

However at this moment we have no idea of what DATA1,2,3,4 really are

So, just store them as varchars until you figure it out. While numeric types enforce the datum to be numeric and date types enforce that it be a date, varchar has no real constraints other than length and that it be passed as a string (assuming it doesn't implicitly convert it for you).