C++ Builder中SQL语句过长，无法正确执行，怎么办？
我在程序中使用的SQL语句，有时候会很长。这时发现执行的时候SQL语句只能执行一部分。导致程序错误。
不知道SQL语句的长度限制是多少？当语句过长时，有没有别的办法？
问题点数：40、回复次数：6Top
1 楼barbaraboy（第八哥）回复于 2004-01-05 09:58:42 得分 0
把你的SQL贴出来Top
2 楼52vc（方旭）回复于 2004-01-05 10:21:14 得分 20
AnsiString sqlprocc=" CREATE PROCEDURE [REP_HTRL_QMHZ] \n @SETHTLH CHAR(20), \n @ENDHTLH CHAR(20), \n @SETDATE DATETIME \n AS \n DECLARE @QCDATE datetime \n SET @QCDATE=(SELECT TOP 1 XTQC FROM FCOMSYS) \n CREATE TABLE #temp(temp varchar(10)) \n TRUNCATE TABLE #TEMP \n ---23---期末库存------ \n ---QMKC---- \n ----24-其中库存原材料(含半成品)-未含下列[25]-------- \n ------------ 23-25-26 \n --25--库其中存原材料(含半成品)-委外- \n --QWWQM \n --26--其中库存产成品耗用----- \n INSERT INTO HTRL_TEMP (HTLH,KCCCP) \n SELECT HTLH,LBCPZ \n FROM HTRL_LBCPC_V \n WHERE HTLH>=@SETHTLH and HTLH<=@ENDHTLH \n INSERT INTO HTRL_TEMP (HTLH,KCCCP) \n SELECT HTLH,CPZ \n FROM HTRL_QCHTBOM \n WHERE HTLH>=@SETHTLH and HTLH<=@ENDHTLH \n ------库存累计----- \n ------********内部帐存**********--------------- \n -------原料------- \n ----原料当日库存统计表产生----进入固定表 \n INSERT INTO HTRL_TEMP (HTLH,LBRL) \n SELECT HTLH,LJZ \n FROM HTRL_LBLJZ \n WHERE HTLH>=@SETHTLH and HTLH<=@ENDHTLH \n -------成品展原料------ \n ----成品别产生----进入固定表 \n INSERT INTO HTRL_TEMP (HTLH,CPRL) \n SELECT HTLH,HTYL \n FROM HTRL_LBZCCPZK_V \n WHERE HTLH>=@SETHTLH and HTLH<=@ENDHTLH \n INSERT INTO HTRL_TEMP (HTLH,CPRL) \n SELECT HTLH,CPZ \n FROM HTRL_QCLBBOM \n WHERE HTLH>=@SETHTLH and HTLH<=@ENDHTLH \n -------内部累计---- \n -----************差异***************----------- \n DELETE FROM HTRL_PRINT \n SELECT * FROM #TEMP ";
golddata->Mfind1->Close();
golddata->Mfind1->SQL->Clear();
golddata->Mfind1->SQL->Add(sqlprocc);
golddata->Mfind1->Prepared=true;
golddata->Mfind1->ExecSQL();
这个够长吧！你认为会不会出错呢?Top
3 楼hzbigdog（苦命的程序员）回复于 2004-01-05 10:31:19 得分 20
不会把！这个还算长吗？我给你看看我的！
//创建新的表记录获取ID
ADOTable->Open();
ADOTable->Append();
ADOTable->FieldValues["BSY_TITLE"] = edtTitle->Text.Trim();
ADOTable->FieldValues["SPFL_IDLIST"] = astrFLList;
ADOTable->FieldValues["BSY_YX"] = 1;
ADOTable->Post();
NewBSY_ID = IntToStr(ADOTable->FieldByName("BSY_ID")->AsInteger);
ADOTable->Close();
/******************************************************/
/****************创建UPDATA + BSY_ID表*****************/
/******************************************************/
astrSQL = " CREATE TABLE UPDATA"+ NewBSY_ID +" (";
astrSQL = astrSQL + " UPDATA_ID INT NOT NULL IDENTITY (1, 1),";
astrSQL = astrSQL + " UPDATA_SPID INT NOT NULL,";
astrSQL = astrSQL + " UPDATA_SPDM VARCHAR(20) NOT NULL,";
astrSQL = astrSQL + " UPDATA_SPNAME VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " UPDATA_SCQC VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " UPDATA_SPGG VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " UPDATA_DW VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " UPDATA_CSDM CHAR(6) NOT NULL,";
astrSQL = astrSQL + " UPDATA_CSNAME VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " UPDATA_JCDID INT NOT NULL,";
astrSQL = astrSQL + " UPDATA_JCNAME VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " UPDATA_USERID INT NOT NULL,";
astrSQL = astrSQL + " UPDATA_USNAME VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " UPDATA_BZRQ SMALLDATETIME NOT NULL,";
astrSQL = astrSQL + " UPDATA_BCSJ DATETIME NOT NULL DEFAULT (getdate()),";
astrSQL = astrSQL + " UPDATA_SGJ FLOAT NOT NULL DEFAULT 0,";
for (int i = NewJG_ID; i > 0; i--)
{
astrSQL = astrSQL + " UPDATA_SGJ" + IntToStr(i);
astrSQL = astrSQL + " FLOAT NOT NULL DEFAULT 0,";
}
astrSQL = astrSQL + " UPDATA_SCKJ FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPDATA_JD BIT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPDATA_CB BIT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPDATA_BZ VARCHAR(200) NULL,";
astrSQL = astrSQL + " CONSTRAINT CK_UPDATA_CS"+ NewBSY_ID +" CHECK (UPDATA_CSDM LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),";
astrSQL = astrSQL + " CONSTRAINT PK_UPDATA"+ NewBSY_ID +" PRIMARY KEY (UPDATA_ID)";
astrSQL = astrSQL + ");";
astrSQL = astrSQL + " CREATE NONCLUSTERED INDEX idx_UPDATA ON UPDATA"+ NewBSY_ID +"(UPDATA_SPID, UPDATA_CSDM, UPDATA_BZRQ);";
/******************************************************/
/****************创建SHZDATA + BSY_ID表*****************/
/******************************************************/
astrSQL = astrSQL + " CREATE TABLE SHZDATA"+ NewBSY_ID +" (";
astrSQL = astrSQL + " SHZDATA_ID INT NOT NULL IDENTITY (1, 1),";
astrSQL = astrSQL + " SHZDATA_SPID INT NOT NULL,";
astrSQL = astrSQL + " SHZDATA_SPDM VARCHAR(20) NOT NULL,";
astrSQL = astrSQL + " SHZDATA_SPNAME VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " SHZDATA_SPGG VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " SHZDATA_DW VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " SHZDATA_CSDM CHAR(6) NOT NULL,";
astrSQL = astrSQL + " SHZDATA_CSNAME VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " SHZDATA_USERID INT NOT NULL,";
astrSQL = astrSQL + " SHZDATA_USNAME VARCHAR(50) NOT NULL,";
astrSQL = astrSQL + " SHZDATA_BZRQ SMALLDATETIME NOT NULL,";
astrSQL = astrSQL + " SHZDATA_BCSJ DATETIME NOT NULL DEFAULT (getdate()),";
astrSQL = astrSQL + " SHZDATA_SGJ FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " SHZDATA_SQ FLOAT NOT NULL DEFAULT 0,";
for (int i = NewJG_ID; i > 0; i--)
{
astrSQL = astrSQL + " SHZDATA_SGJ" + IntToStr(i);
astrSQL = astrSQL + " FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " SHZDATA_SQ" + IntToStr(i);
astrSQL = astrSQL + " FLOAT NOT NULL DEFAULT 0,";
}
astrSQL = astrSQL + " SHZDATA_SCKJ FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " SHZDATA_JD BIT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " SHZDATA_CB BIT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " SHZDATA_BZ VARCHAR(1024) NULL,";
astrSQL = astrSQL + " CONSTRAINT CK_SHZDATA_CS"+ NewBSY_ID +" CHECK (SHZDATA_CSDM LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),";
astrSQL = astrSQL + " CONSTRAINT PK_SHZDATA"+ NewBSY_ID +" PRIMARY KEY (SHZDATA_ID)";
astrSQL = astrSQL + ");";
astrSQL = astrSQL + " CREATE NONCLUSTERED INDEX idx_SHZDATA ON SHZDATA"+ NewBSY_ID +"(SHZDATA_SPID, SHZDATA_CSDM, SHZDATA_BZRQ);";
/******************************************************/
/****************创建UPXB + BSY_ID表*****************/
/******************************************************/
astrSQL = astrSQL + " CREATE TABLE UPXB"+ NewBSY_ID +" (";
astrSQL = astrSQL + " UPXB_ID INT NOT NULL IDENTITY (1, 1),";
astrSQL = astrSQL + " UPXB_SPID INT NOT NULL,";
astrSQL = astrSQL + " UPXB_BZRQ SMALLDATETIME NOT NULL,";
astrSQL = astrSQL + " UPXB_PJ FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPXB_SQ FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPXB_TQ FLOAT NOT NULL DEFAULT 0,";
for (int i = NewJG_ID; i > 0; i--)
{
astrSQL = astrSQL + " UPXB_PJ" + IntToStr(i);
astrSQL = astrSQL + " FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPXB_SQ" + IntToStr(i);
astrSQL = astrSQL + " FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPXB_TQ" + IntToStr(i);
astrSQL = astrSQL + " FLOAT NOT NULL DEFAULT 0,";
}
astrSQL = astrSQL + " CONSTRAINT PK_UPXB"+ NewBSY_ID +" PRIMARY KEY (UPXB_ID)";
astrSQL = astrSQL + ")";
astrSQL = astrSQL + " CREATE NONCLUSTERED INDEX idx_UPXB ON UPXB"+ NewBSY_ID +"(UPXB_BZRQ, UPXB_SPID)";
/******************************************************/
/****************创建UPYB + BSY_ID表*****************/
/******************************************************/
astrSQL = astrSQL + " CREATE TABLE UPYB"+ NewBSY_ID +" (";
astrSQL = astrSQL + " UPYB_ID INT NOT NULL IDENTITY (1, 1),";
astrSQL = astrSQL + " UPYB_SPID INT NOT NULL,";
astrSQL = astrSQL + " UPYB_BZY SMALLDATETIME NOT NULL,";
astrSQL = astrSQL + " UPYB_PJ FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPYB_SQ FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPYB_TQ FLOAT NOT NULL DEFAULT 0,";
for (int i = NewJG_ID; i > 0; i--)
{
astrSQL = astrSQL + " UPYB_PJ" + IntToStr(i);
astrSQL = astrSQL + " FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPYB_SQ" + IntToStr(i);
astrSQL = astrSQL + " FLOAT NOT NULL DEFAULT 0,";
astrSQL = astrSQL + " UPYB_TQ" + IntToStr(i);
astrSQL = astrSQL + " FLOAT NOT NULL DEFAULT 0,";
}
astrSQL = astrSQL + " CONSTRAINT PK_UPYB"+ NewBSY_ID +" PRIMARY KEY (UPYB_ID)";
astrSQL = astrSQL + ");";
astrSQL = astrSQL + " CREATE NONCLUSTERED INDEX idx_UPYB ON UPYB"+ NewBSY_ID +"(UPYB_BZY, UPYB_SPID);";
DataM->ADOCMD->CommandText = astrSQL;
DataM->ADOCMD->Execute();
//完成监测表基本信息建立
fun.Replace(&astrSQL,39,4);
astrSQLTemp = "Update BSY Set BSY_SQL = '"+ astrSQL +"' ";
astrSQLTemp = astrSQLTemp + " Where BSY_id = "+ NewBSY_ID;
DBTest->ExecSQLNoReturn(&astrSQLTemp);
Beep();
astrMsg = "表结构创建成功！";
MessageDlg(astrMsg, mtInformation, TMsgDlgButtons() <<mbCancel, 0);Top
4 楼hzbigdog（苦命的程序员）回复于 2004-01-05 10:33:29 得分 0
你这个长度没有问题的！
Top
5 楼ccrun（老妖)(www.ccrun.com）回复于 2004-01-05 10:39:57 得分 0
汗。。。Top
6 楼hzbigdog（苦命的程序员）回复于 2004-01-05 10:46:35 得分 0
ccrun(老妖)救救我！
http://expert.csdn.net/Expert/topic/2633/2633491.xml?temp=.9009363Top
