DBIx::Simple variable interpolation problem


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(
'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
 
Ad

Advertisements

P

Paul Lalli

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

I'm tickled that you have that much faith in my powers of debugging,
but please don't discount the wealth of other people in this newsgroup
who can help you.
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) ;

http://search.cpan.org/~timb/DBI-1.58/DBI.pm#Placeholders_and_Bind_Values

(I realize you're using DBIx::Simple, but that's just a wrapper around
DBI itself).

You can't use place holders for column names. Place holders are used
for values only.
I realise you've not got the data to check this, but a
full working code snippet is below
EXCELLENT!

('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();

URG. Why are you connecting to the database multiple times? Very
very wasteful. Connect once, then run your queries multiple times.
my $query = $dataSource->query('SELECT key FROM prospect WHERE ? ~* ?', $field, $sc) ;

Like I said, you can't use placeholders for column names. Just put
the columnname directly into your SQL:
my $query = $dataSource->query("SELECT key FROM prospect WHERE $field
= ?", $sc);

Any suggestions why a field name as a variable makes this not work?

Read the URL I pasted above. The db interface needs to know the
actual syntax of the SQL statement before it can be prepared. The
column name is needed to validate the SQL.

Paul Lalli
 
J

Justin C

I'm tickled that you have that much faith in my powers of debugging,
but please don't discount the wealth of other people in this newsgroup
who can help you.

On the last few posts I've made it's been you who's replied first, and
with a reply that has enabled me to progress. I don't discount others
here, I'm a long time reader of this group - but I'm a long way from
being a contributor - and I've seen the knowledge and experience here is
quite amazing.

[snip]
http://search.cpan.org/~timb/DBI-1.58/DBI.pm#Placeholders_and_Bind_Values

(I realize you're using DBIx::Simple, but that's just a wrapper around
DBI itself).

You can't use place holders for column names. Place holders are used
for values only.

Ah. I see.
EXCELLENT!

I've learnt that much from reading this newsgroup :)
URG. Why are you connecting to the database multiple times? Very
very wasteful. Connect once, then run your queries multiple times.

I did have the connect statement before the subroutine that is the above
code. The connect got moved into the subroutine as part of my 'testing',
forgot to move it back up, and before the loop.
Like I said, you can't use placeholders for column names. Just put
the columnname directly into your SQL:
my $query = $dataSource->query("SELECT key FROM prospect WHERE $field
= ?", $sc);

Oooh, no placeholders, but vars/$scalars are OK? That's good news... I see you've changed the quote from singles to doubles, vars not being interpreted inside singles - I suppose my not having read DBI.pm docs (or possibly my being green) I didn't think of that... I did wonder about putting the var inside the quotes but realised it wouldn't work because they were single, not double... never occurred to me that I might be able to use doubles! [duh!]
Read the URL I pasted above. The db interface needs to know the
actual syntax of the SQL statement before it can be prepared. The
column name is needed to validate the SQL.

I will, and I knew there had to be a way of not writing the SQL statement a whole bunch of times.

Thank you for your help with this.

As a side note, I'm really happy with the way my Perl is improving, the tasks aren't getting any easier, but I think that's because each project I take on is more complex. Where once I couldn't see a way of achieving something and therefore didn't attempt it, I am finding that with each project I finish others are presenting themselves, often things I'd never though of. I just wish work afforded more time to spend coding than it does. My job is managing a wholesale company with a staff of thirteen, I can do it in my sleep, I'm fortunate in that we have a very good team at the moment, but there are a lot of other tasks and projects that mean I only get to sit and look at automation (like the above) a couple of afternoons a week. I know I could pay someone else to do it, but I'm not going to give away the one part of my job I really enjoy.

Justin.
 
Ad

Advertisements

P

Paul Lalli

Oooh, no placeholders, but vars/$scalars are OK?

A string is a string is a string. The query() method has no way of
knowing how that string is being created. The Perl interpreter sees a
double-quoted string, searches the string for any variables,
interpolates any it finds, and then passes the string to the query()
method.
That's good news... I see you've changed the quote from singles
to doubles, vars not being interpreted inside singles

Correct. But query() doesn't have any way of knowing what delimiters
were being used, or if there were any variables interpolated into the
string. By the time the argument is passed, it's simply "a string".
- I suppose my not having read DBI.pm docs (or possibly my being
green) I didn't think of that... I did wonder about putting the
var inside the quotes but realised it wouldn't work because they
were single, not double... never occurred to me that I might be
able to use doubles! [duh!]

The only difference in Perl between single-quoted and double-quoted
strings is interpolation. Anywhere you can use single-quoted strings,
you can use double-quoted strings. The only difference is that Perl
will search the double-quoted strings for any variables or escape
sequences.

Paul Lalli
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top