Oracle CGI Demo

Interfacing Oracle Using C

listDept.pc is a C program that logs into Oracle and lists the department numbers and names. It is a normal C program using embedded SQL, the only difference is that it also outputs the appropriate html tags.

For example, in a C program printf is used to output data:

printf("Here are the list of departments\n");

When formatting this to be output on an html page, the html tags must also be included, the following outputs the same message in bold:

printf("<P><B>Here are the list of departments</B></P>")

After running the program through the Oracle Pro*C precompiler, the executable code is saved as listDept.cgi.

Note, for this to work the Oracle Pre-Compiler needs to be installed (not currenlty available on the Student server)

Click here to run the program.

listDept.pc
/*
* listDept.pc
* Based on sample3.pc from the Oracle demo programs
*
* This program connects to ORACLE, declares and opens a cursor,
* fetches in batches using arrays, and prints the results using
* the function printDept().
*/

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>

#define NAME_LENGTH 15
#define ARRAY_LENGTH 5

/* Another way to connect. */
char *username = "yourOracleUsername";
char *password = "yourOraclePassword";

/* Declare a host structure tag. */
struct {

int dept_number[ARRAY_LENGTH];
char dept_name[ARRAY_LENGTH][NAME_LENGTH];
char location[ARRAY_LENGTH][NAME_LENGTH-1];

} dept_rec;

void sql_error(msg)
char *msg; {

EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s", msg);
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

EXEC SQL ROLLBACK WORK RELEASE;
exit(EXIT_FAILURE);
}

void exitSQL() {

printf("<h3>Au revoir!</h3>");
/* Disconnect from the database. */
EXEC SQL COMMIT WORK RELEASE;

}

void printDept(n)
int n; {

int i=0;
for(i=0; i<n; i++) {

printf("<tr>");
printf("<td>%d</td>", dept_rec.dept_number[i]);
printf("<td>%s</td>", dept_rec.dept_name[i]);
printf("<td>%s</td>", dept_rec.location[i]);
printf("</tr>");
}

}

void printHeader() {

printf("Content-type: text/html\n\n");
printf("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\"\n");
printf("\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\n");
printf("<html xmlns=\"http://www.w3.org/1999/xhtml\">\n");
printf("<head><title>Oracle Department List</title>\n");
printf("<link href=\"MGCode.css\" rel=\"stylesheet\" type=\"text/css\"/>\n");
printf("</head>\n");
printf("<body>\n");
printf("<h2>Accessing Oracle from a C program</h2>");
printf("<h3>List of Departments</h3>");
}

void printTableFooter() {

printf("</tbody>");
printf("</table>");
}

void printTableHeader() {

printf("<table>");
printf("<thead>");
printf("<tr><th>Department Number</th>");
printf("<th><B>Department Name</th>");
printf("<th>Location</th>");
printf("</tr></thead>");
printf("<tbody>");
}

void setPath() {

/* Set the environment variables so that the web server can find the database. */

putenv("TWO_TASK=<your_local_TWO_TASK_value>");
putenv("LD_LIBRARY_PATH=<your_local_LD_LIBRARY_PATH_setting>");
putenv("PATH=<your_local_PATH_setting>");
putenv("ORACLE_SID=<your_local_ORACLE_SID_setting>");
putenv("ORACLE_HOME=<your_local_ORACLE_HOME_setting>");
}

void printFooter() {

printf("</table>");
printf("</body>");
printf("</html>");

}

void main() {

int num_ret; /* number of rows returned */
printHeader();
setPath();

EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:");

/* Connect to ORACLE. */
EXEC SQL CONNECT :username IDENTIFIED BY :password;

EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:");

/* Declare a cursor for the FETCH. */
EXEC SQL DECLARE c1 CURSOR FOR
SELECT deptno, dname, loc FROM Dept ORDER BY deptno;

EXEC SQL OPEN c1;

/* Initialize the number of rows. */
num_ret = 0;

/* Array fetch loop - ends when NOT FOUND becomes true. */
EXEC SQL WHENEVER NOT FOUND DO break;

printTableHeader();
for (;;) {
EXEC SQL FETCH c1 INTO :dept_rec;

/* Print however many rows were returned. */
printDept(sqlca.sqlerrd[2] - num_ret);
num_ret = sqlca.sqlerrd[2]; /* Reset the number. */
}

/* Print remaining rows from last fetch, if any. */
if ((sqlca.sqlerrd[2] - num_ret) > 0)
printDept(sqlca.sqlerrd[2] - num_ret);

EXEC SQL CLOSE c1;
printTableFooter();
printFooter();
exitSQL();
exit(EXIT_SUCCESS);

} /* main */