Single quotes in IB7 field values

Greetings.
I've upgraded an IB 6.5 DB to IB7.x Dialect 3 database.
My OCCUPANT_NAME field in my OCCUPANTS table contains some values
with single quotes like Fred's Market for instance.
Are there any escape characters I can use in My SQL strings?
My front end application is Access2007 and I am using the DataDirect 
ODBC driver.
Earlier versions of IB used to accept double quotes around a fields 
value.

In article <MPG.22498b10b5e30cfa989680@newsgroups.borland.com>, 
AAA@AAA.AA says...
> Greetings.
> I've upgraded an IB 6.5 DB to IB7.x Dialect 3 database.
> My OCCUPANT_NAME field in my OCCUPANTS table contains some values
> with single quotes like Fred's Market for instance.
> Are there any escape characters I can use in My SQL strings?
> My front end application is Access2007 and I am using the DataDirect 
> ODBC driver.
> Earlier versions of IB used to accept double quotes around a fields 
> value.
> 
> 
> Thank you
> 

"Brian" <AAA@AAA.AA> wrote in message 
news:MPG.22498b10b5e30cfa989680@newsgroups.borland.com...
> My OCCUPANT_NAME field in my OCCUPANTS table contains some values
> with single quotes like Fred's Market for instance.
> Are there any escape characters I can use in My SQL strings?
> My front end application is Access2007 and I am using the DataDirect
> ODBC driver.
> Earlier versions of IB used to accept double quotes around a fields
> value.


If you are formatting SQL directly instead of using parameters then you must 
double up single quotes, e.g:

  s := 'where name = ''Fred''s Market''';

Delphi also has a QuotedStr function that makes this easier, e.g. if a user 
enters Fred's Market in an edit control:

    s := 'where name = ' + QuotedStr(Edit1.Text);

However, the best way to avoid these issues is to use parameterized queries. 
Assign the SQL to the dataset as:

  MyDataset.SQL.Text := 'select * from table where name = :name';

Then assign the parameter(s):

  MyDataset.ParamByName('name') := Edit1.Text;


-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca) 

It works the same for all types of SQL.

Brian wrote:

> Thanks. I'll try it. I may have forgotten to disclose that I am trying 
> updates and inserts as we..

In article <47dfcc00$1@newsgroups.borland.com>, "Wayne Niddery
\(TeamB
\)" <wniddery@chaffaci.on.ca> says...
> "Brian" <AAA@AAA.AA> wrote in message 
> news:MPG.22498b10b5e30cfa989680@newsgroups.borland.com...
> > My OCCUPANT_NAME field in my OCCUPANTS table contains some values
> > with single quotes like Fred's Market for instance.
> > Are there any escape characters I can use in My SQL strings?
> > My front end application is Access2007 and I am using the DataDirect
> > ODBC driver.
> > Earlier versions of IB used to accept double quotes around a fields
> > value.
> 
> 
> If you are formatting SQL directly instead of using parameters then you
must 
> double up single quotes, e.g:
> 
>   s := 'where name = ''Fred''s Market''';
> 
> Delphi also has a QuotedStr function that makes this easier, e.g. if a user

> enters Fred's Market in an edit control:
> 
>     s := 'where name = ' + QuotedStr(Edit1.Text);
> 
> However, the best way to avoid these issues is to use parameterized
queries. 
> Assign the SQL to the dataset as:
> 
>   MyDataset.SQL.Text := 'select * from table where name = :name';
> 
> Then assign the parameter(s):
> 
>   MyDataset.ParamByName('name') := Edit1.Text;
> 
> 
> 
Thanks. I'll try it. I may have forgotten to disclose that I am trying 
updates and inserts as we..