Board index » delphi » How to handle apostrophes inside SQL parameterized queries

How to handle apostrophes inside SQL parameterized queries

The following SQL statement works fine except when the text it returns
contains an apostrophe, something like "Children's Treatment
Facilities."

Agencies.SQL.Add('SELECT * FROM agency WHERE cat = :ProgCategory');

The setup is Delphi 3, ODBCExpress, and SQL Anywhere. There ought to
be a simple way, that has thusfar eluded me, to handle such situations
without generating an ODBC error.

 

Re:How to handle apostrophes inside SQL parameterized queries


I believe you just need to double it up : ''

Rkr

Quote
Mark Shapiro wrote:

> The following SQL statement works fine except when the text it returns
> contains an apostrophe, something like "Children's Treatment
> Facilities."

> Agencies.SQL.Add('SELECT * FROM agency WHERE cat = :ProgCategory');

> The setup is Delphi 3, ODBCExpress, and SQL Anywhere. There ought to
> be a simple way, that has thusfar eluded me, to handle such situations
> without generating an ODBC error.

--
                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Delphi Programmer / Analyst             .
. TVisualBasic:=class(None)               .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) home (dot) com    .
-------------------------------------------

Re:How to handle apostrophes inside SQL parameterized queries


Hello Mark,

Quote
> Agencies.SQL.Add('SELECT * FROM agency WHERE cat = :ProgCategory');
> The setup is Delphi 3, ODBCExpress, and SQL Anywhere. There ought to
> be a simple way, that has thusfar eluded me, to handle such situations
> without generating an ODBC error.

You'll have to parse the string looking for. Try this procedure which
I use for setting a filter. It could be easily adapted:-

=== cut ===

procedure TOpenSessionForm.SetCircuitFilter(PassedStr : String);
{ Set the Circuit filter string to cope with " ' "s }
var
  i : integer;
  TempStr : String;
begin
  If PassedStr <> '' then
  begin
    TempStr := '';
    for i := 1 to Length(passedStr) do
    begin
      TempStr := TempStr+PassedStr[i];
      If PassedStr[i] = '''' then
        TempStr := TempStr+''''; { Add another apostrophe to the string }
    end;
    CircuitFilter := '(Circuit = '''+TempStr+''')';
  end else
    CircuitFilter := '';
end;

=== cut ===

THe number of apostrophes becomes very confusing but it does work.
The reason for the brackets is that the filter gets more complecated.
They could probably be left out.

--
Bye for now,
Donald.
don...@kerr.zetnet.co.uk
http://www.kerr.zetnet.co.uk
Database configuration & Web Site Design

Re:How to handle apostrophes inside SQL parameterized queries


Hello Mark,

Quote
> Agencies.SQL.Add('SELECT * FROM agency WHERE cat = :ProgCategory');
> The setup is Delphi 3, ODBCExpress, and SQL Anywhere. There ought to
> be a simple way, that has thusfar eluded me, to handle such situations
> without generating an ODBC error.

You also have to look out for names like O'Brien and O'Toole.

--
Bye for now,
Donald.
don...@kerr.zetnet.co.uk
http://www.kerr.zetnet.co.uk
Database configuration & Web Site Design

Re:How to handle apostrophes inside SQL parameterized queries


Check out the function QuotedStr--it will double up the single quotes so
that it can be accepted by your query.  Good luck.
Gene
Quote
Mark Shapiro wrote in message <365907d9.34766...@news.swbell.net>...
>The following SQL statement works fine except when the text it returns
>contains an apostrophe, something like "Children's Treatment
>Facilities."

>Agencies.SQL.Add('SELECT * FROM agency WHERE cat = :ProgCategory');

>The setup is Delphi 3, ODBCExpress, and SQL Anywhere. There ought to
>be a simple way, that has thusfar eluded me, to handle such situations
>without generating an ODBC error.

Re:How to handle apostrophes inside SQL parameterized queries


You can break up an SQL command into constituent parts by judicious use
of variables.  Although there is no apostrophe in my example below, I am
sure you can see how it can be adapted for your own purposes.  It cuts
down on the length of the SQL command to manageable proportions as well.

HTH

procedure TFrmFront.BtnFindClick(Sender: TObject);
VAR inputStr, mssg, selectStr, fromStr, whereStr : String;

begin
   IF RBtnPostCode.Checked  THEN mssg := 'Give the Post Code in
Capitals';
   IF RBtnTelephone.Checked THEN mssg := 'Give the Telephone Number';
   IF RBtnAgNum.Checked     THEN mssg := 'Give the Agreement Number';
   inputStr := InputBox('Search Query Data',
                        mssg,
                        'XXXXXXXXXXX');

   selectStr := 'SELECT CliNum,hotnums.AgNum,DateReg,BizName,Address1,Ad
dress2,Address3, Town,PostCode ';
   fromStr   := 'FROM HotNums,Hotuser ';
   whereStr  :=  'WHERE hotNums.AgNum = hotUser.AgreementN ';
   WITH DmHotline.QueryHot DO BEGIN
      Close;
      SQL.Clear;
      IF RBtnPostCode.Checked THEN BEGIN
           SQL.Add(selectStr + fromStr + whereStr +  'AND (PostCode =
:VAR1)');
           ParamByName('VAR1').AsString := InputStr;
      END;
      IF RBtnTelephone.Checked THEN BEGIN
           SQL.Add(selectStr + fromStr + whereStr + 'AND (CliNum =
:VAR1)');
           ParamByName('VAR1').AsString := InputStr;
      END;
      IF RBtnAgNum.Checked THEN BEGIN
           SQL.Add(selectStr + fromStr + whereStr + 'AND (hotNums.AgNum
= :VAR1)');
           ParamByName('VAR1').AsInteger := StrToInt(InputStr);
      END;
      Open;

      IF RecordCount<1 THEN ShowMessage('Record Not Found!');

   END;

In article <365907d9.34766...@news.swbell.net>, Mark Shapiro
<info...@swbell.net> writes

Quote
>The following SQL statement works fine except when the text it returns
>contains an apostrophe, something like "Children's Treatment
>Facilities."

>Agencies.SQL.Add('SELECT * FROM agency WHERE cat = :ProgCategory');

>The setup is Delphi 3, ODBCExpress, and SQL Anywhere. There ought to
>be a simple way, that has thusfar eluded me, to handle such situations
>without generating an ODBC error.

rgds, John

--
John Matthews

Re:How to handle apostrophes inside SQL parameterized queries


Hello Gene,

Quote
> Check out the function QuotedStr--it will double up the single quotes so
> that it can be accepted by your query.  Good luck.

The probelem with that is the extra apostrophe at the beginning and
end of the string. BTW, I never knew that function existed!

--
Bye for now,
Donald.
don...@kerr.zetnet.co.uk
http://www.kerr.zetnet.co.uk
Database configuration & Web Site Design

Other Threads