Re: varchar to char in table with several varchar's

[Paul DuBois]

At 9:32 AM +0100 12/12/01, Willem Bison wrote:
>I have a table with several columns of type 'varchar'. How can I change all
>columns to fixed width char's ? Doing a 'alter' from varchar to char has no
>effect since the column is changed back to varchar.

You have to change them all at ones in the same ALTER TABLE statement.

Here's a Perl DBI function that takes a database handle and a table
name, and returns the ALTER TABLE statement to do what you want.
(You have to execute the statement yourself; the return value is undef
if the table contains no applicable columns.)

sub alter_to_char
{
my ($dbh, $tbl_name) = @_;
my ($sth, $str);

     $sth = $dbh->prepare ("SHOW COLUMNS FROM $tbl_name");
     $sth->execute ();
     while (my @row = $sth->fetchrow_array ())
     {
         if ($row[1] =~ /^varchar/)      # it's a VARCHAR column
         {
             $row[1] =~ s/^var//;
             $str .= ",\n\t" if $str;
             $str .= "MODIFY $row[0] $row[1]";
             $str .= ($row[2] eq "YES" ? "" : " NOT") . " NULL";
             $str .= " DEFAULT " . $dbh->quote ($row[4]);
         }
     }
    
     $sth->finish ();
     $str = "ALTER TABLE $tbl_name\n\t$str" if $str;
     return ($str);
}


Or, if you wanna go in the other direction:

sub alter_to_varchar
{
my ($dbh, $tbl_name) = @_;
my ($sth, $str);

     $sth = $dbh->prepare ("SHOW COLUMNS FROM $tbl_name");
     $sth->execute ();
     while (my @row = $sth->fetchrow_array ())
     {
         if ($row[1] =~ /^char/)     # it's a CHAR column
         {
             $row[1] = "var" . $row[1];
             $str .= ",\n\t" if $str;
             $str .= "MODIFY $row[0] $row[1]";
             $str .= ($row[2] eq "YES" ? "" : " NOT") . " NULL";
             $str .= " DEFAULT " . $dbh->quote ($row[4]);
         }
     }
    
     $sth->finish ();
     $str = "ALTER TABLE $tbl_name\n\t$str" if $str;
     return ($str);
}

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php