ADOQuery的SQL属性是不是有长度限制
如题,数据库连接的是用VFP的DBF文件
执行如下SQL(两条SQL都可以在VFP中正常执行)
1
select xq,dw,dwm,count(*) bms,sum(iif(zf>0,1,0)) cks,sum(iif(zf>=533,1,0)) talgfs,sum(iif(zf>=533,1,0))/count(*)*100 talgfl,sum(iif(zf>=520,1,0)) talyxs,sum(iif(zf>=520,1,0))/count(*)*100 talyxl,sum(iif(zf>=390,1,0)) talhgs,sum(iif(zf>=390,1,0))/count(*)*100 talhgl,sum(zf)/count(*) talpjf,(((sum(iif(zf>=520,1,0))/count(*))*100)*0.3)+(((sum(iif(zf>=390,1,0))/count(*))*100)*0.3)+((sum(zf)/count(*))*0.4) talzpf,sum(iif(yw>=96,1,0)) cj1yxs,sum(iif(yw>=96,1,0))/count(*)*100 cj1yxl,sum(iif(yw>=72,1,0)) cj1hgs,sum(iif(yw>=72,1,0))/count(*)*100 cj1hgl,sum(yw)/count(*) cj1pjf,(((sum(iif(yw>=96,1,0))/count(*))*100)*0.3)+(((sum(iif(yw>=72,1,0))/count(*))*100)*0.3)+((sum(yw)/count(*))*0.4) cj1zpf,sum(iif(sx>=96,1,0)) cj2yxs,sum(iif(sx>=96,1,0))/count(*)*100 cj2yxl,sum(iif(sx>=72,1,0)) cj2hgs,sum(iif(sx>=72,1,0))/count(*)*100 cj2hgl,sum(sx)/count(*) cj2pjf,(((sum(iif(sx>=96,1,0))/count(*))*100)*0.3)+(((sum(iif(sx>=72,1,0))/count(*))*100)*0.3)+((sum(sx)/count(*))*0.4) cj2zpf,sum(iif(wy>=96,1,0)) cj3yxs,sum(iif(wy>=96,1,0))/count(*)*100 cj3yxl,sum(iif(wy>=72,1,0)) cj3hgs,sum(iif(wy>=72,1,0))/count(*)*100 cj3hgl,sum(wy)/count(*) cj3pjf,(((sum(iif(wy>=96,1,0))/count(*))*100)*0.3)+(((sum(iif(wy>=72,1,0))/count(*))*100)*0.3)+((sum(wy)/count(*))*0.4) cj3zpf,sum(iif(wkzh>=96,1,0)) cj4yxs,sum(iif(wkzh>=96,1,0))/count(*)*100 cj4yxl,sum(iif(wkzh>=72,1,0)) cj4hgs,sum(iif(wkzh>=72,1,0))/count(*)*100 cj4hgl,sum(wkzh)/count(*) cj4pjf,(((sum(iif(wkzh>=96,1,0))/count(*))*100)*0.3)+(((sum(iif(wkzh>=72,1,0))/count(*))*100)*0.3)+((sum(wkzh)/count(*))*0.4) cj4zpf,sum(iif(lkzh>=96,1,0)) cj5yxs,sum(iif(lkzh>=96,1,0))/count(*)*100 cj5yxl,sum(iif(lkzh>=72,1,0)) cj5hgs,sum(iif(lkzh>=72,1,0))/count(*)*100 cj5hgl,sum(lkzh)/count(*) cj5pjf,(((sum(iif(lkzh>=96,1,0))/count(*))*100)*0.3)+(((sum(iif(lkzh>=72,1,0))/count(*))*100)*0.3)+((sum(lkzh)/count(*))*0.4) cj5zpf from zkbmdb group by dw
2
select xq,dw,dwm,count(*) bms,sum(iif(zf>0,1,0)) cks,sum(iif(zf>=533,1,0)) talgfs,sum(iif(zf>=533,1,0))/sum(iif(zf>0,1,0))*100 talgfl,sum(iif(zf>=520,1,0)) talyxs,sum(iif(zf>=520,1,0))/sum(iif(zf>0,1,0))*100 talyxl,sum(iif(zf>=390,1,0)) talhgs,sum(iif(zf>=390,1,0))/sum(iif(zf>0,1,0))*100 talhgl,sum(zf)/sum(iif(zf>0,1,0)) talpjf,(((sum(iif(zf>=520,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+(((sum(iif(zf>=390,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+((sum(zf)/sum(iif(zf>0,1,0)))*0.4) talzpf,sum(iif(yw>=96,1,0)) cj1yxs,sum(iif(yw>=96,1,0))/sum(iif(zf>0,1,0))*100 cj1yxl,sum(iif(yw>=72,1,0)) cj1hgs,sum(iif(yw>=72,1,0))/sum(iif(zf>0,1,0))*100 cj1hgl,sum(yw)/sum(iif(zf>0,1,0)) cj1pjf,(((sum(iif(yw>=96,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+(((sum(iif(yw>=72,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+((sum(yw)/sum(iif(zf>0,1,0)))*0.4) cj1zpf,sum(iif(sx>=96,1,0)) cj2yxs,sum(iif(sx>=96,1,0))/sum(iif(zf>0,1,0))*100 cj2yxl,sum(iif(sx>=72,1,0)) cj2hgs,sum(iif(sx>=72,1,0))/sum(iif(zf>0,1,0))*100 cj2hgl,sum(sx)/sum(iif(zf>0,1,0)) cj2pjf,(((sum(iif(sx>=96,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+(((sum(iif(sx>=72,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+((sum(sx)/sum(iif(zf>0,1,0)))*0.4) cj2zpf,sum(iif(wy>=96,1,0)) cj3yxs,sum(iif(wy>=96,1,0))/sum(iif(zf>0,1,0))*100 cj3yxl,sum(iif(wy>=72,1,0)) cj3hgs,sum(iif(wy>=72,1,0))/sum(iif(zf>0,1,0))*100 cj3hgl,sum(wy)/sum(iif(zf>0,1,0)) cj3pjf,(((sum(iif(wy>=96,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+(((sum(iif(wy>=72,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+((sum(wy)/sum(iif(zf>0,1,0)))*0.4) cj3zpf,sum(iif(wkzh>=96,1,0)) cj4yxs,sum(iif(wkzh>=96,1,0))/sum(iif(zf>0,1,0))*100 cj4yxl,sum(iif(wkzh>=72,1,0)) cj4hgs,sum(iif(wkzh>=72,1,0))/sum(iif(zf>0,1,0))*100 cj4hgl,sum(wkzh)/sum(iif(zf>0,1,0)) cj4pjf,(((sum(iif(wkzh>=96,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+(((sum(iif(wkzh>=72,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+((sum(wkzh)/sum(iif(zf>0,1,0)))*0.4) cj4zpf,sum(iif(lkzh>=96,1,0)) cj5yxs,sum(iif(lkzh>=96,1,0))/sum(iif(zf>0,1,0))*100 cj5yxl,sum(iif(lkzh>=72,1,0)) cj5hgs,sum(iif(lkzh>=72,1,0))/sum(iif(zf>0,1,0))*100 cj5hgl,sum(lkzh)/sum(iif(zf>0,1,0)) cj5pjf,(((sum(iif(lkzh>=96,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+(((sum(iif(lkzh>=72,1,0))/sum(iif(zf>0,1,0)))*100)*0.3)+((sum(lkzh)/sum(iif(zf>0,1,0)))*0.4) cj5zpf from zkbmdb group by dw
在VFP的环境中执行以上两条SQL都正常
在程序中执行查询1正常,执行查询2跳出CPU调试窗口,异常
程序代码
MyModule->ADOQuery2->Close();
MyModule->ADOQuery2->SQL->Clear();
MyModule->ADOQuery2->SQL->Add("select xq,dw,dwm," + bms + " bms," + cks + " cks,");
MyModule->ADOQuery2->SQL->Add("sum(iif(zf>=" + Gffs + ",1,0)) talgfs,sum(iif(zf>=" + Gffs + ",1,0))/" + TjJs + "*100 talgfl,sum(iif(zf>=" + Yxfs + ",1,0)) talyxs,sum(iif(zf>=" + Yxfs + ",1,0))/" + TjJs + "*100 talyxl,sum(iif(zf>=" + Hgfs + ",1,0)) talhgs,sum(iif(zf>=" + Hgfs + ",1,0))/" + TjJs + "*100 talhgl,sum(zf)/" + TjJs + " talpjf,(((sum(iif(zf>=" + Yxfs + ",1,0))/" + TjJs + ")*100)*0.3)+(((sum(iif(zf>=" + Hgfs + ",1,0))/" + TjJs + ")*100)*0.3)+((sum(zf)/" + TjJs + ")*0.4) talzpf,");
MyModule->ADOQuery2->SQL->Add("sum(iif(yw>=96,1,0)) cj1yxs,sum(iif(yw>=96,1,0))/" + TjJs + "*100 cj1yxl,sum(iif(yw>=72,1,0)) cj1hgs,sum(iif(yw>=72,1,0))/" + TjJs + "*100 cj1hgl,sum(yw)/" + TjJs + " cj1pjf,(((sum(iif(yw>=96,1,0))/" + TjJs + ")*100)*0.3)+(((sum(iif(yw>=72,1,0))/" + TjJs + ")*100)*0.3)+((sum(yw)/" + TjJs + ")*0.4) cj1zpf,");
MyModule->ADOQuery2->SQL->Add("sum(iif(sx>=96,1,0)) cj2yxs,sum(iif(sx>=96,1,0))/" + TjJs + "*100 cj2yxl,sum(iif(sx>=72,1,0)) cj2hgs,sum(iif(sx>=72,1,0))/" + TjJs + "*100 cj2hgl,sum(sx)/" + TjJs + " cj2pjf,(((sum(iif(sx>=96,1,0))/" + TjJs + ")*100)*0.3)+(((sum(iif(sx>=72,1,0))/" + TjJs + ")*100)*0.3)+((sum(sx)/" + TjJs + ")*0.4) cj2zpf,");
MyModule->ADOQuery2->SQL->Add("sum(iif(wy>=96,1,0)) cj3yxs,sum(iif(wy>=96,1,0))/" + TjJs + "*100 cj3yxl,sum(iif(wy>=72,1,0)) cj3hgs,sum(iif(wy>=72,1,0))/" + TjJs + "*100 cj3hgl,sum(wy)/" + TjJs + " cj3pjf,(((sum(iif(wy>=96,1,0))/" + TjJs + ")*100)*0.3)+(((sum(iif(wy>=72,1,0))/" + TjJs + ")*100)*0.3)+((sum(wy)/" + TjJs + ")*0.4) cj3zpf,");
MyModule->ADOQuery2->SQL->Add("sum(iif(wkzh>=96,1,0)) cj4yxs,sum(iif(wkzh>=96,1,0))/" + TjJs + "*100 cj4yxl,sum(iif(wkzh>=72,1,0)) cj4hgs,sum(iif(wkzh>=72,1,0))/" + TjJs + "*100 cj4hgl,sum(wkzh)/" + TjJs + " cj4pjf,(((sum(iif(wkzh>=96,1,0))/" + TjJs + ")*100)*0.3)+(((sum(iif(wkzh>=72,1,0))/" + TjJs + ")*100)*0.3)+((sum(wkzh)/" + TjJs + ")*0.4) cj4zpf,");
MyModule->ADOQuery2->SQL->Add("sum(iif(lkzh>=96,1,0)) cj5yxs,sum(iif(lkzh>=96,1,0))/" + TjJs + "*100 cj5yxl,sum(iif(lkzh>=72,1,0)) cj5hgs,sum(iif(lkzh>=72,1,0))/" + TjJs + "*100 cj5hgl,sum(lkzh)/" + TjJs + " cj5pjf,(((sum(iif(lkzh>=96,1,0))/" + TjJs + ")*100)*0.3)+(((sum(iif(lkzh>=72,1,0))/" + TjJs + ")*100)*0.3)+((sum(lkzh)/" + TjJs + ")*0.4) cj5zpf");
MyModule->ADOQuery2->SQL->Add(" from zkbmdb group by " + GroupByStr);
MyModule->ADOQuery2->Open();
[解决办法]
我的天。。。。。
[解决办法]
你把MyModule->ADOQuery2->SQL的值先显示在一个Memo内,复制到VFP里运行一下,看是不是语句哪个地方写错了.
[解决办法]
貌似这是出来吓人的
[解决办法]
看着乱
------解决方案--------------------
正常情况下，2200多个字符的SQL语句可以执行。你用的哪个驱动连接VFP库？
[解决办法]
你把第二个SQL语句弄成一个String，然后一次性添加到ADOQuery的SQL中，再执行看看？
[解决办法]
要测试驱动是不是支持这么长的,可以用第三方工具,如Excel的导入外部数据功能来试试.
插播广告,用这个小工具试一下你的SQL会不会出错:http://www.cppprog.com/2008/1225/20.html
[解决办法]
