7. Using BLOB Data

7.1 Using the PUT BLOB Statement

7.2 Using the GET BLOB Statement

7.3 Summary of the Difference Between PUT BLOB and GET BLOB

7. Using BLOB Data

When the BLOB data size is unknown at preparation time, or your buffer is not large enough, you may want to get or put partial BLOB data each time until you can get or put the entire data.

Note: If you can allocate enough buffer size in a program, or don't care whether you can get the whole BLOB or not, then you can use the original ESQL syntax to retrieve the BLOB column, and you don't need to use the following syntax.

7.1 Using the PUT BLOB Statement

STEPS:

Prepare an ESQL statement and declare the BLOB host variable as a question mark, to indicate that this BLOB host variable will be bound later.

EXEC SQL PREPARE stmt_name FROM "SQL SYNTAX" | :sql_string_host_variable];

Where "SQL SYNTAX" is a complete SQL syntax, which dmppcc will treat this syntax as static ESQL syntax, the syntax will be parsed and store the execution plan when preprocessing the syntax. When PREPARE syntax has :sql_string_host_variable, dmppcc will treat it as Dynamic ESQL syntax, the syntax will not be parsed until run time. Please refer to Chapter "Dynamic ESQL" for detail.

If you want to handle BLOB with GET/PUT BLOB syntax later, you should define "?" for the BLOB host variable.

For example, suppose we want to insert emp_pic into emp_table:

EXEC SQL PREPARE stmt1 FROM    
    "INSERT INTO emp_table (emp_id,    emp_pic) VALUES (:emp_id, ?)";

Execute this ESQL statement:

EXEC SQL EXECUTE stmt_name;

For example:

EXEC SQL EXECUTE stmt1;

Declare when to begin to put the BLOB:

EXEC SQL BEGIN PUT BLOB FOR stmt_name;

For example:

EXEC SQL BEGIN PUT BLOB FOR stmt1;

Define which BLOB variable to use for putting the BLOB and filling the information of the bufsize and bufptr. The order for PUT BLOB must be sequential from the first to the last unbound BLOB column.

EXEC SQL PUT BLOB FOR stmt_name USING :host_var [:indicator_var] 
/*host_var's data type must be longvarchar or longvarbinary) */

For example:

strcpy(buf, "This is a test");    
b1.buf = buf; 
b1.bufsize = strlen(buf); 
EXEC SQL PUT BLOB FOR stmt1 USING :b1;

Follow step 4 until there is no more BLOB data to put in this column.
If there is more than one BLOB column to put, you must declare when to begin putting the next BLOB column, then go to step 4 above.

EXEC SQL PUT NEXT BLOB FOR stmt_name;

For example:

EXEC SQL PUT NEXT BLOB FOR stmt1;

If all the BLOB column has been put into the database, you must declare that the PUT BLOB operation is finished.

EXEC SQL END PUT BLOB FOR stmt_name;

For example:

EXEC SQL END PUT BLOB FOR stmt1;

For example:

/********************************************************************** 
  * Prepare an insert statement; the input BLOB columns should use a  
 * question mark to denote it.    
 *******************************************************************/    
EXEC SQL PREPARE stmt1 FROM "insert into emp_table \ 
             (emp_id, emp_pic, emp_memo) values (:id, ?, ?)";

id = 1000+j;

/**********************************************************************    
 * Execute this statement. 
 ***********************************************************************/    
EXEC SQL execute stmt1;

/**********************************************************************    
 * If there are 300 characters to put into emp_pic, and we want to 
 * put it 100 characters at a time.    
 **********************************************************************/    
pic_buffer.bufsize = 100; /* max buffer size */

/* you must allocate enough buffer as indicated in field bufsize, or  
 *point to a valid buffer pointer */    
pic_buffer.buf = user_buf;

/**********************************************************************    
 * Begin PUT BLOB. 
 ***********************************************************************/    
EXEC SQL BEGIN PUT BLOB FOR stmt1;

/**********************************************************************    
 * Loop 3 times to put data. 
 **********************************************************************/    
for (i=0; i < 3; i++) 
   { 
    sprintf(pic_buffer.buf, "user's picture %dth's data........ ", i); 
    EXEC SQL PUT BLOB FOR stmt1 USING :pic_buffer;    
    }

/**********************************************************************    
 * Now start to put the next BLOB column's data. 
 **********************************************************************/    
EXEC SQL put next blob FOR stmt1;

/**********************************************************************    
 * If there are 200 characters to put into emp_memo, and we want to 
 * put it 100 characters at a time.    
 **********************************************************************/    
memo_buffer.bufsize = 100; /* max buffer size */ 
/* you must allocate enough buffer as indicated in field bufsize, or  
 *point to a valid buffer pointer */    
memo_buffer.buf = user_buf;

/**********************************************************************    
 * loop 2 times to put data 
 **********************************************************************/    
for (i=0; i < 2; i++) 
    { 
    sprintf(memo_buffer.buf, "user's memo %dth's data...........", i); 
    EXEC SQL PUT BLOB FOR stmt1 USING :memo_buffer;    
    }

/*********************************************************************    
 * end PUT BLOB 
 **********************************************************************/    
EXEC SQL END PUT BLOB FOR stmt1;

Using the GET BLOB Statement

CASE 1: Multiple-Row Data Retrieved Using Cursor

STEPS:

Prepare an ESQL statement and declare the BLOB host variable as a question mark (it means this BLOB host variable is not yet bound).

EXEC SQL PREPARE stmt_name FROM "SQL SYNTAX";

(Unlike dynamic ESQL syntax, the SQL SYNTAX is known at preprocessing time, and must include the bound host variable name; if it's a BLOB, and you want to use the GET/PUT BLOB method, then you must define '?'.)

For example: suppose we want to fetch emp_pic from emp_table:

EXEC SQL PREPARE stmt1 FROM "select emp_pic from emp_table into ?";

Declare a cursor for a prepared statement.

EXEC SQL DECLARE cursor_name CURSOR FOR stmt_name;

For example:

EXEC SQL DECLARE myCur CURSOR FOR stmt1;

Open the cursor.

For example:

EXEC SQL OPEN myCur;

Fetch the cursor.

For example:

EXEC SQL FETCH myCur;

Unlike PUT BLOB, you don't need to declare when to begin GET BLOB, but you must define the column number in the BLOB host variable and the available buffer size and valid buffer pointer.

EXEC SQL GET BLOB COLUMN :blobcol_num FOR stmt_name USING :host_var [:indicator_var]

For example:

EXEC SQL BEGIN DECLARE SECTION;    
int nCol; 
longvarchar b1; 
EXEC SQL END DECLARE SECTION;

nCol = 1; /* assign nCol as the column order in projection */ 
b1.bufsize = 50; /* if you want to get 50 bytes at a time */ 
b1.buf = buf; /* assign a valid buffer pointer to b1 */ 
EXEC SQL GET BLOB COLUMN :nCol FOR stmt2 USING :b1

Note: You can specify an indicator variable to get the remaining buffer size before getting this BLOB column..

EXEC SQL PREPARE stmt1 FROM "select c6 from d1 into ?"; 
EXEC SQL EXECUTE stmt1;

/* fetch BLOB with size = 0 first, to know total size with indicator */ 
nCol = 1; 
b1.bufsize = 0;    
b1.buf = wkbuf;    
EXEC SQL GET BLOB COLUMN :nCol FOR stmt1 USING :b1 :i_b1; 
if (SQLCODE == 0)    
    printf("left size is %d\n", i_b1);

/* loop fetch BLOB with size = 2, and print out "last" remain size */ 
for (i = 0; SQLCODE != SQL_NO_DATA_FOUND; i++) 
    { 
    b1.bufsize = 2; /* get 1 char plus null ptr at a time */ 
    EXEC SQL GET BLOB COLUMN :nCol FOR stmt1 USING :b1 :i_b1; 
    if (SQLCODE != SQL_NO_DATA_FOUND)    
    printf("b1 = %s, last remain size is %d\n", b1.buf, i_b1); 
    chkErr(); 
    }

Or you can also set bufsize = DB_ALLOCATE_MEMORY, when you want DBMaker to allocate the memory for getting this blob. DBMaker will free the allocated memory related to the blob when you call next FETCH or CLOSE CURSOR statement. Please be careful with this option, because for allocating enough memory for the blob, maybe cause "OUT OF MEMORY" problem in the system. And because after the next FETCH or CLOSE statement, the memory will be freed by the database, the pointer of blob variable will refer to an invalid address. This may cause core dumped or error executing result in your program, please do it with cautious.

Continue as in the previous step, if there's more BLOB data to get.

For example:

EXEC SQL PREPARE stmt1 FROM "select c6 from d1 into ?"; 
EXEC SQL EXECUTE stmt1;

/* fetch BLOB with size = 0 first, to know total size with indicator */ 
nCol = 1; 
b1.bufsize = 0; 
b1.buf = wkbuf; 
EXEC SQL GET BLOB COLUMN :nCol FOR stmt1 USING :b1 :i_b1; 
if (SQLCODE == 0) 
    printf("left size is %d\n", i_b1);

/* loop fetch BLOB with size = 2, and print out "last" remain size */ 
for (i = 0; SQLCODE != SQL_NO_DATA_FOUND; i++) 
    { 
    b1.bufsize = 2; /* get 1 char plus null ptr at a time */ 
    EXEC SQL GET BLOB COLUMN :nCol FOR stmt1 USING :b1 :i_b1; 
    if (SQLCODE != SQL_NO_DATA_FOUND)    
    printf("b1 = %s, last remain size is %d\n", b1.buf, i_b1);

    chkErr(); 
    }

If all data has been retrieved or you don't want to get the rest of the BLOB data, you can continue to fetch the rest of the result buffer of this cursor until no more rows are found.

For example:

#define MAX_BUF_SIZE 101

/* Prepare a SELECT statement, the output BLOB column should use */ 
/* a question mark to denote it. */    
EXEC SQL PREPARE stmt1 FROM "select emp_id, emp_pic from emp_table 
                             into :id, ?";

/* Declare a cursor to associate it with this statement. */ 
EXEC SQL DECLARE myCur CURSOR FOR stmt1;

/* Open this cursor. */ 
EXEC SQL OPEN myCur;

/* To get one MAX_BUF_SIZE character    at a time, we must fill the  
 * following field first. */ 
nCol = 2; /* second output column in projection.*/ 
pic_buffer.bufsize = MAX_BUF_SIZE; /* max buffer size */

/* You must allocate enough buffer as indicated in field bufsize, or  
   point to a valid buffer pointer. */    
pic_buffer.buf = user_buf;

/* loop fetch result. */ 
while (1) 
    { 
    EXEC SQL FETCH myCur INTO :id, ?; /* fetch cursor */ 
    if (SQLCODE) 
        { 
         if (SQLWARN0 != 'W') 
             break; 
        }

    printf("emp_id = %d\n", id);    
    printf("emp_pic = ");    
    /* loop get BLOB data until no data is found. */ 
    for (i = 0; SQLCODE != SQL_NO_DATA_FOUND; i++) 
        { 
         EXEC SQL GET BLOB COLUMN :nCol FOR stmt1    
                           USING :pic_buffer :pic_ind; 
         if (SQLCODE != SQL_NO_DATA_FOUND)    
             { 
             if (pic_ind == SQL_NULL_DATA)    
                 printf("(null)"); 
             else 
                 { 
                  for(j = 0; j < MAX_BUF_SIZE-1; j++)    
                  printf("%c", pic_buffer.buf[j]);    
                 } 
             } 
        } 
    printf("\n"); 
    }
   /* Close the cursor. */ 
   EXEC SQL CLOSE myCur;

7.3 Summary of the Difference Between PUT BLOB and GET BLOB

Because it's not necessary to GET BLOB data in sequence, we can assign which column to get and don't have to GET BLOB data from left column to right (small column numbers to large column numbers). It's also not necessary to get the entire BLOB data if you don't want to.

PUT BLOB must start from the first to the last BLOB column, and it must be indicated when to begin and when to stop. You can't put the BLOB randomly. If you did not put each BLOB column accordingly, and did not indicate when to end PUT BLOB, the BLOB column will not actually be inserted into the database. In this case, the operation will be aborted (because the operation never finished) when you disconnect or exit from the program. If you disconnect from the database, an error will show that the last statement has been canceled.