Don't print on the last loop

[omega_monk]

I am trying to pull the schema out of an access database, in order to create the same schema in a mysql database. I am having one problem. I am adding a comma(and newline) to the end of each line that is output in the while loop of the column_info db connect. I need to not add the comma on the last iteration of that loop. I have looked everywhere, but I am unable to find the answer. Also if there is a "better" way that I could have done this, please edify me. Thanks.

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

my $dsn = 'TEST';
# open the database handle using the ODBC driver to the TEST dsn
my $dbh = DBI->connect("DBI:ODBC:$dsn") or die "Could not connect to t
+he database: " . DBI->errstr;

# get the table info from the dbase handle
my $sth = $dbh->table_info("","","","TABLE") or die "Could not get the
+ table info: " . DBI->errstr;
my @tables;

# iterate through the rows of tables, and push the names of the tables
+ to an array(@tables)
while (my ($catalog, $schema, $tab) = $sth->fetchrow_array()) { push(@
+tables,$tab); }

# iterate through the tables names, and print the table name, the colu
+mns , and the type/size of that column, etc
foreach my $table (@tables) {
   print "create table $table (\n ";
   $sth = $dbh->column_info("", "",  my $t=$table, "");
   while ( my ( $cat, $schem, $tn, $cn, $dt, $tyn, $cs, $bl, $dd, $npr
+, $nul) = $sth->fetchrow_array()) { 
      print "\t$cn ";
      if (($tyn eq 'DATETIME') || ($tyn eq 'DOUBLE')) {
         print "$tyn";
      } else {
         print "$tyn($cs)";
      }
      print " not null" if ($nul == 0);
      # need to put a condition on adding the comma to not add the com
+ma on the last loop.
      print ",\n";
      
   }
   print ');'."\n\n";
}

# disconnect the database handle
$dbh->disconnect();

[Fletch]

One way would be to push the results onto an array and use join to print everything after your loop is done.

[jdhedden]

The easiest way is to move the comma printing to the front of the loop, and add a flag to control printing it:

   my $add_comma=0;
   while ( my ( $cat, $schem, $tn, $cn, $dt, $tyn, $cs, $bl, $dd, $npr
+, $nul) = $sth->fetchrow_array()) { 
      if ($add_comma++) {
          print ",\n";
      }
      print "\t$cn ";
      if (($tyn eq 'DATETIME') || ($tyn eq 'DOUBLE')) {
         print "$tyn";
      } else {
         print "$tyn($cs)";
      }
      print " not null" if ($nul == 0);
   }

[calin]

Another idea is to unroll the first iteration of the loop. This way you don't have to test for a flag on subsequent iterations. I believe this is the correct approach optimisation-wise - if one condition is true only once and then guaranteed false for the next 1.000.000 times, checking it programatically (and doing other stuff like modifying the flag variable) 1.000.001 times is a waste. Pseudocode:

if ( CONDITION ) {
  do_iteration();
  while ( CONDITION ) {
    print ",\n";
    do_iteration();
  }
}

[omega_monk]

jdhedden++ Thanks, you guys rock.

[jeffa]

If the code is not terribly complex, you can always use join. If it is, consider pulling out the last element (with pop) and saving it for after the loop. You will most likely want to encapsulate the body of the loop into a subroutine. Something like this:

my $last = pop @tables;

foreach my $table (@tables) {
    process($dbh,$table);
    print ",\n";
}

process($dbh,$last);

[mrborisguy]

Another way that still uses print statements (probably less memory than storing as an array or string), would be to set up a counter initially, then print a comma at the beginning of the loop as long as the counter isn't 0.

my $counter = 0;
while(...) {
   print ",\n" if $counter;
   $counter++;
   ...
   # and the rest
   ...
}

    -Bryan

[merlyn]

That's one of the reasons I like Template Toolkit's idea of the "loop control variable". You can ask "IF loop.last ...". Very slick.

[omega_monk]

Thanks for the pointer here.

I checked it out a bit, and I am thinking that this will suite another project that I will be starting on soon. So, thanks again.

[punkish]

1. build a scalar with your schema: my $schema; $schema .= <your schema step-by-step>;
2. chop($schema)

[tphyahoo]

Maybe something along these lines? (Untested):

my @fields = my ( $cat, $schem, $tn, $cn, $dt, $tyn, $cs, $bl, $dd, $n
+pr, $nul) = $sth->fetchrow_array()
my $num_fields = @fields; #0-indexed number of fields, ie 10 if there 
+are 11 fields.

$field_index = 0;
while ( $cat, $schem, $tn, $cn, $dt, $tyn, $cs, $bl, $dd, $npr, $nul )
+ { 
      print "\t$cn ";
      if (($tyn eq 'DATETIME') || ($tyn eq 'DOUBLE')) {
         print "$tyn";
      } else {
         print "$tyn($cs)";
      }
      print " not null" if ($nul == 0);
      # need to put a condition on adding the comma to not add the com
+ma on the last loop.
      if ( $field_index < $num_fields) {
          print ",\n";  
      }
      $field_index++;
   }

Actually, the other solutions are a lot nicer, but... you know... timtowtdi... :)