Array parameter for TADOQuery in Delphi 2010


5 days ago


1.1k time


I need to execute a simple query:

SELECT * FROM MyTable WHERE Id IN (:ids)

Obviously, it returns the set of records which have their primary key 'Id' in the given list. How can I pass an array of integer IDs into ADOQuery.Parameters for parameter 'ids'? I have tried VarArray - it does not work. Parameter 'ids' has FieldType = ftInteger by default, if it matters.

2 answers


В парам просто принимает разделенных запятыми значений , как (1,2,3,4,5) , поэтому я предполагаю , что вы установите тип данных в ftstring и просто построить строку и передать ...? Не пробовал , но это то , что я бы попробовать ...


There is no parameter type that can be used to pass a list of values to in. Unfortunately, this is one of the shortcomings of parameterized SQL.

You'll have to build the query from code to either generate the list of values, or generate a list of parameters which can then be filled from code. That's because you can pass each value as a different parameter, like this:

SELECT * FROM MyTable WHERE Id IN (:id1, :id2, :id3)

But since the list will probably have a variable size, you'll have to alter the SQL to add parameters. In that case it is just as easy to generate the list of values, although parametereized queries may be cached better, depending on which DB you use.