Selecting test with quote using perl?

Selecting test with quote using perl?

Post by Hemant Sha » Sun, 22 Jun 2003 02:36:06



Folks,

  I am having trouble select a CHAR column that has (') single quote in it
  using perl. It is also a problem while running CLP from command line.
  So I would prefer generic solution, but perl specific solution will also
  work.

  I am using DBI, DBD::DB2 modules.

  Following is the fragment of the perlcode I am running:

  ---------cut-------------cut-------------cut-------------cut----

sub ExactSearchForSentence
{
   my($String) = shift;
   my($Language) = shift;
   my(%Sentencehash) = ();
   my($SqlStmt) = "SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
                     FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
                     WHERE S.SENTENCE = '$String'
                     AND LANGUAGE = '$Language'
                     AND S.SRC_NUM = N.SRC_NUM
                     FETCH FIRST 100 ROWS ONLY FOR READ ONLY";

   print "DEBUG: $SqlStmt\n";
   eval
   {
      $SqlStmtHdl = $DbHandle->prepare($SqlStmt);
      $SqlStmtHdl->execute();
   };


   {

   }

   while(($FileName,$Sentence) = $SqlStmtHdl->fetchrow())
   {
      $Sentencehash{$FileName} = $Sentence;
   }
   $SqlStmtHdl->finish();

   return(%Sentencehash);

Quote:}

$String = "This field is required if the 'transaction fee use code' is a '2'.";
%SentenceHash = ExactSearchForSentence($String, 'EN1');
foreach $Key (keys %SentenceHash)
{
   print "$Key\t[$SentenceHash{$Key}]\n";

Quote:}

  ---------cut-------------cut-------------cut-------------cut----

  When I run the perl script I get following error:

DEBUG: SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
                     FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
                     WHERE S.SENTENCE = 'This field is required if the 'transaction fee use code' is a '2'.'
                     AND LANGUAGE = 'EN1'
                     AND S.SRC_NUM = N.SRC_NUM
                     FETCH FIRST 100 ROWS ONLY FOR READ ONLY
lidp6|ExactSearchForSentence: DBD::DB2::db prepare failed: [IBM][CLI Driver][DB2/6000] SQL0104N  An unexpected token "transaction" was found following "is required if the '".  Expected tokens may include:  "UNION".  SQLSTATE=42601

In the above example I am setting $String in the code, but In actual program
it is passed from a Perl/TK program and it could come from different sources.

Here is the version of perl I am running on AIX 5.2.

# perl -v

This is perl, v5.8.0 built for aix-thread-multi

Copyright 1987-2002, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using `man perl' or `perldoc perl'.  If you have access to the
Internet, point your browser at http://www.perl.com/, the Perl Home Page.

Thanks.

--
Hemant Shah                           /"\  ASCII ribbon campaign

                                       X     against HTML mail
TO REPLY, REMOVE NoJunkMail           / \      and postings      
FROM MY E-MAIL ADDRESS.          
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind,                Above opinions are mine only.
it's backed up on tape somewhere.      Others can have their own.

 
 
 

Selecting test with quote using perl?

Post by Knut Stolz » Sun, 22 Jun 2003 02:46:08


Hemant Shah wrote on Friday 20 June 2003 19:36:

Quote:

> Folks,

>   I am having trouble select a CHAR column that has (') single quote in it
>   using perl. It is also a problem while running CLP from command line.
>   So I would prefer generic solution, but perl specific solution will also
>   work.

>   I am using DBI, DBD::DB2 modules.

>   Following is the fragment of the perlcode I am running:

>   ---------cut-------------cut-------------cut-------------cut----

> sub ExactSearchForSentence
> {
>    my($String) = shift;

If you want to have embedded single-quotes, you have to escape them with
another single quote.  So you could just add this line here:

  $String =~ s/'/''/g;

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

 
 
 

Selecting test with quote using perl?

Post by Michael Budas » Sun, 22 Jun 2003 02:53:04




> Folks,

>   I am having trouble select a CHAR column that has (') single quote in it
>   using perl. It is also a problem while running CLP from command line.
>   So I would prefer generic solution, but perl specific solution will also
>   work.

>   I am using DBI, DBD::DB2 modules.

>   Following is the fragment of the perlcode I am running:

>   ---------cut-------------cut-------------cut-------------cut----

> sub ExactSearchForSentence
> {
>    my($String) = shift;
>    my($Language) = shift;
>    my(%Sentencehash) = ();
>    my($SqlStmt) = "SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
>                      FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
>                      WHERE S.SENTENCE = '$String'
>                      AND LANGUAGE = '$Language'
>                      AND S.SRC_NUM = N.SRC_NUM
>                      FETCH FIRST 100 ROWS ONLY FOR READ ONLY";

>    print "DEBUG: $SqlStmt\n";
>    eval
>    {
>       $SqlStmtHdl = $DbHandle->prepare($SqlStmt);
>       $SqlStmtHdl->execute();
>    };


>    {

>    }

>    while(($FileName,$Sentence) = $SqlStmtHdl->fetchrow())
>    {
>       $Sentencehash{$FileName} = $Sentence;
>    }
>    $SqlStmtHdl->finish();

>    return(%Sentencehash);
> }

> $String = "This field is required if the 'transaction fee use code' is a
> '2'.";
> %SentenceHash = ExactSearchForSentence($String, 'EN1');
> foreach $Key (keys %SentenceHash)
> {
>    print "$Key\t[$SentenceHash{$Key}]\n";
> }

>   ---------cut-------------cut-------------cut-------------cut----

>   When I run the perl script I get following error:

> DEBUG: SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
>                      FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
>                      WHERE S.SENTENCE = 'This field is required if the
>                      'transaction fee use code' is a '2'.'
>                      AND LANGUAGE = 'EN1'
>                      AND S.SRC_NUM = N.SRC_NUM
>                      FETCH FIRST 100 ROWS ONLY FOR READ ONLY
> lidp6|ExactSearchForSentence: DBD::DB2::db prepare failed: [IBM][CLI
> Driver][DB2/6000] SQL0104N  An unexpected token "transaction" was found
> following "is required if the '".  Expected tokens may include:  "UNION".  
> SQLSTATE=42601

> In the above example I am setting $String in the code, but In actual program
> it is passed from a Perl/TK program and it could come from different sources.

short answer: use DBI's placeholders (type 'perldoc DBI' and scan for
'placeholders') thusly:

sub ExactSearchForSentence
{
   my($String) = shift;
   my($Language) = shift;
   my(%Sentencehash) = ();
   my($SqlStmt) = "SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
                     FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
                     WHERE S.SENTENCE = ?
                     AND LANGUAGE = ?
                     AND S.SRC_NUM = N.SRC_NUM
                     FETCH FIRST 100 ROWS ONLY FOR READ ONLY";

   print "DEBUG: $SqlStmt\n";
   eval
   {
      $SqlStmtHdl = $DbHandle->prepare($SqlStmt);
      $SqlStmtHdl->execute($String, $Language);
   };
...

hth -
--
Michael Budash

 
 
 

Selecting test with quote using perl?

Post by dw » Sun, 22 Jun 2003 03:59:25



Quote:>    my($SqlStmt) = "SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
>                      FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
try changing this:
>                      WHERE S.SENTENCE = '$String'

to
                      WHERE S.SENTENCE = '?'

and this

Quote:>                      AND LANGUAGE = '$Language'

to
                      AND LANGUAGE = '?'

Quote:>                      AND S.SRC_NUM = N.SRC_NUM
>                      FETCH FIRST 100 ROWS ONLY FOR READ ONLY";

              ......
Quote:>       $SqlStmtHdl = $DbHandle->prepare($SqlStmt);
and this
>       $SqlStmtHdl->execute();

to
        $SqlStmtHdl->execute($String, $Language);
 
 
 

Selecting test with quote using perl?

Post by Hemant Sha » Sun, 22 Jun 2003 04:08:35



:)Hemant Shah wrote on Friday 20 June 2003 19:36:
:)
:)>
:)> Folks,
:)>
:)>   I am having trouble select a CHAR column that has (') single quote in it
:)>   using perl. It is also a problem while running CLP from command line.
:)>   So I would prefer generic solution, but perl specific solution will also
:)>   work.
:)>
:)>   I am using DBI, DBD::DB2 modules.
:)>
:)>   Following is the fragment of the perlcode I am running:
:)>
:)>   ---------cut-------------cut-------------cut-------------cut----
:)>
:)> sub ExactSearchForSentence
:)> {
:)>    my($String) = shift;
:)
:)If you want to have embedded single-quotes, you have to escape them with
:)another single quote.  So you could just add this line here:
:)
:)  $String =~ s/'/''/g;

 Thanks this will solve my problem in ksh scripts too.

:)
:)--
:)Knut Stolze
:)Information Integration
:)IBM Germany / University of Jena

--
Hemant Shah                           /"\  ASCII ribbon campaign

                                       X     against HTML mail
TO REPLY, REMOVE NoJunkMail           / \      and postings      
FROM MY E-MAIL ADDRESS.          
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind,                Above opinions are mine only.
it's backed up on tape somewhere.      Others can have their own.

 
 
 

Selecting test with quote using perl?

Post by Hemant Sha » Sun, 22 Jun 2003 04:09:35





:)
:)> Folks,
:)>
:)>   I am having trouble select a CHAR column that has (') single quote in it
:)>   using perl. It is also a problem while running CLP from command line.
:)>   So I would prefer generic solution, but perl specific solution will also
:)>   work.
:)>
:)>   I am using DBI, DBD::DB2 modules.
:)>
:)>   Following is the fragment of the perlcode I am running:
:)>
:)>   ---------cut-------------cut-------------cut-------------cut----
:)>
:)> sub ExactSearchForSentence
:)> {
:)>    my($String) = shift;
:)>    my($Language) = shift;
:)>    my(%Sentencehash) = ();
:)>    my($SqlStmt) = "SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
:)>                      FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
:)>                      WHERE S.SENTENCE = '$String'
:)>                      AND LANGUAGE = '$Language'
:)>                      AND S.SRC_NUM = N.SRC_NUM
:)>                      FETCH FIRST 100 ROWS ONLY FOR READ ONLY";
:)>
:)>
:)>    print "DEBUG: $SqlStmt\n";
:)>    eval
:)>    {
:)>       $SqlStmtHdl = $DbHandle->prepare($SqlStmt);
:)>       $SqlStmtHdl->execute();
:)>    };
:)>

:)>    {

:)>    }
:)>
:)>    while(($FileName,$Sentence) = $SqlStmtHdl->fetchrow())
:)>    {
:)>       $Sentencehash{$FileName} = $Sentence;
:)>    }
:)>    $SqlStmtHdl->finish();
:)>
:)>    return(%Sentencehash);
:)> }
:)>
:)>
:)> $String = "This field is required if the 'transaction fee use code' is a
:)> '2'.";
:)> %SentenceHash = ExactSearchForSentence($String, 'EN1');
:)> foreach $Key (keys %SentenceHash)
:)> {
:)>    print "$Key\t[$SentenceHash{$Key}]\n";
:)> }
:)>
:)>   ---------cut-------------cut-------------cut-------------cut----
:)>
:)>   When I run the perl script I get following error:
:)>
:)> DEBUG: SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
:)>                      FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
:)>                      WHERE S.SENTENCE = 'This field is required if the
:)>                      'transaction fee use code' is a '2'.'
:)>                      AND LANGUAGE = 'EN1'
:)>                      AND S.SRC_NUM = N.SRC_NUM
:)>                      FETCH FIRST 100 ROWS ONLY FOR READ ONLY
:)> lidp6|ExactSearchForSentence: DBD::DB2::db prepare failed: [IBM][CLI
:)> Driver][DB2/6000] SQL0104N  An unexpected token "transaction" was found
:)> following "is required if the '".  Expected tokens may include:  "UNION".  
:)> SQLSTATE=42601
:)>
:)>
:)>
:)> In the above example I am setting $String in the code, but In actual program
:)> it is passed from a Perl/TK program and it could come from different sources.
:)
:)short answer: use DBI's placeholders (type 'perldoc DBI' and scan for
:)'placeholders') thusly:

  Thanks for the tip. I am not sure why I did not think about it before.
  It fixed the problem.

:)
:)
:)sub ExactSearchForSentence
:){
:)   my($String) = shift;
:)   my($Language) = shift;
:)   my(%Sentencehash) = ();
:)   my($SqlStmt) = "SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
:)                     FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
:)                     WHERE S.SENTENCE = ?
:)                     AND LANGUAGE = ?
:)                     AND S.SRC_NUM = N.SRC_NUM
:)                     FETCH FIRST 100 ROWS ONLY FOR READ ONLY";
:)
:)
:)   print "DEBUG: $SqlStmt\n";
:)   eval
:)   {
:)      $SqlStmtHdl = $DbHandle->prepare($SqlStmt);
:)      $SqlStmtHdl->execute($String, $Language);
:)   };
:)...
:)
:)hth -
:)--
:)Michael Budash

--
Hemant Shah                           /"\  ASCII ribbon campaign

                                       X     against HTML mail
TO REPLY, REMOVE NoJunkMail           / \      and postings      
FROM MY E-MAIL ADDRESS.          
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind,                Above opinions are mine only.
it's backed up on tape somewhere.      Others can have their own.

 
 
 

Selecting test with quote using perl?

Post by Bill Smit » Sun, 22 Jun 2003 08:05:33



> Folks,

>   I am having trouble select a CHAR column that has (') single quote
in it
>   using perl. It is also a problem while running CLP from command
line.
>   So I would prefer generic solution, but perl specific solution will
also
>   work.

>   I am using DBI, DBD::DB2 modules.

>   Following is the fragment of the perlcode I am running:

>   ---------cut-------------cut-------------cut-------------cut----

> sub ExactSearchForSentence
> {
>    my($String) = shift;
>    my($Language) = shift;
>    my(%Sentencehash) = ();
>    my($SqlStmt) = "SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
>                      FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
>                      WHERE S.SENTENCE = '$String'
>                      AND LANGUAGE = '$Language'
>                      AND S.SRC_NUM = N.SRC_NUM
>                      FETCH FIRST 100 ROWS ONLY FOR READ ONLY";

>    print "DEBUG: $SqlStmt\n";
>    eval
>    {
>       $SqlStmtHdl = $DbHandle->prepare($SqlStmt);
>       $SqlStmtHdl->execute();
>    };


>    {

>    }

>    while(($FileName,$Sentence) = $SqlStmtHdl->fetchrow())
>    {
>       $Sentencehash{$FileName} = $Sentence;
>    }
>    $SqlStmtHdl->finish();

>    return(%Sentencehash);
> }

> $String = "This field is required if the 'transaction fee use code' is
a '2'.";
> %SentenceHash = ExactSearchForSentence($String, 'EN1');
> foreach $Key (keys %SentenceHash)
> {
>    print "$Key\t[$SentenceHash{$Key}]\n";
> }

>   ---------cut-------------cut-------------cut-------------cut----

>   When I run the perl script I get following error:

> DEBUG: SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
>                      FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
>                      WHERE S.SENTENCE = 'This field is required if the

'transaction fee use code' is a '2'.'
Quote:>                      AND LANGUAGE = 'EN1'
>                      AND S.SRC_NUM = N.SRC_NUM
>                      FETCH FIRST 100 ROWS ONLY FOR READ ONLY
> lidp6|ExactSearchForSentence: DBD::DB2::db prepare failed: [IBM][CLI

Driver][DB2/6000] SQL0104N  An unexpected token "transaction" was found
following "is required if the '".  Expected tokens may include:
"UNION".  SQLSTATE=42601
Quote:

> In the above example I am setting $String in the code, but In actual
program
> it is passed from a Perl/TK program and it could come from different

sources.

Your problem has nothing to do with perl.  The quotes in you expanded
SQL are invalid.  You must find out how to 'escape'  single quote marks
in your version of SQL.

The perl solution is to use the perl substitute command to replace all
single quotes in $String with escaped single quotes.  An escaped single
quote may be two single quotes.  If so...

$String =~ s/\'/\'\'/g;

Good Luck,
Bill

 
 
 

Selecting test with quote using perl?

Post by Jonathan Leffle » Sun, 22 Jun 2003 11:05:00



> Folks,

>   I am having trouble select a CHAR column that has (') single quote in it
>   using perl. It is also a problem while running CLP from command line.
>   So I would prefer generic solution, but perl specific solution will also
>   work.

>   I am using DBI, DBD::DB2 modules.

>   Following is the fragment of the perlcode I am running:

>   ---------cut-------------cut-------------cut-------------cut----

> sub ExactSearchForSentence
> {
>    my($String) = shift;
>    my($Language) = shift;
>    my(%Sentencehash) = ();
>    my($SqlStmt) = "SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
>                      FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
>                      WHERE S.SENTENCE = '$String'

Replace '$string' with a solitary question mark (placeholder); ditto
for '$Language'; then supply $String and $Language as arguments for
the placeholders in the $SqlStmtHdl->execute call.

Or, use the DBI-provided $dbh->quote() method to quote your string
correctly for the database you are using:

WHERE S.SENTENCE = " . $DbHandle->quote($String) . " ...

I'd need to read the doc (perldoc DBI) to check whether you supply the
quotes around the string or not -- but I think not.

Quote:>                      AND LANGUAGE = '$Language'
>                      AND S.SRC_NUM = N.SRC_NUM
>                      FETCH FIRST 100 ROWS ONLY FOR READ ONLY";

>    print "DEBUG: $SqlStmt\n";
>    eval
>    {
>       $SqlStmtHdl = $DbHandle->prepare($SqlStmt);
>       $SqlStmtHdl->execute();
>    };

[...snip...]

--
Jonathan Leffler                   #include <disclaimer.h>

Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

 
 
 

Selecting test with quote using perl?

Post by Eric Amic » Mon, 23 Jun 2003 03:37:13


On Fri, 20 Jun 2003 17:36:06 +0000 (UTC), Hemant Shah


>Folks,

>  I am having trouble select a CHAR column that has (') single quote in it
>  using perl. It is also a problem while running CLP from command line.
>  So I would prefer generic solution, but perl specific solution will also
>  work.
>  Following is the fragment of the perlcode I am running:

>  ---------cut-------------cut-------------cut-------------cut----

>sub ExactSearchForSentence
>{
>   my($String) = shift;
>   my($Language) = shift;
>   my(%Sentencehash) = ();
>   my($SqlStmt) = "SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
>                     FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
>                     WHERE S.SENTENCE = '$String'
>                     AND LANGUAGE = '$Language'
>                     AND S.SRC_NUM = N.SRC_NUM
>                     FETCH FIRST 100 ROWS ONLY FOR READ ONLY";

[snip]

Quote:>$String = "This field is required if the 'transaction fee use code' is a '2'.";

[snip]

Quote:>  When I run the perl script I get following error:

>DEBUG: SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
>                     FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
>                     WHERE S.SENTENCE = 'This field is required if the 'transaction fee use code' is a '2'.'
>                     AND LANGUAGE = 'EN1'
>                     AND S.SRC_NUM = N.SRC_NUM
>                     FETCH FIRST 100 ROWS ONLY FOR READ ONLY
>lidp6|ExactSearchForSentence: DBD::DB2::db prepare failed: [IBM][CLI Driver][DB2/6000] SQL0104N  
>An unexpected token "transaction" was found following "is required if the '".  Expected tokens may include:  "UNION".  SQLSTATE=42601

SQL allows single quotes within a string if you use two consecutive
single quotes.  Add the statement

$String =~ s/'/''/g;   # two single quotes, not a double quote!

after you load the value of $String.

--
Eric Amick
Columbia, MD

 
 
 

Selecting test with quote using perl?

Post by Zur Aoug » Mon, 23 Jun 2003 19:14:30



> Folks,

>   I am having trouble select a CHAR column that has (') single quote in it
>   using perl. It is also a problem while running CLP from command line.
>   So I would prefer generic solution, but perl specific solution will also
>   work.

...
> {
>    my($String) = shift;
>    my($Language) = shift;
>    my(%Sentencehash) = ();
>    my($SqlStmt) = "SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
>                      FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
>                      WHERE S.SENTENCE = '$String'
>                      AND LANGUAGE = '$Language'
>                      AND S.SRC_NUM = N.SRC_NUM
>                      FETCH FIRST 100 ROWS ONLY FOR READ ONLY";
...

> $String = "This field is required if the 'transaction fee use code' is a '2'.";
> %SentenceHash = ExactSearchForSentence($String, 'EN1');
...
>   When I run the perl script I get following error:

> DEBUG: SELECT RTRIM(SRC_NAME),RTRIM(SENTENCE)
>                      FROM GBLCODE.SENTENCE S, GBLCODE.SOURCENAME N
>                      WHERE S.SENTENCE = 'This field is required if the 'transaction fee use code' is a '2'.'
>                      AND LANGUAGE = 'EN1'
>                      AND S.SRC_NUM = N.SRC_NUM
>                      FETCH FIRST 100 ROWS ONLY FOR READ ONLY
> lidp6|ExactSearchForSentence: DBD::DB2::db prepare failed: [IBM][CLI Driver][DB2/6000] SQL0104N  An unexpected token "transaction" was found following "is required if the '".  Expected tokens may include:  "UNION".  SQLSTATE=42601
...
> --
> Hemant Shah                           /"\  ASCII ribbon campaign

>                                        X     against HTML mail
> TO REPLY, REMOVE NoJunkMail           / \      and postings      
> FROM MY E-MAIL ADDRESS.          

Try to change
        WHERE S.SENTENCE = '$String'
to
        WHERE S.SENTENCE = \"$String\"

or change
 $String = "This field is required if the 'transaction fee use code' is a '2'.";
to
 $String = "This field is required if the ''transaction fee use code'' is a ''2''.";

The last entry is: double single quote is an escape for one quote!

Zur Aougav