1.批量备注:
select 'comment on column '||table_name||'.'||column_name||'
is ''直达配比'';' From user_col_comments
where column_name = 'NONSTOP_SI' and comments is null;
2.查询某字段已备注说明:
select * From user_col_comments
where column_name = 'ARRIVAL_SI' and comments is not
null;
3.读图片
selectblob file_content into :picBlob from
pub.tb_carg_ticket_pic a where a.pic_id =:id;
此PB语句只能读取LONG RAW类型字段,不能读取BLOB类型字段,否则会报如下错误
4.更新LONG RAW类型字段
updateblob pub.tb_carg_ticket_pic set
file_content = :tot_b where pic_id = :pic_id;
if sqlca.SQLNRows <= 0
then
post MessageBox("提示","保存图像内容出错!"+sqlca.SQLErrText ,
stopsign!)
goto sql_error
end if
此PB语句同样不能读取BLOB类型字段
语句中:picBlob和:tot_b均为blob类型变量
在设计数据库的时候,要存取图片等如果要用到上述SQL语句,不能设计为blob字段,只能设计为LONG
RAW字段,否则会报上述错误,我用的是PB7.0,oralce 驱动是O73 ORACLE 7.3
附一个保存图片的函数:
string ls_pathname, ls_filename, ls_image_flag
long row , pic_id
dec li_id
integer li_FileNum, loops, i
long flen, bytes_read, new_pos, ll_tot
blob b, tot_b
ll_tot = dw_1.rowcount()
//判断是否一定需要票证影像
if ll_tot = 0 then
select nvl(image_flag,'N') into :ls_image_flag from
pub.ma_error_code where error_code =:as_error_code;
if ls_image_flag = 'Y' then
MessageBox("提示", "差错" + as_error_code + "需要票证影像,请添加影像!" ,
stopsign!)
goto sql_error
end if
end if
//循环读影像文件
for row = 1 to ll_tot
tot_b = blob("")
b = blob("")
ls_pathname = dw_1.object.pathname[row]
ls_filename = dw_1.object.filename[row]
flen = dw_1.object.filelen[row]
li_FileNum = FileOpen(ls_pathname, StreamMode!, Read!,
LockRead!)
//计算循环次数
IF flen > 32765 THEN
IF Mod(flen, 32765) = 0 THEN
loops = flen/32765
ELSE
loops = (flen/32765) + 1
END IF
ELSE
loops = 1
END IF
//循环读文件内容
new_pos = 1
FOR i = 1 to loops
bytes_read = FileRead(li_FileNum, b)
tot_b = tot_b + b
NEXT
FileClose(li_FileNum)
//先插入普通字段的数据
select pub.seq_carg_ticket_pic.nextval into :pic_id from
dual;
insert into
pub.tb_carg_ticket_pic(pic_id,main_record_id,file_name) values
(:pic_id,:al_record_id,:ls_filename);
if sqlca.sqlcode<>0 then
post MessageBox("提示","图像" + ls_pathname +
"插入出错!"+sqlca.SQLErrText , stopsign!)
goto sql_error
end if
//然后更新blob字段
updateblob pub.tb_carg_ticket_pic set file_content = :tot_b
where pic_id = :pic_id;
if sqlca.SQLNRows <= 0 then
post MessageBox("提示","保存图像内容出错!"+sqlca.SQLErrText ,
stopsign!)
goto sql_error
end if
next
dw_1.reset()
return 1
sql_error:
rollback;
return -1