J
Justin C
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(
'dbig: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
"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(
'dbig: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