Greetings
I've encountered a problem while writing a web-based app that stores information in a database.
I have a package file which handles the database side, and a perl script which handles the CGI front end.
The trouble is: I want to dynamically select an ORDER BY value by using a CGI popup menu, but the value seems to be ignored by the section that interfaces with DBI.
Here's some code to (hopefully) clarify: (this isn't production code, but it recreates the problem. Tablenames, passwords etc changed to protect the guilty)

foo.pl

#!/usr/bin/perl -w

use strict;
use Apache::Registry;
use Apache::DBI;
use lib ".";
use Bar;
use CGI::Carp qw(fatalsToBrowser);
use CGI qw(:standard -no_xhtml);

my $q = new CGI;
$q->default_dtd('-//W3C//DTD HTML 4.0 Transitional//EN');

my $order_by = param("order_by") || "part_no";

print header(-expires=>"-1d");
print start_html(-title=>"Test");

if(exists $ENV{"MOD_PERL"}) {
        print "Running under mod_perl<br>";
} else {
        print "<b>Not</b> Running under mod_perl<br>";
}
print start_form;
print popup_menu(-name=>"order_by", -values=>["column1", "column2", "c
+olumn3", "column4"]);
print submit;
print end_form;
Bar->get_top_level_kit($order_by);

print end_html;
Bar->close;
[download]

Bar.pm

package Bar;
use strict;
use Apache::Registry;
use Apache::DBI;
use DBI;
use Exporter;
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);

$VERSION     = 1.00;
@ISA         = qw(Exporter);
@EXPORT      = qw(get_top_level close);
@EXPORT_OK   = qw();
%EXPORT_TAGS = ();

my $dbname = "name";
my $dbuser = "user";
my $dbpass = "pass";
my $dbhost = "localhost";
my $dbport = 3306;
my $dsn    = "DBI:mysql:database=$dbname;host=$dbhost;port=$dbport";

our $db = DBI->connect($dsn, $dbuser, $dbpass)
        or die "Couldn't connect to the database: $!\n";

sub get_top_level_kit {
        my ($package, $order_by) = @_;
        print $order_by;
        my $top_level_query    = "SELECT row_id FROM table WHERE other
+_column=0 ORDER BY ?";
        my $top_level_sth      = $db->prepare($top_level_query);
        $top_level_sth->execute($order_by)
                or die "Unable to execute $top_level_query: " . $top_l
+evel_sth->errstr . "\n";
        while(my @current = $top_level_sth->fetchrow_array) {
                print "Got: $current[0]<br>\n";
        }
        $top_level_sth->finish();
}

sub close {
        $db->disconnect;
}
1;
[download]

The problem occurs when run as a normal CGI and when under mod_perl - I thought it might variable persistence or somesuch.
When I execute foo.pl from the browser, although the reported order_by value gets as far as the get_top_level_kit sub, it isn't being used in the execution of the SQL statement, because the order of the kit returned is the same every time.
I've tried hardcoding values for order_by into the SQL query, and I get the expected result (different order depending on value).
Can anybody see if I'm being stupid, and where I'm doing it?
davis