Recent

Author Topic: Fulltextsearch, MySQL, issue  (Read 1278 times)

teresa

  • New Member
  • *
  • Posts: 19
Fulltextsearch, MySQL, issue
« on: April 06, 2024, 05:25:27 am »
Hi

I'm having difficulty with a full-text search in MariaDB/MySQL, using a MySQL 5.6 connection ( conSQLQuery is  TMySQL56Connection).

I have a full text index on a name field (contractorDisplayName varchar100)) and want to retrieve the UIDs that include names - for example 'John Smith', 'John Smith and Co', 'Smith and John Contracting' should all be retrieved given John and Smith as search terms.

The little demo program fails in one of two ways. Either conSQLQuery.FieldByName('contractorUID') fails, with 'field contractorUID dows not exist'  or zero records are returned (conSQLQuery.fieldcount = 0)

The query that is generated works fine while interacting directly with MariaDB.

I've tried all sorts of variations, without success. What am I missing?

Cheers
Teresa
Code: Pascal  [Select][+][-]
  1. Program test;
  2.  
  3. uses data_access;
  4.  
  5. const sql='select * from contractors where match(contractordisplayname) against (''+name1 +name2'' in Boolean Mode)';
  6.  
  7. var
  8.   name1, name2: String;
  9.   query, UIDfound: String;
  10.  
  11.  
  12. procedure TForm1.Button1Click(Sender: TObject);
  13. begin
  14. // database components created/initialised in unit data_access;
  15.    data_setup;
  16.  
  17. //Names read by form
  18.   name1 := form1.Edit1.Text;
  19.   name2 := form1.Edit2.Text;  
  20.   query := StringReplace(sql, 'name1',name1,[rfignorecase]);
  21.   query := StringReplace(query, 'name2',name2,[rfignorecase]);
  22.  
  23. // A check here shows query='select * from contractors where match(contractordisplayname) against('+John +Smith' // in Boolean mode'. And that query executes fine interactively, returning 8 records.
  24.  
  25.   conSQLQuery.sql.text:=query;
  26.   conSQLQuery.ExecSQL;
  27.   UIDfound :=  conSQLQuery.FieldByName('contractorUID').AsString;
  28.  
  29.   etc ....
  30.  

teresa

  • New Member
  • *
  • Posts: 19
Re: Fulltextsearch, MySQL, issue
« Reply #1 on: April 06, 2024, 07:40:19 am »
A first obvious error: replace   conSQLQuery.ExecSQL; with consSQLQuery.open;

But that only fixes it "most of the time". From time to time the same old errors occur - zero records or 'field does not exist'.

Zvoni

  • Hero Member
  • *****
  • Posts: 2364
Re: Fulltextsearch, MySQL, issue
« Reply #2 on: April 06, 2024, 08:13:35 am »
1) your double quoting to escape the name-strings looks fishy
2) Match/against is not allowed in a WHERE-clause: https://dev.mysql.com/doc/refman/8.3/en/fulltext-search.html

Edit: ok, you’re not escaping but replacing. You missed the +-signs
« Last Edit: April 06, 2024, 08:16:18 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

bytebites

  • Hero Member
  • *****
  • Posts: 644
Re: Fulltextsearch, MySQL, issue
« Reply #3 on: April 06, 2024, 08:28:36 am »
Less error prone query string with
Code: Pascal  [Select][+][-]
  1.  format('query %s %s',[name1,name2])

teresa

  • New Member
  • *
  • Posts: 19
Re: Fulltextsearch, MySQL, issue
« Reply #4 on: April 06, 2024, 09:04:37 am »
1) your double quoting to escape the name-strings looks fishy
2) Match/against is not allowed in a WHERE-clause: https://dev.mysql.com/doc/refman/8.3/en/fulltext-search.html

Edit: ok, you’re not escaping but replacing. You missed the +-signs

Umm...
match-against works fine in a WHERE clause, using Boolean mode - the query in the code snippet works fine interactively.
and the + signs are there - in the template and the resulting qyuery - which works!

teresa

  • New Member
  • *
  • Posts: 19
Re: Fulltextsearch, MySQL, issue
« Reply #5 on: April 06, 2024, 09:05:22 am »
Yes, but same result!

Zvoni

  • Hero Member
  • *****
  • Posts: 2364
Re: Fulltextsearch, MySQL, issue
« Reply #6 on: April 06, 2024, 12:24:59 pm »
1) your double quoting to escape the name-strings looks fishy
2) Match/against is not allowed in a WHERE-clause: https://dev.mysql.com/doc/refman/8.3/en/fulltext-search.html

Edit: ok, you’re not escaping but replacing. You missed the +-signs

Umm...
match-against works fine in a WHERE clause, using Boolean mode - the query in the code snippet works fine interactively.
and the + signs are there - in the template and the resulting qyuery - which works!
Have to agree. I went to the detailed site of MySQL, and you’re correct.
Try: after you do the replace, but before executing the query, write out the SQL-string.
We might see something there

Edit: just saw it in your first post: you don’t check fieldcount for „how many rows are returned“
You could check RecordCount AFTER moving to the last record
« Last Edit: April 06, 2024, 12:40:52 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018