DBIx::Simple variable interpolation problem


Yes, it's me again with more of the same... Maybe I should just say
"Thank you Paul" now?

I'm querying a database, trying to use a broad query so data from almost
any field can be used and all fields are searched to find that data, the
search should then return the record numbers that match.

I have the following query statement:

my $query = $dataSource->query('SELECT key FROM prospect WHERE ? ~* ?', $field, $sc) ;

$field is pulled from an array which is a list of the fields to be
searched. $sc is the search criteria. This isn't matching/returning
anything. If I, however, replace the first ? with the actual field name
I get results. I've tried this code with a print statement just before
$field is called, and it contains what I expect.

I realise you've not got the data to check this, but a
full working code snippet is below ('working' meaning that should I
change the first ? to a field name then I get results). Anyway, here's
the code I have:

#!/usr/bin/perl

use warnings ;
use strict ;
use DBIx::Simple ;

my ($dataSource, @results) ;
sub db_connect {
my ( $user, $password) = ("justin", "grobble") ;
$dataSource = DBIx::Simple->connect(
'dbi:Pg:database=prospects', $user, $password,
{ RaiseError => 1 , AutoCommit => 1 }
) or die DBI::Simple->error ;
}

while (@ARGV) {
my $sc = pop @ARGV ; # Search criteria
my @dbFields = qw/contact co_name ad1 ad2 ad3 town county p_code country tel1 tel2/ ;
foreach my $field (@dbFields) {
db_connect();
my $query = $dataSource->query('SELECT key FROM prospect WHERE ? ~* ?', $field, $sc) ;
while (my @row = $query->list){
push @results, $row[0];
}
}
}

foreach (@results) {
print $_, "\n";
}

Any suggestions why a field name as a variable makes this not work? Or
is there something wrong with my $field that I'm not seeing?

Thank you for any help you can give with this.

Justin

--
Justin Catterall www.masonsmusic.co.uk
Director T: +44 (0)1424 427562
Masons Music Ltd F: +44 (0)1424 434362
For full company details see our web site
.