Uploading and downloading files from and to BLOB column is a painstaking process in Oracle Pro*C. More details: download, read BLOB and upload, write BLOB.
Pro*C code example:
create table blobtable (id number(12), filename varchar2(100), fileb blob);Code sample for reading and writing BLOB column is here.
One has to recognize whether file can be swallowed in a single mouthful (uff !) and issue EXEC SQL LOB WRITE ONE. If not then a complicated logic should be developed to recognize the first chunk (EXEC SQL LOB WRITE FIRST), intermediate chunk(s) (EXEC SQL LOB WRITE NEXT) and the closing chunk (EXEC SQL LOB WRITE LAST). The same for reading a BLOB column. DBMB_LOB utility is only a partial solution because it can read/write files from and to Oracle server.
Embedded SQL (DB2) BLOB
In DB2 (Embedded SQL) it is much simpler. Just use BLOB_FILE host variable for BLOB reading and writing. The whole code looks very simple now. Works also while executed at the client or server side.
int writeBlob(char *filename) {
struct sqlca sqlca;
int error = 0;
EXEC SQL BEGIN DECLARE SECTION;
int id;
char pfilename[500];
SQL TYPE IS BLOB_FILE FILEB;
EXEC SQL END DECLARE SECTION;
strcpy(pfilename,filename);
EXEC SQL select id into :id FROM blobtable WHERE id = 1 ;
EVALRESULT("Select blob record");
if (error) return 0;
if (sqlca.sqlcode == 100) {
// not found
EXEC SQL INSERT INTO blobtable VALUES(1,:pfilename,NULL);
EVALRESULT("Insert record to blobtable");
if (error) return 0;
}
strcpy (FILEB.name, pfilename);
FILEB.name_length = strlen(pfilename);
FILEB.file_options = SQL_FILE_READ;
EXEC SQL UPDATE blobtable SET filename=:pfilename,fileb=:FILEB WHERE ID=1;
EVALRESULT("Insert blob value");
if (error) return 0;
return 1;
}
int readBlob(char *filename) {
struct sqlca sqlca;
int error = 0;
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS BLOB_FILE FILEB;
EXEC SQL END DECLARE SECTION;
strcpy (FILEB.name, filename);
FILEB.name_length = strlen(filename);
FILEB.file_options = SQL_FILE_OVERWRITE;
EXEC SQL select fileb into :FILEB FROM blobtable WHERE id = 1 ;
EVALRESULT("Read blob record");
if (error) return 0;
printf("sql code=%u\n",sqlca.sqlcode);
if (sqlca.sqlcode == 100) {
// not found
printf("No record in blobtable table\n");
return 0;
}
return 1;
}
