山寨机中的战斗机！	程序优化工程师到底对IT界有没有贡献

[ODBC Visual FoxPro Driver]SQL: Statement too long. 紧急求助!!搞了一天了.

楼主xavier（尼日利亚）2006-07-04 19:11:03 在其他数据库开发 / VFP 提问

我在做一个c#的项目,用到了foxpro的数据库.但是查询的时候总会出现这个错误,异常的郁闷,有人帮忙解决一下吗?
下面是查询子副串:
select campaign.camp_code,campaign.desc as Campaign,adv.name as Advertise,brand.Desc as Brand,product.Desc as Product, start_date as StartDate ,end_date as EndDate from campaign,adv,product,brand where campaign.adv_code=adv.adv_code and campaign.brand_code=brand.brand_code and campaign.prod_code=product.prod_code and (adv.adv_code in (1275,1273,1272,1271,1270,1266,1261,1260,1259,1258,1255,1254,1252,1251,1250,1249,1247,1244,1240,1238,1235,1233,1231,1229) or adv.adv_code in(1227,1223,1220,1219,1217,1216,1214,1213,1212,1210,1208,1206,1204,1201,1200,1199,1198,1197,1196,1195,1194,1193,1192,1191) or adv.adv_code in(1190,1189,1130,1129,1128,1127,1126,1125,1124,1123,1122,1121,1119,1118,1117,1116,1112,1111,1109,1108,1105,1104,1102,1100) or adv.adv_code in(1098,1097,1096,1094,1089,1087,1083,1081,1072,1052,1048,1040,1035,1034,1022,1018,1017,1015,1008,1005)) and ((start_date<=ctod('01/03/2005') and End_date>=ctod('01/30/2005') ) or (start_date<=ctod('01/03/2005') and End_date >=ctod('01/03/2005')) or (start_date <=
ctod('01/30/2005') and End_date>=ctod( '01/30/2005')) or (start_date >=ctod('01/03/2005') and End_date <=ctod('01/30/2005')) or (start_date<=ctod('01/31/2005') and End_date >=ctod('02/27/2005' )) or (start_date<=ctod('01/31/2005') and End_date >=ctod('01/31/2005')) or (start_date <=ctod('02/27/2005') and End_date >=ctod('02/27/2005')) or ( start_date>=ctod('01/31/2005') and End_date<=ctod('02/27/2005')) or (start_date<=ctod('02/28/2005') and End_date>=ctod('03/27/2005' )) or (start_date<=ctod('02/28/2005') and End_date>=ctod('02/28/2005')) or (start_date<=ctod('03/27/2005') and End_date>=ctod('03/27/2005')) or (start_date>=ctod('02/28/2005') and End_date<=ctod('03/27/2005'))) 问题点数：100、回复次数：17Top

1 楼wwwwb（）回复于 2006-07-05 07:52:58 得分 20

1、字符中、串超长，用数个变量相加试试；
2、优化一下SQL语句，如IN中如数字连续，可用BETWEEN ANDTop

2 楼apple_8180（十豆三）回复于 2006-07-05 09:06:28 得分 60

改成这样试试：

lcStr1="select campaign.camp_code,campaign.desc as Campaign,adv.name as Advertise,brand.Desc as Brand,product.Desc as Product, start_date as StartDate ,end_date as EndDate from campaign,adv,product,brand "

lcStr2="where campaign.adv_code=adv.adv_code and campaign.brand_code=brand.brand_code and campaign.prod_code=product.prod_code and "

lcStr3="(adv.adv_code in (1275,1273,1272,1271,1270,1266,1261,1260,1259,1258,1255,1254,1252,1251,1250,1249,1247,1244,1240,1238,1235,1233,1231,1229) or "

lcStr4="adv.adv_code in(1227,1223,1220,1219,1217,1216,1214,1213,1212,1210,1208,1206,1204,1201,1200,1199,1198,1197,1196,1195,1194,1193,1192,1191) or "

lcStr5="adv.adv_code in(1190,1189,1130,1129,1128,1127,1126,1125,1124,1123,1122,1121,1119,1118,1117,1116,1112,1111,1109,1108,1105,1104,1102,1100) or "

lcStr6="adv.adv_code in(1098,1097,1096,1094,1089,1087,1083,1081,1072,1052,1048,1040,1035,1034,1022,1018,1017,1015,1008,1005)) and "

lcStr7="((start_date<=ctod('01/03/2005') and End_date>=ctod('01/30/2005')) or (start_date<=ctod('01/03/2005') and End_date>=ctod('01/03/2005')) or "

lcStr8="(start_date<=ctod('01/30/2005') and End_date>=ctod('01/30/2005')) or (start_date>=ctod('01/03/2005') and End_date<=ctod('01/30/2005')) or "

lcStr9="(start_date<=ctod('01/31/2005') and End_date>=ctod('02/27/2005')) or (start_date<=ctod('01/31/2005') and End_date>=ctod('01/31/2005')) or "

lcStr10="(start_date<=ctod('02/27/2005') and End_date>=ctod('02/27/2005')) or (start_date>=ctod('01/31/2005') and End_date<=ctod('02/27/2005')) or "

lcStr11="(start_date<=ctod('02/28/2005') and End_date>=ctod('03/27/2005')) or (start_date<=ctod('02/28/2005') and End_date>=ctod('02/28/2005')) or "

lcStr12="(start_date<=ctod('03/27/2005') and End_date>=ctod('03/27/2005')) or (start_date>=ctod('02/28/2005') and End_date<=ctod('03/27/2005')))"

然后传入字符串时：

lcStr1+lcStr2lcStr3+lcStr4+lcStr5+lcStr6+lcStr7+lcStr8+lcStr9+lcStr10+lcStr11+lcStr12Top

3 楼apple_8180（十豆三）回复于 2006-07-05 09:09:39 得分 0

因为VFP能够处理的最大字符串长度为255，
太长的串会产生 "命令中含有不能识别的短语或关键字。" 错误。但是, 如果你把它们分割到多个相连的串中, 就可以传递长的 SQL 语句。Top

4 楼dfwxj（清风）回复于 2006-07-05 10:12:41 得分 2

是的，如十豆三所说Top

5 楼xavier（尼日利亚）回复于 2006-07-05 11:06:27 得分 0

this.adptODBC = new OdbcDataAdapter(strTmp[0] + strTmp[1] + strTmp[2] + strTmp[3] + strTmp[4] + strTmp[5] + strTmp[6]
+ strTmp[7] + strTmp[8] + strTmp[9] + strTmp[10] + strTmp[11] + strTmp[12] + strTmp[13] + strTmp[14] + strTmp[15]
+ strTmp[16] + strTmp[17] + strTmp[18] + strTmp[19] + strTmp[20] + strTmp[21] + strTmp[22] + strTmp[23] + strTmp[24]
+ strTmp[25] + strTmp[26] + strTmp[27] + strTmp[28] + strTmp[29] + strTmp[30], cnnODBC);

this.ds = new DataSet();
adptODBC.Fill(this.ds);

strTmp 是那条很长的SQL 语句拆分组成的数组,在执行这句adptODBC.Fill(this.ds),依然会出现同样的错误.STATEMENT TOO LONG,十豆三说的是这个意思还是其他的方法?Top

6 楼jxlee365（老牛自知黄昏晚，不待扬鞭自奋蹄）回复于 2006-07-05 13:10:07 得分 5

sql语句本身长度不能超过255。
C#有没有TABLE类的数据集？Top

7 楼xavier（尼日利亚）回复于 2006-07-05 13:14:12 得分 0

c#有类似TABLE的数据集,SQL语句的长度是8000.jxlee365有好的解决办法?Top

8 楼LAIYANGPJ（小丑）回复于 2006-07-05 13:17:29 得分 2

第一次看到这么长的SQL语句,作者的SQL语句的具体含义是什么?Top

9 楼xavier（尼日利亚）回复于 2006-07-05 13:28:23 得分 0

主要是为了查询出campaign表里的一些信息,后面那些是限制条件,循环做出来的,还可以更长.

Top

10 楼chenyuandxm（一剑平江湖）回复于 2006-07-05 14:34:31 得分 5

我以前也碰到过类似的问题，确实是sql语句太长了。
所以可以分拆，然后再合并。
sql1="......"
sql2="......"
.....
sqln="......"
ssql=sql1+sql2+...+sqlnTop

11 楼jxlee365（老牛自知黄昏晚，不待扬鞭自奋蹄）回复于 2006-07-05 14:59:08 得分 0

我用DELPHI的时候，也发生SQL语句过长的问题，只能用TABLE来修改。Top

12 楼jxlee365（老牛自知黄昏晚，不待扬鞭自奋蹄）回复于 2006-07-05 15:00:33 得分 0

再次申明：255不是VF的限制，而是来自于数据库引擎！Top

13 楼jxlee365（老牛自知黄昏晚，不待扬鞭自奋蹄）回复于 2006-07-05 15:02:34 得分 0

除了用TABLE外，可以自己判断控制SQL语句的长度。Top

14 楼lucasu（）回复于 2006-07-05 17:53:03 得分 2

首先要确定你的语法是正确的，然后：
调用sys(3055)函数，增加 FOR 和 WHERE 子句的复杂性。具体用法参考VFP帮助。Top

15 楼apple_8180（十豆三）回复于 2006-07-06 09:02:34 得分 0

没错，首先确认你的SQL 语句是合法的，然后用我的方法肯定可以。

可以简化你的SQL语句，一点一点来调试，肯定可以找出原因。Top

16 楼wolf_fq（科兒）回复于 2006-07-06 09:47:55 得分 2

搂主能写出这么长的sql查询语句真的是很佩服Top

17 楼TERRYYRRET（命运）回复于 2006-07-06 15:58:56 得分 2

同意豆三兄Top