Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Correctly passing strings containing punctuation marks

Posted on 1999-06-24
10
Medium Priority
?
229 Views
Last Modified: 2010-04-06
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.
0
Comment
Question by:evansj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 

Author Comment

by:evansj
ID: 1384479
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.)'

0
 
LVL 12

Expert Comment

by:rwilson032697
ID: 1384480
Put double quotes around the entire string.

Cheers,

Raymond.

0
 
LVL 15

Expert Comment

by:simonet
ID: 1384481
Not much left to say here, Ray... you've said it all.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 1384482
hi evansj,

try this partchange

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

meikl
0
 
LVL 1

Expert Comment

by:wamoz
ID: 1384483
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.
0
 

Author Comment

by:evansj
ID: 1384484
rwilson:
Doesn't work.
0
 

Author Comment

by:evansj
ID: 1384485
meikl:
Doesn't work.

Passing as a query parameter would work, but how would you implement this?
0
 

Author Comment

by:evansj
ID: 1384486
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.


0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 480 total points
ID: 1384487
hi evansj,

glad that this helps you.

i just wondered about it doesn't work first, because i do it every time when i build up a sql-string with variable-string entry's (as your code) and where i don't what the string contains.

well, but i see it works also for you now.

good luck again

meikl ;-)
0
 
LVL 12

Expert Comment

by:rwilson032697
ID: 1384488
Interestingly, QuotedStr does not appear to do anything with double-quote chars (at least according to the help...)

Cheers,

Raymond.

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question