Link to home
Start Free TrialLog in
Avatar of evansj
evansj

asked on

Correctly passing strings containing punctuation marks

Alright folks,
Here is my dilemma:
I have an app that allows the user to select multiple text items and query the database based on a string that I build. Here is the code:

sql:='select CE,FY,SO,PD from RS_' + copy(cboSite.Text,1,3) + '.FED WHERE PD IS NOT NULL';
sql2:='select count(*) "QTY" from RS_' + copy(cboSite.Text,1,3) + '.FED WHERE PD IS NOT NULL';

a:=' and (';

     for x:= 0 to listCl.Items.Count -1 do
     begin
     b:= listCl.Items[x];
     a:= a + 'cdesc = ' + '''' + b + '''' + ' or ';
     end;
sequel:= sql + a;
sequel2:= sql2 + a;

screen.Cursor:=crHourGlass;
dmRep.query1.Close;
dmRep.query1.SQL.Clear;
dmRep.query1.SQL.Add(sequel);
dmRep.query1.Open;
dmRep.query3.Close;
dmRep.query3.SQL.Clear;
dmRep.query3.SQL.Add(sequel2);
dmRep.query3.Open;
dbgrid3.Visible:=true;
dbtext1.Visible:=true;
label22.Caption:=dmRep.Query3.fieldByName('QTY').AsString;
screen.Cursor:=crDefault;

Now if the user can click a choice from a combo box. As long as he clicks a choice it will add it to a listbox.

Now when he clicks the query button, it will query the database with the query that's been built for his choices. The problem arises if the text has punctuation in it like single quotes or apostrophe's:

Example choices

Can't get in touch
New 'HOLES' in prog.

with the above code would produce this string:

'and cdesc = 'Can't get in touch' or cdesc = 'New 'HOLES' in prog.'


when the string is fed to the query object it results in errors like 'Missing right quote' errors. How would you code around this to allow text with punctuation to be correctly passed to the query object? I can get around this if I restrict the choice to ONE item only by the parambyname property; this allows punctuation in the text.
Hope you can understand what I'm trying to do based on my code snippet and explanation.
Avatar of evansj
evansj

ASKER

Sorry, I left out an important line:
a:=copy(a,1,((length(a)-3))) + ')';

the code should read as follows:
sql:='select CE,FY,SO,PD from RS_' + copy(cboSite.Text,1,3) + '.FED WHERE PD IS NOT NULL';
sql2:='select count(*) "QTY" from RS_' + copy(cboSite.Text,1,3) + '.FED WHERE PD IS NOT NULL';

a:=' and (';

     for x:= 0 to listCl.Items.Count -1 do
     begin
     b:= listCl.Items[x];
     a:= a + 'cdesc = ' + '''' + b + '''' + ' or ';
     end;

a:=copy(a,1,((length(a)-3))) + ')';

sequel:= sql + a;
sequel2:= sql2 + a;

screen.Cursor:=crHourGlass;
dmRep.query1.Close;
dmRep.query1.SQL.Clear;
dmRep.query1.SQL.Add(sequel);
dmRep.query1.Open;
dmRep.query3.Close;
dmRep.query3.SQL.Clear;
dmRep.query3.SQL.Add(sequel2);
dmRep.query3.Open;
dbgrid3.Visible:=true;
dbtext1.Visible:=true;
label22.Caption:=dmRep.Query3.fieldByName('QTY').AsString;
screen.Cursor:=crDefault;

and the string produced would be:

'and (cdesc = 'Can't get in touch' or cdesc = 'New 'HOLES' in prog.)'

Put double quotes around the entire string.

Cheers,

Raymond.

Avatar of simonet
Not much left to say here, Ray... you've said it all.
hi evansj,

try this partchange

a:= a + 'cdesc = ' + QuotedStr( b ) + ' or ';

meikl
Actually, rwilson has *not* addressed the problem completely - which is unusual for him.

Things get more tangled when the string contains *both* embedded single and double quotes.

There are two possible approaches that work. You can either cop out of the whole problem by passing the string value to the query via a parameter, or you can write a (very elaborate) routine to correctly escape single quotes the way you would in Delphi code.

For example, the string
    Can't get 'enough of quotes!'
has to become
    Can''t get ''enough of quotes!''

Personally I favour passing the string value as a parameter of a TQuery.
Avatar of evansj

ASKER

rwilson:
Doesn't work.
Avatar of evansj

ASKER

meikl:
Doesn't work.

Passing as a query parameter would work, but how would you implement this?
Avatar of evansj

ASKER

Meikl: resubmit your suggestion as an answer, it works:
a:= a + 'cdesc = ' + quotedstr(b) +  ' or ';
Thanks.
BTW, I don't know why it did not work at first.


ASKER CERTIFIED SOLUTION
Avatar of kretzschmar
kretzschmar
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Interestingly, QuotedStr does not appear to do anything with double-quote chars (at least according to the help...)

Cheers,

Raymond.