Re: DBIx::Simple variable interpolation problem

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.

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

.

Relevant Pages

  • Re: Syntax error
    ... > DATES in SQL statement must be in the format mm-dd-yyyy or the format ... > Paul wrote: ... >> Dim rs As New ADODB.Recordset 'Dim variables required in procedure ... >> Dim strSql As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL 6.5 sa Password, how to change?
    ... After changing it I can no longer connect to SQL ... > combination of letters, numbers, and symbol characters within the ... > and is not a dictionary word, command name, person's name, or system user ...
    (microsoft.public.sqlserver.security)
  • Re: Another try - inserting datset into sql
    ... Paul, ... but no new records are added to the sql table. ... > rows from the DataTable/DataSet ... > //Generate the 'INSERT' command ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: server error 18456 when trying to login
    ... Do you mean you can connect to your SQL Server Instance now? ... Ekrem Önsoy ... "Paul" wrote in message ... will you check out your Windows Event Logs to have more info about ...
    (microsoft.public.sqlserver.setup)