Insert a localized exception message from a C UDR

To avoid this restriction, you can use a UDR that prepares the INSERT statement (with mi_prepare( )) to load the error messages:

• Use placeholders ('?' symbols) for the SQLSTATE value and the error-message text. These values are in the first (sqlstate) and last columns (message) of the syserrors system catalog table.
• Hardcode the name of the locale that the message text uses. The locale name is in the second column (locale) of syserrors.

For example, the following line prepares an INSERT statement for messages in the default locale (en_us) on a UNIX™ system:

stmt = mi_prepare(conn, 
   "insert into syserrors (?, 'en_us.8859-1', 0, 1, ?)", NULL);

The following UDR code uses a message array (enus_msg) to hold the SQLSTATE values and their associated message text. It puts information about each element of this message array in the appropriate placeholder arrays (args, lens, nulls, and types) of the mi_exec_prepared_statement( ) function.

#include <stdio.h>
#include <string.h>
#include "mi.h"

#define MAX_MSG 3
char *enus_msg[MAX_MSG][2] =  {
   "XT010", "First error message for insertion",
   "XT020", "Second error message for insertion",
   "XT030", "Third error message for insertion"
    };

/*
 * Title: gls_insert_enus
 * Purpose: Add localized messages to 'syserrors' system error table 
 *          for given locale, independent of session locale setting.
 */ 
mi_integer
gls_insert_enus()
{
MI_DATUM       args[2];             /* pointers to column values */
mi_integer     lens[2];             /* lengths of column values */
mi_integer     nulls[2];            /* null capability of columns */
mi_string     *types[2];            /* types of columns */
mi_integer     i;
MI_STATEMENT  *stmt;
MI_CONNECTION *conn = mi_open(NULL, NULL, NULL);

/*
 * Prepare statement using placeholder values for sqlstate and message
 * columns and fixed values for locale, level, and seqno columns.
 */
stmt = mi_prepare(conn,
         "insert into syserrors values(?,'en_us.8859-1',0,1,?)", NULL);
for (i=0; i<MAX_MSG; i++)           /* Loop through message array */
{
   args[0] = (MI_DATUM)enus_msg[i][0]; 
      /* Set pointer to sqlstate string */
   lens[0] = strlen(args[0]);      /* Set length of sqlstate string */
   nulls[0] = MI_FALSE;            /* Set null handling capability */
   types[0] = "char(5)";           /* Set sqlstate column type */
   args[1] = (MI_DATUM)enus_msg[i][1]; 
      /* Set pointer to message string */
   lens[1] = strlen(args[1]);      /* Set length of message string */
   nulls[1] = MI_FALSE;            /* Set null handling capability */
   types[1] = "varchar(255)";      /* Set message column type */

mi_exec_prepared_statement(stmt,0,0,2,args,lens,nulls,types,
   NULL,NULL);
}
mi_close(conn);
return 0;
}