I was trying to do something similar; creating a table with the column names and corresponding types that the user input (ie I assume that they know enough SQL to know what the column types should be). I found that it was easier (though arguably less elegant) to just store the column names and types in a scalar variable with the pairs just seperated by commas. You can then plug this variable straight into the SQL CREATE TABLE command.
#Get the input from the user, I have this in a subroutine which is cal
+led repeatedly until the user enters 'done'
print "Please enter the name of the column, when you are finished type
+ 'done':\n";
chomp(my $column_name=<>);
print "Please enter the column type:\n";
chomp (my $column_type=<>);
if (defined $fields) {
$fields = "$fields, $column_name $column_type";
} else {
$fields = "$column_name $column_type";
}
#Otherwise $fields begins with ', ' which is not what we want
my $create_table = "CREATE TABLE $table_name ($fields)";
#Put this in a variable so that it is all interpolated before being pa
+ssed to the SQL
my $sth=$dbh->do($create_table) or die "Could not prepare table $check
+table: $DBI::errstr\n";
print "Table $table_name created\n"
This method is somewhat adapted from this article. Hope it makes sense =)
|