三层结构中，如果客户端的查询条件是动态变化的，在应用服务器端该如何处理？(100分)

在客户端提供了若干个可供查询的条件，用户可以随意组合。举个例子：客户端的查询条件
有如下几个：1.文件题目；2.发文日期；3.文件编号等等。用户可以选择查询条件并给出相
应的值。我应该如何写服务器端的sql语句，客户端又是如何实现的。请写详细点。我是个新
手。

你可以用SQL语句，
Select * from Table_A,Table_B
Where 文件题目='XXX' and 发文日期='XXX' 。。。。

当然以上的SQL语句要动态生成，
如客户选了 "3.文件编号 " 你就加上“ and 文件编号='XXX' “

to lha
我现在的问题就是不知道该如何动态地加上查询条件。

在应用服务器端加个DatasetProvider设定它的option的poAllowCommandText为true
然后在客户端加入ClientDataSet把Provider连到刚才的DataSetProvider后加入如下
语句:
ClientDataSet1.Close;
ClientDataSet1.CommandText:=动态SQL语句;
ClientDataSet1.open;

to xiecc
怎么往动态SQL语句中传参数。比如sql语句如下：select 文件题目 from XXXX
where XXXX.文件编号=araFileNo;

ClientDataSet1.Close;
ClientDataSet1.CommandText:='select 文件题目 from XXXX where XXXX.文件编号=araFileNo';
ClientDataSet1.params.parambyname('paraFileNo').value:=;
ClientDataSet1.open;

在应用服务器端加TQuery TProvider 在Tprovider 的 onDataRequest方法加入
Query.sql.text:=input;
在客户端加入ClientDataSet把ProviderName连到刚才的TProvider 在查询数据用下面代码

ClientDataSet1.Close;
ClientDataSet1.Provider.DataRequest('Select * from Table1 where ....');// 合法SQL语句
ClientDataSet1.open;

wumeng的代码应该是对的,不过这样的话SQL语句最好还是写的应用服务器上,
客户端只要替换参数就行了,做三层的一个原因就是要将SQL语句移到应用服务器

我想 glasscrystal 的意思是 动态的生成 SQL 语句 ，即：

SQL ： string；

At first ,
SQL ：= 'select 文件题目 from XXXX where ';

if the user select condition 1. then the SQL will like this :

SQL := SQL + '文件编号=araFileNo';

if user also select condition 2. then you should add the follow :

SQL := SQL + 'and 发文日期="XXX" ';

at last the SQL is :
select 文件题目 from XXXX where 文件编号=araFileNo and 发文日期="XXX" ;

这样大概可以了

lha的意思是对的，不过我的问题不在于不会写这个动态的SQL语句，而是我不太清楚用什么
方法可以把SQL语句中的参数（参数个数是不定的，根据用户作出的选择）传给服务器端。

SQL ： string；

At first ,
SQL ：= 'select 文件题目 from XXXX where ';

if the user select condition 1. then the SQL will like this :

SQL := SQL + '文件编号=araFileNo';

if user also select condition 2. then you should add the follow :

SQL := SQL + 'and 发文日期=araFileDate ';

at last the SQL is :
select 文件题目 from XXXX where 文件编号=araFileNo and 发文日期=araFileData ;
这个SQL语句我想在客户端生成，然后传给服务器,但是paraFileNo和paraFileDate着两个参数
如何传递给服务器.服务器端的TQuery控件没有写SQL语句。

顺便问一下，如果SQL语句是string型的，对SQL语句的长度是否有限制？

这好说，动态去组织它吗。
看看我的程序吧！

procedure TRe_KCPD_frm.BitBtn1Click(Sender: TObject);
var
m,n:Integer;
sql,s_item,s_price,dat1,dat2,s_type:wideString;
begin

s_type:='';
if sbType.Down then
begin
if cbType.Text='西药片剂' then s_type:=' and b.typename=''西药片剂'' ';
if cbType.Text='针粉酊膏' then s_type:=' and b.typename in(''西药针剂'',''器械'',''非药品'') ';
if cbType.Text='中成药' then s_type:=' and b.typename=''中药'' ';
end;

if not(sbSPS.Down or sbQCKC.Down or sbWFS.Down or sbZMS.Down or sbJHS.Down or sbXSS.Down) then
begin
showmessage('没有选查询项');
Exit;
end;

if sbStarDate.Down then
dat1:=DateTimeToStr(dtpStarDate.Datetime)
else
dat1:='1980-01-01';

if sbLastDate.Down then
dat2:=DateTimeToStr(dtpLastDate.Datetime)
else
dat2:='2100-01-01';

s_price:='';
if sbMinWsprice.Down then
s_price:=s_price+' and a.wsprice>='+FloatToStr(seMinWsprice.Value);

if sbMaxWspirce.Down then
s_price:=s_price+' and a.wsprice<='+FloatToStr(seMaxWsprice.Value);

if sbMinRprice.Down then
s_price:=s_price+' and a.rprice>='+FloatToStr(seMinRprice.Value);

if sbMaxRprice.Down then
s_price:=s_price+' and a.rprice<='+FloatToStr(seMaxRprice.Value);

m:=lvItemList.Items.Count;
if sbItemList.Down and (m>0) then
begin
s_item:=' and a.itemdcode in (';
for n:=1 to m do
if m=n then
s_item:=s_item+''''+lvItemList.Items[n-1].Caption+''''+') '
else
s_item:=s_item+''''+lvItemList.Items[n-1].Caption+''''+',';
end;

sql:=' Select itemdcode,itemname,speci,qtyunit,proname,wsprice'; //,SUM(a1) AS SPXL, SUM(a2) AS QCKK, SUM(a1)-SUM(a3) AS ZMS, SUM(a3) AS WFS, SUM(a4) AS WFJE, SUM(a5) AS JH, SUM(a6) AS XS ';
if sbSPS.Down then sql:=sql+' , SUM(a1) AS SPSL, (SUM(a1)*wsprice) AS SPJE ';
if sbZMS.Down then sql:=sql+' , (SUM(a1)-SUM(a3)) AS ZMSL, (SUM(a1)-SUM(a3))*wsprice AS ZMJE ';
if sbWFS.Down then sql:=sql+' , SUM(a3) AS WFSL, SUM(a4) AS WFJE ';
if sbQCKC.Down then sql:=sql+' , SUM(a2) AS QCKK ';
if sbJHS.Down then sql:=sql+' , SUM(a5) AS JH ';
if sbXSS.Down then sql:=sql+' , SUM(a6) AS XS ';

sql:=sql+' from ( ';

if sbSPS.Down then
begin
sql:=sql+' select a.itemdcode,b.itemname,b.speci,b.qtyunit,c.proname,b.wsprice';//,SUM(a.qty) AS a1,0 AS a2,0 AS a3,0 AS a4,0 AS a5,0 AS a6 '
if sbSPS.Down then sql:=sql+' , SUM(a.qty) AS a1 ';
if sbQCKC.Down then sql:=sql+' , 0 AS a2 ';
if sbWFS.Down or sbZMS.Down then sql:=sql+' , 0 AS a3, 0 AS a4 ';
if sbJHS.Down then sql:=sql+' , 0 AS a5 ';
if sbXSS.Down then sql:=sql+' , 0 AS a6 ';

sql:=sql+' from t_itembase AS c INNER JOIN(t_itembusiness AS b INNER JOIN t_salelist AS a ON b.itemdcode=a.itemdcode) ON c.itemdcode=a.itemdcode '
+' where whflag>=''2'' and saleflag>=''2'' '+s_price+s_item+s_type+' and a.configdate= '
+' (select distinct max(configdate) from t_salelist where itemdcode=a.itemdcode and whflag>=''2'' and saleflag>=''2'' and configdate<=#'+dat2+'#) '
+' group by a.itemdcode,b.itemname,b.speci,b.qtyunit,c.proname,b.wsprice ';
if sbQCKC.Down or sbWFS.Down or sbZMS.Down or sbJHS.Down or sbXSS.Down then sql:=sql+' union '
end;

if sbQCKC.Down then
begin
sql:=sql+' select a.itemdcode,b.itemname,b.speci,b.qtyunit,c.proname,b.wsprice ';//,0 AS a1,SUM(a.qty) AS a2,0 AS a3,0 AS a4,0 AS a5,0 AS a6 '
if sbSPS.Down then sql:=sql+' , 0 AS a1 ';
if sbQCKC.Down then sql:=sql+' , SUM(a.qty) AS a2 ';
if sbWFS.Down or sbZMS.Down then sql:=sql+' , 0 AS a3, 0 AS a4 ';
if sbJHS.Down then sql:=sql+' , 0 AS a5 ';
if sbXSS.Down then sql:=sql+' , 0 AS a6 ';

sql:=sql+' from t_itembase AS c INNER JOIN(t_itembusiness AS b INNER JOIN t_salelist AS a ON b.itemdcode=a.itemdcode) ON c.itemdcode=a.itemdcode '
+' where whflag>=''2'' and saleflag>=''2'' '+s_price+s_item+s_type+' and a.configdate= '
+' (select distinct max(configdate) from t_salelist where itemdcode=a.itemdcode and whflag>=''2'' and saleflag>=''2'' and configdate<=#'+dat1+'#) '
+' group by a.itemdcode,b.itemname,b.speci,b.qtyunit,c.proname,b.wsprice ';
if sbWFS.Down or sbZMS.Down or sbJHS.Down or sbXSS.Down then sql:=sql+' union '
end;

if sbZMS.Down or sbWFS.Down then
begin
sql:=sql+' select a.itemdcode,b.itemname,b.speci,b.qtyunit,c.proname,b.wsprice ';//,0 AS a1,0 AS a2,SUM(a.qtypaid) AS a3,SUM(amtpaid) AS a4,0 AS a5,0 AS a6 '
if sbSPS.Down then sql:=sql+' , 0 AS a1 ';
if sbQCKC.Down then sql:=sql+' , 0 AS a2 ';
if sbWFS.Down or sbZMS.Down then sql:=sql+' , SUM(a.qtypaid) AS a3, SUM(amtpaid) AS a4 ';
if sbJHS.Down then sql:=sql+' , 0 AS a5 ';
if sbXSS.Down then sql:=sql+' , 0 AS a6 ';

sql:=sql+' from t_itembase AS c INNER JOIN(t_itembusiness AS b INNER JOIN t_salelist AS a ON b.itemdcode=a.itemdcode) ON c.itemdcode=a.itemdcode '
+' where optype=''购入'' and whflag>=''2'' and saleflag>=''2'' '+s_price+s_item+s_type+' and configdate>=#'+dat1+'# and configdate<=#'+dat2+'# '
+' group by a.itemdcode,b.itemname,b.speci,b.qtyunit,c.proname,b.wsprice ';
if sbJHS.Down or sbXSS.Down then sql:=sql+' union '
end;

if sbJHS.Down then
begin
sql:=sql+' select a.itemdcode,b.itemname,b.speci,b.qtyunit,c.proname,b.wsprice ';//,0 AS a1,0 AS a2,0 AS a3,0 AS a4,SUM(a.qty) AS a5,0 AS a6 '
if sbSPS.Down then sql:=sql+' , 0 AS a1 ';
if sbQCKC.Down then sql:=sql+' , 0 AS a2 ';
if sbWFS.Down or sbZMS.Down then sql:=sql+' , 0 AS a3, 0 AS a4 ';
if sbJHS.Down then sql:=sql+' , SUM(a.qty) AS a5 ';
if sbXSS.Down then sql:=sql+' , 0 AS a6 ';

sql:=sql+' from t_itembase AS c INNER JOIN(t_itembusiness AS b INNER JOIN t_salelist AS a ON b.itemdcode=a.itemdcode) ON c.itemdcode=a.itemdcode '
+' where optype=''购入'' and whflag>=''2'' and saleflag>=''2'' '+s_price+s_item+s_type+' and configdate>=#'+dat1+'# and configdate<=#'+dat2+'# '
+' group by a.itemdcode,b.itemname,b.speci,b.qtyunit,c.proname,b.wsprice ';
if sbXSS.Down then sql:=sql+' union ';
end;

if sbXSS.Down then
begin
sql:=sql+' select a.itemdcode,b.itemname,b.speci,b.qtyunit,c.proname,b.wsprice ';//,0 AS a1,0 AS a2,0 AS a3,0 AS a4,0 AS a5,SUM(a.qty) AS a6 '
if sbSPS.Down then sql:=sql+' , 0 AS a1 ';
if sbQCKC.Down then sql:=sql+' , 0 AS a2 ';
if sbWFS.Down or sbZMS.Down then sql:=sql+' , 0 AS a3, 0 AS a4 ';
if sbJHS.Down then sql:=sql+' , 0 AS a5 ';
if sbXSS.Down then sql:=sql+' , SUM(a.qty) AS a6 ';

sql:=sql+' from t_itembase AS c INNER JOIN(t_itembusiness AS b INNER JOIN t_salelist AS a ON b.itemdcode=a.itemdcode) ON c.itemdcode=a.itemdcode '
+' where optype=''批发'' and whflag>=''2'' and saleflag>=''2'' '+s_price+s_item+s_type+' and configdate>=#'+dat1+'# and configdate<=#'+dat2+'# '
+' group by a.itemdcode,b.itemname,b.speci,b.qtyunit,c.proname,b.wsprice ';
end;
sql:=sql+' ) '
+' Group by itemdcode,itemname,speci,qtyunit,proname,wsprice ';

if cdsKCPD.Active then cdsKCPD.Close;
cdsKCPD.DataRequest(sql);
cdsKCPD.Open;

pnlKclFind.Visible:=False;
end;

其实wumeng和xiecc都对，不过我觉得wumeng的比较好，一方面效率高，
另一方面服务器端也不要使用过多的DataSetProvider，只要一个就可以了。相对来说，
在客户端使用ClientDataSet.CommandText来引起动态SQL语句，会比利用服务器来得快。
当然，利用服务器来引起SQL语句也有它的好处，如可以最大限度地减少被他人窥看到SQL
语句的可能。不过要动态引起SQL，千万要将服务器的DataSetProvider.Options[dgAllowCommandText]=True;

要传递参数可以直接使用
CDS.commandText :='select * from table where name = '''+Edit1.Text +''''
或者:
CDS.commandText :='select * from table where name = :name';
CDS.Params[0].AsString := Edit1.Text;
均可

我记得在这种情况下好像用=:可以传参数(不是:=)

很早以前编程序我用的是这种方法

TO glasscrystal：
动态生成SQL以后，调用中间层函数将它动态赋给ADOQUERY，（中间层放ADOQUERY，
DATAPROVIDER，客户端用CLIENTDATASET取值），唯一需要注意的是中间层函数末尾不要
调用SETCOMPLETE。
此法我一直使用，灵活，高效（自卖自夸，哈哈！）

李大侠：
小弟急需TeeTree控件，最好附带源代码，不知在那里能找到，需李大侠指点，万分感激！

这有什么难，在应用服务器端添加Tquery和TdatasetProvider，TDatasetProvider连接Tquery，
并且把TDataSetProvider.options :=[poAllowCommandText]，然后运行注册。在前端，
添加TclientDataset1，属性ProviderName=DatasetProvider1，然后在button1的Onclick
事件为：
procedure TForm.Button1Click(Sender: TObject);
var
sqlstring:string;
begin
if edit1.text<>'' then
begin
sqlstring:=' 文件题目 ='''+edit1.text+'''';
end;
if edit2.text<>'' then
begin
if sqlstring<>'' then
begin
sqlstring:=sqlstring+' and 发文日期='''+edit2.text+'''';
end
else
begin
sqlstring:=' 发文日期='''+edit2.text+'''';
end;
end;
if edit3.text<>'' then
begin
if sqlstring<>'' then
begin
sqlstring:=sqlstring+' and 文件编号='''+edit3.text+'''';
end
else
begin
sqlstring:=' 文件编号='''+edit3.text+'''';
end;
end;

with clientdataset1 do
begin
close;
commandtext:='select * from table1 where '+sqlstring;
open;
end;
end;

各位的回答都很精彩，主人应接受答案了。

多人接受答案了。