Abigail

You asked the same question yesterday (selecting from a mysqldatabase). You got excellent advice and yet you come again here with the same shameless code.

Why don't you start mending what you were told yesterday? somebody has even written the code for you!

As for your specific problem, here it comes:

Separate your programming logic from its visual interface.

What do you say? Is it too abstract? Well, seeing what you did with the practical advice you received yesterday, I thought I would try with something else.

Two things:

• Never use SELECT * in code. It's for interactive use. The only exception is SELECT COUNT(*). (Reason: If the table gets a new column, or the order of the columns are changed, your code will break. Also, it's slow).
• Always use placeholders

"Never use SELECT * in code."

Along the lines of "never say never", I find this oft repeated bit of advice completely misleading. Certainly one can get in trouble with "SELECT *" and should know about its pitfalls. But there are many situations in which it is perfectly fine to use it and others in which it is the *only* thing to use.

Example 1: if you are using bind_columns (which is, after all, the fastest way to fetch), then this is fine:

    my $sth = $dbh->prepare("SELECT * FROM foo");
    $sth->execute;
    my %row;
    $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
    while ($sth->fetch) {
        print "$row{region}: $row{sales}\n";
    }
[download]

Example 2: You want to display a table to examine it's structure visually, without knowing its structure in advance.

Should one be wary of code that expects the structure of a table to remain unchanged? Yep. Should one therefore never use "SELECT *"? Nope.

if you are using bind_columns (which is, after all, the fastest way to fetch), then this is fine

That's a reasonable example. It's still slower at the database level, though.

You want to display a table to examine it's structure visually, without knowing its structure in advance.

Looking over the structure of the data falls under interactive use (though you're probably better off using something like MySQL's describe [table] command), which is the reason why SELECT * works at all.

If you're writing code for a real application, you ought to know what the columns are. If you don't, look them up. If you can't look them up, you've got other problems (either poor documentation or bad internal politics). The only exception I can think of to this is when you don't know what table you'll be using in advance (though I'm not sure about even that case).

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

Your reason for not using select * is not entirly true. If you use select * your code can continue to work (all depeds on how you wrote your code). If you need to check what the column order is you can use ...

$colNames = $sth->{'NAME'};
$nColumns = $sth->{'NUM_OF_FIELDS'};
[download]

Also, 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 * . Again it all depends on how you wrote your code.



Plankton: 1% Evil, 99% Hot Gas.

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

We're not talking about hypothetical code. bory uses $sth->fetchrow_array which is a problem.

   while (my ($identifier)=$sth->fetchrow_array ) {
[download]

   my $sth=$dbh->prepare(qq{
      SELECT name,
             state,
             phone
      FROM user
   });
   $sth->execute();
   while (my ($name, $state, $phone)=$sth->fetchrow_array ) {
     ### now you can use $name, $state, and $phone ###
     print "User name:  $name\n";
     print "     state: $state\n";
     print "     phone: $phone\n";
   }

   $sth->finish;
[download]

Yes, `Stat`, `Project` and `Version` are being used as column names in the SQL query. No, the back-ticks do not have special meaning for Perl in this case, because the query text (with the back-ticks) is enclosed in double-quotes.

Back-ticks in an SQL statement are sometimes needed around a column name if that name also happens to be a reserved keyword in SQL.

I just learned this myself recently, when I tried to inspect a table that a co-worker had created, with a column called "group" -- which is a natural enough name for a column, but you can't do a query like "select group from my_table" unless you put back-ticks around that column name, because "group" is a reserved word (as in "group by").

fetchrow_array returns an array and it is assigning into an array. This is a pretty standard idiom for reading single columns. The while loop even distinguishes between an empty array for end of the data set and undef for NULL value.

my ($identifier)=$sth->fetchrow_array();

my @array = $sth->fetchrow_array();
($identifier) = @array;
[download]

example of placeholders:

my $sth=$dbh->prepare("SELECT * FROM ddts WHERE `Stat`=? AND `Project`
+=? AND `Version`=?");
$sth->execute("T","Def",join(@version));
[download]

I'm not sure on the join, but then i wasn't sure from your code what you expected @version to return. Other things that will help you would be to format your code in a way that you can see where the blocks are starting and ending

y @compile=param ('compile');
my $dbh=DBI->connect($dsn,$user,$password,) 
             or die "Error $DBI:: error connecting to $dsn";
my $sth=$dbh->prepare("SELECT * FROM ddts WHERE `Stat`=? AND `Project`
+=? AND `Version`=?");
$sth->execute("T","Def",join(@version));

print "<table border=2>";

while (my ($identifier) = $sth->fetchrow_array ) {
    foreach $elem (@compile) {
        if ($elem eq $identifier){
            print "<b><center>1696</center></b>";
            print "<tr><td>$identifier</td></tr>";
        }
    }
}

print "</table>";
$sth->finish;
[download]

Upon formating that I found the if that was burried in there. The point is format it nice and you will prob be able to see some of the stuff yourself, and it makes it easier for anyone else to help you. It looks like you want that if to control when the table element is printed. Little note on HTML, that <b><center>1696</center></b> needs to be inside a <td> or outside the table all together. If its in the middle of a table like that but not in a <td> then there is no telling how different browsers will show it. I would determine under what circumstances you want it printed and move it up about your print "<table border=2>"; so that it is printed before the top of the table. Thats all just guess though since i'm not realy clear when or where you want it to print.