Re: COMMENTS, USER_TAB_COMMENTS. Comments?

From: David A. Bibeau <David.A.Bibeau_at_dartmouth.edu>
Date: 30 May 1994 20:37:02 GMT
Message-ID: <2sdipe$bsv_at_dartvax.dartmouth.edu>


In article <CqFx4B.GF_at_cmutual.com.au> aaj_at_cmutual.com.au (Tony Jambu) writes:

> Why not use the
> COMMENTS COMMENT ON TABLE user.table IS 'text'
> or
> COMMENT ON COLUMN user.table.column IS 'text'.
>
> You could generate these statement from with your forms application.

Forms won't let you put these statements in a trigger, why I don't know. Anyway, I solved this problem using a very simple C code user exit. Since user exits in Forms are crappy to begin with, it isn't a great compromise, but has the advantage of at least being fast and easy. All the user exit does is accept a piece of SQL code that isn't valid in a trigger, and executes it in immediate mode. Build a form that uses the views for comments, then add an on-update trigger that looks something like:

      DEFINE TRIGGER

         NAME = on-update
         TRIGGER_TYPE = V3
         TEXT = <<<
         declare
           cline char(255);
         begin
           cline := 'do_comment comment on column ';
           cline := cline||:b1.table_name||'.'||:b2.column_name||
                    ' is '''||:b2.comments||'''';
           user_exit(cline);
           if form_failure then
             bell;
             raise form_trigger_failure;
           end if;
         end;
         >>>

      ENDDEFINE TRIGGER




and the C code goes as follows:

/*

    Written by: David Bibeau
    Date: 7/26/93

    A user exit routine to do comments. This routine is passed a valid     "comment on" statement via the cmd string. This is just in turn     executed in immediate mode in dynamic sql method 1. Actually this     routine should be able to execute any DDL statement passed to it.

    to precompile:
    proc iname=do_comment host=c

    to compile:
    cc do_comment

    to link:
    gliap do_comment,exittable

*/

#include <stdio.h>
#include <string.h>
#include "ora_proc:sqlca.h"

#define min(a,b) ((a < b) ? a : b)

/* Include the oracle ca */

EXEC SQL INCLUDE ora_proc:oraca;

EXEC ORACLE OPTION (ORACA=YES); EXEC ORACLE OPTION (RELEASE_CURSOR=YES); EXEC SQL BEGIN DECLARE SECTION;
  varchar sqlstmt1[255];
EXEC SQL END DECLARE SECTION; int do_comment(cmd, cmdlen, msg, msglen, query)

     char *cmd,*msg;
     int  *cmdlen,*msglen,*query;

{

  /* Set up local variables */

  FILE *outfile;

  /*

     Declare a local variable to hold the cmd line, and parse off the
     first 11 characters of the command line which have to be by
     definition "do_comment ".

  */

  char *cp = cmd + 11;

  char errmsg[81];
  int errlen;

  /* set up the error handler */

  EXEC SQL WHENEVER SQLERROR GOTO sqlerror;

  /* copy the command line into the sqlstmt placeholder */

  strcpy(sqlstmt1.arr,cp);
  sqlstmt1.len = strlen(sqlstmt1.arr);

  /* do it */

  EXEC SQL EXECUTE IMMEDIATE :sqlstmt1;

  /* return success */
end_of_job:
  return(IAPSUCC);

sqlerror:
  /* get the error message from the sqlca, and display it on the form

     message area via the sqliem routine and return failure.   */
  strcpy(errmsg,"DO_COMMENT: ");  

strncat(errmsg,sqlca.sqlerrm.sqlerrmc,min(72,sqlca.sqlerrm.sqlerrml));   errlen = strlen(errmsg);
  sqliem(errmsg,&errlen);
  return(IAPFAIL);
} Received on Mon May 30 1994 - 22:37:02 CEST

Original text of this message