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

If you use select * your code can continue to work

Sure it could. It's just much more fragile. It'll take you less than half a minute to look up what fields you need, and the gain is that you don't have to worry about table mutation.

if someone where to add a column or change the order of the columns your code could break even if you did not do a select * .

Huh? A SELECT id, col1, col2 FROM . . . will be safe as long as id, col1, and col2 (plus whatever fields you might stick in your WHERE clause) exist in the table. Doesn't matter what order they're in the database, as you're guarenteed that the they'll come out in the order you specified (unless your database is broken, in which case you have other problems). Can you give a situation where the above could be broken by adding or reordering columns?

----
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

Replies are listed 'Best First'.
Re: Re: Re: Re: selecting again from a mysql database
by Plankton (Vicar) on Nov 25, 2003 at 20:24 UTC
    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.
[reply]
[d/l]
[select]

      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