in reply to Re: Re: Re: selecting again from a mysql database
in thread selecting again from a mysql database

Well I see you couldn't come up a situation where select * is better than the more explict select col_a, col_b, ... so I will provide you with one. This script , given a table name, creates control files for Oracle's sqlldr. 
...
my $SQL = <<SQL;
Select
        *
from
        $table
SQL

$dbh->{LongReadLen} = 65500;
my $sth = $dbh->prepare($SQL) || die $dbh->errstr;
$sth->execute();
my $colNames = $sth->{'NAME'};
my $nColumns = $sth->{'NUM_OF_FIELDS'};

my $colString;

for my $colName ( @$colNames ) {
        $colString .= "\n\t$colName,";
}
chop($colString);

print <<DOIT;
OPTIONS (ROWS=1)
LOAD DATA
INFILE *
REPLACE INTO TABLE $table
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
DOIT

print "$colString\n";

print<<DOIT;
)
BEGINDATA
DOIT
while ( my @r = $sth->fetchrow_array ) {
        print join ( '|', map ( $_ , trim( @r )) );
        print "\n";
}
$sth->finish || die;
$dbh->disconnect;
...

[download]
Would your solution to this be have huge if/elsif statement that would have a different $SQL string for each table in the database? 
# can't use select * so ...
if ( $table eq "MY_FIRST_TABLE" ) {
    $SQL = "select blah_1, blah_2 from MY_FIRST_TABLE";
} elsif ( $table eq "MY_2ND_TABLE" ) {
    $SQL = "select another_col, dob from MY_2ND_TABLE";
} ...

[download]
Or would you choose to access the database twice, once to do your describe table (which BTW is not portable to other DBMSs) and a second time to get the data from the table? Why not just access the database once?

Plankton: 1% Evil, 99% Hot Gas.

Replies are listed 'Best First'.
Re: Re: Re: Re: Re: selecting again from a mysql database
by hardburn (Abbot) on Nov 25, 2003 at 20:51 UTC

    Would your solution to this be have huge if/elsif statement . . .

    No, I'd put the columns I wanted for each table in an HoA:

    my %COLUMNS = (
    MY_FIRST_TABLE => [ qw( blah_1 blah_2 ) ], 
    MY_2ND_TABLE   => [ qw( another_col dob ) ], 
    . . . 
);

# $table defined elsewhere
my $SQL = 'SELECT ' . join(',', @{ $COLUMNS{$table} }) . " FROM $table
+";

    [download]

    Clean, efficient, no SELECT *.

    Update: Added some missing whitespace in the SQL generated.

    . . . describe table (which BTW is not portable to other DBMSs) . . .

    MySQL's describe table isn't, but DBI's column_info method is. (Though I wouldn't use that particular solution).

    ----
    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    : () { :|:& };:

    Note: All code is untested, unless otherwise stated

[reply]
[d/l]
[select]
      This would not work ... if a column is added to a table you have update your script because it breaks. You would have to modify your script even it some added a table. This might be acceptable for tiny databases of a few tables, but what about large databases with hundereds of tables?

      Plankton: 1% Evil, 99% Hot Gas.
[reply]
      A reply falls below the community's threshold of quality. You may see it by logging in.

In Section Seekers of Perl Wisdom