TSQLQuery - error while using Like wildcard
lazarus / 16-Databases

----------------------------------------------------------------------
reddy · 2018-12-11 06:10

Hi Experts,

I am trying to use where like wildcard with TSQLQuery as follows:

Query.SQL.Clear;
Query.SQL.Text := 'SELECT * FROM EMPTBL WHERE ADDRESS LIKE :CITY;';
Query.Params.ParamByName('CITY').AsString := '%Dallas%';

This does not seems to work and also trying to with other options like
'''%%Dallas%%''' does not work as well.

Appreciate your help
Thanks

----------------------------------------------------------------------
reddy · 2018-12-11 07:35

Posting here so that it might help others.

Ok - after playing with Format was able to execute with results.. here
is the working code.


Query.SQL.Clear;
Query.SQL.Text := 'SELECT * FROM EMPTBL ' + Format('WHERE CITY LIKE
"%%%s%%"', [cityname]);
Query.Prepare;
Query.Open;

----------------------------------------------------------------------
dsiders · 2018-12-11 07:48

Quote from: reddy on December 11, 2018, 08:35:56 amOk - after playing
with Format was able to execute with results.. here is the working
code.
Code: [Select]Query.SQL.Clear;
Query.SQL.Text := 'SELECT * FROM EMPTBL ' + Format('WHERE CITY LIKE
"%%%s%%"', [cityname]);
Query.Prepare;
Query.Open;

Just so you know...

It works because the second query uses the correct column name (CITY).
The original SQL statement was searching in the ADDRESS column.

----------------------------------------------------------------------
reddy · 2018-12-11 08:26

Sorry for the confusion.

Actually the SQL Statement was right (was trying to give an example
using Address/City".. just the formatting was not working..

Original query:
Query.Params.ParamByName('CITY').AsString := '%Dallas%'; - results in
exceptions..

Modified query: using format string
Format('WHERE CITY LIKE "%%%s%%"', [cityname]); // Worked.

Do you know how to use the above query with Params??

----------------------------------------------------------------------
reddy · 2018-12-11 08:27

fyi, I am using the Lazarus 2.0.0 RC2. if that helps.
Thanks

----------------------------------------------------------------------
dsiders · 2018-12-11 08:58

Quote from: reddy on December 11, 2018, 09:26:00 amSorry for the
confusion.

Actually the SQL Statement was right (was trying to give an example
using Address/City".. just the formatting was not working..

Original query:
Query.Params.ParamByName('CITY').AsString := '%Dallas%'; - results in
exceptions..

Modified query: using format string
Format('WHERE CITY LIKE "%%%s%%"', [cityname]); // Worked.

Do you know how to use the above query with Params??

No. The queries are using different column names in the WHERE clause.
The first one used ADDRESS. The second one used CITY.

Code: [Select]Query.SQL.Clear;

// note the WHERE clause
Query.SQL.Text := 'SELECT * FROM EMPTBL WHERE CITY LIKE :PVal';

Query.ParamCheck := True;
Query.Prepare;
Query.Params.ParamByName('PVal').AsString := '%Dallas%';
Query.Open;

----------------------------------------------------------------------
rvk · 2018-12-11 09:37

Quote from: reddy on December 11, 2018, 09:26:00 amOriginal query:
Query.Params.ParamByName('CITY').AsString := '%Dallas%'; - results in
exceptions..
Also always provide us with WHAT exceptions you get.
"It doesn't work" isn't really a problem-description.
State WHAT is not working, what you see as a result, exceptions,
error-messages or normal result included.

----------------------------------------------------------------------
reddy · 2018-12-11 10:28

Thanks dsiders. I will try the suggested code, and get back if I need
any more help.

rvk, understood, will make sure to capture and post executions going
forward.

Again - thanks for all your support

----------------------------------------------------------------------
Zvoni · 2018-12-11 11:02

Works as it's supoosed to work
Code: [Select]MySQLQuery.SQL.Text:='SELECT * FROM Manufacturer WHERE
Name LIKE :TEMP';
MySQLQuery.ParamByName('TEMP').AsString:='%mark%';
MySQLQuery.Open;
Writeln(MySQLQuery.FieldByName('Name').AsString);  //--> Returns
'Lexmark'

----------------------------------------------------------------------
reddy · 2018-12-11 14:57

Ok,
Still having issues if I go with ParamByName..

No error - however, if I use ParamByName no records found. However, if
I use Format() then the same query works.

I have this value assigned to a variable called:
docName:='015270000NaZvHQ'; <= 15 chars

However, in the documents table I do have 18 chars values. I am trying
to do a partial match using LIKE on the docName value against the tbl
field "Name".

I think I might be doing something wrong the way I assign values using
Params.

Same code as shared by dsiders. However, I pass the variable in place
of string as shown in above example.

The only difference here:
I assign variable name - docName to
Query.Params.ParamByName('Pval').AsString := '%'+docName+'%';
Or
Query.Params.ParamByName('Pval').AsString :=
quotedstr('%'+docName+'%');
Or
Query.Params.ParamByName('Pval').AsString := Format('"%%%s%%"',
[docName]);

None works - no errors or exceptions.. except no results selected.

Any help much appreciated.
Thanks

----------------------------------------------------------------------
rvk · 2018-12-11 15:04

Quote from: reddy on December 11, 2018, 03:57:51 pmThe only difference
here:
I assign variable name - docName to
Query.Params.ParamByName('Pval').AsString := '%'+docName+'%';
Or
Query.Params.ParamByName('Pval').AsString :=
quotedstr('%'+docName+'%');
Or
Query.Params.ParamByName('Pval').AsString := Format('"%%%s%%"',
[docName]);
None works - no errors or exceptions.. except no results selected.
You shouldn't quote parameters. So the quotedstr() and Format()
variants will not work.
The first one, however, should work.
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
Query.Params.ParamByName('Pval').AsString := '%'+docName+'%';
You should note that LIKE is case sensitive so it will only check
exact values (case included).

Maybe you can show the complete code you have now (including SQL).

----------------------------------------------------------------------
Zvoni · 2018-12-11 16:15

No idea if it makes a difference, but you did see, that i use the
ParamByName-Property of the Query-Object, and not the Params-
Enumeration?

Query.ParamByName('Name').asstring....... //No Params.Enumeration!

----------------------------------------------------------------------
reddy · 2018-12-11 17:07

Yes - I too was referring to this statement:
Query.Params.ParamByName('Pval').AsString := '%'+docName+'%';

However, I'm still using the format() - which is working - I will work
on parambyname sometime later this week. Need to hack this application
today.

Further, debugging the Query object - found out the actual  Create and
Database Connection had to be brought within the same function for it
to work - running queries and the results were stuck on first queried
record even on multiple loops..

Previously I had the code like this...

function getDocumentFromDB(docID: Integer): String;
begin
      if AConnection.Connected then
      begin
             Query.SQL.Clear;
             Query.SQL.Text:='SELECT DOC_ID, NAME, TYPE, DESC FROM
DOCUMENTS ' +
              Format('WHERE DOC_ID LIKE '%%%s%%'; [docID]);
             Query.Prepare;
             Query.Open;
             // I had also included Query.ExecSQL; I have commented
this out...

             Query.First;
             while not Query.EOF do
             begin
                    Fetch some records.....
                    The above query always returns 1 record - since
the matching doc_id has one-to-one record.
              end;

// while we exit this function and return back from DBConnection -
since we will fetch second document - based on the external function
which parses html files.. and then reenter this function. Doing so the
query will always return the previous record.

The way I fixed this is by moving Query Create and Database within
this function and that seems to have fixed the results stuck at the
first record.
      end;
end;

Procedure DBConnection()
begin
      Query := TSQLQuery.Create(nil);
      Query.DataBase := AConnection;
      getDocumentFromDB(documentID);
end;


I hope I was clear.
Again thanks for your continued support - really appreciate LaZarus
and Freepascal - and great community so supportive

----------------------------------------------------------------------
lucamar · 2018-12-11 17:23

Quote from: reddy on December 11, 2018, 06:07:36 pmYes - I too was
referring to this statement:
Query.Params.ParamByName('Pval').AsString := '%'+docName+'%';

However, I'm still using the format() - which is working - [...]

Note that Format('%%%s%%', [docName] will return the exact same string
as '%'+docName+'%'. There should be no problem using either form,
although the simpler string addition is quicker--in case you need to
do it lots of times.

----------------------------------------------------------------------
Zvoni · 2018-12-11 17:39

Errrr.... i was under the impression, that you also need a
Transaction-Object attached to your Query-Object?!?!??!?