Send Small Amounts of LOB Data as Parameters

Sending a small amount of LOB data as an input parameter to stored procedures or as a parameter to a prepared SQL statement is the same as sending non-LOB parameters.

To send a small amount of LOB data, allocate memory for the command and data and directly send these to the server using ct_param() or ct_setparam().

You must set the maxlength field for the CS_DATAFMT structure when using text, unitext, or image parameters. The maxlength value indicates whether all of the LOB data is sent at once or streamed to the server. When maxlength is greater than zero, the LOB data is sent in one chunk. When maxlength is set to CS_UNUSED, the LOB data is sent in a stream, using a loop of ct_send_data() calls to send the data in chunks. A chunk length of zero indicates the end of the data stream.

Example 1

Sends a small amount of LOB data as an input parameter to a stored procedure:

CS_TEXT textvar[50];
CS_DATAFMT paramfmt;
CS_INT datalen;
CS_SMALLINT ind;

...
ct_command(cmd, CS_RPC_CMD, ...)

/*
** Clear and setup the CS_DATAFMT structure, then pass
** each of the parameters for the RPC.
*/
memset(&paramfmt, 0, sizeof(paramfmt));

/*
** First parameter, an integer.
*/
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), ind))

/*
** Second parameter, a (small) text parameter.
*/

strcpy((CS_CHAR *)textvar, “The Open Client and Open
   Server products both include Bulk-Library and
   CS-Library. ”);
datalen = sizeof(textvar);
strcpy(paramfmt.name, "@textparam");
paramfmt.namelen = CS_NULLTERM;
paramfmt.datatype = CS_TEXT_TYPE;
paramfmt.maxlength = EX_MYMAXTEXTLEN;
paramfmt.status = CS_INPUTVALUE;
paramfmt.locale = NULL;
ct_setparam(cmd, &paramfmt, (CS_VOID *)&textvar,
   &datalen, &ind);

ct_send(cmd);
ct_results(cmd, &res_type);

...

Example 2

Sends a small amount of LOB data using a prepared statement:

/* 
** 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, "my_dyn_stmt", CS_NULLTERM,
   statement, CS_NULLTERM);

ct_send(cmd);
handle_results(cmd);

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

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

   /*
   ** Execute the dynamic statement.
   */ 

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

    /*
   ** Define the input parameter 
   */

   memset(&data_format, 0, sizeof(data_format));
   data_format.status = CS_INPUTVALUE;
   data_format.namelen = CS_NULLTERM ;
   data_format.datatype = CS_TEXT_TYPE;
   data_format.format = CS_FMT_NULLTERM;
   data_format.maxlength = EX_MYMAXTEXTLEN;
   ct_setparam(cmd, &data_format, 
      (CS_VOID *)mytexttitle, &datalen, &ind);

ct_send(cmd);
handle_results(cmd);
...
}