qsql.pc

Oracle PRO*C dynamic SQL sample with C source.

#include <stdio.h>
#include <stdlib.h>
#include <ctype.h>
#include <orastd.h>
#include <malloc.h>

/*
Name:           QSQL : Quick SQL processor
Function:       Simple program to demonstrate dynamic SQL statements
                with the Pro*C compiler.

To Build:       proc host=c iname=qsql
                qcl /W1 /AL /G2 /c qsql.c
                link qsql,/NOI,,+SQLMSC /SEG:256 /ST:8192;
*/


#undef T
#undef F

#define CR 13

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR uid[30];                /* user id */
VARCHAR psswd[30];              /* user password */
char *stmt;                     /* input SQL statement from user */
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;

SQLDA *bdp;                     /* a descriptor used for BIND vars */
SQLDA *sdp;                     /* a descriptor used for SELECT vars */
short *sdt = 0;                 /* arr of original DESCRIBE types */
int sdtl;                       /* # of entries in sdt[] */
char *vars = 0;                 /* area used to hold BIND vars */
int bdSize = 5;                 /* size of BIND variable descriptor */
int bvSize = 10;                /* max # of chars in BIND var name */
int sdSize = 5;                 /* size of SELECT variable descriptor */
int svSize = 80;                /* max # of chars in select list colnames */
int autoCom = FALSE;            /* perform automatic commits */
int done = FALSE;               /* done executing all statements */
int reExec;                     /* re-Execute last statement */

extern SQLDA *sqlald();         /* allocate descriptor */
VOID mufwrn();           /* issue SQLWARNING err msg */

main(argc, argv)
int argc;
char *argv[];
{
        int i;
        int firstTime;          /* 1st time statement executed */
        char answer[4];         /* string to hold answer to questions */

        printf("MU V1.1  Interactive SQL Statement Executor\n\n");
        printf("For help, type '/help;' at the prompt.\n");

        login(argc, argv);              /* login in the user to oracle */

        /* Allocate storage for the various descriptors and strings */
        /* which will be used, and get the first command string */

        stmt = malloc(256);
        if (stmt == NULL )      /* Problem allocating memory ? */
        {
                puts("MU Fail: stmt = malloc(256)");
                exit(EX_FTL);
        }

        bdp = sqlald(bdSize, bvSize, 10);

        if ( bdp == NULL )      /* Problem allocating memory ? */
        {
                puts("MU Fail: bdp = sqlald(bdSize, bvSize, 10)");
                exit(EX_FTL);
        }

        sdp = sqlald(sdSize, svSize, 0);      /* Allocating a new descriptor */

        if ( sdp == NULL )      /* Problem allocation memory ? */
        {
                puts("MU Fail: sdp = sqlald(sdSize, svSize, 10)");
                exit(EX_FTL);
        }

        sdp->N = 0;             /* init in case exit before describe */

        while (!getCom(stmt));

        /* begin MAIN loop */

        while (!done)
        {
                EXEC SQL WHENEVER SQLERROR GOTO CHECKERR;
                /* process any pseudo commands which the user enters */
                while (stmt[0] == '/')
                {
                        procCmd(&stmt[1]);      /* Process the pseudo command */
                        if (done) break;        /* Dont get another statement */
                        while (!getCom(stmt));  /* Get another command */
                }
                if (done) break;                /* exit loop and cleanup */

                EXEC SQL PREPARE S FROM : stmt; /* parse entered SQL stmt */

                if (sqlca.sqlwarn[0] == 'W' )
                        mufwrn();            /* issue SQLWARNING Err Msg */

               EXEC SQL DECLARE C CURSOR FOR S;        /* create the cursor C */

                /* describe the bind variables into the descriptor bdp */
                bdp->N = bdSize;
                descBind();

                firstTime = TRUE;
                /* make a note that this is the first time thru */
                /* so that if we re-execute we neednt reparse etc. */

                do
                /* this loop executes the statement above with different */
                /* values for the bind vars */
                {
                        if (bdp->F != 0) getBVVals(&vars);
                        EXEC SQL OPEN C USING DESCRIPTOR bdp;
                        if (firstTime)
                        {
                                firstTime = FALSE;
                                sdp->N = sdSize;
                                descSel();   /* describe the select list vars */
                                if (sdp->F != 0) fillSelDesc();
                        }
                        if (sdp->N != 0) doFetches();
                        else
                        {
                              printf("%u rows processed.\n", sqlca.sqlerrd[2]);
                              if (autoCom) commitWork();
                        }
                        reExec = FALSE;
                        for ( ; ; )
                                /* UNTIL cmd == SQL statement or exit or run */
                        {
                                while (!getCom(stmt));
                                if (stmt[0] != '/') break; /* SQL statement */
                                procCmd(&stmt[1]);
                                if (done || reExec) break; /* got exit or run */
                        }
                }
                while(reExec);

                EXEC SQL CLOSE C;
                if (sdp->N != 0) freeSelVars();
                if (vars != 0) {
                        free(vars);
                        vars = 0;
                }
                continue;

CHECKERR:
                reptError();
                sqlca.sqlcode = 0;
                printf("\nCare to continue? ");
                fflush(stdout);
                gets(answer);
                if (toupper(answer[0]) != 'Y')
                {       /* user does not wish to continue. Rollback and exit */
                        rollBkPr();
                        break;
                }
                /* user does want to continue */
                while (!getCom(stmt));
        }

        cleanUp();
        EXEC SQL COMMIT WORK RELEASE;
        return;

ERR_EXIT:
        reptError();
        cleanUp();
        EXEC SQL WHENEVER SQLERROR CONTINUE;
        EXEC SQL ROLLBACK WORK RELEASE;
        return;
        /************************
        * E N D   o f   M A I N *
        ************************/
}

EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL WHENEVER NOT FOUND CONTINUE;

/* Issue SQLWARNING message */
VOID mufwrn()
{
        if (sqlca.sqlwarn[1] == 'W')
                puts("SQLWARNING: Column was truncated.");
        else if (sqlca.sqlwarn[2] == 'W')
              puts("SQLWARNING: Null values in aggregate (MAX, SUM) function.");
        else if (sqlca.sqlwarn[3] == 'W')
                puts("SQLWARNING: INTO var count not equal column count.");
        else if (sqlca.sqlwarn[4] == 'W')
                puts("SQLWARNING: Update or Delete without Where clause.");
        else if (sqlca.sqlwarn[5] == 'W')
                puts("SQLWARNING: ???.");
        else if (sqlca.sqlwarn[6] == 'W')
                puts("SQLWARNING: Rollback required.");
        else if (sqlca.sqlwarn[7] == 'W')
             puts("SQLWARNING: Change after query start on Select For Update.");
}

/* release the malloc-ed variables */
cleanUp()
{
        if (sdp->N != 0) freeSelVars();
        if (vars != 0) {
                free(vars);
                vars = 0;
        }
        sqlclu(bdp);
        sqlclu(bdp);
}

/* Get command from user if it is continued get the additional lines */
getCom(s)
char *s;
{
        int i, l;
        char temp[256];

        printf("MU: ");
        fflush(stdout);
        gets(s);
        if ((l=strlen(s)) < 2) return(FALSE);
        if (s[l-1] == ';') {
                s[l-1] ='\0';
                return(TRUE);
        }
        for (i =2; ; i++)
        {
                printf("%*u: ", 2, i);
                fflush(stdout);
                gets(temp);
                strcat(s, " ");
                strcat(s, temp);
                if (s[(l=strlen(s)-1)] == ';') {
                        s[l] = '\0';
                        return(TRUE);
                }
        }
}

/* print one row of the select output */
print(dp)
SQLDA *dp;
{
        int i;
        int *vp;
        short *ip;

        for (i = 0; i < dp->N; i++)
        {
                ip = dp->I[i];
                if (*ip < 0)            /* if the value was null */
                {
                        char temp[80];
                        char awidth[80];
                        int radix = 10;
                        strcpy(temp, "%-");
                        itoa(dp->L[i], awidth, radix);
                        strcat(temp, awidth);
                        strcat(temp, "s ");
                        printf(temp, " ");
                }
                else
                {
                        char temp[80];
                        char awidth[80];
                        int radix = 10;
                        /*     printf("%-.10s ", dp->V[i]); */
                        strcpy(temp, "%-.");
                        itoa(dp->L[i], awidth, radix);
                        strcat(temp, awidth);
                        strcat(temp, "s ");
                        printf(temp, dp->V[i]);
                }
        }

        putchar('\n');
}

/* describe the bind variables into bdp descriptor */
descBind()
{
        EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bdp;
        if (bdp->F < 0)         /* descriptor not large enough */
        {
                bdSize = -(bdp->F);
                sqlclu(bdp);    /* get rid of current descriptor */
                bdp = sqlald(bdSize, 10, 0);    /* allocate larger */
                EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bdp;
        }
        bdp->N = bdp->F;
}

/* log user into oracle */
login(argc, argv)
int argc;
char *argv[];
{
        if ( argc == 2)
        {
                strcpy(uid.arr, argv[1]);
                uid.len = strlen(argv[1]);
                psswd.len = 0;
EXEC SQL CONNECT :