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

I am using DBI and i have an array that hold strings. Is there a way to have a table with every element of the array being one column? 

$dbh->do("CREATE TABLE my_table ("
     foreach $column(@columns) { "INT column" . $column  }");

[download]

This is a previous effort but i know its wrong. Thanks for the help

Replies are listed 'Best First'.
Re: A large table with DBI
by jZed (Prior) on Feb 10, 2006 at 20:05 UTC
    Try 
    my $colStr = join(' INTEGER,', @colArray) . ' INTEGER';
my $sql = "CREATE TABLE my_table ( $colStr )";
# print $sql # for debugging
$dbh->do($sql);

    [download]
[reply]
[d/l]
Re: A large table with DBI
by duff (Parson) on Feb 10, 2006 at 21:17 UTC

    I'd do like jZed but I think this is a good opportunity to use map: 

    my $table_def = join ',', map { "INT column$_" } @columns;
$dbh->do("CREATE TABLE my_table ($table_def)");

    [download]

    update: thanks to davidrw for the correction

    duff

[reply]
[d/l]
[select]
      join ',' instead of join '' (but y, i like not repeating "int" too)
[reply]
[d/l]
[select]
Re: A large table with DBI
by Tanktalus (Canon) on Feb 11, 2006 at 16:15 UTC

    My personal buzzphrase is to write your code in the domain of the solution rather than the problem. This is why duff's solution is better IMO than jZed's. It reads exactly the same way as what you think it should (which is a definite plus!), but it also reads the same as your question: "every element of the array being the [same type]". To get the type of the column with the column name, you just put "INT" or "INTEGER" in front. So you get "INT $column". You want to do that for each element in the array, so that's a map. You then want to join the columns, so that's a join.

    Of course, if you later decide that there are some CHARs or something, and the default is INT, it's as easy as having a hash with the extra definitions, and modifying the map. jZed's solution, while working for your precise question, won't scale to new requirements (unless those new requirements are more INT columns). 

    my %defs = (
 colC => { type => 'CHAR', len => '(90)' },
 colH => { type => 'TIMESTAMP' }
);
my $table_def = join ',', map { 
 if ($defs{$_}) {
  "$defs{$_}{type} column $_" . ($defs{$_}{len} || '')
 } else {
  "INT column $_"
 }
} @columns;

    [download]
    It's starting to get a bit long... but you could factor it out into its own function, and use map to call it for each function.

    Strictly speaking, you don't seem to need this now. But I save a lot of time by writing code to prepare for the future. In my job, that can save a lot of time during the crunch at the end of a project. YMMV.

[reply]
[d/l]

Back to Seekers of Perl Wisdom