5.0 Use logging through C Stored Procedures

The logging framework routines are written in C, so you can use them directly from your C stored procedures without having to make a call to the logging stored procedures. The example C stored procedure below uses the logging API. To use the logging methods, include the splogger shared library in your project.

Include splogger.h in your C stored procedure that will define the logging methods prototypes.

Listing 8. Logging framework API — splogger.h header file

                
#ifndef SPLOGGER_H_
#define SPLOGGER_H_
#endif /*SPLOGGER_H_*/
#include <sys/timeb.h>
#define NAMESIZE 31    
#define DBSIZE 9    

typedef struct _handle Handle;
struct _handle 
{
        char spName[NAMESIZE+DBSIZE];
        struct timeb startTime; /* Pass starttime to know total elapsed time */
        struct timeb prevTime; /* Pass previous time to know elapsed time 
                                  since previous logger step */
};

#define BYTEHANDLESIZE (sizeof(Handle))
	
void openLog(char *name, char *handle);
void closeLog(char *handle);
void setDBName(struct sqludf_dbinfo *dbInfo);
void logger(char *handle, char *fmt, ... );
void logginfo(char *handle, char *fmt, ... );

Listing 9. Sample C stored procedure to show logging methods API

                
SQL_API_RC SQL_API_FN Snow
(
   char outMessage[100],
   sqlint16 *outMessageNullInd,
   char sqlstate[6],
   char qualName[28],
   char specName[19],
   char diagMsg[71],
   struct sqludf_dbinfo *dbInfo
)
{
   char byteHandle[BYTEHANDLESIZE];  
   char dbName[128] = "";
       
   setDBName(dbInfo);		  
   openLog("TESTSP", byteHandle);
   strncpy(outMessage, (char *)(dbInfo->dbname), dbInfo->dbnamelen);
   outMessage[dbInfo->dbnamelen] = '\0';
   logger(byteHandle, "The database name from dbInfo is %s", outMessage);
   strcat(outMessage, "::");  
   logger(byteHandle, "The HOME variable is %s", getenv("HOME"));
   strcat(outMessage, getenv("HOME"));
   strcat(outMessage, "::");
   strcat(outMessage, (char *)(dbInfo->appl_id));
   logger(byteHandle, "The Application ID is %s", (char *)(dbInfo->appl_id));
   *outMessageNullInd = 0;
   closeLog(byteHandle);
   return 0;  
}

Use the following SQL to register the example stored procedure.

Listing 10. Registering the example stored procedure

CREATE PROCEDURE DB.SNOW
(
   OUT   MSG       VARCHAR(100)
)
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE C
PARAMETER STYLE SQL
DBINFO
FENCED NOT THREADSAFE
NO SQL
PROGRAM TYPE SUB
EXTERNAL NAME 'splogger!Snow';