Re: DBIx::Simple variable interpolation problem

In article <1183728033.340188.296130@xxxxxxxxxxxxxxxxxxxxxxxxxxx>, Paul Lalli wrote:
On Jul 6, 6:29 am, Justin C <justin.0...@xxxxxxxxxxxxxx> wrote:
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.

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

Ah. I see.

I realise you've not got the data to check this, but a
full working code snippet is below

EXCELLENT!

I've learnt that much from reading this newsgroup :)

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

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.

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

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


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.

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.

--
Justin C, by the sea.
.

Relevant Pages