Subject: TRIGGER AND STORED PROCEDURE HELP NEEDED From: "Leffler, Jonathan" Date: Thu, 14 May 1998 14:04:47 -0700 Ramki Natesan (rnatesan@bactc.com) asked: >I have the following trigger stmnt. >create trigger get_wo_num >insert on workorder >after (execute procedure create_wo_for_nt()) Q: Can I do this in isql mode? JL: Yes. ISQL can handle any statement which does not have semi-colons in the middle of it. The statements which have such semi-colons are CREATE PROCEDURE statements. For those, you need to use DB-Access. >then, p1.sql file has the following:- >CREATE PROCEDURE create_wo_for_nt() [...several statements, hence several semi-colons...] >END PROCUDERE; >Q: Can I run this (p1.sql) file in isql mode? No, because there are semi-colons in the middle of the statement and ISQL gets confused. Use DB-Access. If that isn't available, go to the server machine where the database is and use the copy of DB-Access which is on that. Or, if you have ESQL/C, you can either collect SQLCMD from the IIUG software archives at http://www.iiug.org or you can use the program MKPROC in the shell archive below which will execute the CREATE PROCEDURE statements from a source file (or any other statements which return no data to the caller). If none of this is feasible, then you need to chat to your System Administrator and/or DBA and get DB-Access on your machine. Yours, Jonathan Leffler (jleffler@visa.com) #include : "@(#): shar.sh,v 1.11 1998/03/16 20:36:16 jleffler Exp $" #! /bin/sh # # This is a shell archive. # Remove everything above this line and run sh on the resulting file. # If this archive is complete, you will see this message at the end: # "All files extracted" # # Created: Thu May 14 13:59:36 PDT 1998 by jleffler at Visa International Inc. # Files archived in this archive: # getopt.h # mkproc.1 # mkproc.ec # mkproc.mk # stderr.c # stderr.h # #-------------------- if [ -f getopt.h -a "$1" != "-c" ] then echo shar: getopt.h already exists else echo 'x - getopt.h (1772 characters)' sed -e 's/^X//' >getopt.h <<'SHAR-EOF' X/* X@(#)File: $RCSfile: getopt.h,v $ X@(#)Version: $Revision: 1.10 $ X@(#)Last changed: $Date: 1998/04/09 18:48:20 $ X@(#)Purpose: Declarations for GETOPT(3) and GETSUBOPT(3) X@(#)Author: J Leffler X@(#)Copyright: JLSS (C) 1992-93,1996-97 X@(#)Product: :PRODUCT: X*/ X X#ifndef GETOPT_H X#define GETOPT_H X X#ifdef MAIN_PROGRAM X#ifndef lint Xstatic const char getopt_h[] = "@(#)$Id: getopt.h,v 1.10 1998/04/09 18:48:20 jleffler Exp $"; X#endif /* lint */ X#endif /* MAIN_PROGRAM */ X X/* X** GNU getopt provides facilities not available in standard getopt. X** Specifically, it will reorder all option arguments before all non-option X** arguments unless the environment variable _POSIX_OPTION_ORDER is X** defined. It can also handle optional arguments, which must be attached X** to option letter on the command line, indicated by two colons after the X** option letter. It can be told to return all arguments in order, with a X** value of '\0' indicating a file option by starting the options string X** with a '-'. It also has a different interface from standard getopt X** because the second (argv) argument is not const. X*/ X X#ifdef USE_GNU_GETOPT X#define GETOPT(argc, argv, opts) gnu_getopt(argc, argv, opts) X#define opterr gnu_opterr X#define optind gnu_optind X#define optarg gnu_optarg X#define optopt gnu_optopt X#else X#define GETOPT(argc, argv, opts) getopt(argc, argv, opts) X#endif /* USE_GNU_GETOPT */ X Xextern int optopt; Xextern int opterr; Xextern int optind; Xextern char *optarg; X Xextern int getopt(int argc, char *const*argv, const char *opts); Xextern int getsubopt(char **opt, char *const*names, char **value); Xextern int gnu_getopt(int argc, char **argv, const char *opts); X X#endif /* GETOPT_H */ SHAR-EOF chmod 440 getopt.h if [ `wc -c mkproc.1 <<'SHAR-EOF' X.\" @(#)$Id: mkproc.1,v 1.1 1998/05/14 20:56:55 jleffler Exp $ X.\" @(#)Manual page: MKPROC -- Execute SQL statements in files X.ds fC "Version: $Revision: 1.1 $ ($Date: 1998/05/14 20:56:55 $) X.TH MKPROC 1S "JLSS UNIX Tools" X.SH NAME Xmkproc \(em Execute SQL statements in files X.SH SYNOPSIS X\fBmkproc\fP [-stV] -d dbase file [...] X.SH DESCRIPTION XThe command \fBMkproc\fP opens the given database and then Xexecutes the contents of each of the files specified on the Xcommand line using the ESQL/C CREATE PROCEDURE FROM statement. XThe statements should, therefore, not return any data, but within Xthat constraint, most statements can be used. X.P XIf the database has transactions, mkproc will enclose each Xstatement in a transaction unless the '\*c-t\*d' flag is Xspecified. XIf more than one file is specified and the '\*c-s\*d' flag is not Xmentioned, then the file names will echoed as they are executed. XIf the -V flag is specified, the version number is printed and Xthe program exits successfully. X.SH "SEE ALSO" Xsqlcmd(1) X.SH DIAGNOSTICS XVarious, depending on SQL errors. XError -461 (file open error) means that the named file could not Xbe found. X.SH BUGS XNone known. X.SH AUTHOR XJonathan Leffler X.br XJLSS X.br X14th May 1998 SHAR-EOF chmod 440 mkproc.1 if [ `wc -c mkproc.ec <<'SHAR-EOF' X/* X@(#)File: $RCSfile: mkproc.ec,v $ X@(#)Version: $Revision: 2.2 $ X@(#)Last Changed: $Date: 1998/05/14 20:57:20 $ X@(#)Purpose: Create stored procedures from named files X@(#)Author: Jonathan Leffler X@(#)Copyright: (C) JLSS 1992-93,1998 X*/ X X#include X#include X#include "getopt.h" X#include "stderr.h" X Xstatic void mkproc(char *file, int tflag); Xstatic void sql_error(const char *stmt, const char *obj); X Xstatic const char usestr[] = "[-stV] -d dbase procfile [...]"; X X#ifndef lint Xstatic const char rcs[] = "@(#)$Id: mkproc.ec,v 2.2 1998/05/14 20:57:20 jleffler Exp $"; X#endif X Xint main(int argc, char **argv) X{ X int i; X int dflag = 0; /* Database selected */ X int sflag = 1; /* Echo file names? */ X int tflag = 1; /* Do transactions? */ X EXEC SQL BEGIN DECLARE SECTION; X char *dbase; X EXEC SQL END DECLARE SECTION; X X setarg0(argv[0]); X while ((i = GETOPT(argc, argv, "d:stV")) != EOF) X { X switch (i) X { X case 'd': X dbase = optarg; X dflag = 1; X EXEC SQL DATABASE:dbase; X sql_error("database", dbase); X break; X case 's': X sflag = 0; X break; X case 't': X tflag = 0; X break; X case 'V': X version("MKPROC", &"@(#)$Revision: 2.2 $ ($Date: 1998/05/14 20:57:20 $)"[4]); X break; X default: X usage(usestr); X break; X } X } X if (optind == argc || dflag == 0) X usage(usestr); X X /* Don't echo file names if there is only one file */ X if (optind == argc - 1) X sflag = 0; X X /* Transactions? */ X if (sqlca.sqlwarn.sqlwarn1 != 'W' && tflag == 1) X tflag = 0; X X for (i = optind; i < argc; i++) X { X if (sflag) X puts(argv[i]); X mkproc(argv[i], tflag); X } X X return(0); X} X Xstatic void sql_error(const char *stmt, const char *obj) X{ X char buffer[512]; X X if (sqlca.sqlcode != 0) X { X fprintf(stderr, "%s: %s %s\n", getarg0(), stmt, obj); X rgetmsg(sqlca.sqlcode, buffer, sizeof(buffer)); X fprintf(stderr, "SQL %ld: ", sqlca.sqlcode); X fprintf(stderr, buffer, sqlca.sqlerrm); X if (sqlca.sqlerrd[1] != 0) X { X rgetmsg(sqlca.sqlerrd[1], buffer, sizeof(buffer)); X fprintf(stderr, "ISAM %ld: ", sqlca.sqlerrd[1]); X fprintf(stderr, buffer, sqlca.sqlerrm); X } X exit(1); X } X} X Xstatic void mkproc(char *file, int tflag) X{ X EXEC SQL BEGIN DECLARE SECTION; X char *procfile = file; X EXEC SQL END DECLARE SECTION; X X if (tflag) X { X EXEC SQL BEGIN WORK; X sql_error("begin", "work");; X } X X EXEC SQL CREATE PROCEDURE FROM $procfile; X sql_error("create procedure from", procfile); X X if (tflag) X { X EXEC SQL COMMIT WORK; X sql_error("commit", "work");; X } X} SHAR-EOF chmod 440 mkproc.ec if [ `wc -c mkproc.mk <<'SHAR-EOF' X# $Id: mkproc.mk,v 1.1 1998/05/14 20:56:42 jleffler Exp $ X# X# Makefile for MKPROC command X XCC = gcc XXFLAGS = -Wall -Wshadow -Wstrict-prototypes -Wmissing-prototypes X X#CC = cc -g X#XFLAGS = X XESQL = INFORMIXC="${CC}" ${ESQL_CMD} XESQL_CMD = esql XECFLAGS = X XRM = rm -f XOFLAGS = -O XPFLAGS = -D_POSIX_SOURCE -D_POSIX_C_SOURCE=199309 XINC1 = ${INFORMIXDIR}/incl/esql XINC2 = ${INFORMIXDIR}/incl XINCDIRS = -I${INC1} -I${INC2} XSTRIP = -s XLDFLAGS = ${STRIP} XCFLAGS = ${SFLAGS} ${INCDIRS} ${OFLAGS} ${XFLAGS} ${PFLAGS} X X.SUFFIXES: X.SUFFIXES: .ec .c .o X X.ec.o: X ${ESQL} -c ${ECFLAGS} ${CFLAGS} $*.ec X ${RM} $*.c X XPROGRAM = mkproc XFILES.o = mkproc.o stderr.o X Xall: ${PROGRAM} X X${PROGRAM}: ${FILES.o} X ${ESQL} -o $@ ${FILES.o} ${LDFLAGS} X Xclean: X ${RM} ${PROGRAM} ${FILES.o} SHAR-EOF chmod 440 mkproc.mk if [ `wc -c stderr.c <<'SHAR-EOF' X/* X@(#)File: $RCSfile: stderr.c,v $ X@(#)Version: $Revision: 6.21 $ X@(#)Last changed: $Date: 1998/04/07 19:09:04 $ X@(#)Purpose: Error reporting routines -- using stdio X@(#)Author: J Leffler X@(#)Copyright: (C) JLSS 1988-91,1996-98 X@(#)Product: :PRODUCT: X*/ X X/*TABSTOP=4*/ X/*LINTLIBRARY*/ X X#include X#include X#include X#include X#include X#include X#include X#include X#include "stderr.h" X Xstatic char arg0[15] = "**undefined**"; Xstatic FILE *errout = stderr; X X#ifndef lint Xstatic const char rcs[] = "@(#)$Id: stderr.c,v 6.21 1998/04/07 19:09:04 jleffler Exp $"; X#endif /* lint */ X X/* Change the definition of 'stderr', reporting on the old one too */ X/* NB: using err_stderr((FILE *)0) simply reports the current 'stderr' */ XFILE *err_stderr(FILE *newerr) X{ X FILE *old = errout; X if (newerr != (FILE *)0) X errout = newerr; X return(old); X} X Xconst char *getarg0(void) X{ X return(arg0); X} X Xvoid remark2(const char *s1, const char *s2) X{ X err_report(ERR_REM, ERR_STAT, "%s %s\n", (s1), (s2)); X} X Xvoid remark(const char *s1) X{ X err_report(ERR_REM, ERR_STAT, "%s\n", (s1)); X} X Xvoid error2(const char *s1, const char *s2) X{ X err_report(ERR_ERR, ERR_STAT, "%s %s\n", (s1), (s2)); X} X Xvoid error(const char *s1) X{ X err_report(ERR_ERR, ERR_STAT, "%s\n", (s1)); X} X Xvoid stop(const char *s1) X{ X err_report(ERR_ABT, ERR_STAT, "%s\n", (s1)); X} X Xvoid usage(const char *s1) X{ X err_report(ERR_USE, ERR_STAT, (s1)); X} X Xconst char *err_rcs_string(const char *s2, char *buffer, size_t buflen) X{ X const char *src = s2; X char *dst = buffer; X char *end = buffer + buflen - 1; X X /* X ** Bother RCS! We've probably been given something like: X ** "$Revision: 6.21 $ ($Date: 1998/04/07 19:09:04 $)" X ** We only want to emit the revision number and the date/time. X ** Skip the components between '$' and ': ', copy up to ' $', X ** repeating as necessary. And we have to test for overflow! X */ X while (*src != '\0' && dst < end) X { X while (*src != '\0' && *src != '$') X { X *dst++ = *src++; X if (dst >= end) X break; X } X if (*src == '$') X src++; X while (*src != '\0' && *src != ':' && *src != '$') X src++; X if (*src == '\0') X break; X if (*src == '$') X { X /* Unexpanded keyword '$Keyword$' notation */ X src++; X continue; X } X if (*src == ':') X src++; X if (*src == ' ') X src++; X while (*src != '\0' && *src != '$') X { X *dst++ = *src++; X if (dst >= end) X break; X } X if (*src == '$') X { X if (*(dst-1) == ' ') X dst--; X src++; X } X } X *dst = '\0'; X return(buffer); X} X X/* Report version information, removing embedded RCS keyword strings (but not values) */ Xvoid version(const char *s1, const char *s2) X{ X char buffer[64]; X X if (strchr(s2, '$')) X s2 = err_rcs_string(s2, buffer, sizeof(buffer)); X err_logmsg(stdout, ERR_ERR, EXIT_SUCCESS, "%s Version %s\n", s1, s2); X} X X/* Store basename of command, excluding trailing slashes */ X/* Doesn't handle two pathological cases -- "/" and "" */ Xvoid setarg0(const char *argv0) X{ X const char *cp; X size_t nbytes = sizeof(arg0) - 1; X X if ((cp = strrchr(argv0, '/')) != (char *)0 && *(cp + 1) == '\0') X { X /* Skip backwards over trailing slashes */ X const char *ep = cp; X while (ep > argv0 && *ep == '/') X ep--; X /* Skip backwards over non-slashes */ X cp = ep; X while (cp > argv0 && *cp != '/') X cp--; X cp++; X nbytes = ep - cp + 1; X if (nbytes > sizeof(arg0) - 1) X nbytes = sizeof(arg0) - 1; X } X else if (cp != (char *)0) X { X /* Regular pathname containing slashes */ X cp++; X } X else X { X /* Basename of file only */ X cp = argv0; X } X strncpy(arg0, cp, nbytes); X arg0[nbytes] = '\0'; X} X X/* Format a time string for now (using ISO8601 format) */ X/* Allow for future settable time format with tm_format */ Xstatic char *err_time(void) X{ X static char buffer[32]; X static const char tm_format[] = "%Y-%m-%d %H:%M:%S"; X time_t now; X struct tm *tp; X X now = time((time_t *)0); X tp = localtime(&now); X strftime(buffer, sizeof(buffer), tm_format, tp); X return(buffer); X} X X/* Most fundamental (and flexible) error message printing routine */ X/* Not singularly convenient for ordinary mortals -- see err_logmsg() */ Xvoid err_fprint(FILE *fp, int flags, int estat, const char *string, va_list args) X{ X int errnum = errno; /* Capture errno before it is damaged! */ X if (flags & ERR_FLUSH) X (void)fflush(stdout); X if (flags & ERR_USAGE) X (void)fprintf(fp, "Usage: %s %s\n", arg0, string); X else if (flags & ERR_COMM) X { X if ((flags & ERR_NOARG0) == 0) X (void)fprintf(fp, "%s: ", arg0); X if (flags & ERR_STAMP) X (void)fprintf(fp, "%s - ", err_time()); X if (flags & ERR_PID) X (void)fprintf(fp, "pid=%d: ", (int)getpid()); X (void)vfprintf(fp, string, args); X if (flags & ERR_ERRNO) X (void)fprintf(fp, "error (%d) %s\n", errnum, strerror(errnum)); X } X (void)fflush(fp); X if (flags & ERR_ABORT) X abort(); X if (flags & ERR_EXIT) X exit(estat); X} X X/* Most convenient external interface to err_fprint() */ Xvoid err_logmsg(FILE *fp, int flags, int estat, const char *string, ...) X{ X va_list args; X X va_start(args, string); X err_fprint(fp, flags, estat, string, args); X va_end(args); X} X X/* Cover function for err_fprint() using default output file (normally stderr) */ Xvoid err_print(int flags, int estat, const char *string, va_list args) X{ X err_fprint(errout, flags, estat, string, args); X} X Xvoid err_remark(const char *format, ...) X{ X va_list args; X X va_start(args, format); X err_print(ERR_REM, ERR_STAT, format, args); X va_end(args); X} X Xvoid err_error(const char *format, ...) X{ X va_list args; X X va_start(args, format); X err_print(ERR_ERR, ERR_STAT, format, args); X va_end(args); X} X Xvoid err_report(int flags, int estat, const char *string, ...) X{ X va_list args; X X va_start(args, string); X err_print(flags, estat, string, args); X va_end(args); X} X X#ifdef TEST X Xstatic const char *list[] = X{ X "/usr/fred/bloggs", X "/usr/fred/bloggs/", X "/usr/fred/bloggs////", X "bloggs", X "/.", X ".", X "/", X "//", X "///", X "////", X "", X (char *)0 X}; X Xint main(int argc, char **argv) X{ X const char **name; X char *data; X X setarg0(argv[0]); X X err_logmsg(stdout, ERR_LOG, EXIT_SUCCESS, "testing ERR_LOG\n"); X err_logmsg(stdout, ERR_STAMP|ERR_REM|ERR_FLUSH, EXIT_SUCCESS, X "testing ERR_STAMP\n"); X err_logmsg(stdout, ERR_PID|ERR_REM|ERR_FLUSH, EXIT_SUCCESS, X "testing ERR_PID\n"); X errno = EXDEV; X err_logmsg(stdout, ERR_ERRNO|ERR_REM|ERR_FLUSH, EXIT_SUCCESS, X "testing ERR_ERRNO\n"); X X remark("testing values for argv[0]"); X X for (name = list; *name != (char *)0; name++) X { X data = malloc(strlen(*name) + 1); X strcpy(data, *name); X printf("name = <<%s>>; ", *name); X setarg0(*name); X printf(" (<<%s>>) arg0 = <<%s>>\n", *name, getarg0()); X free(data); X } X X setarg0(argv[0]); X remark("reporting arguments to program"); X while (*++argv != (char *)0) X remark2("next argument", *argv); X X remark("reporting on version!"); X version("STDERR", "$Revision: 6.21 $ ($Date: 1998/04/07 19:09:04 $)"); X return(0); X} X X#endif /* TEST */ SHAR-EOF chmod 440 stderr.c if [ `wc -c stderr.h <<'SHAR-EOF' X/* X@(#)File: $RCSfile: stderr.h,v $ X@(#)Version: $Revision: 6.17 $ X@(#)Last changed: $Date: 1998/04/07 19:02:25 $ X@(#)Purpose: Header file for standard error functions X@(#)Author: J Leffler X@(#)Copyright: (C) JLSS 1989-93,1996-98 X@(#)Product: :PRODUCT: X*/ X X#ifndef STDERR_H X#define STDERR_H X X#ifdef MAIN_PROGRAM X#ifndef lint Xstatic const char stderr_h[] = "@(#)$Id: stderr.h,v 6.17 1998/04/07 19:02:25 jleffler Exp $"; X#endif X#endif X X#include X#include X X/* -- Definitions for error handling */ X X#define ERR_STAT (1) /* Default exit status */ X X#define ERR_COMM (0x0001) /* Print message on stderr */ X#define ERR_USAGE (0x0002) /* Print usage on stderr */ X#define ERR_EXIT (0x0004) /* Exit -- do not return */ X#define ERR_ABORT (0x0008) /* Abort -- do not return */ X#define ERR_FLUSH (0x0010) /* Flush stdout */ X#define ERR_STAMP (0x0020) /* Timestamp messages */ X#define ERR_NOARG0 (0x0040) /* Do not print arg0 prefix */ X#define ERR_PID (0x0080) /* Include pid=nnnnn info */ X#define ERR_ERRNO (0x0100) /* Include system error */ X X/* -- Standard combinations of flags */ X X#define ERR_USE (ERR_USAGE|ERR_EXIT|ERR_FLUSH) X#define ERR_REM (ERR_COMM|ERR_FLUSH) X#define ERR_ERR (ERR_COMM|ERR_EXIT|ERR_FLUSH) X#define ERR_ABT (ERR_COMM|ERR_ABORT|ERR_FLUSH) X#define ERR_LOG (ERR_STAMP|ERR_PID|ERR_COMM|ERR_FLUSH) X X/* -- Global definitions */ X Xextern const char *getarg0(void); Xextern void setarg0(const char *argv0); X Xextern FILE *err_stderr(FILE *fp); Xextern const char *err_rcs_string(const char *s, char *buffer, size_t buflen); X Xextern void err_error(const char *format, ...); Xextern void err_fprint(FILE *fp, int flags, int estat, const char *string, va_list args); Xextern void err_logmsg(FILE *fp, int flags, int estat, const char *string, ...); Xextern void err_print(int flags, int estat, const char *string, va_list args); Xextern void err_remark(const char *format, ...); Xextern void err_report(int flags, int estat, const char *string, ...); X Xextern void error(const char *s1); Xextern void error2(const char *s1, const char *s2); Xextern void remark(const char *s1); Xextern void remark2(const char *s1, const char *s2); Xextern void stop(const char *s1); Xextern void usage(const char *s1); Xextern void version(const char *s1, const char *s2); X X#endif /* STDERR_H */ SHAR-EOF chmod 440 stderr.h if [ `wc -c