Fulltextsearch, MySQL, issue
lazarus / 16-Databases

----------------------------------------------------------------------
teresa · 2024-04-06 03:25

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Program
test; uses data_access; const sql='select * from contractors where
match(contractordisplayname) against (''+name1 +name2'' in Boolean
Mode)'; var  name1, name2: String;  query, UIDfound:
String;  procedure TForm1.Button1Click(Sender: TObject);begin//
database components created/initialised in unit data_access; 
 data_setup;  //Names read by form  name1 := form1.Edit1.Text;  name2
:= form1.Edit2.Text;    query := StringReplace(sql,
'name1',name1,[rfignorecase]);  query := StringReplace(query,
'name2',name2,[rfignorecase]); // 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.   conSQLQuery.sql.text:=query; 
conSQLQuery.ExecSQL;  UIDfound :=
 conSQLQuery.FieldByName('contractorUID').AsString;    etc ....

----------------------------------------------------------------------
teresa · 2024-04-06 05:40

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 · 2024-04-06 06:13

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

----------------------------------------------------------------------
bytebites · 2024-04-06 06:28

Less error prone query string withCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 format('query %s %s',[name1,name2])

----------------------------------------------------------------------
teresa · 2024-04-06 07:04

Quote from: Zvoni on April 06, 2024, 08:13:35 am1) 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 · 2024-04-06 07:05

Yes, but same result!

----------------------------------------------------------------------
Zvoni · 2024-04-06 10:24

Quote from: teresa on April 06, 2024, 09:04:37 amQuote from: Zvoni on
April 06, 2024, 08:13:35 am1) 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