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

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();

[download]

Replies are listed 'Best First'.
Re: Don't print on the last loop
by Fletch (Bishop) on Jun 13, 2005 at 14:11 UTC

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

    --
    We're looking for people in ATL

[reply]
[d/l]
Re: Don't print on the last loop
by jdhedden (Deacon) on Jun 13, 2005 at 14:13 UTC
    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);
   }

    [download]

    Remember: There's always one more bug.
[reply]
[d/l]

      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();
  }
}

      [download]
[reply]
[d/l]
[reply]
Re: Don't print on the last loop
by jeffa (Bishop) on Jun 13, 2005 at 14:14 UTC

    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);

    [download]

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
Re: Don't print on the last loop
by mrborisguy (Hermit) on Jun 13, 2005 at 14:15 UTC

    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
   ...
}

    [download]

        -Bryan

[reply]
[d/l]
Re: Don't print on the last loop
by merlyn (Sage) on Jun 13, 2005 at 18:35 UTC
[reply]
      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.
[reply]
Re: Don't print on the last loop
by punkish (Priest) on Jun 13, 2005 at 14:48 UTC
    1. build a scalar with your schema: my $schema; $schema .= <your schema step-by-step>;
    2. chop($schema)
    --

    when small people start casting long shadows, it is time to go to bed
[reply]
[d/l]
[select]
Re: Don't print on the last loop
by tphyahoo (Vicar) on Jun 13, 2005 at 14:23 UTC
    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++;
   }

    [download]
    Actually, the other solutions are a lot nicer, but... you know... timtowtdi... :)
[reply]
[d/l]

Back to Seekers of Perl Wisdom