Firebird parametrized query with the LIKE operator

[Hendrikus]

Hello all,
I'm new to Lazarus (but I do have expercience with Delphi 7 Enterprise). I'm strugling with the follwoing problem:
a table with some charvar-fields (O_CONTACT VARCHAR(24)). Contacting the database in Lazarus is no problem. When I execute the following query, I get 1 row:
with dm.q do begin
    Close;
    SQL.Clear;
    Sql.Add('SELECT * FROM t_opdrachtgever ');
    Sql.Add('WHERE (t_opdrachtgever.o_id > 0) ');
    Sql.Add('AND (t_opdrachtgever.o_contact LIKE ''%Jones%'')');
    If not prepared then prepare
    Open;
end;
which works great. But...
when i do the same with a parameter:
with dm.q do begin
    Close;
    SQL.Clear;
    Sql.Add('SELECT * FROM t_opdrachtgever ');
    Sql.Add('WHERE (t_opdrachtgever.o_id > 0) ');
    Sql.Add('AND (t_opdrachtgever.o_contact LIKE :pContact)');
    If not prepared then prepare
    ParamsByName('pContact').asString :=  '%' + txtCp.text + '%';
    Open;
end;
no records returned.
Even when I define a variable (sBuffer) and fill that with : sBuffer := '%' + txtCp.text + '%';
sBuffer := QutedStr(sBuffer) and use sBuffer as the value for the parameter, no records are returned.
Does anyone has a simple example in Lazarus, in conjunction wirh Firebird, to use the LIKE-operator in a parameterized query?
Thanks in advance.
Best regards,
Hendrikus.




           

[wcleyton]

Hi,

Try:

ParamsByName('pContact').asString :=  QuotedStr('%' + txtCp.text + '%');

[Hendrikus]

Hi,

Try:

ParamsByName('pContact').asString :=  QuotedStr('%' + txtCp.text + '%');

Thanks for your reply.
Already tried, doesn't work. Even putting "QuotedStr('%' + txtCP.text + '%')" into a stringvariable and use that as a value for the parameter (ParamByName('pContact').asString := sBuffer) won't work. Can't be that hard, can it?
Regards, Hendrikus.

[mangakissa]

you can't use a parameter for LIKE. Even in Delphi it's not possible. The only way to do it is using format function. But you have to create the where clause manually. The problem relies on (% ) . In some way the internal string helper can't find the ':'. I think it has something to do with '%'

[Hendrikus]

you can't use a parameter for LIKE. Even in Delphi it's not possible. The only way to do it is using format function. But you have to create the where clause manually. The problem relies on (% ) . In some way the internal string helper can't find the ':'. I think it has something to do with '%'

Problem solved. Thank you all for uor comments.

Create a stringvar, fill it with: Format('%s%s%s',['%', txtSearch.text,'%']) and use this as the value of the parameter. Voila !!!
See the next codesnippet. I've tried to give as much english comment as possible:
procedure TF_SCHERM100.cmdSearchClick(Sender: TObject);
var liFound  : Longint;
    sBuffer  : String;
    iIndex   : Integer;
begin
   liFound := 0;                              // Records found
   iIndex  := cboSearch.ItemIndex;            // In what column are you looking?

   gridList.Clear;                            // Clear the dbgrid for the result

   sBuffer := Trim(UpperCase(txtSearch.text));// Trim and convert to uppercase, the searchargument
   If (iIndex > 1) then begin
       // We don't want to search on Clientid (which is 1 in the combo)
       sBuffer := Format('%s%s%s',['%', Trim(Uppercase(txtSearch.text)),'%']);   // % + arg + %   !!!!!!!!!!!!!!!!!!!!!!!!!!!!! SOLVED !!!!!!!!!!!!!!!!!!!!!!
   end;

   // Built the query, dm101 is the datamodule which is created in the OnCreate() of the form
   Try
       with dm101.qSelect do begin
             Close;                                             // Close the query
             DisableControls;                                   // Disconnect the data aware controls
             Sql.Clear;                                         // Clear the sql
             Params.Clear;                                      // Clear the parameterlist

             Sql.Add('SELECT * FROM t_opdrachtgever ');         // t_opdrachtgever is a Firebirdtable with clientinfo
             Sql.Add('WHERE (t_opdrachtgever.o_id > 0) ');      // 'Base' where clause
             // Parameters?
             case iIndex of
                1: Sql.Add('AND (t_opdrachtgever.o_id = :pId)');                           // o_id = Clientid
                2: Sql.Add('AND (Upper(t_opdrachtgever.o_naam) LIKE :pNaam)');             // o_naam = Name of the client
                3: Sql.Add('AND (Upper(t_opdrachtgever.o_kontaktpersoon) LIKE :pKontakt)');// o_kontaktpersoon = the contact
                4: Sql.Add('AND (Upper(t_opdrachtgever.o_postcode) LIKE :pPc)');           // o_postcode = the zipcode
                5: Sql.Add('And (Upper(Trim(t_opdrachtgever.o_plaats)) LIKE :pPlaats)');   // o_plaats = the city
             end;
             if not Prepared then
                Prepare;

             case iIndex of
                1: ParamByName('pID').asInteger     := StrToInt(sBuffer);  // Search for the id
                2: ParamByName('pNaam').asString    := sBuffer;            // Search for the name
                3: ParamByName('pKontakt').asString := sBuffer;            // Search for the contactperson
                4: ParamByName('pPc').asString      := sBuffer;            // Search for the zip-code
                5: ParamByName('pPlaats').asString  := sBuffer;            // Search for the city
             end;
             Open;                     // Open the query
             First;                    // Go to the first record
             liFound := RecordCount;   // How many records match the searchcriteria
             EnableControls;           // Connect the data aware controls
       end;

   Except
      on E:Exception do begin
        LogError(E,'Kan klantgegevens niet ophalen.');     // Error trap
      end;
   end;

   Knoppen(liFound);                // If there are records involved, set the buttons

end;