/* * sample2.pc * * This program connects to ORACLE, declares and opens a cursor, * fetches the names, salaries, and commissions of all * salespeople, displays the results, then closes the cursor. */ #include #include #include #include typedef char sqlstring[64]; EXEC SQL TYPE sqlstring IS STRING(64) REFERENCE; typedef struct student_struct{ sqlstring name; float grade; } Student; typedef struct { char *name; float grade; } InsStudent; InsStudent glinsarr[] = { {"Nathan Folkert", 66}, {"Stephen Lindholm", 68}, {"Vincent Chu", 69}, {"Ting-I Cheng", 69}, {"Thomas Edison", 22}, {"Isaac Newton", 19}, {"Albert Einstein", 24}, {"Jennifer Widom", 75}, {"Jeffrey Ullman", 75}, }; int glNumInserts = sizeof(glinsarr)/sizeof(glinsarr[0]); void sql_error(); int Initialize(void); int GetUserName(char *usernamebuf); int GetPassWord(char *passwordbuf); int FindStringFor(const char *var, char *val); int FindVar(const char *var); char glInput[1024]; int glLength; int Initialize(void) { char *value; int length, i; value = getenv("CONTENT_LENGTH"); if (!value) return 0; sscanf(value, "%d", &length); if (length >= 1024) return 0; for (i = 0; i < length; i++) { glInput[i] = getc(stdin); } glInput[length] = '\0'; glLength = length; #ifdef DEBUG printf("%s\n", glInput); #endif return 1; } int FindStringFor(const char *var, char *val) { int index, i, j; index = FindVar(var); if (index < 0) return 0; for (i = index, j = 0; i < glLength; i++, j++) { if (glInput[i] == '&') break; val[j] = glInput[i]; } val[j] = '\0'; #ifdef DEBUG printf("%s\n", val); #endif return 1; } int FindVar(const char *var) { int i, j; for (i = 0; i < glLength; i++) { for (j = 0; glInput[i + j] == var[j]; j++); if (j == strlen(var)) return i + j + 1; while (i < glLength && glInput[i] != '&') i++; } return -1; } int GetUserName(char *usernamebuf) { return FindStringFor("username", usernamebuf); } int GetPassWord(char *passwordbuf) { return FindStringFor("password", passwordbuf); } main() { struct student_struct *s; VARCHAR username[64]; VARCHAR password[64]; VARCHAR dynamicStmt[128]; VARCHAR localName[64]; float localGrade; int i; putenv("ORACLE_BASE=/usr/pubsw/apps/oracle"); putenv("ORACLE_HOME=/usr/pubsw/apps/oracle/8.1.7"); putenv("ORACLE_SID=SHR1_PRD"); putenv("ORACLE_DOC=/usr/pubsw/apps/oracledoc"); putenv("ORACLE_TERM=xsun5"); putenv("TNS_ADMIN=/usr/class/cs145/netadmin"); putenv("TWO_TASK=SHR1_PRD"); putenv("PATH=/usr/pubsw/apps/oracle/8.1.7/bin"); putenv("LD_LIBRARY_PATH=/usr/pubsw/apps/oracle/8.1.7/lib"); /* CGI HEADER */ printf("content-type: text/html\r\n\r\n"); /* HTML HEADER */ printf(" \n\n"); printf("Sample Database Interface\n"); printf("\n"); if (!Initialize()) exit(0); if (!GetUserName((char*)username.arr)) exit(0); username.len = strlen(username.arr); if (!GetPassWord((char*)password.arr)) exit(0); password.len = strlen(password.arr); EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--"); EXEC SQL CONNECT :username IDENTIFIED BY :password; s = (Student *) malloc (sizeof(Student)); if (!s) exit(1); /* HTML BODY */ printf("\n"); printf("

Sample Database Interface for %s

\n", username.arr); printf("

CS145 Students

\n"); EXEC SQL CREATE TABLE TMP145Student (name VARCHAR2(64), grade FLOAT); strcpy(dynamicStmt.arr, "INSERT INTO TMP145Student VALUES (:v1, :v2)"); dynamicStmt.len = strlen(dynamicStmt.arr); EXEC SQL PREPARE InsertStmt FROM :dynamicStmt; for (i = 0; i < glNumInserts; i++) { strcpy(localName.arr, glinsarr[i].name); localName.len = strlen(localName.arr); localGrade = glinsarr[i].grade; EXEC SQL EXECUTE InsertStmt USING :localName, :localGrade; } EXEC SQL COMMIT; EXEC SQL DECLARE badcs145students CURSOR FOR SELECT name, grade FROM TMP145Student WHERE grade < (SELECT AVG(grade) FROM TMP145Student); EXEC SQL OPEN badcs145students; printf("

Below Average Students

\n"); printf("
\n"); printf("\n"); EXEC SQL WHENEVER NOT FOUND DO break; while (1) { EXEC SQL FETCH badcs145students INTO :s; /* Be aware that the strings input by the user will be valid HTML -- you might want to take caution that they will not be interpreted as such by converting < and > to < and > respectively (and & to &) */ printf("\n", s->name, s->grade); } printf("

NAME

GRADE

%s%.2f
\n"); EXEC SQL CLOSE badcs145students; EXEC SQL COMMIT; EXEC SQL DECLARE cs145students CURSOR FOR SELECT name, grade FROM TMP145Student; EXEC SQL OPEN cs145students; printf("

All Students: Pick One or More to Query:

\n"); printf("
"); printf("\n\n"); printf("\n
\n
\n"); printf("Note: menu doesn't actually do anything.\n"); EXEC SQL CLOSE cs145students; EXEC SQL DROP TABLE TMP145Student; EXEC SQL COMMIT WORK RELEASE; printf("
\n"); printf("This document was written for Jennifer Widom's CS145 class\n"); printf(" in Spring, 2000, by Nathan Folkert.\n"); printf("\n\n"); exit(0); } void sql_error(msg) char *msg; { char err_msg[512]; size_t buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\n%s\n", msg); /* Call sqlglm() to get the complete text of the * error message. */ buf_len = sizeof (err_msg); sqlglm(err_msg, &buf_len, &msg_len); printf("%.*s\n", msg_len, err_msg); EXEC SQL ROLLBACK RELEASE; exit(0); }