DBIx::Simple variable interpolation problem

Discussion in 'Perl Misc' started by Justin C, Jul 6, 2007.

  1. Justin C

    Justin C Guest

    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
     
    Justin C, Jul 6, 2007
    #1
    1. Advertising

  2. Justin C

    Paul Lalli Guest

    On Jul 6, 6:29 am, Justin C <> 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.

    > 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
     
    Paul Lalli, Jul 6, 2007
    #2
    1. Advertising

  3. Justin C

    Justin C Guest

    In article <>, Paul Lalli wrote:
    > On Jul 6, 6:29 am, Justin C <> 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.
     
    Justin C, Jul 7, 2007
    #3
  4. Justin C

    Paul Lalli Guest

    On Jul 7, 6:38 am, Justin C <> wrote:
    > In article <>, Paul Lalli wrote:
    > >> 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?


    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
     
    Paul Lalli, Jul 7, 2007
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Al Tobey
    Replies:
    0
    Views:
    553
    Al Tobey
    Oct 24, 2003
  2. I & L Fogg
    Replies:
    1
    Views:
    235
    Ben Morrow
    Jun 21, 2004
  3. phrankster

    Variable Interpolation with %%variable

    phrankster, Aug 9, 2005, in forum: Perl Misc
    Replies:
    9
    Views:
    154
    James Taylor
    Aug 14, 2005
  4. Justin Catterall

    DBIx::Simple, authentication fails

    Justin Catterall, Jun 27, 2007, in forum: Perl Misc
    Replies:
    2
    Views:
    113
    Justin C
    Jun 27, 2007
  5. Justin C
    Replies:
    5
    Views:
    227
    Justin C
    Jun 29, 2007
Loading...

Share This Page