qsql.pc

Oracle PRO*C dynamic SQL sample with C source.

                uid;
                if (sqlca.sqlcode == 0)
                        return;

                puts("Problem logging in, please try again. . .\n");
        }

        for ( ; ; )
        {
                printf("Username: ");
                fflush(stdout);
                gets(uid.arr);
                if (uid.arr[0] == '\0') return;
                uid.len = strlen(uid.arr);
                printf("Password: ");
                fflush(stdout);
                gets(psswd.arr);
                psswd.len = strlen(psswd.arr);

EXEC SQL CONNECT :
uid IDENTIFIED BY :
                psswd;

                if (sqlca.sqlcode == 0)
                {
                        puts("Successfully connected.\n");
                        break;
                }

                puts("Problem logging in, please try again...\n");
        }

        EXEC SQL WHENEVER SQLERROR STOP;
}

/* get Bind Variable Values and update vars */
/* Get user to supply values for each of the bind vars. */
/* Allocate a 10 byte string for each bind variable */
getBVVals(vars)
char **vars;    /* a ptr to a ptr  to storage for bind variables */
{
        int i;

        if (*vars == 0)
        {
                *vars = malloc((bdp->F) * 10);
                if (*vars == NULL)
                {               /* *** Problem allocating memory */
                        puts("MU Fail: *vars = malloc((bdp->F) * 10)");
                        exit(EX_FTL);
                }
        }

        /* Allocate a 10 byte string to hold the value that */
        /* the user gives for each bind variable. */

        printf("Please enter values for the bind variables:\n");
        for (i = 0; i < bdp->F; i++)
        {
                printf("%.*s: ", bdp->C[i], bdp->S[i]);
                bdp->V[i] = &((*vars)[i*10]);
                fflush(stdout);
                gets(bdp->V[i]);
                bdp->T[i] = 1;
                bdp->L[i] = strlen(bdp->V[i]);
                bdp->I[i] = 0;
        }
}

descSel()
/* describe the select list variables into sdp, expanding if */
/* necessary. Set desc. size to number of columns found. */
{
        EXEC SQL DESCRIBE SELECT LIST FOR S INTO sdp;
        if (sdp->F < 0)
        {
                sdSize = -(sdp->F);
                sqlclu(sdp);
                sdp = sqlald(sdSize, 10, 0);
                EXEC SQL DESCRIBE SELECT LIST FOR S INTO sdp;
        }
        sdp->N = sdp->F;
}

doFetches()
/* Print the column headers, do the fetches, and call print
   for each row found.  Print count found. */
{
        int cnt;
        char colname[100], col_h_fmt[100];
        int colnamel, cl;

        EXEC SQL WHENEVER NOT FOUND GOTO NOT_FND;

        putchar('\n');

        /* print a column name : left justify for char, rt-just for numbers */
        for (cnt = 0; cnt < sdp->N; cnt++)
        {
                /* colnamel=min(sdp->L[cnt], sdp->C[cnt]); */
                if (sdp->L[cnt] < sdp->C[cnt]) colnamel = sdp->L[cnt];
                else colnamel = sdp->C[cnt];

                cl=sdp->L[cnt];

                /* colnamel=min(colnamel, sizeof(colname-1)); */
                if (colnamel > sizeof(colname)-1) colnamel=(sizeof(colname)-1);

                memcpy(colname, sdp->S[cnt], colnamel);
                colname[colnamel]='\0';

                if (sdt[cnt] == 2)
                {       /* NUMBER: right justify */
                          printf("%*s ", cl, colname);
                }
                else
                {       /* NOT A NUMBER: left justify */
                          printf("%-*s ", cl, colname);
                }
        }
        putchar('\n');

        /* UNDERLINE the column names */
        for (cnt = 0; cnt < sdp->N; cnt++)
        {
                int i;

                for (i = 0; i < sdp->L[cnt]; i++)
                        putchar('-');
                putchar(' ');
        }

        putchar('\n');

        /* Acutally do the fetches and print the results */
        for (cnt = 0; ; cnt++)
        {
                EXEC SQL FETCH C USING DESCRIPTOR sdp;
                print(sdp);     /* print the results of the fetch */
        }

NOT_FND:

        printf("\n%u row(s) selected.\n", cnt);
        EXEC SQL WHENEVER NOT FOUND CONTINUE;
}

fillSelDesc()
/* Allocate storage for each of the select list vars, and
   allocate storage for an indicator variable for each column */
{
        int i;
        unsigned char prec;
        char scale;

        if (!sdt)
        {       /* haven't allocated sdt[] yet */
                sdt = (short *)malloc(sizeof(short) * sdp->N);
        }
        else if (sdtl < sdp->N)
        {       /* need to reallocate saved type array. */
                sdt = (short *)realloc(sdt, sizeof(short) * sdp->N);
        }

        sdtl = sdp->N;
        for (i = 0; i < sdp->N; i++)
        {
                /* save original types and clear possible NULL high order bit */
                sdp->T[i] = (sdp->T[i] & 0x7FFF);
                sdt[i] = sdp->T[i];
                if (sdp->T[i] == 2)
                {          /* Have a number: need to get precision and scale. */
                        prec = (unsigned char)(sdp->L[i] >> 8);
                        scale = (unsigned char)sdp->L[i];
                        if (prec == 0)
                        {       /* No Precision. User Default. */
                                prec = 10;
                        }
                        sdp->L[i] = prec;
                        if (scale < 0)
                        {       /* Has -scale. Need to add trailing zeros. */
                                sdp->L[i] += -scale;
                        }
                    sdp->L[i] += 2; /* +2 for possible sign and decimal point */
                }
                else if (sdp->T[i] == 12)
                {       /* have a DATE. Need to set default len for DD-MON-YY */
                        sdp->L[i] = 9;
                }

                /* Coerce to CHAR, with max len = 240 */
                sdp->T[i] = 1;
                /* sdp->L[i] = min(sdp->L[i], 240); */
                if (sdp->L[i] > 240) sdp->L[i]=240;
                sdp->V[i] = malloc(sdp->L[i]);
                sdp->I[i] = malloc(sizeof(short));
        }
}

freeSelVars()
{
        int i;
        for (i = 0; i < sdp->N; i++)
        {
                free(sdp->V[i]);
                free(sdp->I[i]);
        }
        sdp->N = 0;
}

setReExecPr()   {
        reExec = TRUE;
}
resetAutoPr()   {
        autoCom = FALSE;
        printf("No longer in AutoCommit mode\n");
}
setAutoPr()     {
        autoCom = TRUE;
        printf("Now in AutoCommit mode\n");
}
setDonePr()     {
        done = TRUE;
}
rollBkPr()      {
        rollBackWork();
        printf("Work Rolled Back\n");
}
commitPr()      {
        commitWork();
        printf("Work Committed\n");
}
helpPr()
{
        printf("\nMU V1.1 Help\n");
        printf("To execute a SQL statement, simply type the statement\n");
        printf("when given the prompt.  The statement may be entered\n");
        printf("on several lines and must be terminated by a ';'.  In\n");
        printf("fact, all commands must be terminated by a ';'. There\n");
        printf("are several pseudo commands which begin with a '/'.\n");
        printf("\nThe pseudo commands are:\n");
        printf("   /c[ommit]    Commit Work\n");
        printf("   /ro[llback]  Rollback Work\n");
        printf("   /ru[n]       Reexecute the last SQL statement\n");
        printf("   /e[xit]      Exit this program\n");
        printf("   /a[uto]      Enter AutoCommit Mode\n");
        printf("   /m[anual]    Exit AutoCommit Mode\n");
        printf("   /h[elp]      Get this information\n");
        printf("For more detailed information, read the code.\n");
}

struct comTabEntry {
        char *cmd;
        int (*proc)();
}
comTab[] =
{
        "c",    commitPr,
        "ro",   rollBkPr,
        "ru",   setReExecPr,
        "e",    setDonePr,
        "a",    setAutoPr,
        "m",    resetAutoPr,
        "h",    helpPr,
        "",     0};

procCmd(s)
char *s;
{
        int i;
        int (*proc)();

        if (strlen(s) == 0) return;
        for (i = 0; comTab[i].proc; i++)
        {
                if (isPrfx(comTab[i].cmd, s))
                {
                        proc = comTab[i].proc;
                        (*proc)();
                        return;
                }
        }
        printf("\nUnrecognized Command: %s\n", s);
}

isPrfx(prfx, s)
char *prfx, *s;
{
        int i, l;
        char cmd[32], prc[5];
        if ((l = strlen(prfx)) == 0) return(FALSE);
        strcpy(cmd, s);
        strcpy(prc, prfx);
        for (i = 0; i < l; i++)
        {
                if (toupper(prc[i]) != toupper(cmd[i]) )
                        return(FALSE);
        }
        return(TRUE);
}

rollBackWork()
{
        EXEC SQL ROLLBACK WORK;
}

commitWork()
{
        EXEC SQL COMMIT WORK;
}

reptError()
{
        printf("%.70s\n", sqlca.sqlerrm.sqlerrmc);
}