Using 4.37 and having trouble with the following

UPDATE customer SET directions = ' 234 North 'D' Street'

I'd like to be able to use single quotes within the field value parts of an UPDATE statement.

What's the easiest method for using single quotes within the update text?

TIA,

John

<John Easley> wrote in message
news:067DF512-CAAC-4D48-BCAA-DE7C5DD8FEFF@news.elevatesoft.com...
> Using 4.37 and having trouble with the following
>
> UPDATE customer SET directions = ' 234 North 'D' Street'
>


Hi John

Assuming you are assembling the SQL in code:-

sDirections is a string with the funny address

SQL.Text := 'UPDATE customer SET directions = ' + QuotedStr(sDirections) +
'WHERE ...';

Or if you are entering the SQL into DBSYS then double up the quotes:-

UPDATE customer SET directions = ' 234 North ''D'' Street'

HTH

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

If a users were to enter 234 North 'D' Street within an edit box, and I'm using it as part of my
update, what's the best method for parsing the single quote characters and then inserting
the extra single quote?

It would be nice to have the engine handle this stuff..

John

"Jeff Cook" wrote:

<John Easley> wrote in message
news:067DF512-CAAC-4D48-BCAA-DE7C5DD8FEFF@news.elevatesoft.com...
> Using 4.37 and having trouble with the following
>
> UPDATE customer SET directions = ' 234 North 'D' Street'
>


Hi John

Assuming you are assembling the SQL in code:-

sDirections is a string with the funny address

SQL.Text := 'UPDATE customer SET directions = ' + QuotedStr(sDirections) +
'WHERE ...';

Or if you are entering the SQL into DBSYS then double up the quotes:-

UPDATE customer SET directions = ' 234 North ''D'' Street'

HTH

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Nevermind, using the following:

result := StringReplace(AString, #39, #39#39, [rfReplaceAll]);

John

John Easley wrote:

If a users were to enter 234 North 'D' Street within an edit box, and I'm using it as part of my
update, what's the best method for parsing the single quote characters and then inserting
the extra single quote?

It would be nice to have the engine handle this stuff..

John

"Jeff Cook" wrote:

<John Easley> wrote in message
news:067DF512-CAAC-4D48-BCAA-DE7C5DD8FEFF@news.elevatesoft.com...
> Using 4.37 and having trouble with the following
>
> UPDATE customer SET directions = ' 234 North 'D' Street'
>


Hi John

Assuming you are assembling the SQL in code:-

sDirections is a string with the funny address

SQL.Text := 'UPDATE customer SET directions = ' + QuotedStr(sDirections) +
'WHERE ...';

Or if you are entering the SQL into DBSYS then double up the quotes:-

UPDATE customer SET directions = ' 234 North ''D'' Street'

HTH

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

www.aspect.co.nz

<John Easley> wrote in message
news:187EEDC3-AE1C-4FB0-93D8-80961AD800B1@news.elevatesoft.com...
> Nevermind, using the following:
>
> result := StringReplace(AString, #39, #39#39, [rfReplaceAll]);
>

John

Your solution will probably work as long as you add the preceding and
following quote.

result := #39 + StringReplace(AString, #39, #39#39, [rfReplaceAll]) + #39;

Simpler and more elegant (I think) to use the functions provided:-

In DBISAM3, I always used QuoteStr (SysUtils) to handle the quoting.

In ElevateDB, I use  QuotedSQLStr .

Your on DBISAM4 so ...
-------------------------------------------------------------------------------------------------------------------------------------------------------
The ElevateDB manual says:-

When dynamically building SQL statements that contain literal string
constants, you can use the
TEDBEngine QuotedSQLStr method to properly format and escape any embedded
single quotes in the
string.

For example, suppose you have a TEdit component that contains the following
string:

Pete's Garage

The string contains an embedded single quote, so it cannot be specified
directly without causing an error
in the SQL statement.

To build an SQL INSERT statement that inserts the above string into a
VARCHAR column, you should use
the following code:

MyEDBQuery.SQL.Text:='INSERT INTO MyTable '+ '(MyVarCharColumn) VALUES ('+
Engine.QuotedSQLStr(MyEdit.Text)+')';
------------------------------------------------------------------
Cheers

Jeff

> If a users were to enter 234 North 'D' Street within an edit box,

Consider carefully user entered data. One day the young boy called "Mike ; DROP *"
may use your forms.

The best option is really to do
UPDATE customer SET directions = :DIRECTIONS

and then use
MyQuery.Prepare;
MyQuery.ParamByName('DIRECTIONS').AsString := UserText;

This way nothing can cause your SQL to get hacked.

/Matthew Jones/