04-11-06, 04:39
|
|
Registered User
|
|
Join Date: Mar 2006
Posts: 4
|
|
|
help,about clob in store procedure
|
|
db2 v817 luw
i have try insert a clob(1G) data into a table via a store procedure
db2 throw a error  QL10003C
i have checked my computer have enough memory(2G).
and my code like this:
Quote:
CREATE PROCEDURE DB2ADMIN.sp_Topic_insert ( IN userName VARCHAR(50)
,IN userID INTEGER
,IN topicTitle VARCHAR(1000)
,IN topicBody CLOB(1G)
,IN boardId INTEGER
,IN postDate DATE)
------------------------------------------------------------------------
-- SQL 存储过程
-- userName 发表作者
-- topicTitle 主题标题
-- topicBody 主题内容
-- postDate 发表时间
------------------------------------------------------------------------
P1: BEGIN
-- 声明变量
--DECLARE ENDTABLE INT DEFAULT 0;
DECLARE v_user VARCHAR(50);
DECLARE v_userId INTEGER;
DECLARE v_title VARCHAR(512);
DECLARE v_body CLOB(1G);
DECLARE v_boardId INTEGER;
DECLARE v_date DATE;
DECLARE v_c INTEGER;
DECLARE v_chkC INTEGER;
DECLARE v_now DATE;
SET v_user = userName;
SET v_userId = userID;
SET v_title = topicTitle;
SET v_body = topicBody;
SET v_boardId = boardId;
SET v_date = postDate;
--主要程序
SELECT DATE(CURRENT TIMESTAMP) INTO v_now FROM SYSIBM.SYSDUMMY1;
SELECT BOARD_ISCOMMENT,BOARD_ISCHKCOMMENT INTO v_c,v_chkC FROM TB_BOARD WHERE BOARD_ID = v_boardId;
INSERT INTO TB_TOPIC (TOPIC_TITLE,TOPIC_BODY,BOARD_ID,USER_NAME,USER_ID ,TOPIC_HITS,LAST_POST_DATE,VOTE_ID,ISVOTE,ISLOCK,I SVIEW,ISTOP,ISCOMMENT,ISCHKCOMMENT,ISBEST,LAST_POS T_USER,COMMENT_COUNT)
VALUES (v_title,v_body,v_boardId,v_user,v_userId,0,v_now, 0,0,0,1,0,v_c,v_chkC,0,v_user,0);
UPDATE TB_BOARD SET BOARD_POST_NUM = BOARD_POST_NUM + 1,BOARD_LAST_POST = v_title,BOARD_POST_DATE = v_now,BOARD_POST_USER = v_user WHERE BOARD_ID = v_boardId;
UPDATE TB_BOARD SET BOARD_LAST_POST = v_title,BOARD_POST_DATE = v_now,BOARD_POST_USER = v_user WHERE BOARD_ROOT_ID = (SELECT BOARD_ROOT_ID FROM TB_BOARD WHERE BOARD_ID = v_boardId) AND BOARD_ORDER_ID = 0;
END P1
|
thanks a lot.
|
|
dBforums
>
help,about clob in store procedure
Linear Mode
