Lazarus

Programming => Databases => Topic started by: reddy on December 11, 2018, 07:10:11 am

Title: TSQLQuery - error while using Like wildcard
Post by: reddy on December 11, 2018, 07:10:11 am

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

Title: Re: TSQLQuery - error while using Like wildcard
Post by: reddy on December 11, 2018, 08:35:56 am

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;

Title: Re: TSQLQuery - error while using Like wildcard
Post by: dsiders on December 11, 2018, 08:48:46 am

Quote from: reddy on December 11, 2018, 08:35:56 am

Ok - 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.

Title: Re: TSQLQuery - error while using Like wildcard
Post by: reddy on December 11, 2018, 09:26:00 am

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??

Title: Re: TSQLQuery - error while using Like wildcard
Post by: reddy on December 11, 2018, 09:27:24 am

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

Title: Re: TSQLQuery - error while using Like wildcard
Post by: dsiders on December 11, 2018, 09:58:57 am

Quote from: reddy on December 11, 2018, 09:26:00 am

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??

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;


Title: Re: TSQLQuery - error while using Like wildcard
Post by: rvk on December 11, 2018, 10:37:10 am

Quote from: reddy on December 11, 2018, 09:26:00 am

Original 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.

Title: Re: TSQLQuery - error while using Like wildcard
Post by: reddy on December 11, 2018, 11:28:20 am

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  :)

Title: Re: TSQLQuery - error while using Like wildcard
Post by: Zvoni on December 11, 2018, 12:02:30 pm

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'


Title: Re: TSQLQuery - error while using Like wildcard
Post by: reddy on December 11, 2018, 03:57:51 pm

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

Title: Re: TSQLQuery - error while using Like wildcard
Post by: rvk on December 11, 2018, 04:04:34 pm

Quote from: reddy on December 11, 2018, 03:57:51 pm

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.

You shouldn't quote parameters. So the quotedstr() and Format() variants will not work.
The first one, however, should work.

Code: Pascal  [Select][+][-]

1. 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).

Title: Re: TSQLQuery - error while using Like wildcard
Post by: Zvoni on December 11, 2018, 05:15:18 pm

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!

Title: Re: TSQLQuery - error while using Like wildcard
Post by: reddy on December 11, 2018, 06:07:36 pm

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  :)

Title: Re: TSQLQuery - error while using Like wildcard
Post by: lucamar on December 11, 2018, 06:23:33 pm

Quote from: reddy on December 11, 2018, 06:07:36 pm

Yes - 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.

Title: Re: TSQLQuery - error while using Like wildcard
Post by: Zvoni on December 11, 2018, 06:39:37 pm

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