Parameter without Inverted Commas

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
moppelstroppel
Posts: 19
Joined: Sat 25 Aug 2018 08:09

Parameter without Inverted Commas

Post by moppelstroppel » Fri 08 Jan 2021 10:05

Hello all

I have the following statement

"SELECT * FROM WHERE n_id IN (:ids)"
UnicodeString idList= "12, 15, 30";
Query->ParamByName("ids")->AsString = idList;


The executed statement is
"SELECT * FROM WHERE n_id IN ('12, 15, 30')"

MySQL returns only one record n_id = 12

I need this statement.
"SELECT * FROM WHERE n_id IN (12, 15, 30)"

Thanks
Thomas

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

Re: Parameter without Inverted Commas

Post by ViktorV » Mon 11 Jan 2021 16:38

This behavior of MyDAC is correct, since in this case, the query SELECT id, name FROM table WHERE id IN ('1, 2, 3') will be created, during execution of which MySQL server will return only one row. You can check this by executing a corresponding query using the standard means, for example, MySQL Workbench.
To solve the task, you can use the following code:

Code: Select all

   MyQuery.SQL.Text := 'SELECT * FROM table WHERE id n_id IN (:parameter1, :parameter2, :parameter3)';
   MyQuery.ParamByName('parameter1').AsInteger := 12;
   MyQuery.ParamByName('parameter2').AsInteger := 15;
   MyQuery.ParamByName('parameter3').AsInteger := 30;
   MyQuery.Open;
Also to solve your task, you can use Macros. For example:

Code: Select all

var
  InConditione: string;
...
  MyQuery.SQL.Text := 'SELECT * FROM table WHERE id n_id IN (&InConditione)';
  InConditione := '12, 15, 30';
  MyQuery.MacroByName('InConditione').Value := InConditione;
  MyQuery.Open;
You can learn more about Macros at: http://www.devart.com/mydac/docs/work_macros.htm

moppelstroppel
Posts: 19
Joined: Sat 25 Aug 2018 08:09

Re: Parameter without Inverted Commas

Post by moppelstroppel » Tue 12 Jan 2021 06:31

Sorry, but both is not working.
I don't know the number of parameters and I tried with macro. But I can only use a string lile

"1, 5, 7, 99, 12"

and then also this wil be between ' '.

I create the SQL statement on my own and it is working.

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

Re: Parameter without Inverted Commas

Post by ViktorV » Fri 15 Jan 2021 09:05

Unfortunately, we could not reproduce the issue.
In order for us to be able to give you a detailed answer, please compose a small sample demonstrating the described behavior with macros and send it to us through the contact form https://devart.com/company/contactform.html including the scripts for creating database objects.

Post Reply