Subject | RE: [IBO] Apostrphe within single quotes |
---|---|
Author | Helen Borrie |
Post date | 2004-11-13T06:54:32Z |
At 12:00 AM 13/11/2004 -0500, you wrote:
of the nice Delphi features will fall into place. Unless you have an
absolute need for a totally ad hoc query, avoid them. The SQL property for
same query you quote above should be written like this:
Select c.name, p.LastName, p.Name
from Companies c
JOIN Persons p
ON p.CompanyNr = c.CompanyNr
where p.Name containing :p_name
In your interface, you collect the person's name from some input field:
MyQuery.ParamByName('p_name').AsString := QuotedStr(MyEditBox.Text);
Or, if you are picking it up from some data-aware control:
MyQuery.ParamByName('p_name').AsString :=
QuotedStr(MyOtherQuery.FieldByName('Name').AsString);
That's how it's done. Be glad at least that IB/Fb and Delphi actually use
the same convention for escaping apostrophes. !!!!! For some DBMSs you
have to roll your own function.
Helen
>Yes, that works, but the query is formed externally to Delphi, and is moreThis isn't a good way to do queries. Get used to using parameters and all
>involved, so that I cannot see how to parse the portion to send to
>QuotedStr(). Suppose that my query is:
>
> >>
>Select c.name, p.LastName, p.Name from Companies c, Persons p
>where p.Name containing 'James O'Rourke'
>and p.CompanyNr = c.CompanyNr
><<
>
>Here is where I ran into my problem: It becomes ambiguous to know how to
>pass the correct parameter (James O'Rourke) to QuotedStr. How can Delphi
>know whether the second apostrophe terminates the string (James O), or that
>it should go on to find a third apostrophe?
of the nice Delphi features will fall into place. Unless you have an
absolute need for a totally ad hoc query, avoid them. The SQL property for
same query you quote above should be written like this:
Select c.name, p.LastName, p.Name
from Companies c
JOIN Persons p
ON p.CompanyNr = c.CompanyNr
where p.Name containing :p_name
In your interface, you collect the person's name from some input field:
MyQuery.ParamByName('p_name').AsString := QuotedStr(MyEditBox.Text);
Or, if you are picking it up from some data-aware control:
MyQuery.ParamByName('p_name').AsString :=
QuotedStr(MyOtherQuery.FieldByName('Name').AsString);
That's how it's done. Be glad at least that IB/Fb and Delphi actually use
the same convention for escaping apostrophes. !!!!! For some DBMSs you
have to roll your own function.
Helen