Re: [INTERFACES] Dumb Perl-related question

Brendan McKenna wrote:
> 
> Hi,
> 
>         This question is so dumb that I am embarassed to ask it, but I can't
> seem to figure it out myself.  I have a select statement with a where clause,
> where I am trying to select all values from the database that match a string
> literal.  Only whenever I try to specify a string literal in the query, it
> gives me a syntax error on the first word in the literal.
> 
>         What I'm doing looks like this:
> 
>    $query   = "select recipe_name, occasion, num_served, prep_time, " .
>               "freezable, instructions "                              .
>               "from recipe "                                          .
>               "where recipe_name = '$recipe';";
>    $result  = $dbconn->exec($query);
>    $rstatus = $result->resultStatus;
>    if ($rstatus != PGRES_TUPLES_OK && $rstatus != PGRES_EMPTY_QUERY) {
>       croak "getRecipe:  Database error during query: " .
>             $dbconn->errorMessage;
>    }
Try changing the $query assignment to:
   $query   = <<QUERY_STOP;   select recipe_name, occasion, num_served, prep_time,           freezable, instructions
     from recipe          where recipe_name = '${recipe}';   QUERY_STOP
 

The two significant changes are to use a here document and put brackets
around your
variable name.  The first change, I believe, simply makes the SQL code
more readable,
and it should cause you to have to escape less characters (although in
your select
query there were no escapes necessary).  The bracketing of the variable
name
will insure that perl will interpret the variable properly.


> 
>         Every time, it prints out the following error:
> 
> getRecipeEquipment: Database error during query: ERROR:  parser: parse error at or near "no" at ./recipeDisplay line
33
> 
>         The recipe name I am using is 'No Such Recipe'.
> 
>         There would only be one row ever returned, since recipe_name is the
> primary key of the recipe table.
> 
>         I am using perl 5.005_55, postgreSQL 6.4.2 (with the Pg module which
> is distributed with it).
> 
>         I have tried every different type of quote that my keyboard will
> allow me to enter, casts, you name it, I think I've tried it.  Well,
> obviously not, since I am certain that this MUST work somehow or another.
> About half of the things I've tried work fine in psql, just not in the Perl
> interface.
Single quotes were what you should use around the SQL literal.  Double
quotes should
be used (if not using a here document) around the whole query, if you
wish your 
variable to be replaced by its contents.

I hope this helps...james
> 
>         Any help would be greatly appreciated.
> 
>                                                         Brendan
> --
> Brendan McKenna
> Technical Director                      Phone: +353-(0)61-338177 x4143
> W3 Services Ltd.                          Fax: +353-(0)61-338065
> Innovation Centre                       Email: brendan@w3s.ie
> National Technological Park
> Limerick
> Ireland