TMyQuery and ParamByName

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
alphawiz1
Posts: 2
Joined: Sat 13 Apr 2019 18:50

TMyQuery and ParamByName

Post by alphawiz1 » Sun 14 Feb 2021 18:26

I'm trying to build a dynamic query where the user can select the field as part of the search.

My question is can I do this by using ParamByName?

Example :
MyQuery1.SQL := 'select * from mytable where :field = :value;'
MyQuery1.ParamByName('field').AsString := 'fieldname';
MyQuery1.ParamByName('value').AsString := 'myvalue';
MyQuery1.Execute;

The test didn't return any errors but it didn't return any results either when I reference MyQuery1.RecordCount , so I'm guessing it doesn't necessarily work this way, but if it does, what am I doing wrong? Or is there an easier way to do it? If so, could an example be provided?

hkarmeh
Posts: 4
Joined: Thu 01 Apr 2010 22:41

Re: TMyQuery and ParamByName

Post by hkarmeh » Mon 15 Feb 2021 07:33

Try

MyQuery1.SQL := 'select * from mytable where &field = :value;

MyQuery1.MacroByName('field').AsString := 'fieldname';
MyQuery1.ParamByName('value').AsString := 'myvalue';
MyQuery1.Execute;

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: TMyQuery and ParamByName

Post by ViktorV » Mon 15 Feb 2021 09:59

alphawiz1 wrote: Sun 14 Feb 2021 18:26 I'm trying to build a dynamic query where the user can select the field as part of the search.

My question is can I do this by using ParamByName?

Example :
MyQuery1.SQL.Text := 'select * from mytable where :field = :value;'
MyQuery1.ParamByName('field').AsString := 'fieldname';
MyQuery1.ParamByName('value').AsString := 'myvalue';
MyQuery1.Execute;

The test didn't return any errors but it didn't return any results either when I reference MyQuery1.RecordCount , so I'm guessing it doesn't necessarily work this way, but if it does, what am I doing wrong? Or is there an easier way to do it? If so, could an example be provided?
As hkarmeh wrote correctly to solve the issue, you can use Macros. For example:

Code: Select all

  MyQuery1.SQL := 'select * from mytable where &field = :value;'
  MyQuery1.MacroByName('field').Value := 'fieldname'; //If you set macro with the AsString property, it will be quoted. 
  MyQuery1.ParamByName('value').AsString := 'myvalue';
  MyQuery1.Execute;
You can learn more about Macros at: http://www.devart.com/mydac/docs/work_macros.htm

Post Reply