Sending large amounts of LOB data as parameters

Large amounts of LOB data is sent in streams to the server to better manage resources. Use ct_send_data() in a loop to send data to the server in chunks.

To send a LOB data parameter in chunks, use these settings to define the parameter:

• Set the datatype field of the CS_DATAFMT structure to CS_TEXT_TYPE, CS_UNITEXT_TYPE, or CS_IMAGE_TYPE.

• Set maxlength field of the CS_DATAFMT structure to CS_UNUSED.

• Set the *data pointer argument of the ct_param() function to NULL.

• Set the datalen argument of the ct_param() function to 0.

#define BUFSIZE 2048

int fp;
char sendbuf[BUFSIZE]

/*
** Clear and setup the CS_DATAFMT structure, then pass
** each of the parameters for the RPC.
*/
memset(&paramfmt, 0, sizeof(paramfmt));
strcpy(paramfmt.name, "@intparam");
paramfmt.namelen = CS_NULLTERM;
paramfmt.datatype = CS_INT_TYPE;
paramfmt.maxlength = CS_UNUSED;
paramfmt.status = CS_INPUTVALUE;
paramfmt.locale = NULL;

ct_param(cmd, &paramfmt, (CS_VOID *)&intvar,
   sizeof(CS_INT), 0))

/*
** Text parameter, sent as a BLOB.
*/
strcpy(paramfmt.name, "@textparam");
paramfmt.namelen = CS_NULLTERM;
paramfmt.datatype = CS_TEXT_TYPE;
paramfmt.maxlength = CS_UNUSED;
paramfmt.status = CS_INPUTVALUE;
paramfmt.locale = NULL;

/*
** Although the actual data will not be sent here, we
** must invoke ct_setparam() for this parameter to send
** the parameter format (paramfmt) information to the
** server, prior to sending all parameter data.
** Set *data to NULL and datalen = 0, to indicate that
** the length of text data is unknown and we want to
** send it in chunks to the server with ct_send_data().
*/
ct_setparam(cmd, &paramfmt, NULL, 0, 0);

/*
** Another LOB parameter (image), sent in chunks with
** ct_send_data()
*/
strcpy(paramfmt.name, "@textparam");
paramfmt.namelen = CS_NULLTERM;
paramfmt.datatype = CS_IMAGE_TYPE;
paramfmt.maxlength = CS_UNUSED;
paramfmt.status = CS_INPUTVALUE;
paramfmt.locale = NULL;

/*
** Just like the previous parameter, invoke 
** ct_setparam() for this parameter to send the
** parameter format.
*/
ct_setparam(cmd, &paramfmt, NULL, 0, 0);

/*
** Repeat this sequence of filling paramfmt and calling
** ct_param() for any subsequent parameter that needs
** to be sent before finally sending the data chunks for
** the LOB type parameters.
*/
strcpy(paramfmt.name, "@any_otherparam");
paramfmt.namelen = CS_NULLTERM;
paramfmt.datatype = CS_MONEY_TYPE;
...

/*
** Send the first LOB (text) parameter in chunks of
** ‘BUFSIZE’ to the server. We must end with a 0 bytes
** write to indicate the end of the current parameter.
*/
fp = open(“huge_text_file”, O_RDWR, 0666);

do
{
   num_read = read(fp, sendbuf, BUFSIZE);
   ct_send_data(cmd, (CS_VOID *)sendbuf, num_read);
}  while (num_read != 0);

/*
** Repeat the ct_send_data() loop for the next LOB
** parameter.
** Send the image parameter in chunks of ‘BUFSIZE’
** to the server as well and end with a 0 bytes write
** to indicate the end of the current parameter.
*/
fp = open(“large_image_file”, O_RDWR, 0666);
do
{
   num_read = read(fp, sendbuf, BUFSIZE);
   ct_send_data(cmd, (CS_VOID *)sendbuf, num_read);
} while (num_read != 0);

/*
** Ensure that all the data is flushed to the server
*/
ct_send(cmd);

/* 
** Prepare the sql statement.
*/
sprintf(statement, "select title_id from mybooks 
   where title like (?) ");

/* 
** Send the prepared statement to the server 
*/
ct_dynamic(cmd, CS_PREPARE, "mydyn_stmt", CS_NULLTERM, 
   statement, CS_NULLTERM);

ct_send(cmd);
handle_results();

/* 
** Promt user to provide a value for title
*/
printf("Enter title id value - enter an X if you wish
   to stop: \n");

while (toupper(myblobtitle[0]) != 'X') 
{
   printf("Retrieve detail record for title: ?");
   fgets(myblobtitle, 50,  stdin); 

   /*
   ** Execute the dynamic statement.
   */
   ct_dynamic(cmd, CS_PREPARE, "my_dyn_stmt",
   CS_NULLTERM, statement, CS_NULLTERM);

   /*
   ** Define the input parameter, a TEXT type that we
   want to send in chunks to the server.
   */
   memset(&data_format, 0, sizeof(data_format)) ;
   data_format.namelen = CS_NULLTERM ;
   data_format.datatype = CS_TEXT_TYPE; 
   data_format.maxlength = CS_UNUSED;
   data_format.status = CS_INPUTVALUE;
   ct_setparam(cmd, &data_format, NULL, 0, 0);

   /*
   ** Send the ‘myblobtitle’ data in chunks of
   ** ‘CHUNKSIZE’ to the server with ct_send_data() and
   ** end with 0 bytes to indicate the end of data for
   ** this parameter. This is just an example to show
   ** how chunks can be sent. (myblobtitle[] is used as
   ** a simple example. This could also be replaced by
   ** large file which would be read in chunks from disk
   ** for example).
   */
   bytesleft = strlen(myblobtitle);
   bufp = myblobtitle;

   do
   {
      sendbytes = min(bytesleft, CHUNKSIZE); 
      ct_send_data(cmd, (CS_VOID *)bufp, sendbytes);
      bufp += bufp + sendbytes;
      bytesleft -= sendbytes;
   }  while (bytesleft > 0)

   /*
   ** End with 0 bytes to indicate the end of current
   data.
   */
   ct_send_data(cmd, (CS_VOID *)bufp, 0);

   /*
   ** Insure that all the data is sent to the server.
   */
   ct_send(cmd);
   handle_results(cmd)
   ...
}

/*
** Deallocate the prepared statement and finish up.
*/

ct_dynamic(cmd, CS_DEALLOC, "my_dyn_stmt", CS_NULLTERM,
   NULL, CS_UNUSED);

ct_send(cmd);
handle_results(cmd);