Read and write simple large objects to an optical disc (UNIX)

In a table, columns of type simple-large-object do not include the simple-large-object data in the table itself. Instead, the simple-large-object column contains a 56-byte simple-large-object descriptor that includes a forward pointer (rowid) to the location where the first segment of simple-large-object data is stored. The descriptor can point to a dbspace blobpage, a blobspace blobpage, or a platter in an optical storage subsystem. For details, see your HCL OneDB™ Administrator's Guide and the .

When a simple large object is stored on a write-once-read-many (WORM) optical-storage subsystem, you can have a single physical simple large object in more than one table to conserve storage space on the WORM optical disc. The LOC_DESCRIPTOR flag enables you to migrate a simple-large-object descriptor, rather than the simple large object itself, from one table to another.

When you read or write a simple-large-object column that is stored on a WORM optical disc, you can manipulate only the simple-large-object descriptor if you set the loc_oflags field of the locator structure to LOC_DESCRIPTOR.

Important: Only use LOC_DESCRIPTOR with simple large objects that are stored on WORM optical media.

The following figure shows a code fragment that selects the stock_num, manu_code, cat_descr, and cat_picture columns from the catalog table of the named database. The program uses the DESCR() SQL function expression to retrieve the simple-large-object descriptor, rather than to retrieve the simple large object itself, for the cat_picture column. The program then sets the loc_oflags field of the cat_picture locator structure to LOC_DESCRIPTOR to signal that the simple-large-object descriptor, rather than the simple large object, is to be inserted into the cat_picture column of the pictures table. The result is that the cat_picture columns in both the catalog and pictures tables refer to a single set of physical simple large objects.

Figure 1: Code fragment to retrieve the simple-large-object descriptor

#include <stdio.h>
EXEC SQL include locator;

char errmsg[400];

EXEC SQL BEGIN DECLARE SECTION;
   mlong cat_num;
   int2 stock_num;
   char manu_code[4];
   ifx_loc_t cat_descr;
   ifx_loc_t cat_picture;
EXEC SQL END DECLARE SECTION;

main(argc, argv)
mint argc;
char *argv[];
{
   EXEC SQL BEGIN DECLARE SECTION;
      char db_name[250];
   EXEC SQL END DECLARE SECTION;

   if (argc > 2)                               /* correct no. of args? */
   {
      printf("\nUsage: %s [database]\nIncorrect no. of argument(s)\n",
         argv[0]);
      exit(1);
   }
   strcpy(db_name, "stores7");
   if(argc == 2)
      strcpy(db_name, argv[1]);
   EXEC SQL connect to :db_name;
   sprintf(db_msg, "CONNECT TO %s",db_name);
   err_chk(db_msg);
   EXEC SQL declare catcurs cursor for        /* setup cursor for select */
      select stock_num, manu_code, cat_descr, DESCR(cat_picture)
      from catalog 
      where cat_picture is not null;
   /*
    * Prepare locator structures cat_descr(TEXT) and
    * cat_picture (BYTE that is the simple-large-object descriptor).
    */
   cat_descr.loc_loctype = LOCMEMORY;       /* set loctype for in memory */
   cat_picture.loc_loctype = LOCMEMORY;     /* set loctype for in memory */
   while(1)
      {
      /*
       * Let server get buffers and set loc_buffer (buffer for 
       * simple-large-object descriptor) and loc_bufsize (size of buffer)
       */
      cat_descr.loc_bufsize = -1;
      cat_picture.loc_bufsize = -1;
      /*
       * Select row from catalog table (descr() returns TEXT descriptor
       * for cat_picture.  For cat_descr, the actual simple LO is returned.
       */
      EXEC SQL fetch catcurs into :stock_num, :manu_code, :cat_descr,
         :cat_picture;
      if(err_chk("FETCH") == SQLNOTFOUND)                 /* end of data */
         break;
      /*
       * Set LOC_DESCRIPTOR in loc_oflags to indicate simple-large-object
       * descriptor is being inserted rather than simple-large-object data.
       */
      cat_picture.loc_oflags |= LOC_DESCRIPTOR;
      /*
       * Insert
       */
      EXEC SQL insert into pictures values (:stock_num, :manu_code,
         :cat_descr, :cat_picture);
      if(err_chk("INSERT") < 0)
         printf("Insert failed for stock_num %d, manu_code %s", stock_num,
            manu_code);
      }
   /* Clean up db resources */
   EXEC SQL close catcurs;
   EXEC SQL free catcurs;

   /* Deallocate memory buffers */
   free(cat_descr.loc_buffer);
   free(cat_picture.loc_buffer);

   EXEC SQL disconnect current;
}


/*
 * err_chk() checks sqlca.sqlcode and if an error has occurred, it uses
 * rgetlmsg() to display to stderr the message for the error number in
 * sqlca.sqlcode.
 */

int err_chk(stmt)
char *stmt;
{
   char buffer[512];

   if(sqlca.sqlcode < 0)
      {
      fprintf(stderr, "Error: %s\n", stmt);
      rgetlmsg(sqlca.sqlcode, buffer, sizeof(buffer));
      fprintf(stderr, "SQL %d: ", sqlca.sqlcode);
      fprintf(stderr, buffer sqlca.sqlerrm);
      if (sqlca.sqlerrd[1] != 0)
         {
         rgetlmsg(sqlca.sqlerrd[1], buffer, sizeof(buffer));
         fprintf(stderr, "ISAM %d: ", sqlca.sqlerrd[1]);
         fprintf(stderr, buffer, sqlca.sqlerrm);
         }
      exit(1);
      }
   return(sqlca.sqlcode);
}

You can also use the SQL DESCR() function to achieve the same result without a loc_oflags value of LOC_DESCRIPTOR. The SQL statement shown in the following figure accomplishes the same task as the locator structure in the preceding example.

Figure 2: Using DESCR() to access a simple- large-object descriptor

EXEC SQL insert into pictures (stock_num, manu_code, cat_descr, cat_picture)
   select stock_num, manu_code, cat_descr, DESCR(cat_picture) 
   from catalog
   where cat_picture is not null;