📄 blob_demo.pc
字号:
/*
drop table a_SPTC_PACKAGE_CTL ;
CREATE TABLE a_SPTC_PACKAGE_CTL
(
PACK_ID NUMBER(10),
DATA_LEN NUMBER(10),
BLOB_DATA BLOB
)
*/
/*
创建blob存储过程
create or replace package blob_it as
my_blob blob;
function get_blob_len return number;
procedure read_blob(amount in out number, offset in number,
buf in out raw);
procedure write_blob(amount in out number, offset in number,
buf in out raw);
end;
/
create or replace package body blob_it as
function get_blob_len return number is
begin
return DBMS_LOB.GETLENGTH(my_blob);
end;
procedure read_blob(amount in out number, offset in number,
buf in out raw) is
begin
DBMS_LOB.READ(my_blob,amount,offset,buf);
end;
procedure write_blob(amount in out number, offset in number,
buf in out raw) is
begin
DBMS_LOB.WRITE(my_blob,amount,offset,buf);
end;
end;
/
*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <signal.h>
#include <time.h>
#include <sys/timeb.h>
#include <stdarg.h>
EXEC SQL include SQLCA;
EXEC SQL include SQLDA;
/* 10485760=1024*1024*10 */
EXEC SQL BEGIN DECLARE SECTION;
#define MAX_SINGLE_WRITE_BLOB_LEN 32512
#define PACKAGE_CTL_READ_SIZE 10485721 /*31744 32768 10485760*/
EXEC SQL END DECLARE SECTION;
EXEC SQL BEGIN DECLARE SECTION;
typedef struct ctl_vr_t
{
short len;
unsigned char arr[PACKAGE_CTL_READ_SIZE];
} ctl_vr;
EXEC SQL END DECLARE SECTION;
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR ctl_oracleid[20];
EXEC SQL TYPE ctl_vr IS VARRAW(10485760);
ctl_vr ctl_my_vr;
EXEC SQL END DECLARE SECTION;
int db_open(char * connstr)
{
EXEC SQL BEGIN DECLARE SECTION;
char user[64];
EXEC SQL END DECLARE SECTION;
int ret ;
strcpy(user,connstr);
EXEC SQL CONNECT :user;
ret=sqlca.sqlcode;
return ret ;
}
int do_commit()
{
int ret ;
int retry_times=10 ;
EXEC SQL COMMIT WORK ;
ret = sqlca.sqlcode ;
return ret;
}
/*rollback release*/
int do_rollback()
{
int ret ;
int retry_times=10 ;
EXEC SQL ROLLBACK WORK ;
ret = sqlca.sqlcode ;
return ret;
}
/* ------------------------------------------------------------------------------- */
void db_close()
{
EXEC SQL COMMIT WORK RELEASE;
}
main()
{
/*proc5/abc@test*/
/*expwaynew/expwaynew@or9*/
printf("\n\n proc5/abc@test \n\n");
db_open("proc5/abc@test");
insert_sptc_package_ctl_NEW();
printf("\n\n\n insert_sptc_package_ctl_NEW end \n\n\n");
select_sptc_package_ctl_NEW();
printf("\n\n\n select_sptc_package_ctl_NEW end \n\n\n");
}
int select_sptc_package_ctl_NEW()
{
EXEC SQL BEGIN DECLARE SECTION;
long blob_data_len=0;
long package_num=0;
unsigned char BLOB_DATA[PACKAGE_CTL_READ_SIZE+1];
long pack_len=0,i=0;
long single_pack_len=0;
long cur_pos_pack_len=0;
int cnt=0;
EXEC SQL END DECLARE SECTION;
int ret;
int break_flag=0;
memset(BLOB_DATA,0,sizeof BLOB_DATA );
package_num=sizeof(BLOB_DATA)-1;
printf("package_num=%ld \n",package_num);
EXEC SQL SELECT
PACK_ID,
DATA_LEN
INTO
:package_num,
:blob_data_len
FROM a_SPTC_PACKAGE_CTL WHERE PACK_ID = :package_num;
printf("1 select sqlca.sqlcode=%d\n",sqlca.sqlcode);
#if 1
EXEC SQL EXECUTE
BEGIN
SELECT BLOB_DATA INTO blob_it.my_blob
FROM a_SPTC_PACKAGE_CTL
WHERE PACK_ID = :package_num
FOR UPDATE;
END;
END-EXEC;
#endif
printf("2 select sqlca.sqlcode=%d\n",sqlca.sqlcode);
#if 1
EXEC SQL EXECUTE
BEGIN
:pack_len := DBMS_LOB.GETLENGTH(blob_it.my_blob);
END;
END-EXEC;
printf("2.1 select sqlca.sqlcode=%d GETLENGTH=%ld \n",sqlca.sqlcode,pack_len);
#endif
#if 1
single_pack_len=pack_len;
cur_pos_pack_len=0;
memset(BLOB_DATA,0, sizeof(BLOB_DATA) );
while(1)
{
#if 0
if ( pack_len > MAX_SINGLE_WRITE_BLOB_LEN )
{
single_pack_len=MAX_SINGLE_WRITE_BLOB_LEN ;
}
else
#endif
{
single_pack_len=pack_len;
break_flag=1;
if (single_pack_len == 0 ) break;
}
memset(ctl_my_vr.arr,0, sizeof(ctl_my_vr.arr) );
/*ctl_my_vr.len = single_pack_len ;*/
cur_pos_pack_len = cur_pos_pack_len + 1 ;
EXEC SQL EXECUTE
BEGIN
DBMS_LOB.READ(blob_it.my_blob,:single_pack_len,:cur_pos_pack_len,:ctl_my_vr);
END;
END-EXEC;
cur_pos_pack_len = cur_pos_pack_len - 1 ;
memcpy(&BLOB_DATA[cur_pos_pack_len],ctl_my_vr.arr, single_pack_len );
cnt ++ ;
printf("select [%d] sqlca.sqlcode=%d\n",cnt,sqlca.sqlcode);
printf("select cur_pos_pack_len=%d pack_len=%d single_pack_len=%d \n",
cur_pos_pack_len,pack_len,single_pack_len);
if ( break_flag == 1 ) break ;
cur_pos_pack_len = cur_pos_pack_len + single_pack_len ;
pack_len = pack_len - single_pack_len ;
printf("select cur_pos_pack_len=%d pack_len=%d single_pack_len=%d \n",
cur_pos_pack_len,pack_len,single_pack_len);
if ( sqlca.sqlcode !=0 )
{
return sqlca.sqlcode ;
}
}
#endif
printf("3 select sqlca.sqlcode=%d\n BLOB_DATA=%s\n cur_pos_pack_len=%d\n",
sqlca.sqlcode,BLOB_DATA,cur_pos_pack_len);
return 0 ;
}
int insert_sptc_package_ctl_NEW()
{
EXEC SQL BEGIN DECLARE SECTION;
long blob_data_len=0;
long package_num=0;
unsigned char BLOB_DATA[PACKAGE_CTL_READ_SIZE+1];
long pack_len=0,i=0;
long single_pack_len=0;
long cur_pos_pack_len=0;
int cnt=0;
EXEC SQL END DECLARE SECTION;
int ret;
int break_flag=0;
memset(BLOB_DATA,0,sizeof BLOB_DATA );
/*
blob_data_len=sizeof(BLOB_DATA);
package_num=sizeof(BLOB_DATA);
*/
printf("PACKAGE_CTL_READ_SIZE=%ld,sizeof(BLOB_DATA)=%ld \n",PACKAGE_CTL_READ_SIZE,sizeof(BLOB_DATA) );
for(i=0;i<sizeof(BLOB_DATA);i++)
{
BLOB_DATA[i]='1';
}
BLOB_DATA[sizeof(BLOB_DATA)-1]=0;
blob_data_len=strlen((const char *)BLOB_DATA);
package_num=strlen((const char *)BLOB_DATA);
printf("blob_data_len=%ld,package_num=%ld \n",blob_data_len,package_num );
EXEC SQL INSERT INTO a_SPTC_PACKAGE_CTL
( PACK_ID,
DATA_LEN,
BLOB_DATA
)
VALUES
( :package_num,
:blob_data_len,
empty_blob()
);
printf("1 sqlca.sqlcode=%d\n",sqlca.sqlcode);
#if 1
EXEC SQL EXECUTE
BEGIN
SELECT BLOB_DATA INTO blob_it.my_blob
FROM a_SPTC_PACKAGE_CTL
WHERE PACK_ID = :package_num
FOR UPDATE;
END;
END-EXEC;
#endif
printf("2 sqlca.sqlcode=%d\n",sqlca.sqlcode);
#if 1
pack_len=blob_data_len;
single_pack_len=blob_data_len;
cur_pos_pack_len=0;
while(1)
{
if ( pack_len > MAX_SINGLE_WRITE_BLOB_LEN )
{
single_pack_len=MAX_SINGLE_WRITE_BLOB_LEN ;
}
else
{
single_pack_len=pack_len;
break_flag=1;
}
memset(ctl_my_vr.arr,0, sizeof(ctl_my_vr.arr) );
memcpy(ctl_my_vr.arr, &BLOB_DATA[cur_pos_pack_len], single_pack_len );
ctl_my_vr.len = single_pack_len ;
cur_pos_pack_len = cur_pos_pack_len + 1 ;
EXEC SQL EXECUTE
BEGIN
DBMS_LOB.WRITE(blob_it.my_blob,:single_pack_len,:cur_pos_pack_len,:ctl_my_vr);
END;
END-EXEC;
cur_pos_pack_len = cur_pos_pack_len - 1 ;
cnt ++ ;
printf("[%d] sqlca.sqlcode=%d\n",cnt,sqlca.sqlcode);
printf("cur_pos_pack_len=%d pack_len=%d single_pack_len=%d \n",
cur_pos_pack_len,pack_len,single_pack_len);
if ( break_flag == 1 ) break ;
cur_pos_pack_len = cur_pos_pack_len + single_pack_len ;
pack_len = pack_len - single_pack_len ;
printf("cur_pos_pack_len=%d pack_len=%d single_pack_len=%d \n",
cur_pos_pack_len,pack_len,single_pack_len);
if ( sqlca.sqlcode !=0 )
{
return sqlca.sqlcode ;
}
}
#endif
do_commit();
printf("3 sqlca.sqlcode=%d\n",sqlca.sqlcode);
return 0 ;
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -