in reply to DBI question

Please edit your post and remove the root password. Then change it on your host. (Unless you already posted a random root password in this post).

Second, your code is quite unsafe. The filename can contain anything, and you don't check

if (open my $fh, "<", $filename) {
    chomp (my $line = <$fh>);
    "$line\t" =~ m/^(?:[A-Za-z]\w*\t)+\z/ or die "Bad header in $filen
+ame";
    $dbh->do ("create table $tablename (".
        (join "," => map { "$_ varchar (20)" } split m/\t/ => $line)."
+)");
    }

[download]

Would be a way to do what you ask, but you didn't e.g. define what types the fields have to be.


Enjoy, Have FUN! H.Merijn

Replies are listed 'Best First'.
Re^2: DBI question
by gogoglou (Beadle) on Jun 23, 2010 at 11:17 UTC
    Thank you both for the answers and suggestons. The problem lies now that the first filed is varchar (txt) but the rest are integers. So I am trying to do it first by creating the table and then adding the additional columns. any suggestions on that? Thanks again
[reply]
      if (open my $fh, "<", $filename) {
    chomp (my $line = <$fh>);
    "$line\t" =~ m/^(?:[A-Za-z]\w*\t)+\z/ or die "Bad header in $filen
+ame";
    my ($fld1, @rest) = split m/\t/ => $line;
    $dbh->do ("create table $tablename (".
        (join "," => "$fls1 text", map { "$_ integer" } @rest).")");
    }

      [download]

      Of course TIMTOWTDI


      Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
      While you could either take the lump approach I've taken above or execute a single CREATE TABLE command followed by a series of ALTER TABLE ... ADD statements is fairly irrelevant from an algorithmic perspective. Both require that you specify the data type at run time. I would suggest using a hash to specify your desired types, and interpolate these into the appropriate SQL statements. Another possibility is a simple if-else clause.

      It sounds like you have a specific file and set of column names in mind - if this is the case, wouldn't it be easier to just craft the SQL rather than trying to do all this autodiscovery?

[reply]
        well the problem is that it not exacytly a specific set of columns. I havea series of tab delimited files, where usually some of the columns differ, that is the reason why I want to create the database depending on the file. Of coyurse it would be much easier to just craft the SQL, but it would be really helpfull if I could automate the procedure. Again thank you very much for your suggestions!
[reply]

In Section Seekers of Perl Wisdom