Re: COMMENTS, USER_TAB_COMMENTS. Comments?
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