I have a code structure I use a lot:

Write SQL Like this:

SELECT field1, field2
FROM SomeTable
WHERE ID = %s


Then, in delphi when this SQL opens use the code:

IF POS('%s', Q.SQL.Text) > 0 then
 begin
   s := Format(Q.SQL.Text, [ID]); {here ID is a string property that Delphi goes & looks up}
   Q.SQL.Text := s;
 end;

---


In many, many cases this works very well but I now need to deal with SQL like this:

SELECT field1, field2
FROM SomeTable
WHERE IDList LIKE "%[need to put search string in here!]%"

Note that I cannot use "%s" as the replacement wildcard. This would result in a WHERE
Clause that reads:

WHERE IDList LIKE "%%s%"

... which does not compile!

--

I can re-write my Delphi code to test for other FormatStrings, i.e. use (say) @@ instead
of %s in the SQL:


SELECT field1, field2
FROM SomeTable
WHERE IDList LIKE "%@@%"


IF (POS('%s', Q.SQL.Text) > 0) then
 begin
   s := Format(Q.SQL.Text, [ID]); {here ID is a property that Delphi goes & looks up}
   Q.SQL.Text := s;
 end;

IF (POS('@@', Q.SQL.Text) > 0) then
 begin
   s := {code function here to return replacement SQL.Text factoring out the @@}
   Q.SQL.Text := s;
 end;

But I worry that I am just digging myself a bigger hole, i.e. I will later find that @@ is
a bad string to use or whatever.

--

Can anyone suggest a more elegant solution to this type of problem??

Adam

adam


I may be missing something but why not just use parameters?

Roy Lambert

Dear Roy,

Thank you for this excellent suggestion ... I will try to see whether it will work in my
way of working, but ...

I haven't used parameters for years because I use Queries 'anonomously' ... I don't use
Datamodules, my queries are created on the fly as they are needed & so I need the most
flexible mechanism possible to cope with the SQL Strings, as I am often putting strings
together in code far away from the Query & if you are passing a string around you have no
"ParamByName" method to hook into, but for the specific problem I am dealing with here I
may have to try to think how to use them.

Adam

adam


It depends on the process flow but remember you can shove a parameter in as part of creating the sql programmatically (eg WHERE field = ram1) and you have a final chance to replace that just before you go .Open or .ExecSQL.

Roy Lambert