Delphi:如何将列表作为参数传递给SQL查询? [英] Delphi: how to pass a list as a parameter to a SQL query?
问题描述
这是一个例子。 MyQuery是这样的:
select * from myTable其中intKey in:listParam
/ pre>
我将参数设置为列表或数组或其他内容:
MyQuery.ParamByName('listParam')。AsSomething:= [1,2,3];
它会导致此查询发送到sql server:
select * from myTable其中intKey in(1,2,3)
如果解决方案也可以使用字符串,这个查询将会更好:
select * from myTable where stringKey in:listParam
成为:
select * from myTable where stringKey in('a','b','c')
我相信这是一个简单的问题,但IN不是搜索网络的好关键词。
请回答我应该如何配置IDE中的参数,查询以及如何传递参数。
我正在使用Delphi 7。
已编辑:我正在考虑答案是不可能直接执行。如果有人给我一个非黑客答案,接受的答案将会改变。
解决方案AFAIK,这是不可能的。
您必须将列表转换为纯文本的SQL列表。
例如: / p>
function ListToText(const Args:数组的字符串):string;超载;
var i:integer;
begin
result:='(';
for i:= 0 to high(Args)do
result:= result + QuotedStr(Args [i])+', ';
result [length(result)]:=')';
结束
函数ListToText(const Args:整数数组):string;超载;
var i:integer;
begin
result:='(';
for i:= 0 to high(Args)do
result:= result + IntToStr(Args [i])+', ';
result [length(result)]:=')';
结束
如此使用:
SQL.Text:='select * from myTable其中intKey在'+ ListToText([1,2,3]);
SQL.Text:='select * from myTable其中stringKey在'+ ListToText(['a','b','c']));
I have a list of integers or of strings and need to pass it as a parameter for a Delphi DataSet. How to do it?
Here is an example. MyQuery is something like:
select * from myTable where intKey in :listParam
I'd set a parameter as a list or array or something else:
MyQuery.ParamByName('listParam').AsSomething := [1,2,3];
and it would result in this query sent to the sql server:
select * from myTable where intKey in (1, 2, 3)
It would be even better if the solution would also work with strings, making this query:
select * from myTable where stringKey in :listParam
become:
select * from myTable where stringKey in ('a', 'b', 'c')
I believe this is a simple question, but "IN" isn't a good keyword for searching the web.
Please answer how I should configure the parameter in the IDE, the query and how to pass the parameters.
I'm using Delphi 7.
Edited: I'm considering the answer is "it isn't possible to do directly". If someone give me a non-hackish answer, the accepted answer will be changed.
解决方案AFAIK, it is not possible directly.
You'll have to convert the list into a SQL list in plain text.
For instance:
function ListToText(const Args: array of string): string; overload; var i: integer; begin result := '('; for i := 0 to high(Args) do result := result+QuotedStr(Args[i])+','; result[length(result)] := ')'; end; function ListToText(const Args: array of integer): string; overload; var i: integer; begin result := '('; for i := 0 to high(Args) do result := result+IntToStr(Args[i])+','; result[length(result)] := ')'; end;
To be used as such:
SQL.Text := 'select * from myTable where intKey in '+ListToText([1,2,3]); SQL.Text := 'select * from myTable where stringKey in '+ListToText(['a','b','c']);
这篇关于Delphi:如何将列表作为参数传递给SQL查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!