Lazarus SQlite Query and Like not as expected lazarus / 16-Databases ---------------------------------------------------------------------- WillyWortel · 2020-12-30 11:26 I'm new to this forum and new to Lazarus / SQLite. On my webpage i have kind of logbook function using html/php/MySQL. Now i'm trying to get the same functionality with Lazarus and SQlite. For some reason the Like command does not do what i'm expecting. When using like in a query it seems to me that the last char of the search text is not seen by the funtion. // open database     //Table doks              //Field DOK            |    Field Name    ---------------------------------------    //Field content: A01    |    Amsterdam1                 A02    |    Amsterdam2                 A03    |    .........                    ...    |    .........                 A10    |    Berlin1                 A11    |    Berlin2                          A12    |    .........                 ...    |    .........                 ...    |    .........                                    try       Connect := TSQLite3Connection.Create(nil);       Trans := TSQLTransaction.Create(Connect);       Connect.Transaction := Trans;       Connect.DatabaseName := DatabaseName;       Trans.StartTransaction;       SQLQuery := TSQLQuery.Create(nil);       SQLQuery.Transaction := Trans;       SQLQuery.ParseSQL := False;    except       Result := False;    end; //  select query      ASearch := '"' + ASearch + '%"';     SQLQuery.SQL.text:= 'select * from doks where DOK like ' + ASearch;     SQLQuery.Open;     SQLQuery.Last;     SQLQuery.First;      if ASearch := 'A%' result is a list of all 'A..' as expected     if ASearch := 'A1%' result is a list of all 'A1.' as expected     if ASearch := 'A12%' result is a empty list; <---------- this is NOT expected     same on field Name:     ASearch := '"' + ASearch + '%"';     SQLQuery.SQL.text:= 'select * from doks where Name like ' + ASearch;     SQLQuery.Open;     SQLQuery.Last;     SQLQuery.First;     if ASearch := 'A%' result is a list of all 'A.......' as expected     if ASearch := 'Amster%' result is a list of 'Amsterdam1' 'Amsterdam2' as expected     if ASearch := 'Amsterdam1%' result is a empty list; <---------- this is NOT expected Does someone know what i am missing? ---------------------------------------------------------------------- rvk · 2020-12-30 15:27 Quote from: WillyWortel on December 30, 2020, 12:26:42 pmDoes someone know what i am missing? Documentation says: QuoteA percent symbol ("%") in the LIKE pattern matches any sequence of zero or more characters in the string.https://sqlite.org/lang_expr.html And for me (testing) it also works fine (when % matches zero characters). i.e. select * from test where test1 like "A12%" results in the record with A12 for me. So there must be something wrong at your end. Try to show the complete SQL just before execution with Showmessage(SQLQuery.SQL.text); Press the message and press Ctrl+C to copy the text and paste it here in a message. ---------------------------------------------------------------------- WillyWortel · 2020-12-31 13:10 Thanks for replying rvk,  i'm sure its on my end. This was working in other apps, its just a simple SQL statement. as requested: Content messageshow with no result select * from doks where DOK like "A12%" ===  no results Content messageshow with result select * from doks where DOK like "A1%" === result 10   A10   Mannheim                   Baden    11   A11   Offenburg                   Baden    12   A12   Pforzheim                   Baden    13   A13   Hochrhein - Klettgau   Baden    14   A14   Schwarzwald           Baden    15   A15   Renchtal                   Baden    16   A16   Emmendingen           Baden    17   A17   Bad Säckingen           Baden    18   A18   Furtwangen           Baden    19   A19   Odenwald                   Baden       I really have no idea what's going on. I think it must be in my code because the SQL statement just works in SQLiteStudio. This is the code I'm using: //Table //CREATE TABLE doks ( //    ID       INTEGER PRIMARY KEY AUTOINCREMENT, //    DOK      TEXT, //    Name     TEXT, //    District TEXT, //    Remarks  TEXT //); //procedure TfrmDok.dokShowlist(ASearch: string); //var //  i:integer; //begin //  try //    with SQLQuery do  //Fill Countries //    begin //      Close; //      //SQLQuery.ParseSQL := true;  //      if ASearch = '' then //          SQLQuery.SQL.text := 'select * from doks where 1' //      else //        begin //          ASearch := '"' + ASearch + '%"'; //          SQLQuery.SQL.text:= 'select * from doks where DOK like ' + ASearch; //        end; //      //showmessage(sql.text); //      Open; //      //Last; //      //First; // //      SGD.BeginUpdate; //      SGD.Clear; //      i:= 0; //      //if (SQLQuery.RecordCount > 0) then //      //begin //        while not (SQLQuery.EOF) do //        begin //          SGD.RowCount := SGD.RowCount + 1; //          SGD.Cells[0, i] := SQLQuery['DOK']; //          SGD.Cells[1, i] := SQLQuery['Name']; //          SGD.Cells[2, i] := SQLQuery['District']; //          SQLQuery.Next;  //get next row. //          inc(i); //        end; {while} //      //end; // //      SGD.endupdate; //     end; // //  except //    on E: EDatabaseError do //      ShowMessage('Database error: ' + E.ClassName + #13#10 + E.Message); //    on E: Exception do //      ShowMessage('Error: ' + E.ClassName + #13#10 + E.Message); //  end; //end; but I also did try to use substr:        select * from doks where substr(DOK,1,3) = 'A12'; and it was exactly the same result   select * from doks where substr(DOK,1,2) = 'A1'; <-- result   select * from doks where substr(DOK,1,3) = 'A12'; <-- no result Everytime no result on a exact match    select * from doks where DOK="A12"' gives no result <-- no result ================================================================= While writing this i tryed something else: I have only values of 3 char in the DOK field of the table so i changed  the value A12 to A121 in the table. The result of this is that i now find out that every search value bigger as 2 char's  leads to no result. So i think that someware in the chain, the value of my search var is cut to 2 chars. The problem is not the query but to find out where the value  in the search is being cut. (i think) This makes my previously asked question unnecessary and I have to find out where the error lies. Even though I have no idea what it could be. In any case, thank you for responding to my question. Happy New Year. Wim ---------------------------------------------------------------------- rvk · 2020-12-31 13:26 Really strange. I made a fiddle here https://www.db- fiddle.com/f/2hRDS2RcgQdYg3RSP834if/0 When you press Run you'll see it should work in SQLite 3.3. The query is exactly as your SQL.Text select * from doks where DOK like "A12%" So, there shouldn't be any cut at 2 positions for that. Did you try to do this in a database manager just to see if it's cut there too? (for example SQliteMan) ---------------------------------------------------------------------- WillyWortel · 2020-12-31 14:01 Yes i did, i do test all my queries in SQLiteStudio. And first i did not have latest SQlite dll, but i already updated to 3.34.0.0 But i'm very shure that it is not sqlite. And the code i posted is how i use it right now. I'm using Lazarus IDE v20.0.10 r63526 as lot of others do. I wil start a smal new app to test this again as you did. ---------------------------------------------------------------------- WillyWortel · 2020-12-31 15:50 Got it,.... TSringGrid: if there is 1 row result it didn't show up because of the Fixedrows=1 and I didn't realise that i had to add a extra row at  the star of the list. It was hidden. ---------------------------------------------------------------------- rvk · 2020-12-31 17:11 Yeah, sometimes it's the simplest things. Maybe a TDBGrid was easier