由于目标数据表和字段是未确定的,又需要能够存入和提取blob字段。
faq-it.org/asm/试了Dynamic SQL的四种方式,没能成功,不知哪位高手以前实现过类似问题!
用存储过程也必须实现BLOB类型的传入和输出
注意blob中不是OLE数据。
数据库为ORACLE
---------------------------------------------------------------
在oracle的函数里用动态sql 看看行不行??
EXECUTE IMMEDIATE V_SQLSTR;
---------------------------------------------------------------
我觉得好象不行吧
---------------------------------------------------------------
UPDATEBLOB SELECTBLOB不是标准的SQL语句,动态执行应该不可以
---------------------------------------------------------------
回复人: yaozw_mountain(山林) ( ) 信誉:100 2004-11-12 11:44:17 得分: 0
折腾了几天,虽然不理想,但终于用比较简单的办法实现了。
开始的思路是PB 动态sql,结果发现UPDATEBLOB SELECTBLOB并不支持,
想直接用UPDATE SELECT,结果发现pb没有blob的参数类型......
然后想用存储过程 oracle动态sql ,先是用 rpc fun,blob传入/传出参数大小有限制,并且还有一些其它问题......
再是直接用存储过程,传参数一直报类型不匹配的错误,没辙.....
又想固定的中间blob表 oracle动态sql ,开始是在中间表的触发器中处理blob和动态sql,把blob数据传到目标表,dbms_lob处理不熟练,出了一大堆问题,差点把服务器弄崩溃,理不清了.......
猛然间,想到Oracle 对blob许多处理像对待常规列,用sql语句转移blob数据,好像能行,没想到先前的操作遗留问题,仍有影响,时行时不行,恼火...最后,重启oracle,又试,可以了,
最后的实现的笨办法:中间blob表 pb 动态sql 。
我把实现贴出来,大家给个意见:
//=====================SELECTBLOB
DELETE FROM TRANS_SAVEBLOB
WHERE KEYID = :li_1 USING itr_sqlca ;
ls_2= "INSERT INTO TRANS_SAVEBLOB ( KEYID, TABLENAME, FIELDNAME, BLOBVALUE ) " &;amp;
" VALUES (" string( li_1) " , '" "SAMPLETARGETTABLE" "', '" "TYPE6" "', ( " &;amp;
" SELECT " "TYPE6" " FROM " "SAMPLETARGETTABLE" " WHERE " ls_1 " ) )"
EXECUTE IMMEDIATE :ls_2 USING itr_sqlca ;
SELECTBLOB BLOBVALUE INTO :ab_image
FROM TRANS_SAVEBLOB
WHERE KEYID = :li_1 USING itr_sqlca;
DELETE FROM TRANS_SAVEBLOB
WHERE KEYID = :li_1 USING itr_sqlca ;
Commit USING itr_sqlca;
//========================UPDATEBLOB
DELETE FROM TRANS_SAVEBLOB
WHERE KEYID = :li_1 USING itr_sqlca ;
INSERT INTO TRANS_SAVEBLOB ( KEYID, TABLENAME, WHERECLAUSE, FIELDNAME )
VALUES ( :li_1, 'SAMPLETARGETTABLE', :ls_1, 'TYPE6' )
USING itr_sqlca ;
UPDATEBLOB TRANS_SAVEBLOB SET BLOBVALUE = :ab_image
WHERE KEYID = :li_1 USING itr_sqlca;
ls_2= "UPDATE " "SAMPLETARGETTABLE" " SET " "TYPE6" " = ( " &;amp;
" SELECT BLOBVALUE FROM TRANS_SAVEBLOB WHERE ( KEYID =" string( li_1 ) " ) ) " &;amp;
" WHERE " ls_1
EXECUTE IMMEDIATE :ls_2 USING itr_sqlca ;
DELETE FROM TRANS_SAVEBLOB
WHERE KEYID = :li_1 USING itr_sqlca ;
准备结贴"
不知道这是不是唯一解,要不再让斑竹看看
faq-it.org/asm/试了Dynamic SQL的四种方式,没能成功,不知哪位高手以前实现过类似问题!
用存储过程也必须实现BLOB类型的传入和输出
注意blob中不是OLE数据。
数据库为ORACLE
---------------------------------------------------------------
在oracle的函数里用动态sql 看看行不行??
EXECUTE IMMEDIATE V_SQLSTR;
---------------------------------------------------------------
我觉得好象不行吧
---------------------------------------------------------------
UPDATEBLOB SELECTBLOB不是标准的SQL语句,动态执行应该不可以
---------------------------------------------------------------
回复人: yaozw_mountain(山林) ( ) 信誉:100 2004-11-12 11:44:17 得分: 0
折腾了几天,虽然不理想,但终于用比较简单的办法实现了。
开始的思路是PB 动态sql,结果发现UPDATEBLOB SELECTBLOB并不支持,
想直接用UPDATE SELECT,结果发现pb没有blob的参数类型......
然后想用存储过程 oracle动态sql ,先是用 rpc fun,blob传入/传出参数大小有限制,并且还有一些其它问题......
再是直接用存储过程,传参数一直报类型不匹配的错误,没辙.....
又想固定的中间blob表 oracle动态sql ,开始是在中间表的触发器中处理blob和动态sql,把blob数据传到目标表,dbms_lob处理不熟练,出了一大堆问题,差点把服务器弄崩溃,理不清了.......
猛然间,想到Oracle 对blob许多处理像对待常规列,用sql语句转移blob数据,好像能行,没想到先前的操作遗留问题,仍有影响,时行时不行,恼火...最后,重启oracle,又试,可以了,
最后的实现的笨办法:中间blob表 pb 动态sql 。
我把实现贴出来,大家给个意见:
//=====================SELECTBLOB
DELETE FROM TRANS_SAVEBLOB
WHERE KEYID = :li_1 USING itr_sqlca ;
ls_2= "INSERT INTO TRANS_SAVEBLOB ( KEYID, TABLENAME, FIELDNAME, BLOBVALUE ) " &;amp;
" VALUES (" string( li_1) " , '" "SAMPLETARGETTABLE" "', '" "TYPE6" "', ( " &;amp;
" SELECT " "TYPE6" " FROM " "SAMPLETARGETTABLE" " WHERE " ls_1 " ) )"
EXECUTE IMMEDIATE :ls_2 USING itr_sqlca ;
SELECTBLOB BLOBVALUE INTO :ab_image
FROM TRANS_SAVEBLOB
WHERE KEYID = :li_1 USING itr_sqlca;
DELETE FROM TRANS_SAVEBLOB
WHERE KEYID = :li_1 USING itr_sqlca ;
Commit USING itr_sqlca;
//========================UPDATEBLOB
DELETE FROM TRANS_SAVEBLOB
WHERE KEYID = :li_1 USING itr_sqlca ;
INSERT INTO TRANS_SAVEBLOB ( KEYID, TABLENAME, WHERECLAUSE, FIELDNAME )
VALUES ( :li_1, 'SAMPLETARGETTABLE', :ls_1, 'TYPE6' )
USING itr_sqlca ;
UPDATEBLOB TRANS_SAVEBLOB SET BLOBVALUE = :ab_image
WHERE KEYID = :li_1 USING itr_sqlca;
ls_2= "UPDATE " "SAMPLETARGETTABLE" " SET " "TYPE6" " = ( " &;amp;
" SELECT BLOBVALUE FROM TRANS_SAVEBLOB WHERE ( KEYID =" string( li_1 ) " ) ) " &;amp;
" WHERE " ls_1
EXECUTE IMMEDIATE :ls_2 USING itr_sqlca ;
DELETE FROM TRANS_SAVEBLOB
WHERE KEYID = :li_1 USING itr_sqlca ;
准备结贴"
不知道这是不是唯一解,要不再让斑竹看看