Page 1 of 1
Parameter without Inverted Commas
Posted: Fri 08 Jan 2021 10:05
by moppelstroppel
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
Re: Parameter without Inverted Commas
Posted: Mon 11 Jan 2021 16:38
by ViktorV
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
Re: Parameter without Inverted Commas
Posted: Tue 12 Jan 2021 06:31
by moppelstroppel
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.
Re: Parameter without Inverted Commas
Posted: Fri 15 Jan 2021 09:05
by ViktorV
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.